Use Replication for backups? Are you schemas consistent?

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.

Tags: , ,

4 Responses to “Use Replication for backups? Are you schemas consistent?”

  1. Sean Hull says:

    Excellent article Ronald. I wasn’t aware of this tool. Will definitely try it out myself.

  2. 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.

  3. [...] Use Replication for backups? Are you schemas consistent? Read the perspective of Ronald Bradford. [...]

  4. Cédric says:

    You can also use mk-table-checksum from MAATKIT to analyze schema and data