CODEWITHSUNDEEP
deploymentyiimysqlweb-deployment
b20000
b20000

Reputation: 1005

test and production server deployment with yii framework - syncing DB changes

I am working on a Yii framework based app where I have to test the app on my local machine and then when ready move the changes to the production server.

the app will be developed as people are using it and ask for new features. So when I make changes to my DB schema on the test machine I have to apply these to the schema of the production DB without destroying data there.

is there a recommended and convenient way to deal with this? syncing source code is less of an issue, i am using svn and can do svn export ; rsync ...

Upvotes: 1

Views: 1394

Answers (4)

hobs
hobs

Reputation: 19259

For a similar project we

  1. use MySQLWorkbench (MWB) to design and edit the schema
  2. share the .mwb file through a VCS.
  3. When one of us is comfortable with a change he uses mysqldump --complete-insert... on the production and test schemas to generate a copy of the existing test and production data with field names
  4. pull out all the production server insert statements in (3) and put them in protected/data/insert.sql
  5. use the "forward engineer" menu item in MWB on the modified model to generate sql to save to a file called protected/data/create.sql, hand-editing as appropriate (be sure to use the if exists clause to reduce errors)
  6. write a drop.sql file based on drop statements in (3)
  7. use MWB, run the sql (drop.sql, create.sql, insert.sql) after issuing the appropriate "use database" command that identifies the production database
  8. deal with all the errors in (7) by getting rid of any invalid inserts due to columns/fields that are not needed in the new models/schema. Rerun (7)
  9. deal with new fields in (7) that need data other than Null. Rerun (7)

Now you have a snapshot of your schema (drop.sql create.sql) and your data that should revive either your test or production server if you ever have a problem. And you have a "fixture" of the data that was in your production server (insert.sql) that can be used to bring your test server up to speed, or as a backup of the production server data (that will quickly be outdated). Obviously all the foreign key relationships are what are really painful, so it's rare that the insert.sql is useful for anything except upgrading the schema and restoring the production data after this change. Clearly it takes some time to work out the kinks in the process so that the delay between (3) and (9) is small enough that the production server users don't notice the downtime.

Clearly "Rerun (7)" gets repetitive and quickly turns into a shell script that calls mysql directly. Also other steps in the sql editing process become sed scripts.

Upvotes: 1

ldg
ldg

Reputation: 9402

MySQLWorkbench can be helpful for syncing db schema as well as other database design tasks.

Yii does support Migrations (since v1.1.6), although it can be more trouble than it's worth depending on how often you make changes and how collaborative your project is.

Another approach I've used is to keep a log of update statements in svn and basically handled the migrations manually.

The best approach is going to depend on the cost/benefits to your particular project/workflow.

Upvotes: 2

Devart
Devart

Reputation: 121922

Have a look at schema comparison tool in dbForge Studio for MySQL. This tool will help you to compare and synchronize two databases or a database project with specified database.

Also there is separate tool - dbForge Schema Compare for MySQL.

Upvotes: 0

Ashwin A
Ashwin A

Reputation: 3857

You can try SQLyog's Schema Synchronization Tool, which is a visual comparison/synchronization tool designed for developers who work between different MySQL servers or need to keep databases between two MySQL servers in sync. This means reporting the differences between tables, indexes, columns and routines of two databases, and generating scripts to bring them in Sync. Only the Schema will be synced in the target.

Upvotes: 1

Related Questions