|< Day Day Up >|
7.3 Under the Hood
What really happens during replication? What does the binary log contain? What's different in Version 4.0? To help answer those questions, let's get deeper into the details and then walk through the steps that MySQL performs during replication. We'll start with an insert on the master and follow it to completion on the slave. We'll also look at how MySQL 3.23 and 4.x differ.
7.3.1 Replication in 3.23
MySQL's original replication code provides basic replication services. The master logs all write queries to the binary log. The slave reads and executes the queries from the master's binary log. If the two are ever disconnected, the slave attempts to reconnect to the master.
If you follow a query from start to finish, here's what's happening behind the scenes:
That's a relatively simple arrangement. The master simply logs any queries that change data. The slave reads those queries from the master, one by one, and executes each of them. If there are any discrepancies between the results on the master and the slave, the slave stops replicating, logs an error, and waits for human intervention.
The simplicity of this system has problems, however. If the master and slave are separated by a slow network, the speed at which replication can occur becomes bounded by the network latency. Why? Because the process is highly serialized. The slave runs in a simple "fetch query, execute query, fetch query, ..." loop. If the "fetch query" half of the loop takes more than a trivial amount of time, the slave may not be able to keep up with the master during very heavy workloads. The master may be able to execute and log 800 queries per second, but if the slave requires 25 msec to fetch each query over the network, it can replicate no more than 40 queries per second.
This can be problematic even with a fast network connection. Suppose the master executes a query that takes five minutes to complete. Maybe it's an UPDATE that affects 50 million records. During the five minutes the slave spends running the same query, it isn't pulling new queries from the master. By the time it completes the query, it's effectively five minutes behind the master, in terms of replication. It has a fair bit of catching up to do. If the master fails during that five-minute window, there's simply no way for the slave to catch up until the master reappears. Some of these problems are solved in 4.0.
7.3.2 Replication in 4.0
To solve the problem of slaves falling behind because of slow queries or slow networks, the replication code was reworked for Version 4.0. Instead of a single thread on the slave that runs in a "fetch, execute, fetch, ..." loop, there are two replication threads: the IO thread and the SQL thread.
These two threads divide the work in an effort to make sure the slave can always be as up to date as possible. The IO thread is concerned only with replicating queries from the master's binary log. Rather than execute them, it records them into the slave's relay log. The SQL thread reads queries from the local relay log and executes them.
To put this in context, let's look at the step-by-step breakdown for replication in MySQL 4.0:
While the steps are presented as a serial list, it's important to realize that Steps 4-5 and 6-9 are running as separate threads and are mostly independent of each other. The IO thread never waits for the SQL thread; it copies queries from the master's binary log as fast as possible, which helps ensure that the slave can bring itself up to date even if the master goes down. The SQL thread waits for the IO thread only after it has reached the end of the relay log. Otherwise it is working as fast as it can to execute the queries waiting for it.
This solution isn't foolproof. It's possible for the IO thread to miss one or more queries if the master crashes before the thread has had a chance to read them. The amount of data that could be missed is greatly reduced compared to the 3.23 implementation, however.
7.3.3 Files and Settings Related to Replication
There are several files and configuration options related to replication in this chapter. Without going into a lot of detail on any one of them (that's done elsewhere), the files fall into three categories: log files, log index files, and status files.
188.8.131.52 Log files
The log files are the binary log and the relay log. The binary log contains all write queries that are written when the log is enabled. The log-bin option in my.cnf enables the binary log. Binary log files must be removed when they're no longer needed because MySQL doesn't do so automatically.
The relay log stores replicated queries from a MySQL 4.0 slave (from the master's binary log) before it executes them. It's best thought of as a spool for queries. The relay log is enabled automatically in 4.0 slaves. The relay-log option in my.cnf can customize the name and location of the relay log's base filename:
relay-log = /home/mysql/relay.log
Like the binary log, MySQL always appends a sequence number to the base name, starting with 001. Unlike the binary log, MySQL takes care of removing old relay logs when they are no longer needed. MySQL 3.23 servers don't use relay logs.
184.108.40.206 Log index files
You can add settings to my.cnf to specify the log index filenames and locations:
log-bin-index = /var/db/logs/log-bin.index relay-log-index = /var/db/logs/relay-log.index
Never change these settings once a slave is configured and replicating. If you do, MySQL uses the new values when it is restarted and ignores the older files.
220.127.116.11 Status files
MySQL 3.23 and 4.0 slaves use a file named master.info to store information about their master. The file contains the master's hostname, port number, username, password, log file name, position, and so on. MySQL updates the log position and log file name (as necessary) in this file as it reads queries from the master's binary log. While you should never need to edit the file, it's worth knowing what it is used for.
The master-info-file option in my.cnf can be used to change the name and location of the master.info file:
master-info-file = /home/mysql/master-stuff.info
However, there's rarely a need to do so.
MySQL 4.0 slaves use an additional status file for the SQL thread to track its processing of the relay log, in much the same way the master.info file is used. The relay-log-info-file setting can be used to change the filename and path of this file:
relay-log-info-file = /home/mysql/logs/relay-log.info
Again, you won't need to change the default.
There may be times when you don't need to replicate everything from the master to the slave. In such situations you can use the various replication filtering options to control what is replicated. This is well covered in the MySQL documentation, so we'll just recap the important parts.
There are two sets of configuration options for filtering. The first set applies to the binary log on the master and provide per-database filtering:
Any queries filtered on the master aren't written to its binary log, so the slave never sees them either.
The second set of options applies to the relay log on the slave. That means the slave still has to read each query from the master's binary log and make a decision about whether or not to keep the query. The CPU overhead involved in this work is minimal, but the network overhead may not be if the master records a high volume of queries.
Here is the second set of options:
replicate-do-table=dbname.tablename replicate-ignore-table=dbname.tablename replicate-wild-do-table=dbname.tablename replicate-wild-ignore-table=dbname.tablename replicate-do-db=dbname replicate-ignore-db=dbname replicate-rewrite-db=from_dbname->to_dbname
|< Day Day Up >|