MySQL 同步（一）
时间：2006-11-03 来源：qiyu15555
MySQL 同步（一）
译者：叶金荣（Email:），来源：MySQL手册版本 5.0.20，转载请注明译者和出处，并且不能用于商业用途，违者必究。
6 MySQL 同步
同步功能在MySQL 3.23.15就开始引进了，它可以把一个MySQL服务器上的数据复制到另一个服务器上去。本章描述了MySQL的各种复制特性。介绍了同步的概念，如何设置同步服务器，以及可用服务器的参照。还提供了一系列的常见问题及其答案，疑难解答。
"14.6 Replication Statements"中介绍了同步相关的SQL语句语法。
我们建议经常访问"http://www.mysql.com"经常阅读本章的最新内容。同步功能一直在改进，我们经常把这部分的手册更新到当前的最新内容。
6.1 同步介绍
MySQL 3.23.15及更高的版本支持单向同步。一个服务器作为master(主服务器)，一个或者多个服务器作为slave(从服务器)。master服务器把更新的内容写到二进制日志(binary log或binlog)中，并且维护了一个索引文件来记录日志循环的情况。这些日志中的更新部分会被发送到slave服务器。一个slave连接到master之后，它通知master最后一次成功增量更新的日志位置。slave会找出所有从那个时刻开始的更新操作，然后阻塞并等待master发送新的更新操作。
如果想要做一个同步服务器链的话，slave同时也可以作为master。
注意，启用同步后，所有要同步的更新操作都必须在master上执行。否则，必须注意不要造成用户在master上的更新和在slave上的更新引起冲突。
单向同步的好处是稳健，高速，系统易管理：
有了master/slave机制后，就更稳健了。当master上发生问题时，可以把slave作为备用切换过去。
可以在slave和master之间分担一些查询，这就能加速响应时间。SELECT 查询就可以在slave上执行以减少master的负载。更新数据的语句则要放在mater上执行以保持master和slave的同步。当非更新操作占多数时，负载均衡就很有效了，不过这只是普通情况而言。
另一个好处是可以在slave上备份数据，无需干扰master。备份数据时master照样继续运作。详情请看"5.7.1 Database Backups"。
6.2 同步机制实现概述
MySQL同步机制基于master把所有对数据库的更新、删除 等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。详情请看"5.9.4 The Binary Log"。
每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。
应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。
把master上的数据拷贝过来的方法之一实在slave上执行 LOAD DATA FROM MASTER 语句。不过要注意，LOAD DATA FROM MASTER 是从MySQL 4.0.0之后才开始可以用的，而且只支持master上的 MyISAM 类型表。同样地，这个操作需要一个全局的读锁，这样的话传送日志到slave的时候在master上就不会有更新操作了。当实现了自由锁表热备份时(在MySQL 5.0中)，全局读锁就没必要了。
由于有这些限制，因此我们建议只在master上相关数据比较小的时候才执行 LOAD DATA FROM MASTER 语句，或者在master上允许一个长时间的读锁。由于每个系统之间 LOAD DATA FROM MASTER 的速度各不一样，一个比较好的衡量规则是每秒能拷贝1MB数据。这只是的粗略的估计，不过master和slave都是奔腾700MHz的机器且用100MBit/s网络连接时就能达到这个速度了。
slave上已经完整拷贝master数据后，就可以连接到master上然后等待处理更新了。如果master当机或者slave连接断开，slave会定期尝试连接到master上直到能重连并且等待更新。重试的时间间隔由 --master-connect-retry 选项来控制，它的默认值是60秒。
每个slave都记录了它关闭时的日志位置。msater是不知道有多少个slave连接上来或者哪个slave从什么时候开始更新。
6.3 同步实现细节
MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行 START SLAVE 语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行 SHOW PROCESSLIST 语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的 Binlog Dump 线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。
如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。
在MySQL 4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。
slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，但I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。
执行 SHOW PROCESSLIST 语句就会告诉我们所关心的master和slave上发生的情况。
下例说明了 SHOW PROCESSLIST 结果中的3个线程是什么样的。这是在MySQL 4.0.15及更新上执行 SHOW PROCESSLIST 的结果，State 字段的内容已经比旧版本显示的更有意义了。
在master上，SHOW PROCESSLIST 的结果如下：
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL 在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。 在slave上，SHOW PROCESSLIST 的结果如下： mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Has read all relay log; waiting for the slave I/O
thread to update it
Info: NULL 这表明线程10是I/O线程，它正连接到master上；线程11是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。 注意，Time 字段的值告诉我们slave上的日志比master晚了多久。详情请看"6.9 Replication FAQ"。 6.3.1 Master 同步线程状态 以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。 Sending binlog event to slave 事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。 Finished reading one binlog; switching to next binlog 读取完了一个二进制日志，正切换到下一个。 Has sent all binlog to slave; waiting for binlog to be updated 已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。 Waiting to finalize termination 当前线程停止了，这个时间很短。 6.3.2 Slave的I/O线程状态 以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。 Connecting to master 该线程证尝试连接到master上。 Checking master version 确定连接到master后出现的一个短暂的状态。 Registering slave on master 确定连接到master后出现的一个短暂的状态。 Requesting binlog dump 确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。 Waiting to reconnect after a failed binlog dump request 如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由 --master-connect-retry 选项来指定。 Reconnecting after a failed binlog dump request 该线程正尝试重连到master。 Waiting for master to send event 已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。 Queueing master event to the relay log 已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。 Waiting to reconnect after a failed master event read 读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read 正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。 Waiting for the slave SQL thread to free enough relay log space relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit 当前线程停止了，这个时间很短。 6.3.3 Slave的SQL线程状态 以下列出了slave的SQL线程 State 字段中最常见的几种状态： Reading event from the relay log 从中继日志里读到一个事件以备执行。 Has read all relay log; waiting for the slave I/O thread to update it 已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。 Waiting for slave mutex on exit 当前线程停止了，这个时间很短。 SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。 6.3.4 中继日志及状态文件 默认地，中继日志的名字格式为 `host_name-relay-bin.nnn`，host_name 是服务器的主机名，nnn 是序号。中继日志是根据顺序的序号来创建的，从 000001 (MySQL 4.0 及更旧是 001)开始。slave上用一个索引文件来跟踪当前正在使用的中继日志。默认的中继日志索引文件名是 `host_name-relay-bin.index`。默认地，这个文件位于slave的数据文件目录下。默认文件名可以根据的系统选项 --relay-log 和 --relay-log-index 来替换。详情请看"6.8 Replication Startup Options"。 中继日志和二进制日志的格式一样，因此也可以用 mysqlbinlog 来读取。当SQL线程读取完中继日志中的全部事件后就不再需要它了，会自动删除它。中继日志没有显式的删除机制，因为SQL线程会自动关注这个。不过，从MySQL 4.0.14开始，执行 FLUSH LOGS 的话就会轮转(rotate)中继日志，会让SQL线程删除它们。 在下列条件中会创建一个新的中继日志： slave启动后，I/O线程第一次启动(在MySQL 5.0中，每次I/O线程启动后都会新建一个中继日志，而不只是第一次启动时)。
刷新日志时；例如，执行 FLUSH LOGS 语句或运行 mysqladmin flush-logs 命令(从 MySQL 4.0.14开始才会创建新中继日志)。
当前的中继日志大小太大了；"太大了"是这么判断的： max_relay_log_size, 如果 max_relay_log_size > 0 的话 max_binlog_size, 如果 max_relay_log_size = 0 或 MySQL 低于 4.0.14 slave会在数据文件目录下创建两个额外的文件。它们是状态文件，名字默认为 `master.info` and `relay-log.info`。它们的内容跟执行 SHOW SLAVE STATUS 语句的结果类似。详情请看"14.6.2 SQL Statements for Controlling Slave Servers"。由于是磁盘上的文件，它们在slave关闭后还会留着。下一次slave启动时，就会读取这两个文件来判断从master读取到二进制日志的什么位置了，处理中继日志到什么位置了。 `master.info` 文件由来I/O线程更新。在MySQL 4.1以前，文件的内容和执行 SHOW SLAVE STATUS 语句结果中相对应的字段值一样，如下： Line Description
1 Master_Log_File
2 Read_Master_Log_Pos
3 Master_Host
4 Master_User
5 Password (not shown by SHOW SLAVE STATUS)
6 Master_Port
7 Connect_Retry 从MySQL 4.1开始，文件内容还包括了SSL选项： Line Description
1 Number of lines in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 Password (not shown by SHOW SLAVE STATUS)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key `relay-log.info` 文件由SQL线程来更新。文件的内容和执行 SHOW SLAVE STATUS 语句结果中相对应的字段值一样：
Line Description
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos 备份slave数据时，要把这两个文件也备份起来，和中继日志一道。想要恢复slave时就用得到它们了。如果丢失了中继日志，但是 `relay-log.info` 文件还存在，那么就可以判断出SQL线程执行了多少master二进制日志。然后执行 CHANGE MASTER TO 语句，带上 MASTER_LOG_FILE 和 MASTER_LOG_POS 选项告诉slave要从master的二进制日志哪个位置重新读取。当然了，这要求master上相关的二进制日志都还留着。 如果slav打算同步 LOAD DATA INFILE 语句，那么也要备份对应目录下的任何 `SQL_LOAD-*` 文件。这可以在 LOAD DATA INFILE 被中断后继续保持同步。这个目录由 --slave-load-tmpdir 选项来指定。默认地，如果没有指定的话，它的值就是变量 tmpdir 的值。
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL 在这里，线程2是为一个slave连接创建的。结果表明所有未完成的更新日志已经都发送到slave了，master正等待新的更新日志发生。 在slave上，SHOW PROCESSLIST 的结果如下： mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Has read all relay log; waiting for the slave I/O
thread to update it
Info: NULL 这表明线程10是I/O线程，它正连接到master上；线程11是SQL线程，它执行中继日志中的更新操作。现在，这2个线程都处于空闲状态，正等待新的更新日志。 注意，Time 字段的值告诉我们slave上的日志比master晚了多久。详情请看"6.9 Replication FAQ"。 6.3.1 Master 同步线程状态 以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。也就是说，没有slave连接上来。 Sending binlog event to slave 事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。 Finished reading one binlog; switching to next binlog 读取完了一个二进制日志，正切换到下一个。 Has sent all binlog to slave; waiting for binlog to be updated 已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新的事件，然后读取它们。 Waiting to finalize termination 当前线程停止了，这个时间很短。 6.3.2 Slave的I/O线程状态 以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的 Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。 Connecting to master 该线程证尝试连接到master上。 Checking master version 确定连接到master后出现的一个短暂的状态。 Registering slave on master 确定连接到master后出现的一个短暂的状态。 Requesting binlog dump 确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。 Waiting to reconnect after a failed binlog dump request 如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由 --master-connect-retry 选项来指定。 Reconnecting after a failed binlog dump request 该线程正尝试重连到master。 Waiting for master to send event 已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。 Queueing master event to the relay log 已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。 Waiting to reconnect after a failed master event read 读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read 正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。 Waiting for the slave SQL thread to free enough relay log space relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间。
Waiting for slave mutex on exit 当前线程停止了，这个时间很短。 6.3.3 Slave的SQL线程状态 以下列出了slave的SQL线程 State 字段中最常见的几种状态： Reading event from the relay log 从中继日志里读到一个事件以备执行。 Has read all relay log; waiting for the slave I/O thread to update it 已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。 Waiting for slave mutex on exit 当前线程停止了，这个时间很短。 SQL线程的 State 字段有时候也可能是一个SQL语句。这意味着它从中继日志中读取到一个事件了，从中提取出SQL语句，并执行它。 6.3.4 中继日志及状态文件 默认地，中继日志的名字格式为 `host_name-relay-bin.nnn`，host_name 是服务器的主机名，nnn 是序号。中继日志是根据顺序的序号来创建的，从 000001 (MySQL 4.0 及更旧是 001)开始。slave上用一个索引文件来跟踪当前正在使用的中继日志。默认的中继日志索引文件名是 `host_name-relay-bin.index`。默认地，这个文件位于slave的数据文件目录下。默认文件名可以根据的系统选项 --relay-log 和 --relay-log-index 来替换。详情请看"6.8 Replication Startup Options"。 中继日志和二进制日志的格式一样，因此也可以用 mysqlbinlog 来读取。当SQL线程读取完中继日志中的全部事件后就不再需要它了，会自动删除它。中继日志没有显式的删除机制，因为SQL线程会自动关注这个。不过，从MySQL 4.0.14开始，执行 FLUSH LOGS 的话就会轮转(rotate)中继日志，会让SQL线程删除它们。 在下列条件中会创建一个新的中继日志： slave启动后，I/O线程第一次启动(在MySQL 5.0中，每次I/O线程启动后都会新建一个中继日志，而不只是第一次启动时)。
刷新日志时；例如，执行 FLUSH LOGS 语句或运行 mysqladmin flush-logs 命令(从 MySQL 4.0.14开始才会创建新中继日志)。
当前的中继日志大小太大了；"太大了"是这么判断的： max_relay_log_size, 如果 max_relay_log_size > 0 的话 max_binlog_size, 如果 max_relay_log_size = 0 或 MySQL 低于 4.0.14 slave会在数据文件目录下创建两个额外的文件。它们是状态文件，名字默认为 `master.info` and `relay-log.info`。它们的内容跟执行 SHOW SLAVE STATUS 语句的结果类似。详情请看"14.6.2 SQL Statements for Controlling Slave Servers"。由于是磁盘上的文件，它们在slave关闭后还会留着。下一次slave启动时，就会读取这两个文件来判断从master读取到二进制日志的什么位置了，处理中继日志到什么位置了。 `master.info` 文件由来I/O线程更新。在MySQL 4.1以前，文件的内容和执行 SHOW SLAVE STATUS 语句结果中相对应的字段值一样，如下： Line Description
1 Master_Log_File
2 Read_Master_Log_Pos
3 Master_Host
4 Master_User
5 Password (not shown by SHOW SLAVE STATUS)
6 Master_Port
7 Connect_Retry 从MySQL 4.1开始，文件内容还包括了SSL选项： Line Description
1 Number of lines in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 Password (not shown by SHOW SLAVE STATUS)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key `relay-log.info` 文件由SQL线程来更新。文件的内容和执行 SHOW SLAVE STATUS 语句结果中相对应的字段值一样：
Line Description
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos 备份slave数据时，要把这两个文件也备份起来，和中继日志一道。想要恢复slave时就用得到它们了。如果丢失了中继日志，但是 `relay-log.info` 文件还存在，那么就可以判断出SQL线程执行了多少master二进制日志。然后执行 CHANGE MASTER TO 语句，带上 MASTER_LOG_FILE 和 MASTER_LOG_POS 选项告诉slave要从master的二进制日志哪个位置重新读取。当然了，这要求master上相关的二进制日志都还留着。 如果slav打算同步 LOAD DATA INFILE 语句，那么也要备份对应目录下的任何 `SQL_LOAD-*` 文件。这可以在 LOAD DATA INFILE 被中断后继续保持同步。这个目录由 --slave-load-tmpdir 选项来指定。默认地，如果没有指定的话，它的值就是变量 tmpdir 的值。
相关阅读 更多 +
