CDR CDR - 9 months ago 55
MySQL Question

Synchronize two databases schema in MySQL

I was looking for a portable script or command line program that can synchronize two MySQL databases schema. I am not looking for a GUI based solution because that can't be automated or run with the buid/deployment tool.

Basically what it should do is scan database1 and database2. Check the schema difference (tables and indexes) and propose a bunch of SQL statements to run on one so that it gets the similiar structure of the other minimizing data damage as much as possible.

If someone can indicate a PHP, Python or Ruby package where this type of solution is implemented, I can try to copy the code from there.

A lot of MySQL GUI tools probably can do this, but I am looking for a scriptable solution.

Edit: Sorry for not being more clear: What I am looking for is synchronization in table structure while keeping data intact as far as possible. Not data replication.

More info:

Why replication won't work.

  1. The installation bases are spread around the state.

  2. We want the installer to perform dynamic fixes on the DB based on chagnes made in the latest version, regardless of what older version the end user might be using.

  3. Changes are mostly like adding new column to a tables, creating new indexes, or dropping indexes, adding tables or dropping tables used by the system internally (we don't drop user data table).

If it's a GUI: No it can't be used. We don't want to bunddle a 20MB app with our installer just for DB diff. Specially when the original installer is less than 1 MB.

csl csl
Answer Source

Have you considered using MySQL replication ?