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.

Tagged with: Databases MySQL

Related Posts

Higher Availability (HA) starts with two database servers

Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL.

Read more

Common MySQL Scalability Mistakes

This week I was one of the presenters at the first Surge Scalability Conference in Baltimore. An event that focused not just on one technology but on what essential tools, technologies and practices system architects need to know about for successfully scaling web applications.

Read more

MySQL Replication 102

One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.

Read more