0

If you are working on a development server but also a live production server what is the best way to migrate any DB changes that have happened on the development server to the live server.

My base is PHP and CI

Jess McKenzie
  • 8,345
  • 27
  • 100
  • 170
  • Create upgrade scripts for any DB changes you make, then run them all on the live server. – Sumner Evans Feb 27 '14 at 21:40
  • Have a look at http://stackoverflow.com/questions/225772/compare-two-mysql-databases. There are some tools to automate synchronizing DB schema between two environments. – qux Feb 27 '14 at 21:45
  • http://stackoverflow.com/questions/8418814/db-migration-tool-liquibase-or-flyway/ – Mark O'Connor Feb 28 '14 at 01:06

1 Answers1

0

Migrations and migration management is a huge and complex issue for DB-Code version change management. No short Post could adequetly address it, but here I go...

Typically if you use an ORM like Eloquent or framework they have a migration mechanism where if the schema and model code is updated, you run the migration on the development db. Then when you push that code live, you run it on the live server.

As noted in a comment to your post, a number of third party tools exist, but for most basic situations you could use simple migration files to manage changes. Tools are useful however if you make non-code related changes to DBs, like index alterations or data addition/deletion. Here's MySQL's own tool: http://www.mysql.com/products/workbench/migrate/

Now I'm assuming you're using some kind of version control, so if you're not an orm with build in migration support tools, you could simply keep a directory of sql migration scripts for a give commit level of code manually. When you merge your develop branch code to production branch and push live, run the migration alter table scripts on production DB. These scripts should have all your alters and manual inserts. You can then keep a migration table and keep track of the migration scripts you've run.

For bonus points, you can create a rollback version of the migration. This way you can apply before going back to a prior code version (for when you might need to roll back a production release to deal with an error or something). Then never commit a migration without a partner roll back.

For example if I add a new property to the User class, address2:

migration_1_add_address_2_to_user.sql;

 alter table user add column address2 varchar(64);

rollback_1_add_address_2_to_user.sql

   alter table user drop column address2;

Good Luck!

Ray
  • 40,256
  • 21
  • 101
  • 138