MySQL复制技术分析研究*

田关伟

(四川民族学院)

0 引言

数据复制是将主节点服务器上的数据及其更新实时同步到一个或多个从节点服务器.复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础.复制功能有很多用途，比较常见的有:(1)数据分布:在不同的地理位置分布数据备份，或建立数据中心;(2)负载均衡:将读操作分布到多个服务器上，实现对读密集型应用的优化;(3)冗余和故障切换:避免单节点失败，当一个节点失效，可以换到具有相同数据副本的节点;(4)数据分析和产生报表:直接用数据库中的数据进行数据分析或创建报表时会消耗资源，大大降低服务器性能，可以将类似作业移至具有相同数据副本的节点;(5)调试和审计:可以审查或测试服务器上的查询，而并不影响业务的正常进行.

根据复制发生的时机，可以将复制分为同步复制(synchronous replication)和异步复制(asynchronous replication)两种.在同步复制中，当一事务提交时，所有相关站点均实时同步更新，随着节点增加，存在大量锁竞争从而导致死锁的增加，降低系统的响应速度.在异步复制中，事务在某一站点提交后的某一时刻再复制到其他相关站点，响应时间短，降低了通信量，但不是实时同步，提高了性能，但是可能导致不同节点之间的数据不一致.

1 MySQL复制原理与实现

MySQL是互联网上最流行的开放源代码的数据库软件.在MySQL中，复制技术将数据从一个数据库服务器(master，主库)复制到一个或多个数据库服务器(slaver，备库).在默认情况下这种复制是异步的，备库不需要一直连接到主库接收更新.根据配置，MySQL可以复制所有的数据库、选定的数据库甚至选定在数据库中的表.

1.1 MySQL 复制原理

MySQL的复制技术是在主库上保持对所有数据库改变(更新，删除等)的跟踪，当主库改变数据库的结构或内容时，在二进制记录文件中产生一条记录.通常情况下，select语句不会被记录，因为并没有改变数据库的机构和内容.当一个备库连接到主库时，向主库请求二进制文件的拷贝，然后将二进制文件中记录的事件在备库中进行重放，如，创建表、修改表结构，数据的插入、删除、更新等.

MySQl复制功能使用3个线程来完成工作，分别是二进制转储线程(Binlog dump thread)、I/O线程(Slave I/O thread)和SQL线程(Slave SQL thread)，其中二进制转储线程运行在主库上，I/O线程和SQL线程运行在备库上.如图1所示，复制通过三个步骤来完成:

(1)主库上记录二进制日志.每次准备提交事务完成数据更新前，主库按照事务提交的顺序，将数据更新的事件记录到二进制日志中.

(2)备库将主库的二进制日志复制到自己的中继日志.首先，备库启动一个工作线程I/O线程，I/O线程与主库建立一个连接，然后在主库上启动二进制转储线程，I/O线程读取主库二进制转储线程发送的内容，并将数据拷贝到备库本地，形成中继日志.主库上有信息日志，保存备库到主库的连接状态和当前配置信息，记录日志记录主机名，登陆认证，以及备库在主库二进制日志中已读取的位置坐标.

(3)备库读取中继日志中的事件并重放到备库数据上.备库上的SQL线程从本地的中继日志中读取事件，并在本地执行，从而实现备库数据的更新.

图1 MySQL复制步骤

在复制过程中，备库上的两个线程独立工作，因此SQL线程尽管语句执行的慢，但不会使读取更新变慢.比如，备库一段时间没有运行，当它连接到主库后会快速的读取二进制记录，即使SQL线程会落后于它，如果备库在获取到的所有语句执行之前停止，I/O线程至少已经获取了所有内容，以便将语句的安全副本保存在中继日志中，准备下次备库运行时执行.

1.2 MYSQL复制的实现

为MySQL服务器配置复制非常简单，主库和备库全新安装后，拥有相同的配置和相同的数据，现在假设主库为 server1(IP:192.168.0.1)，备库为 server2(IP:192.168.0.2).

(1)在服务器上创建复制账号，MySQL在备库运行的I/O线程会建立一个到主库的TC/IP连接，因此需要在主库创建一个用户，并授予权限.备库的I/O线程将使用此用户连接到主库并读取其二进制日志:

