Mysql 主从复制细节和binlog原理

Mysql一次主从复制需要有三个线程来实现，其中一个线程（Binlog dump thread）在主服务器上，其它两个线程（Slave I/O thread , Slave SQL thread）在从服务器上（如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程）

主服务器线程 Binlog dump thread

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

image.png

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

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

mysql> SHOW PROCESSLIST\G

 Id: 141
   User: xxx
   Host: server-3:55236
     db: NULL
Command: Binlog Dump
   Time: 963
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

如果有两台从服务器，会列出两条Binlog dump线程的信息

上述字段中的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 线程.

因为Binlog dump 线程操作的文件是bin-log 日志文件，并且实现主从复制在主服务器上主要依靠bin-log日志文件，所以我们简单介绍一下bin-log日志文件。

Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！

Statement-Based优点和缺点分析

优点
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点

1. 不安全，并不是所有的改变数据的语句都会被记录复制。任何的非确定性的行为都是很难被记录复制的。

例如：对于delete 或者update语句，如果使用了limit但是并没有 order by ，这就属于非确定性的语句，就不会被记录

由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

Row-Based优点和缺点分析

优点

1. 所有的改变都会被复制，这是最安全的复制方式
   2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

对比实战

为了便于描述binlog的这三种格式间的区别，我创建了一个表，并初始化几行数据。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

如果要在表中删除一行数据的话，我们来看看这个delete语句的binlog是怎么记录的。

mysql> delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

当binlog_format=statement时(默认为format，set session binlog_format='STATEMENT';可在线动态调整)，binlog里面记录的就是SQL语句的原文。你可以用

mysql> show binlog events in 'master.000001'; // 看具体binlog文件

命令看binlog中的内容。

| mysql-bin.000005 | 1529 | Anonymous_Gtid |    123454 |        1594 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000005 | 1594 | Query          |    123454 |        1685 | BEGIN                                                                         |
| mysql-bin.000005 | 1685 | Query          |    123454 |        1834 | use `binlog`; delete from t   where a>=4 and t_modified<='2018-11-10' limit 1 |
| mysql-bin.000005 | 1834 | Xid            |    123454 |        1865 | COMMIT /* xid=85 */

• 第一行SET @@SESSION.GTID_NEXT=’ANONYMOUS’可以先忽略.
• 第二行是一个BEGIN，跟第四行的commit对应，表示中间是一个事务；
• 第三行就是真实执行的语句了。可以看到，在真实执行的delete命令之前，还有一个“use ‘binlog’”命令。这条命令不是我们主动执行的，而是MySQL根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到binlog库的表t。
  use ‘binlog’命令之后的delete 语句，就是我们输入的SQL原文了。可以看到，binlog“忠实”地记录了SQL命令。
• 最后一行是一个COMMIT。你可以看到里面写着xid=85。

为了说明statement 和 row格式的区别，我们来看一下这条delete命令的执行效果图：

image.png

可以看到，运行这条delete命令产生了一个warning，原因是当前binlog设置的是statement格式，并且语句中有limit，所以这个命令可能是unsafe的。

为什么这么说呢？这是因为delete 带limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

1. 如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行；
2. 但如果使用的是索引t_modified，那么删除的就是 t_modified=’2018-11-09’也就是a=5这一行。

由于statement格式下，记录到binlog里的是语句原文，因此可能会出现这样一种情况：在主库执行这条SQL语句的时候，用的是索引a；而在备库执行这条SQL语句的时候，却使用了索引t_modified。因此，MySQL认为这样写是有风险的(会存在这种情况吗？)。

那么，如果我把binlog的格式改为binlog_format=‘row’， 是不是就没有这个问题了呢？我们先来看看这时候binog中的内容吧。

 | mysql-bin.000005 | 2140 | Anonymous_Gtid |    123454 |        2205 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                          |
| mysql-bin.000005 | 2205 | Query          |    123454 |        2287 | BEGIN                                                                         |
| mysql-bin.000005 | 2287 | Table_map      |    123454 |        2336 | table_id: 108 (binlog.t)                                                      |
| mysql-bin.000005 | 2336 | Delete_rows    |    123454 |        2384 | table_id: 108 flags: STMT_END_F                                               |
| mysql-bin.000005 | 2384 | Xid            |    123454 |        2415 | COMMIT /* xid=107 */

可以看到，与statement格式的binlog相比，前后的BEGIN和COMMIT是一样的。但是，row格式的binlog里没有了SQL语句的原文，而是替换成了两个event：Table_map和Delete_rows。

• Table_map event，用于说明接下来要操作的表是test库的表t;
• Delete_rows event，用于定义删除的行为。

其实，我们通过上面显示的event是看不到详细信息的，还需要借助mysqlbinlog工具，用下面这个命令解析和查看binlog中的内容。因为上面的信息显示，这个事务的binlog是从2140这个位置开始的，所以可以用start-position参数来指定从这个位置的日志开始解析。

sudo mysqlbinlog --no-defaults -vv mysql-bin.000005 --start-position=2140;

