On my local machine, I maintain several InnoDB tables which are mirror replica of the ones on my web server. Whenever I have new or updated data, I perform that on the local tables. For the updates, it's typically to only a few fields of some tables. Doing locally helps me to perform processing and validation of the data before staging. Finally, I want to move these differential data to the remote server.
How can I best achieve this whole process of data migration. There are a few stages where I am looking for your inputs/experience:
- Commands to identify and fetch the differential data from all the tables,
- On the remote site, commands to update the respective tables with this data,
- How can I ensure atomicity of the updates to not break the integrity of the
webpage contents created with it.
I am planning to automate this whole process. In future, there can be 1000s of such updates and hence I would like to have this time and size optimized.
I use python and plan to use fabric to run the commands.
I'd try to use the binary log to record changes to the local database. Then you just need to convert the binary log to an SQL script with the mysqlbinlog tool, and apply that to your web site. Sort of the same process as performing point-in-time recovery.
Use FLUSH LOGS (or mysqladmin flush-logs) on the local system when you're done converting the local binlogs to SQL. That way you know exactly where you left off, and you can start there next time you upload changes.
I'm not sure about applying the changes on the web server in a truly atomic way. You'd have to use a transaction to do that. You could sandwich the SQL script in between START TRANSACTION and COMMIT, but I'd want to make sure the binlog itself doesn't contain transaction statements.
Another tool to compare two databases and figure out the minimal set of changes is Percona Toolkit's pt-table-sync. But that tool is usually meant to make the databases identical. You likely have production data on your web server that isn't in the local developer instance, so you may need to filter to specific tables or maybe pt-table-sync has an "insert but don't delete" option or something. I encourage you to read the doc carefully and experiment on test instances.