(2)配置主库和备库，在主库server1上，需要打开二进制日志，并指定一个唯一的服务器ID，在主库的my.cnf文件中做如下修改:

服务器ID可以设置为1到232－1中的一个整数值，一般默认情况下服务器的ID为1，应204.107避免使用默认值可能会导致和其他服务器的ID冲突.

在备库的my.cnf中增加如下配置:

(3)通知备库连接到主库并从主库复制数据，使用CHANGE MASTER TO语句指示备库如何连接到主库并重放其二进制日志中的事件:

MASTER_LOG_POS是日志的偏移量，因为从日志的开头开始读取，所以设置为0.在mysql命令行使用START SLAVE开始复制，可以使用SHOW SLAVE STATUSG查看备库的状态.

2 MySQL复制模式

MySQL的复制技术有三种模式，分别是基于语句的复制(SBR)，基于行的复制(RBR)，混合复制模式(MBR).

(1)基于语句的复制基于语句的复制，主库记录造成数据更改的语句，当备库进行复制时，实际上是把主库上执行过的SQL语句在备库上再执行一次.这样使复制实现很简单，使二进制中的事件更加紧凑，比如一个更新几兆数据的语句在二进制日志里只占几十个字节，发送时也可以节省资源.基于语句的复制基本上就是执行SQL语句，在备库上发生的数据更新就更容易理解，这样出现问题的时候就可以方便的进行定位.基于语句的复制也有一些缺点，比如同一条SQL语句在主库和备库上执行的时间不同，可能结果就会不相同.另外，更新必须是串行的，这需要更多的锁.

(2)基于行的复制基于行的复制将实际数据记录在二进制日志中，因此可以正确的复制每一行，并且在复制中只需要很少的锁，可以达到更高的并发数.但当某些语句做了全表更新时，基于行的复制会有很大开销，因为每一行的数据都会被记录到二进制日志中，这使得二进制日志事件非常庞大，并且会给主库记录日志和复制增加额外的负载.更慢的日志记录导致降低并发数.

(3)混合复制 基于语句的复制和基于行的复制各有优点和缺点，MySQL可以在复制时在这两种复制模式间动态切换.默认情况下使用的是基于语句的复制方式，但如果发现语句无法被正确执行，就切换到基于行的复制模式.

3 MySQL半同步复制及实现

在MySQL5.5以后增加了半同步复制(semisynchronous replication)，半同步复制的理念是在允许更改操作继续执行前，确保更改操作至少被写入一个备库的磁盘.半同步复制并没有暂停提交事务，只是在事务已被写入到至少一个备库的中继日志之前，避免发送一个答复给客户端.如图2所示，在提交事务的过程中，客户端提交数据更改请求以后，在主库上执行，并写入二进制日志.其中一个备库连接到主库接收二进制日志，随后另一个或多个备库连接到主库请求二进制日志，这时，当其中一台备库接收完毕，并将此事务写入到本地中继日志以后，发送一个确认标志给主库，主库才会给与客户端事务提交后返回的信息.从过程中可以看到，当事务从主库提交到备库之前，如果主库系统崩溃，则每个连接都有可能丢失一个事务，而每一次事务都是在备库提交确认信息后才提交给客户端的，因此最多只会丢失一个事务.

图2 MySQL半同步复制中事务提交过程

使用半同步复制时，需要主库和备库都能够支持，都要运行MySQL5.5或更高版本，并且启用半同步复制机制.启用半同步复制，需要以下步骤:

(1)在主库上安装插件:

(2)在每一台备库上安装插件:

之后重启服务器，半同步复制启动.

4 MySQL多源复制及其实现

4.1 MySQL 复制拓扑

