Read the original article at Bulletproofing MySQL replication with checksums

Your MySQL replications running well? You might not even know if they aren’t. One of the scariest things about MySQL replication is that it can drift out of sync with the master “silently”. No errors, no warnings.

MySQL’s replication solution evolved as a statement based technology. Instead of sending actual block changes, MySQL just has to log committed transactions, and reapply those on the slave side. This affords a wonderful array of topologies and different uses, but

In October 2008, Baron posted How to Check MySQL Replication Integrity Continually. Here at Pythian we have developed a method based on that post, and added “verifying that masters and slaves are in sync” to our standard battery of tests.

We call it “Continual replication sync checking”. This article will explain how it works, how to test and make the procedure non-blocking, benchmarks from the real world, issues we encountered along the way, and finally

One of the most common patterns I see in my consulting work is identifiers that are generated by MD5() or UUID(). Many times this is done in an application framework or something similar — not software the client has written. From the application programmer’s point of view, it’s just an incredibly handy idiom: generate a unique value and use it, you’re done.

Those values tend to appear in session identifiers, but that’s not the only place; I especially notice them in apps that use Java’s Hibernate interfaces, whether session IDs are involved or not. They propagate themselves all around the other tables, where they become secondary indexes and even get combined with other columns to make even bigger keys.

What’s wrong with this? There are two major things that hurt performance in such

This is the last day I'm taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here's what I have done so far. (Click through to the full article to read the details).

Maatkit (formerly MySQL Toolkit) version 1297 contains a significant update to MySQL Table Checksum (which will be renamed soon to avoid trademark violations). The changelog follows. What you don't see in the changelog is the unit test suite! I got a lot more of the code into modules that are tested and re-usable.

2007-11-18: version 1.1.19 

* Check for needed privileges on --replicate table before beginning. 
* Made some error messages more informative. 
* Fixed child process exit status with 8-bit right-shift. 
* Improved checksumming code auto-detects best algorithm and function. 
* Added --ignoreengine option; ignores federated and merge by default. 
* Added --columns and --checksum options. 
* Removed --chunkcol, --chunksize-exact, --index options. 
* --chunksize can be specified as a data size now. 
* Improved chunking algorithm

There is no "first" or "last" aggregate function in SQL. Sometimes you can use MIN() or MAX(), but often that won't work either. There are a couple of ways to solve this vexing non-relational problem.

First, let's be clear: I am posing a very non-relational problem. This is not about the minimum, maximum, top, most, least or any other relationally valid extreme in the group. It's the first or last, in whatever order the rows happen to come. And we all know rows aren't ordered -- in theory. But in practice they are, and sometimes you need the first or last row in a group. This article shows how.

I've just released changes to two of the tools in MySQL Toolkit. MySQL Table Checksum got some convenient functionality to help you recursively check slaves for bad replicated checksum chunks. MySQL Archiver got statistics-gathering functionality to help you optimize your archiving and purging jobs, plus a few important bug fixes.

MySQL Toolkit distribution 620 updates documentation and test suites, includes some major bug fixes and functionality changes, and adds one new tool to the toolkit. This article is mostly a changelog, with some added notes.

Many of the tools have matured and I just needed to make the documentation top-notch, but there's still a lot to be done on the crucial checksumming and syncing tools. Time is in short supply for me right now, though. In fact, I actually finished this release on June 22, but wasn't able to release it till just tonight!

How good are your SQL and/or general coding skills? I have a specific challenge I'd like your help solving. I am not sure it's possible, but I'd love to be proven wrong.