Easiest way to sync a dev MySQL db to a production MySQL db?

Question

We have a staging server with MySQL installed locally. We plan on running Expression Engine, which requires a database. On the production side, we have two apache servers that are load balanced, so we'll need to install a mysql server on dedicated hardware and have the two web servers read from it. Now, since most of our work is done on the staging server (add,edit,deleting articles), I need to figure out how to sync the changes to production. No changes will be done on the production side.

I thought about a master/slave setup, but we don't want to "publish" our changes in real-time; only until management gives us the go ahead.

Answer 1

A master/slave setup could work if you ran "stop slave" on the production box. That would stop all syncing between the two boxes. You would then only need to start the slave whenever management give the go ahead to sync the two up. Just don't forget to run "stop slave" on it when you are done.

I'm assuming that shutting down production is not an option, but if it were able to, you have other options. One option is to shutdown both mysql servers and copy the files from staging to production. That is the easiest way to guarantee that both servers are completely in sync.

Answer 2

As long as you are only doing INSERTs, UPDATEs, and DELETEs of articles I have suggestion:

You should use Master/Slave Replication (MSR). Here is how to work it:

Step 1) Setup MSR with Dev Machine as Master and Prod as Slave

Step 2) Run SET GLOBAL SQL_LOG_BIN = 0;

This will prevent the Dev Machine from Replicating to Production. Each time you restart the Dev machine or restart mysql on the dev machine, make sure you run this before allowing the Prod machine to replicate from Dev. In fact make sure you STOP SLAVE on Prod, restart mysql on Dev, run SET GLOBAL SQL_LOG_BIN = 0; on Dev, and and START SLAVE; on Prod.

Step 3) Here is where it gets really interesting

You need to perform a table synchronizaton using two tools from Percona:
mk-table-checksum and mk-table-sync.

In a shell script, run mk-table-checksum and generate a report of all tables and the checksum values between the master and slave. Locate which tables have table checksum mismatches. The report will have the database in column 2, the table in column 4, and the checksum value in column 7.

Collect those tablenames in a text file as "database.tablename". We will call the file TablesToSync.txt

In another shell script, iterate through TablesToSync.txt and run mk-table-sync with the following parameters:

mk-table-sync --print --with-triggers --sync-to-master h=HostIPofSlave,u=userid,p=password,D=database,t=tablename >> AllChanges.sql

The file AllChanges.sql will contain every SQL statement you need to run to make the slave match the master's content as of the time you ran mk-table-sync against the specified table.

4) Run the AllChanges.sql script in Prod.

CAVEAT

If you knew that a fixed number tables would need syncing, an alternative method would be to skip doing the mk-table-checksum step and go right to running mk-table-sync against every table you know needs to be sync'd.

CONCLUSION

You would have to run this process on demand whenever you want to ship new data from Dev to Prod. Once you change any table structures, (i.e. DDL), those tables need to be mysqldumped over from Dev to Prod.

Answer 3

While these ideas would work, I don't like the idea of pushing up from dev. A slave copy of your prod db is going to be wonderful for backup and recovery purposes. If you want to test queries and wait for approval before deploying into production, make it policy to run the queries on the prod machine directly. Develop and test on your dev system, then when the work is satisfactory, your devs can submit the queries to the proper personal to be run on production. This also provides a separation of duties which can be beneficial in some environments that are under the influence of a regulatory body.

If you disable the slave and enable it periodically, you will have bad data in prod if even only for a brief period. Say you run an alter or update that doesn't have the desired consequences. You then undo it, and run another query that works later. When these are replayed on production, it will have the same effect there.

The maatkit tools may be beneficial in helping sync them if you use that approach, but I encourage you to think about it the other way around. It's going to be safer and wiser to manually sync upstream.

I don't know how your application works either. Will users be generating data in prod via normal use of your site/app? If so, you shouldn't be syncing up to it, it should be syncing down somewhere else unless you have a dual master setup with auto id offset on each. Even then, I still cringe thinking about data becoming munged even with a transactional engine.

Answer 4

It will depend somewhat on the size of your database(s) whether this would be viable but I prefer to go back to basics and do a dump and load. i.e. Dump from the source and load it into the target. Why look for unnecessary complications?