Many people have a master/slave MySQL environment of various different topologies, and many use the slave as a backup.
Is your slave schema identical to your production schema? As long as an SQL statements completes without an error, your slave schema can differ. Common examples are different indexes or storage engines for example. However if you use the slave as backup, you want to ensure when you recover, you are recovering a production environment, not a slave environment.
While the changes may be small, the can lead to different results. For a client I found that the default value of a price field was 10.00 in one schema and 0.00 in another. Not withstanding using defaults for important fields and not defining in an INSERT is a different issue, it highlighted the different schemas can easily exist.
My tool of choice is SchemaSync. The reason why I like this command is that it provides migration scripts to upgrade or downgrade your schemas.
Sean Hull says
Excellent article Ronald. I wasn’t aware of this tool. Will definitely try it out myself.
Daniël van Eeden says
SchemaSync will only sync the schema and not the data, so it might be a good idea to check and sync the data with mk-table-checksum/mk-table-sync.
Cédric says
You can also use mk-table-checksum from MAATKIT to analyze schema and data