MySQL的复制技术非常灵活，可以根据不同场景建立不同的复制模型，但需要遵循每个备库只能有一个主库这个限制条件.复制有多重拓扑结构，每种都可以扩展为多种用途.最基本的拓扑结构有(1)主从复制拓扑结构，一个主库和一个备库相连接;(2)一主多备复制拓扑结构，有两台或两台以上的备库连接到主库上;(3)主主复制拓扑结构，这种结构根据功能分为主动—主动模式和主动—被动模式，主动—主动模式中的两个主库都可以同时写入数据，但在同时更新一行记录时会导致很多问题，主动—被动模式中的写、读操作分别在主动、被动主库上;(4)环形复制拓扑结构，有三个或更多的主库，每一个节点都是在它之前的节点的备库，同时是在它之后的节点的主库;(5)树形复制拓扑结构，多个备库连接到一个主库上，同时每一个备库又作为下一层次节点的主库.

4.2 MySQL 多源复制

多源复制(multi－source)与多主复制(multi－master)并不相同，如图3所示，多主复制通常是环形复制，在环中的每一个节点都作为主库，每个主库都可以拥有多个备库.而多源复制是多个主库将数据复制到一个备库上.MySQL在版本5.7.2中解除了之前的对于一个备库只能有一个主库的限制，但这是一个开发版本的新功能，只能用来作为测试，还不适用于生产环境，因此只能从其他途径来实现多源复制.

图3 多主复制与多源复制

4.3 多源复制实现步骤

尽管只能同时有一个主库，但备库可以分时连接多个主库，将备库在主库之间进行切换，定时轮流从其中一个主库复制，近似的实现多源复制，复制的过程如下:

(1)将备库配置为从一个主库复制，这个主库为当前主库.

(2)设置备库复制的时间段，备库从当前主库中读取更新，然后在时段内应用这些变化.

(3)使用STOP SLAVE IO_THREAD停止备库的I/O线程.

(4)等待中继日志为空，即从主库中二进制日志中读取的事件通过SQL线程重放完毕.

(5)使用STOP SLAVE SQL_THREAD停止备库的SQL线程.

(6)保存当前主库中备库读取的坐标位置.

(7)将备库的复制按顺序转到下一个主库上，利用上一次在此主库上的坐标位置，重新进行连接.

(8)使用START SLAVE重启备库的线程.

从第2个步骤开始重复，直到下一个主库.

4.4 多源复制的实现

整个轮换主库方式实现MySQL多源复制的过程，可以用脚本实现自动轮换，使用python语言，itertools模块的cycle函数，可以轮流从主库中读取数据，编写的脚本代码如下:

import itertools

position = {}

def multi_master(slave，master):

corrent = masters［0］

for master in itertools． cycle(masters);

slave． sql(“STOP SLAVE IO_THＲEAD”);

mysqlrep． wait_for_empty_relay_log(slave)

slave． sql(“STOP SLAVE SQL_THEAD”);

positon ［current． name］= mysqlrep． fetch_slave_

position(slave)

slave． change_master(position ［current，name］)

mastar． sql(“STAＲT SLAVE”)

current = mastar

sleep(60) #sleep 60 second

5 结束语

MySQL的复制技术是使得MySQL广泛流行的特性之一，复制技术简单、便于使用，可以根据需要进行不同形态、不同规模的部署，目前在互联网应用的数据服务中，有很多的基于MySQL复制技术的成功案例.如果拓扑结构且配置正确，MySQL的复制通常是没有问题的，并且很少需要调整，但也有很多情况导致故障.比如，主库崩溃导致二进制时间丢失，主库上表损坏，在主库上正常运行的查询在备库上异常，备库连接超时且重连频繁，备库上的中继日志被破坏等等.因此，在使用复制技术的同时，监控复制、故障排除和灾难恢复显得更加重要.

［1］Bell C.宁青，等译.高可用MySQL——构建健壮的数据中心［M］.北京:电子工业出版社，2011.10.

［2］Schwartz B.宁海元，等译.高性能 MySQL［M］.北京:电子工业出版社，2013.5.

［3］张伟丽，姜春华，魏劲超.MySQL复制技术的研究及应用［J］.计算机科学，2012(11A):168–170.

［4］刘腾.MySQL复制技术的研究与改进［D］.杭州:浙江大学，2011.

［5］方丹辉，张狄.MySQL主从服务器数据库同步的实现［J］.计算机应用，2002，22(7):116–117.

［6］蔡亮，刘腾.基于写操作集的数据库同步复制模型［J］.计算机工程，2011(13):61－63