Fun MySQL fact of the day: the IO thread

On Friday, we looked at a pretty fun and very handy use of the MySQL binary log to perform point-in-time recovery during a make-believe disaster. We're going to circle back to point-in-time recovery a bit later this week, but I want to first start looking at MySQL's replication implementation.

As mentioned last Wednesday, MySQL uses the binary log to facilitate replication and whenever I need to remember how it works, I think of the cartoon, Chester & Spike. But now, you're probably thinking, "what does a cartoon about 2 dogs and an occasional cat have to do with MySQL"? Well, if you haven't seen Chester & Spike, imagine a gruff bulldog, Spike, our "master" database, and a hyper, eager terrier, Chester, our "replica" database, hanging out. Better yet, this is best visualized with a video clip.

Just like in the cartoon, when a replica, Chester, connects to a master database, Spike, Chester eagerly asks, "you have any bin logs, Spike? Do ya? Huh? Huh Spike? Do you have any bin logs?" And, if Spike has binary logs, Spike will send them to Chester. Otherwise, smack. From here, once Chester fully receives all the binary logs from Spike, he saves them locally as his own "relay logs" in his own private stash. Then, Chester asks Spike again, "hey, Spike, you have any bin logs? Do ya? Huh Spike? Do ya?". And on. And on. And on.

Now, to be clear, there's nothing special about relay logs: relay logs are just downloaded binary logs from another database, and, other than being incremental, they're exactly the same: you can still use mysqlbinlog to examine them, and you can still perform point-in-time recovery with them if you so choose. Of course, it's now time to ruin our fun comparison: in MySQL, "Chester" is actually called the "IO Thread", which is tasked with continuously asking for and downloading relay logs from a master database. The IO Thread will continue this cycle until it is either stopped (STOP SLAVE IO_THREAD) or the replica database runs out of disk space---whichever comes first.