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