BEGIN
/*!*/;
# at 2287
#200418 18:14:53 server id 123454  end_log_pos 2336 CRC32 0x0556c96f    Table_map: `binlog`.`t` mapped to number 108
# at 2336
#200418 18:14:53 server id 123454  end_log_pos 2384 CRC32 0x0e66c718    Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
HdOaXhM+4gEAMQAAACAJAAAAAGwAAAAAAAEABmJpbmxvZwABdAADAwMRAQACb8lWBQ==
HdOaXiA+4gEAMAAAAFAJAAAAAGwAAAAAAAEAAgAD//gEAAAABAAAAFvlrwAYx2YO
'/*!*/;
### DELETE FROM `binlog`.`t`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1541779200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 2384
#200418 18:14:53 server id 123454  end_log_pos 2415 CRC32 0x069c96c8    Xid = 107
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

我们可以看到以下几个信息：

• server id 123454，表示这个事务是在server_id=123454的这个库上执行的。
• 每个event都有CRC32的值，这是因为我把参数binlog_checksum设置成了CRC32。
• Table_map event跟在图5中看到的相同，显示了接下来要打开的表，map到数字108。现在我们这条SQL语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。
• 我们在mysqlbinlog的命令中，使用了-vv参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4这些值）。
• binlog_row_image的默认配置是FULL，因此Delete_event里面，包含了删掉的行的所有字段的值。如果把binlog_row_image设置为MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录id=4这个信息。
• 最后的Xid event，用于表示事务被正确地提交了。

你可以看到，当binlog_format使用row格式的时候，binlog里面记录了真实删除行的主键id（这里是@1=4），这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。

为什么会有mixed格式的binlog？

基于上面的信息，我们来讨论一个问题：为什么会有mixed这种binlog格式的存在场景？推论过程是这样的：

• 因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式。
• 但row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。
• 所以，MySQL就取了个折中方案，也就是有了mixed格式的binlog。mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

也就是说，mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。

因此，如果你的线上MySQL设置的binlog格式是statement的话，那基本上就可以认为这是一个不合理的设置。你至少应该把binlog的格式设置为mixed。

比如我们这个例子，设置为mixed后，就会记录为row格式；而如果执行的语句去掉limit 1，就会记录为statement格式。

当然我要说的是，现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。

通过上面row格式演示你可以看出来，即使我执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了insert语句呢？那就更直接了。row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉这被误插入的一行数据就可以了。

如果执行的是update语句的话，binlog里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

其实，由delete、insert或者update语句导致的数据操作错误，需要恢复到操作之前状态的情况，也时有发生。MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的。

从服务器

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

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

image.png

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

mysql> START SLVAE;

然后使用

Mysql> SHOW SLAVE STATUS\G

查看这两个线程情况

 Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 2245
               Relay_Log_File: server-3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

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

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

mysql> SHOW PROCESSLIAT\G

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

************************** 4. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3704
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 12
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3704
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

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

使用命令停止slave机制

mysql> STOP SLVAE;

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

……
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: No
Slave_SQL_Running: No
……

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

Slave I/O线程

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

在上述结果一中我们可以看到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
    当线程停止的时候会短暂的出现该情况

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文件。

relay log文件

relay log 和 主服务器上的bin log很相似，都是一系列的文件，这些文件包括那些包含描述数据库改变的操作事件的文件和索引文件，这个索引文件是relay logs文件的名称集合.

relay log 文件和 bin log文件一样，也是二进制文件，不能直接查看，需要使用mysql自带工具mysqlbinlog查看。

这些relay log文件并不是一直在增加的，当Slave SQL线程执行完一个relay log文件中所有的事件并且不再需要它的时候会把改relay log文件删除。由于是Slave SQL线程来做这些事情，所以也没有什么明确的规则来指定如何删除relay log文件.

再看看官方文档上的说明： https://dev.mysql.com/doc/refman/5.7/en/replication-implementation-details.html

MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:
**Binlog dump thread. ** The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

**Slave I/O thread. ** When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.

The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Slave SQL thread. The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.

In the preceding description, there are three threads per master/slave connection. A master that has multiple slaves creates one binary log dump thread for each currently connected slave, and each slave has its own I/O and SQL threads.

A slave uses two threads to separate reading updates from the master and executing them into independent tasks. Thus, the task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is stored locally in the slave's relay logs, ready for execution the next time that the slave starts.

The following example illustrates how the three threads show up in the output from SHOW PROCESSLIST.

On the master server, the output from SHOW PROCESSLIST looks like this:

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

Here, thread 2 is a Binlog Dump replication thread that services a connected slave. The State information indicates that all outstanding updates have been sent to the slave and that the master is waiting for more updates to occur. If you see no Binlog Dump threads on a master server, this means that replication is not running; that is, no slaves are currently connected.

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

The State information indicates that thread 10 is the I/O thread that is communicating with the master server, and thread 11 is the SQL thread that is processing the updates stored in the relay logs. At the time that SHOW PROCESSLIST was run, both threads were idle, waiting for further updates.