MySQL数据库：主从复制Replication

偏执的太偏执、 2022-04-02 08:52 497阅读 0赞

### 一、主从复制架构的搭建： ###

1、MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解请参考：   
[http://blog.csdn.net/xlgen157387/article/details/51331244\#comments][http_blog.csdn.net_xlgen157387_article_details_51331244_comments]

2、使用mysqlreplicate命令快速搭建 Mysql 主从复制：   
[http://blog.csdn.net/xlgen157387/article/details/52452394][http_blog.csdn.net_xlgen157387_article_details_52452394]

### 二、MySQL主从复制过程及原理： ###

**1、MySQL主从复制的原理：**

Slave从Master获取binlog二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成：

>  *  （1）Slave SQL thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于slave端
>  *  （2）Slave I/O thread线程：读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中，位于slave端：
>  *  （3）Binlog dump thread线程(也称为IO线程)：将bin-log二进制日志中的内容发送到slave服务器，位于master端

**注意：**如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程；要实现MySQL的主从复制，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。

**2、主从复制流程：**

（1）master服务器在执行SQL语句之后，记录在二进制bin-log文件中；

（2）slave端的IO线程连接上master端，并请求从指定bin log日志文件的指定pos节点位置（或者从最开始的日志）开始复制之后的日志内容。

（3）master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。

（4）slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中（该文件存slave端），以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步；

（5）slave端的SQL线程在检测到relay log文件中新增内容后，就马上解析该relay log文件中的内容，然后还原成在master端真实执行的那些SQL语句，再按顺序依次执行这些SQL语句，从而到达master端和slave端的数据一致性；

**3、主从复制的好处：**

（1）读写分离，通过动态增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上执行读功能。

（2）提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。

（3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

### 三、MySQL支持的复制类型及其优缺点： ###

bin-log日志文件有两种格式，一种是Statement-Based（基于语句的复制），另一种是Row-Based（基于行的复制）。默认格式为Statement-Based，如果想改变其格式在开启服务的时候使用 -binlog-format 选项，其具体命令如下：

> mysqld\_safe –user=msyql –binlog-format=格式 &

1、基于语句的复制（Statement-Based）：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。

> （1）优点：
> 
>  *  ① 因为记录的SQL语句，所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
>  *  ② binlog日志文件记录了所有的改变数据库的语句，所以此文件可以作为数据库的审核依据。
> 
> （2）缺点：
> 
>  *  ① 不安全，不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如：对于 delete 或者 update 语句，如果使用了 limit 但是并没有 order by ，这就属于非确定性的语句，就不会被记录。
>  *  ② 对于没有索引条件的update，insert……select 语句，必须锁定更多的数据，降低了数据库的性能。

2、基于行的复制（Row-Based）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从mysql5.0开始支持；

> （1）优点：
> 
>  *  ① 所有的改变都会被复制，这是最安全的复制方式；
>  *  ② 对于 update、insert……select等语句锁定更少的行；
> 
> （2）缺点：
> 
>  *  ① 不能通过binlog日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到什么数据改变。
>  *  ② 因为记录的是数据，所以说binlog日志文件占用的存储空间要比Statement-based大。
>  *  ③ 对于数据量大的操作其花费的时间有更长。

3、混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

### 四、主服务器流程分析： ###

**1、主服务器线程 Binlog dump thread：**

Binlog dump 线程是当有从服务器连接的时候由主服务器创建，其大致工作过程经历如下几个阶段：

![这里写图片描述][20160906191825438]

首先bin-log文件加锁，然后读取更新的操作，读取完毕后将锁释放，最后将读取的记录发送给从服务器。

我们可以使用如下的命令来查看该线程的信息

> mysql> SHOW PROCESSLIST

以我的系统为例，因为我这系统中是一台主服务器和两台从服务器，所以会列出两条Binlog dump线程的信息：

> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
>      Id: 2  
>    User: repuser  
>    Host: 192.168.144.131:41544  
>      db: NULL  
> Command: Binlog Dump  
>    Time: 54  
>   State: Master has sent all binlog to slave; waiting for binlog to be updated  
>    Info: NULL  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
>      Id: 3  
>    User: repuser  
>    Host: 192.168.144.132:40888  
>      db: NULL  
> Command: Binlog Dump  
>    Time: 31  
>   State: Master has sent all binlog to slave; waiting for binlog to be updated  
>    Info: NULL

上述字段中的state字段会有以下几种状态：

> 1. Sending binlog event to slave  
> 表示Binlog dump 线程已经读取完binlog日志中更新的event，现在正在发送给从服务器
> 
> 2. Finished reading one binlog; switching to next binlog  
> 表示Binlog dump 线程已经读取完一个binlog日志，现在正在打开下一个binlog日志读取来发送给从服务器
> 
> 3. Master has sent all binlog to slave; waiting for binlog to be updated  
> 这就是上面我们看到的state的值，表示Binlog dump 线程已经读取完所有的binlog日志文件，并且将其发送给了从服务器。现在处于空闲状态，正在等待读取有新的操作的binlog日志文件
> 
> 4. Waiting to finalize termination  
> 这个状态持续的很短暂，我们几乎看不到。当线程停止的时候显示此状态

上述几个状态就是一次主从复制过程中Binlog dump 线程所经历的状态，如果我们是在测试的环境中，上述1、2、4状态我们几乎是看不到的，因为它执行的很快。

在主从系统中主服务器上的一个主要的文件就是bin-log日志，该线程操作的文件也是此日志文件，因此这是我们需要在配置文件my.cnf 中打开bin-log日志的原因，使用此文件来记录我们的更新操作。

> \[mysqld\]
> 
> log-bin = mysql-bin
> 
> server-id = 1

还有一点需要注意，在上面已经说过，但是在这里觉得有必要再重复一遍，就是有多少个从服务器连接主服务器上就有多少个Binlog dump 线程。

**2、bin-log日志文件管理：**

对于bin-log日志文件，其默认的名称为 mysql-bin.xxxxxx。而且还有一个索引文件mysql-bin.index，其中记录了当前所有的bin-log日志文件。

对于新的主服务器只有一个bin-log日志文件 mysql-bin.000001。此时所有的操作都有这个文件来记录，如果我们想更换bin-log日志文件，可以使用如下命令：

> Mysql>flush logs;

此时会创建一个mysql-bin.000002文件来记录以后的操作。除了使用上述命令以外，当bin-log日志文件达到其最大值的时候也会产生新的bin-log日志文件。

其文件最大值和文件名包括索引文件的名称可以使用 –max\_binlog\_size、–log-bin和—log-bin-index 选项来改变，具体命令如下

> mysqld\_safe –user=msyql –max\_binlog\_size=文件长度 –log-bin=新的日志文件名称 –log-bin-index=新索引文件名 &

对于主服务器来说，总起来一句话：主服务器针对于每一个从服务器都创建一个Binlog dump线程，用来读取bin-log日志中更新的操作将其发送给从服务器，发送完毕以后继续等待bin-log日志是否有更新。

### 五、从服务器流程分析： ###

在主服务器探究这篇文章中我们提到过，在一次主从复制过程中需要用到三个线程：Binlog dump 线程、Slave I/O 线程和Slave SQL线程，其中Binlog dump 线程在主服务器上面，剩下的两个线程是在从服务器上面工作的。

这两个线程在从服务器上面的工作流程如下图所示：

![这里写图片描述][20160906193915196]

对于这两个线程随着从服务器开启slave而产生

> mysql> START SLVAE;

然后使用：

> Mysql> SHOW SLAVE STATUS

查看这两个线程的情况：

> ……  
> Master\_Log\_File: mysql-bin.000003  
> Read\_Master\_Log\_Pos: 1264  
> Relay\_Log\_File: localhost-relay-bin.000002  
> Relay\_Log\_Pos: 878  
> Relay\_Master\_Log\_File: mysql-bin.000003  
> Slave\_IO\_Running: Yes  
> Slave\_SQL\_Running: Yes   
> ……

上面结果中的 `Slave_IO_Running:Yes 和 Slave_SQL_Running:Yes`表示这两个线程正在运行。

然后我们在从服务器上面使用命令：

> mysql> SHOW PROCESSLIAT

显示如下结果（记为 **结果一**）

> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
>      Id: 22  
>    User: system user  
>    Host:  
>      db: NULL  
> Command: Connect  
>    Time: 4  
>   State: Waiting for master to send event  
>    Info: NULL  
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
>      Id: 23  
>    User: system user  
>    Host:  
>      db: NULL  
> Command: Connect  
>    Time: 4  
>   State: Slave has read all relay log; waiting for the slave I/O thread to update it  
>    Info: NULL

从State信息可以看出**Id 22**是I/O线程，正在等待主服务器发送更新的内容；`Id 23`是Slave SQL线程，已经读取了`relay log` 文件中所有更新的内容，正在等待I/O线程更新该文件。

使用命令停止slave机制：

> mysql> STOP SLVAE;

然后我们再次查看会发现结果如下：

说明这两个线程已经停止了运行。此时再次使用 `SHOW PROCESSLIST` 命令，则没有结果显示。

**1、Slave I/O线程：**

Slave I/O 线程去连接主服务器的Binlog dump 线程并要求其发送binlog日志中记录的更新的操作，然后它将Binlog dump 线程发送的数据拷贝到从服务器上（也就是本地）的文件 relay log 文件中。

当然要查看此线程是否运行，除了上面介绍的方法，还可以使用

> mysql> SHOW SLAVE LIKE ‘Slave\_running’;

这时如果出现下面的结果说明该线程正在运行

> \+-----------------+-------------------+  
> | Variable\_name   | Value         |  
> \+-----------------+-------------------+  
> | Slave\_running   | ON              |  
> \+-----------------+-------------------+

在上述结果一中我们可以看到1.row即是Slave I/O线程的信息，其State: Waiting for master to send event 表示正在等待主服务器发送内容。当然State不止这一个值，它还有其它的值，下面列出了State的所有的值

> 1. Waiting for master update  
> 在连接到主服务器之前的初始状态
> 
> 2. Connecting to master  
> 该线程正在连接主服务器，当然如果我们的网络环境优异的话，此状态我们几乎是看不到的
> 
> 3. Checking master version  
> 这个状态发生的时间也非常短暂，该状态在该线程和主服务器建立连接之后发生。
> 
> 4. Registering slave on master  
> 在主服务器上面注册从服务器，每当有新的从服务器连接进来以后都要在主服务器上面进行注册
> 
> 5. Requesting binlog dump  
> 向主服务器请求binlog日志的拷贝
> 
> 6. Waiting to reconnect after a failed binlog dump request  
> 如果5中失败，则该线程进入睡眠状态，此时State就是这个值，等待着去定期重新连接主服务器，那这个周期的大小可以通过CHANGE MASTER TO 来指定
> 
> 7. Reconnecting after a failed binlog dump request  
> 去重新连接主服务器
> 
> 8. Waiting for master to send event  
> 此值就是我们上述结果所显示的，正常情况下我们查看的时候一般都是这个值。其具体表示是这个线程已经和主服务器建立了连接，正在等待主服务器上的binlog 有更新，如果主服务器的Binlog dump线程一直是空闲的状态的话，那此线程会等待很长一段时间。当然也不是一直等待下去，如果时间达到了slave\_net\_timeout规定的时间，会发生等待超时的情况，在这种情况下I/O线程会重新去连接主服务器
> 
> 9. Queueing master event to the relay log  
> 该线程已经读取了Binlog dump线程发送的一个更新事件并且正在将其拷贝到relay log文件中
> 
> 10. Waiting to reconnect after a failed master event read  
> 当该线程读取Binlog dump 线程发送的更新事件失败时，该线程进入睡眠状态等待去重新连接主服务器，这个等待的时间默认是60秒，当然这个值也可以通过CHANGE MASTER TO来设置
> 
> 11. Reconnecting after a failed master event read  
> 该线程去重新连接主服务器，当连接成功以后，那这个State的值会改变为 Waiting for master to send event
> 
> 12. Waiting for the Slave SQL thread to free enough relay log space  
> relay log space的大小是通过relay\_log\_space\_limit来设定的，随着relay logs变得越来越大所有的大小合起来会超过这个设定值。这时该线程会等待SQL线程释放足够的空间删除一些relay log文件
> 
> 13. Waiting for slave mutex on exit  
> 当线程停止的时候会短暂的出现该情况

**2、Slave SQL线程：**

Slave SQL线程是在从服务器上面创建的，主要负责读取由Slave I/O写的 relay log 文件并执行其中的事件。

在上述结果一中2.row即是Slave SQL线程的信息，同样有一个State表示该线程的当前状态。

下面也列出了State所有可能出现的情况。

> 1. Waiting for the next event in relay log  
> 该状态是读取relay log之前的初始状态  
> 2. Reading event from the relay log  
> 该状态表示此线程已经在relay log中读取了一个事件准备执行  
> 3. Making temp file  
> 该状态表示此线程正在执行LOAD\_DATA\_INFILE并且正在创建一个临时文件来保存从服务器将要读取的数据  
> 4. Slave has read all relay log; waiting for the slave I/O thread to update it  
> 该线程已经处理完了relay log中的所有事件，现在正在等待slave I/O线程更新relay log文件  
> 5. Waiting for slave mutex on exit  
> 当线程停止的时候会短暂的出现该情况

上面是对从服务器上的两个线程的简单的介绍，在运行过程中我们会发现这两个线程都离不开的文件就是relay log文件，下面我们简单介绍一下relay log文件。

### 六、如何提高Mysql主从复制的效率： ###

对于提升MySQL的主从复制的复制效率，binlog的大小是非常重要的因素，因为它涉及了I/O和网络传输，所以我们主要看一下master/slave这两端可以如何优化：

**1、master 端：**

master端有2个参数可以控制：

> Binlog\_Do\_DB : 设定哪些数据库需要记录Binlog
> 
> Binlog\_Ignore\_DB : 设定哪些数据库不要记录Binlog

这两项很重要，指定必要数据库，忽略不需要复制的数据库，可以减少binlog的大小，提高了I/O效率，加快网络传输。

但这两项也需要谨慎使用，因为可能会有主从数据不一致和复制出错的风险，。因为MySQL判断是否须要复制某个Event，不是根据产生该Event的语句所在的数据库，而是根据执行时所在的默认数据库，也就是登录时指定的数据库，或运行“USE DATABASE”中所指定的数据库。如果执行语句中明确指定了数据库名称，而这个数据库是被指定不记录Binlog的，那么这个语句在slave中执行时就会出错。

例如：

> garbage 库是被指定不记录日志的
> 
> product 库是指定要记录日志的

执行下面的语句：

> use product;
> 
> delete from garbage.junk;

delete语句会被发送给slave，但slave中没有garbage库，所以执行时报错，复制失败。

**2、slave端：**

slave端有6个参数可以控制：

> Replicate\_Do\_DB : 设定须要复制的数据库，多个DB用逗号分隔
> 
> Replicate\_Ignore\_DB : 设定可以忽略的数据库
> 
> Replicate\_Do\_Table : 设定须要复制的Table
> 
> Replicate\_Ignore\_Table : 设定可以忽略的Table
> 
> Replicate\_Wild\_Do\_Table : 功能同Replicate\_Do\_Table，但可以带通配符来进行设置
> 
> Replicate\_Wild\_Ignore\_Table : 功能同Replicate\_Ig-nore\_Table，可带通配符设置

slave端的配置优化效果要明显小于master端的，因为master端日志都写完了，日志也传过来了。

但这几个参数可以帮助我们减少日志的应用量，因为设置了过滤，实际写入的sql数量变少了，slave端的复制也就加快了

[http_blog.csdn.net_xlgen157387_article_details_51331244_comments]: http://blog.csdn.net/xlgen157387/article/details/51331244#comments
[http_blog.csdn.net_xlgen157387_article_details_52452394]: http://blog.csdn.net/xlgen157387/article/details/52452394
[20160906191825438]: /images/20220402/6498c78d8bd946f1943d0b40e3bb6257.png
[20160906193915196]: /images/20220402/27c1c8f09db140e7be37dae4b41c9fc1.png