Fun MySQL fact of the day: the SQL thread

Yesterday, we looked at how a MySQL replica's IO thread downloads a master's binary logs and stores them as its own "relay logs". We also said this happens until the IO thread is stopped or the replica database runs out of disk space, and while that's true, there's another mechanism involved in replication to help prevent us from running out of disk space. This mechanism is called the "SQL Thread".

The SQL thread is the sole consumer of relay logs, and when a relay log is created, the SQL thread will read them and run them against the database. This happens in nearly the same way that we, ourselves, applied binary logs to a database in last Friday's Fun MySQL Fact Of The Day, but with one exception. When we applied binary logs to our database manually, MySQL didn't know or care from where the changes came. The SQL thread, however, does care: it maintains a record of which logs have been applied, and once a log is fully read, MySQL will delete the relay log file. As such, provided that the SQL thread is running (that is, nobody has run STOP SLAVE SQL_THREAD and the SQL thread hasn't crashed), we have a pretty good certainty that the IO thread will not cause an out-of-disk problem.

As with almost any Fun MySQL Fact Of The Day, there are a lot more details we are ignoring or deferring, but for now, we should have an accurate-enough mental model of how MySQL replication works. We'll continue building upon this story over the next several days and into next week.