MySQL Replication Failures

Over the weekend, I worked on a client’s two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:

a) how this never should have happened in the first place.

b) how “slave drift” could be kept from happening.

c) how this is probably keeping some businesses from using MySQL.

d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.

I’ll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line “CHANGE MASTER” command to set up a new slave. Even so, it makes sense.

It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log into the slave server and mess the data up that way. This last is a database administrator problem, but it affects replication. There are probably other issues that astute readers will point out.

I would like to point out one common issue that would probably be categorized as a replication bug. If the master crashes for whatever reason (say, a hosting company accidentally punches the power button on a master server) it will often cause corruption of the binary log. When the master comes back up, the slave cries about a non-existent binary log position.

Possible solutions:

• Checksums on the replicated data stream. A simple CRC checksum would work, and if the checksum doesn’t match, the data should be resent, and the error logged. If the checksum continues to fail on retries, the slave should stop so the database does not become corrupted.
• Internal checksums on the tables, and configurable periodic comparisons of the checksums on master and slave(s), with the ability to have the servers resolve issues if needed. Interestingly, Sheeri pointed out to me that these are available on MyISAM tables. I didn’t know that, but it is only part of the answer, anyway.
• Moving to row-based replication will resolve issues of non-deterministic function. This is one area that MySQL for which has a fix in place: the soon-to-be-GA 5.1 release allows you to do row-based instead of statement-based replication.

To me, as a DBA, these replication issues are very important. I would say that secure, “fail-safe” replication ranks right under being able to do proper backups and restore (which should be number one in every DBA’s book).

I put fail-safe in quotes as I realize that true fail-safe replication isn’t really possible.  But, when a problem occurs the server should alert the DBA through error logging. Currently, the server often doesn’t even realize when there is a problem. If you are lucky, you find out only because replication gags on something. If you aren’t lucky, you don’t know there is a problem; the master server dies and the slave is promoted to master. Then you find out when someone stumbles across the errant data.

One of the reasons why MySQL is looking to move to row-based replication in server 5.1 is that this move should fix the problem of non-deterministic functions causing slave drift.  But, it will be quite some time before issues with row-based replication are resolved and this feature is extensively used in production. In addition, this fixes only one part of the problem.

How can MySQL’s replication issues be fixed? Can we depend on Sun/MySQL to resolve these problems? Ummm . . .  no.  I’m afraid that, for whatever reason, this isn’t very big on their radar. The only realistic way to resolve this problem is for someone, or some group, to develop a patch for the server that provides some or all of this functionality. (I would attempt this myself, but the MySQL server code is so far above my skill level it isn’t even funny. The only thing I can volunteer to do is coordinate things. Probably not very useful.)

I am sure there are more and possibly better solutions to these problems. I would love to hear from the readers about what they think. It would be cool for a company to step forward and sponsor work on this.

In the meantime—if you do not wish to lose all your hair or have it turn grey—when you are setting up a server for MySQL on Linux you should absolutely use LVM. If you are using Solaris, you should use ZFS as your filesystem. Although I don’t have time to go into this, you can create point-in-time partition snapshots with these, and much more easily re-create a slave if it does get out of sync. If you are using Windows or some other system, I don’t know what to tell you.

Note: I would like to thank Baron Schwartz for being the first person, to my knowledge, to spend time showing how replication can get out of sync and demonstrating how fragile it can be.  Here’s one of his blog posts on the subject. Complaints have been around about this for years, but Baron spent time and effort to demonstrate how it happens and to provide tools for potential fixes. His “mk-table-sync” utility is a good tool, although I find that it is sometimes difficult to get working. More important is that it is a “fix-it-after-the-fact” tool, not a prevention of the problem. Ever heard the saying, an ounce of prevention is worth a pound of cure?