MySQL 从 3.23 版本开始提供复制的功能。复制是指将主数据库的 DDL 和 DML 操作通过
二进制日志传到复制服务器(也叫从服务器)上,然后在从服务器上对这些日志重新执行(也
叫重做),从而使得从服务器和主服务器的数据保持同步。
MySQL 支持一台主服务器同时向多台从服务器进行复制,从服务器同时也可以作为其他服务器的主服务器,实现链状的复制。
MySQL 复制的优点主要包括以下 3 个方面:
如果主服务器出现问题,可以快速切换到从服务器提供服务;
可以在从服务器上执行查询操作,降低主服务器的访问压力;
可以在从服务器上执行备份,以避免备份期间影响主服务器的服务。
注意:由于 MySQL 实现的是异步的复制,所以主从服务器之间存在一定的差距,在从服务器上
进行的查询操作需要考虑到这些数据的差异,一般只有更新不频繁的数据或者对实时性
要求不高的数据可以通过从服务器查询,实时性要求高的数据仍然需要从主数据库获得
安装配置
复制的步鄹
P385
(1)确保主从服务器上安装了相同版本的数据库
(2)在主服务器上,设置一个复制使用的账户,并授予 REPLICATION SLAVE 权限
主要复制启动选项
MASTER_HOST、
MASTER_PORT、 MASTER_USER、 MASTER_PASSWORD、 MASTER_LOG_FILE、 MASTER_LOG_POS。
这几个参数需要在从服务器上配置,用来记录需要复制的主数据库的地址、端口、访问的用
户 等
log-slave-updates
log-slave-updates 这个参数用来配置从服务器上的更新操作是否写二进制日志,默认是不打
开的。但是,如果这个从服务器同时也要作为其他服务器的主服务器,搭建一个链式的复制,
那么就需要打开这个选项,这样它的从服务器将获得它的二进制日志以进行同步操作。
这个启动参数需要和--logs-bin 参数一起使用。
master-connect-retry
master-connect-retry 这个参数用来设置在和主服务器的连接丢失的时候,重试的时间间隔,
默认是 60 秒,即每 60 秒重试一次
read-only
read-only 该参数用来设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误
的对从服务器的更新操作
指定复制的数据库或者表
可以使用 replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table 或
replicate-wild-do-table 来指定从主数据库复制到从数据库的数据库或者表
slave-skip-errors
此参数的作用就是用来定
义复制过程中从服务器可以自动跳过的错误号,这样当复制过程中遇到定义中的错误号时,
便可以自动跳过,直接执行后面的 SQL 语句,以此来最大限度地减少人工干预。此参数可
以定义多个错误号,或者通过定义成 all 跳过全部的错误。具体语法如下:
--slave-skip-errors=[err_code1,err_code2,... | all]
如果从数据库主要是作为主数据库的备份,那么就不应该使用这个启动参数,设置不当,很
可能造成主从数据库的数据不同步。但是,如果从数据库仅仅是为了分担主数据库的查询压
力,且对数据的完整性要求不是很严格,那么这个选项的确可以减轻数据库管理员维护从数
据库的工作量。
日常管理维护
查看从服务器状态
mysql> show slave status \G
我们主要关心“Slave_IO_Running”和“Slave_SQL_Running”这两个
进程状态是否是“yes”,这两个进程的含义分别如下
Slave_IO_Running:此进程负责从服务器(Slave)从主服务器(Master)上读取 BINLOG
日志,并写入从服务器上的中继日志中。
Slave_SQL_Running:此进程负责读取并且执行中继日志中的 BINLOG 日志。
只要其中有一个进程的状态是 no,则表示复制进程停止,错误原因可以从“Last_Errno”字
段的值中看到
主从服务器同步维护
在某些繁忙的 OLTP(在线事务处理)系统上,由于主服务器更新频繁,而从服务器由于
各种原因(比如硬件性能较差)导致更新速度较慢,从而使得主从服务器之间的数据差
距越来越大,最终对某些应用产生影响。在这种情况下,我们就需要定期地进行主从服
务器的数据同步,使得主从数据差距能够减到最小。常用的方法是:在负载较低的时候
暂时阻塞主数据库的更新,强制主从数据库更新同步。具体操作步骤如下。
(1)在主服务器上,执行以下语句(注意,会阻塞主数据库的所有更新操作):
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
记录 SHOW 语句的输出的日志名和偏移量,这些是从服务器复制的目的坐标。
(2)在从服务器上,执行下面语句,其中 MASTER_POS_WAIT()函数的参数是前面步骤
中得到的复制坐标值:
mysql> select MASTER_POS_WAIT('mysql-bin.000039','974');
这个 SELECT 语句会阻塞直到从服务器达到指定的日志文件和偏移量后,返回 0,如果
返回-1,则表示超时退出。查询返回 0 时,则从服务器与主服务器同步。
(3)在主服务器上,执行下面的语句允许主服务器重新开始处理更新:
mysql> UNLOCK TABLES;
从服务器复制出错的处理
上文提到过,在某些情况下,会出现从服务器更新失败,这时,首先需要确定是否是从
服务器的表与主服务器的不同造成的。如果是表结构不同导致的,则修改从服务器的表与主
服务器的相同,然后重新运行 START SLAVE 语句。
如果不是表结构不同导致的更新失败,则需要确认手动更新是否安全,然后忽视来自主
服 务 器 的 更 新 失 败 的 语 句 。 跳 过 来 自 主 服 务 器 的 语 句 的 命 令 为 SET GLOBAL
SQL_SLAVE_SKIP_COUNTER = n,其中 n 的取值为 1 或者 2。如果来自主服务器的更新语句不
使用 AUTO_INCREMENT 或 LAST_INSERT_ID(),n 值应为 1,否则,值应为 2。原因是使用
AUTO_INCREMENT 或 LAST_INSERT_ID()的语句需要从二进制日志中取两个事件。
log event entry exceeded max_allowed_packet 的处理
如果应用中使用大的 BLOG 列或者长字符串,那么在从服务器上恢复的时候,可能会
出现“log event entry exceeded max_allowed_packet”错误,这是因为含有大文本的记录无法
通过网络进行传输导致。解决的办法就是在主从服务器上增加 max_allowed_packet 参数的大
小,这个参数的默认值为 1MB,
可以按照实际需要进行修改
例中将其增大为 16MB
mysql> show variables like 'max_allowed_packet';
mysql> SET @@global.max_allowed_packet=16777216;
同时在 my.cnf 里,设置 max_allowed_packet = 16M,保证下次数据库重新启动后参数继续有效
多主复制时的自增长变量冲突问题
在大多数情况下,一般只使用单主复制(一台主服务器对一台或者多台从服务器)
。但是在某些情况下,可能会需要使用多主复制(多台主服务器对一台从服务器)
如果主服务器的表采用自动增长变量,那么复制到从服务器的同一张表后很可能会引起主键
冲突,因为系统参数 auto_increment_increment 和 auto_increment_offset 默认值为 1,这样多
台主服务器的自增变量列迟早会发生冲突。在单主复制时,可以采用默认设置,不会有主键
冲 突 发 生 。 但 是 使 用 多 主 复 制 时 , 就 需 要 定 制 auto_increment_increment 和
auto_increment_offset 的设置,保证多主之间复制到从数据库不会有重复冲突
auto_increment_offset 参数设置的是每次增加后的偏移量
通过这两个参数可以方便地设置不同的主服务器上的自动增长列的值的范围,这样在这些数
据复制到从服务器上时可以有效地避免主键的重复。
查看从服务器的复制进度
很多情况下,我们都想知道从服务器复制的进度如何
这个值可以通过 SHOW PROCESSLIST 列表中的 Slave_SQL_Running 线程的 Time 值得到,它记
录了从服务器当前执行的 SQL 时间戳与系统时间之间的差距,单位是秒。
由于 MySQL 复制的机制是执行主服务器传输过来的二进制日志,二进制日志中的每个语句
通过设置时间戳来保证执行时间和顺序的正确性,所以每个语句执行之前都会首先设置时间
戳,而通过查询这个进程的 Time 就可以知道最后设置的时间戳和当前时间的差距
切换主从服务器
假设有一个复制的环境,一个主数据库服务器 M,两个从数据库服务器 S1、S2 同时指向主
数据库服务器 M。当主数据库 M 因为某种原因出现故障的时候,需要将其中的一个从数据
库服务器(假设选中 S1)切换成主数据库服务器,同时修改另一个从数据库(S2)的配置,
使其指向新的主数据库(S1)。此外还需要通知应用修改主数据库的 IP 地址,如果可能,将
出现故障的主数据库(M)修复或者重置成新的从数据库。
(1)首先要确保所有的从数据库都已经执行了 relay log 中的全部更新,在每个从服务器
上,执行 STOP SLAVE IO_THREAD,然后检查 SHOW PROCESSLIST 的输出,直到看到状态
是 Has read all relay log,表示更新都执行完毕。
(2)在从数据库 S1 上,执行 STOP SLAVE 停止从服务,然后 RESET MASTER 重置成主数
据库。
(3)在 S2 上,执行 STOP SLAVE 停止从服务,然后执行 CHANGE MASTER TO MASTER_HOST
= 'S1'重新设置主数据库,然后再执行 START SLAVE 启动复制。
(4)通知所有的客户端将应用指向 S1,这样客户端发送的所有的更新语法写入到 S1 的
二进制日志。
(5)删除新的主数据库服务器上的 master.info 和 relay-log.info 文件,否则下次重启的时
候还会按照从服务器启动。
(6)最后,如果 M 服务器可以修复,则可以按照 S2 的方法配置成 S1 的从服务器。
注意:上面测试的步骤是默认 S1 是打开 log-bin 选项的,这样重置成主数据库后可以将二进制日
志传输到其他从服务器。其次,S1 上没有打开 log-slave-updates 参数,否则重置成主数据
库后,可能会将已经执行过的二进制日志重复传输给 S2,导致 S2 的同步错误。
小结
复制是 MySQL 数据库中经常使用的一个功能,它可以有效地保证主数据库的数据安全,并
减轻主数据库的备份压力,以及分担主数据库的一部分查询压力。
MySQL 复制环境的搭建非常简单,在实际使用中,建议给重要的数据库配置复制,如果没
有足够的服务器可以使用,那么也可以在一个主数据库上启动另外一个 MySQL 服务,以作
为另外一个 MySQL 服务的从数据库。
本章重点介绍了复制环境的搭建、日常管理、主要启动选项和主从切换的操作步骤等,希望
对读者在搭建和日常使用复制功能时有所帮助。由于 MySQL 的复制功能还在不断地完善中,
更多的维护操作方法和启动选项的设置读者可以参考 MySQL 最新的官方文档。
