• 环境基于Centos7.6

1.作用

slave_master_info、slave_relay_log_info、slave_worker_info表，用于从服务器上存储复制信息，这三张表都使用InnoDB存储引擎。

slave_master_info：该表提供查询IO线程读取主库的位置信息，以及从库连接主库的IP、账号、端口、密码等信息。

slave_relay_log_info：该表提供查询SQL线程重放的二进制文件对应的主库位置和relay log当前最新的位置。

slave_worker_info：该表提供查询多线程复制时的worker线程状态信息。

2.详解

1>slave_master_info

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
下面是该表中存储的信息内容。

root@localhost : mysql 01:08:29> select * from slave_master_info\G;
*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000292
        Master_log_pos: 194
                  Host: 192.168.2.148
             User_name: qfsys
         User_password: letsg0
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 5
                  Bind:
    Ignored_server_ids: 0
                  Uuid: ec123678-5e26-11e7-9d38-000c295e08a0
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 0
          Channel_name:
           Tls_version:
1 row in set (0.00 sec)

表字段与show slave status输出字段、master.info文件中的行信息对应关系及其表字段含义如下：

master.info文件中的行数mysql.slave_master_info表字段show slave status命令输出字段字段含义描述1Number_of_lines[None]表示master.info中的信息行数或者slave_master_info表中的信息字段数2Master_log_nameMaster_Log_File表示从库IO线程当前读取主库最新的binlog file名称3Master_log_posRead_Master_Log_Pos表示从库IO线程当前读取主库最新的binlog position4HostMaster_Host表示从库IO线程当前正连接的主库IO或者主机名5User_nameMaster_User表示从库IO线程用于连接主库用户名6User_password[None]表示从库IO线程用于连接主库的用户密码7PortMaster_Port表示从库IO线程所连接主库的网络端口8Connect_retryConnect_Retry表示从库IO线程断线重连主库的间隔时间，单位为秒，默认值为609Enabled_sslMaster_SSL_Allowed表示主从之间的连接是否支持SSL10Ssl_caMaster_SSL_CA_File表示CA(Certificate Authority )认证文件名11Ssl_capathMaster_SSL_CA_Path表示CA(Certificate Authority )认证文件路径12Ssl_certMaster_SSL_Cert表示SSL认证证书文件名13Ssl_cipherMaster_SSL_Cipher表示用于SSL连接握手中可能使用到的密码列表14Ssl_keyMaster_SSL_Key表示SSL认证的密钥文件名15Ssl_verify_server_certMaster_SSL_Verify_Server_Cert表示是否需要校验server的证书16Heartbeat[None]表示主从之间的复制心跳包的间隔时间，单位为秒17BindMaster_Bind表示从库可用于连接主库的网络接口，默认为空18Ignored_server_idsReplicate_Ignore_Server_Ids表示从库复制需要忽略哪些server-id，注意：这是一个列表，第一个数字表示需要忽略的实例server-id总数19UuidMaster_UUID表示主库的UUID20Retry_countMaster_Retry_Count表示从库最大允许重连主库的次数21Ssl_crl[None]SSL证书撤销列表文件的路径22Ssl_crl_path[None]包含ssl证书吊销列表文件的目录路径23Enabled_auto_positionAuto_position表示从库是否启用在主库中自动寻找位置的功能（使用1时启动自动寻找位置，如果使用auto_position=0，则不会自耦东找位置）24Channel_nameChannel_name表示从库复制通道名称，一个通道代表一个复制源25Tls_VersionMaster_TLS_Version表示在Master上的TLS版本号。

2>slave_relay_log_info

1
2
3
4
5
6
7
8
9
10
11
12
13
14
下面是该表中存储的信息内容。

root@localhost : mysql 10:39:31> select * from slave_relay_log_info\G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000205
    Relay_log_pos: 14097976
  Master_log_name: mysql-bin.000060
   Master_log_pos: 21996812
        Sql_delay: 0
Number_of_workers: 16
               Id: 1
     Channel_name:
1 row in set (0.00 sec)

表字段与show slave status输出字段、relay-log.info文件中的行信息对应关系及其表字段含义如下：

relay-log.info文件中的行数mysql.slave_relay_log_info表字段show slave status命令输出字段字段含义描述1Number_of_lines[None]表示relay-log.info中的信息行数或者slave_relay_log_info表中的信息字段数，用于版本化表定义2Relay_log_nameRelay_Log_File表示当前最新的relay log文件名称3Relay_log_posRelay_Log_Pos表示当前最新的relay log文件对应的最近一次完整接收的event的位置4Master_log_nameRelay_Master_Log_File表示SQL线程当前正在重放的中继日志对应的主库binlog 文件名5Master_log_posExec_Master_Log_Pos表示SQL线程当前正在重放的中继日志对应主库binlog 文件中的位置6Sql_delaySQL_Delay表示延迟复制指定的从库必须延迟主库多少秒7Number_of_workers[None]表示从库当前并行复制有多少个worker线程8Id[None]用于内部唯一标记表中的每一行记录，目前总是19Channel_nameChannel_name表示从库复制通道名称，用于多源复制，一个通道对应一个主库源

什么是中继日志：

中继日志(relay log)与二进制日志(binlog，即，binary log)中，保存的event数据是一样的(但中继日志中还保存了更多的信息)，也是由一组包含描述数据库变更的事件数据的文件组成，这些文件名后缀带连续编号，此外，还有一个包含所有正在使用的中继日志文件名称的索引文件。
中继日志中的数据存放格式与二进制日志相同，都可以使用mysqlbinlog命令来提取数据，默认情况下，中继日志保存在datadir下，文件名格式为：host_name-relay-bin.nnnnnn，其中host_name是从库服务器主机名，nnnnnn是文件后缀序列号。连续的中继日志文件从000001开始的连续序列号创建。使用索引文件来跟踪当前正在使用的中继日志文件。默认的中继日志索引文件名保存在datadir下，文件名格式为：host_name-relay-bin.index。

  • 中继日志文件和中继日志索引文件名称可分别使用–relay-log和–relay-log-index参数选项指定值覆盖默认值，如果文件名使用默认值，则要注意主机名称不能修改，否则会报无法打开中继日志的错误，建议使用参数选项指定固定的文件名称前缀。如果已经出现了这种情况发生报错了，那么需要修改index文件中的中继日志文件名和datadir下的中继日志文件名前缀为新的主机名，然后重启从库。
    在什么情况下会产生新的中继日志文件。

I/O线程启动时。
使用语句：FLUSH LOGS或mysqladmin flush-logs命令时。
当前中继日志文件的大小变得“太大”时，日志滚动规则如下：

  • 如果max_relay_log_size系统变量的值大于0，那么中继日志按照此参数指定的大小进行滚动。
  • 如果max_relay_log_size系统变量的值为0，则中继日志按照max_binlog_size系统变量指定的大小进行滚动。
    SQL线程在执行完relay log之后，会自行决定何时清理掉这些已经执行完成的relay log文件，但如果使用FLUSH LOGS语句或mysqladmin flush-logs命令强制滚动中继日志时，SQL线程可能会同时清理掉已经执行完成的relay log文件。

3>slave_worker_info

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
slave_worker_info与performance_schema.replication_applier_status_by_worker表的区别是：slave_worker_info表记录worker线程重放的relay log和主库binlog位置信息，而performance_schema.replication_applier_status_by_worker表记录的是worker线程重放的GTID位置信息。
下面是该表中存储的信息内容。

root@localhost : mysql 01:09:39> select * from slave_worker_info limit 1\G;
*************************** 1. row ***************************
                        Id: 1
            Relay_log_name:
             Relay_log_pos: 0
           Master_log_name:
            Master_log_pos: 0
 Checkpoint_relay_log_name:
  Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
 Checkpoint_master_log_pos: 0
          Checkpoint_seqno: 0
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap:
              Channel_name:
1 row in set (0.00 sec)

表字段含义：

Id：表中数据的ID，也是worker线程的ID，对应着performance_schema.replication_applier_status_by_worker表的WORKER_ID字段（如果复制停止，则该字段值仍然存在，不像performance_schema.replication_applier_status_by_worker表中THREAD_ID字段值会清空）。
Relay_log_name：每个worker线程当前最新执行到的relay log文件名。
Relay_log_pos：每个worker线程当前最新执行到的relay log文件中的position。
Master_log_name：每个worker线程当前最新执行到的主库binary log文件名。
Master_log_pos：每个worker线程当前最新执行到的主库binary log文件中的position。
Checkpoint_relay_log_name：每个worker线程最新检查点的relay log文件名。
Checkpoint_relay_log_pos：每个worker线程最新检查点的relay log文件中的position。
Checkpoint_master_log_name：每个worker线程最新检查点对应主库的binary log文件名。
Checkpoint_master_log_pos：每个worker线程最新检查点对应主库的binary log文件中的position。
Checkpoint_seqno：每个worker线程当前最新执行完成的事务号，这个事务号的大小值是相对于每个worker线程自己的最新检查点而言的，并不是真正的事务号。
Checkpoint_group_size：表示每个worker线程的执行队列大于这个字段值时，就会触发当前worker线程执行一次检查点。
Checkpoint_group_bitmap：用于从库crash之后recovery的关键值，它是一个位图值，表示每个worker线程在自己的最新检查点中已经执行的事务。
Channel_name：复制通道名称，多主复制时，显示指定的复制通道名称，单主复制时该字段为空。
该表中记录的内容对从库多线程复制crash recovery至关重要，所以下文对该表中记录的内容如何作用于crash recovery过程进行一些必要的说明。

从库多线程复制如何做复制分发。

我们知道在MySQL 5.7中加入了基于事务的并行复制(基于行)，主库在binlog的GTID事件中新加入了last_commit和sequence_number标记，用于表示在每个binlog中的每个group中的提交顺序(每个binlog中重置这两个计数标记)，在每个给定的binlog中，每个group中的last_commit总是为上一个group中最大的sequence_number、总是为当前group中最小的sequence_number - 1(在每个binlog中，last_commit总是从0开始计数，sequence_number总是从1开始计数)。
从库relay log中记录的主库binlog，不会改变主库的server id、时间戳信息以及last_commit和sequence_number值，这样，从库SQL线程在执行binlog重放时，就可以依据这些信息决定从库是否需要严格按照主库提交顺序进行提交（从库重放的事务只是分发顺序按照主库提交顺序，但是从库自己在提交这些事务时是否按照主库提交顺序进行提交，还需要看从库自己的slave_preserve_commit_order变量设置，设置为1则严格按照relay log中的顺序进行提交，设置为0从库会自行决定提交顺序）。
SQL线程并行分发原理。

  • SQL协调器线程读取到一个新的事务，取出last_commit和sequence_number值。
  • SQL协调器线程判断取出的新事务的当前last_commit是否大于当前已执行完成的sequence_number中的最小值（Low water mark，简称LWM，也叫低水位线标记）。
  • 如果SQL协调器线程读取到的当前事务的last_commit大于当前已执行完成的sequence_number值，则说明上一个group中的事务还没有全部执行完成，此时SQL协调器线程需要等待所有的worker线程执行完成上一个group中的事务，等待LWM变大，直到当前读取到的事务的last_commit与当前已执行完成的事务的最小sequence_number值相等才可以继续分发新的事务给空闲的worker线程（并行复制是针对每个group内的事务才可以并行复制，所以，group之间是串行的，一个group未执行完成之前，下一个group的事务是需要进行等待的。只有同一个group内的事务之间才可以并行执行。根据上文中的描述，每个group中的事务的last_commit总是为当前group中最小的sequence_number - 1，即，如果SQL协调器线程读取到的当前事务的last_commit小于当前已执行完成事务的最小的sequence_number 就说明当前所有worker线程正在执行的事务处于同一个group中，那么也就是说SQL协调器线程可以继续往下寻找空闲的worker线程进行分发，否则SQL协调器线程就需要进行等待）。
  • SQL协调器线程通过统计worker线程返回的状态信息，寻找一个空闲的worker线程，如果没有空闲的线程，则SQL协调器线程需要进行等待，知道找到一个空闲的worker线程为止（如果有多个worker线程，则SQL协调器线程随机选择一个空闲的worker线程进行分发）。
  • 将当前读取到的事务的binlog event分发给选定的空闲worker线程，之后worker线程会去应用这个事务，然后SQL协调器线程继续读取新的binlog event（注意，SQL协调器线程分发是按照event为单位的，不是事务单位，所以，如果当一个事务的第一个event分发给了给定worker线程之后，后续读取到的新的event如果同属于一个事务，则进入下一个事务之前的所有event都会分发给同一个worker线程处理。当一个事务中所有的binlog event组分发完成，读取到下一个新的事务时，SQL协调器线程会重复以上判断流程）。
    从库多线程复制的crash recovery。

从前面多线程复制分发的原理我们可以知道，处于同一个group中的事务是并行应用的，且事务是随机分配的，在从库正常运行过程当中，如果任意掐一刻下去，那么所有worker线程正在执行的事务中，哪些是已经执行完成的，哪些还未执行完成其实是无法使用单个位置来确定（因为从库并行复制时有可能是乱序提交：需要看slave_preserve_commit_order变量如何设置），也就是说所有worker线程中正在执行的最大位置和最小位置之间可能有断点。那MySQL是如何解决从库crash recovery的断点续做问题的呢？
MySQL 为了解决这个问题，对worker线程的执行状态做了很多记录工作，首先，维护了一个队列，这个队列叫做GAQ（Group Assigned Queue），当SQL协调器线程在分配某一个事务时，首先会将这个事务加入到这个队列，然后，才会去按照规则来寻找一个空闲的worker线程来执行，如下图（郑重声明：该图来自书籍《MySQL 运维内参》）：

数值类型

每一个事务在分发到worker线程之后，都会分配一个编号，这个编号在某一段时间内，都是相对固定的，这个编号一旦被分配，就不会再改变。在事务被某个worker线程执行完成之后，它的位置信息就会被flush一次，这与5.5版本中的relay_log_info记录的原理是类似的（relay_log_info中存放了从库当前SQL线程重放的位置），但是现在是多线程，每个worker线程的执行位置不能直接存放在relay_log_info中了，relay_log_info中存放的是所有worker线程汇总之后的位置，每个worker线程独立的位置信息存放在了mysql.slave_worker_info表中，在该表中，有多少个并行复制线程，就有多少行记录（如果是多主复制，则每个复制通道都有slave_parallel_workers变量指定的记录数）。

mysql.slave_worker_info表中，Checkpoint开头的字段记录了每个worker线程的检查点相关的信息（这里与innodb存储引擎的检查点不同，但是概念相通），worker线程的检查点的作用是什么呢？

前面说了SQL协调器线程在分配事务给worker线程之前会将事务先存放到GAQ队列中，但是这个队列的长度是有限的（是不是很熟悉？跟redo log的总大小是有限的概念类似），不可能无限制的增长下去，所以必须要在这个队列中，找到一个位置点，这个位置点就是GAQ的起点位置，这个位置点之前的binlog就表示已经执行完成了。确定这个位置的过程，就叫做检查点。在多线程复制的执行过程中，随着每个worker线程不断第应用事务的binlog，检查点在GAQ中被不断地向前推进，每个worker线程通过Checkpoint_point_bitmap字段记录自己已经执行过的事务和每个已执行事务与之对应的当时的最新检查点的相对位置，这样一来，当复制意外终端之后，重新开始复制时，就可以通过所有的worker线程记录的Checkpoint_point_bitmap字段来计算出哪些事务是已经执行过的，哪些事务是还未执行的，即通过所有worker线程记录的Checkpoint_point_bitmap信息执行一次检查点操作就可以找到一个合适的恢复位置，执行检查点的大概过程如下（注意：这里是执行检查点的过程，与从库crash recovery过程无关）：

  • 在GAQ队列中，从尾部开始扫描，如果是已经执行过的事务，则直接将其从队列中删除。
  • 持续扫描GAQ队列，直到找到一个未执行过的事务为止即停止扫描。
  • 上述步骤中扫描动作停止前扫描到的最后一个事务被确定为检查点的最新位置，并且别标记为LWM（低水位线标记）。
  • 将当前LWM这个事务对应的位置(master_log_pos和relay_log_pos位置)设置为此次检查点对应的位置。
  • 通过所有的worker线程检查自己的检查点，也就是查看每个worker线程自己的Checkpoint_seqno字段值，这个字段值是每个worker线程在执行事务提交时更新的，更新的字段值为每个worker线程在做事务提交时对应的最新检查点的相对位置。
  • 将本次执行检查点的位置记录到mysql.slave_relay_log_info表中，作为全局binlog应用的位置。
    现在，我们来看从库crash recovery的过程：
  • 首先，读取mysql.slave_master_info、mysql.slave_relay_log_info、mysql.slave_worker_info表中的信息读取出来，从mysql.slave_master_info表中找到连接主库的信息，从mysql.slave_relay_log_info表中找到全局最新的复制位置以及worker线程个数，从mysql.slave_worker_info表中找到每一个worker线程对应的复制信息位置。
  • 然后，根据mysql.slave_relay_log_info表中的位置（这个位置就是全局最新的检查点位置）为准来判断所有worker线程的位置，在这个位置之前的worker线程位置就表示已经执行过的了，直接剔除，在这个位置之后的worker线程位置就表示这些事务是还没有执行过的（根据每个worker线程在mysql.slave_worker_info表中记录的Checkpoint_seqno和Checkpoint_group_bitmap字段计算出自己哪些事务没有执行过，然后通过每个worker线程在mysql.slave_worker_info表中记录的其他checkpoint字段信息转换为对应的全局检查点的位置。然后根据所有worker线程的转换位置信息汇总为一个共同的bitmap，根据这个共同的bitmap来比对mysql.slave_relay_log_info表中的位置就可以提取出哪些事务还没有执行过），找出了哪些事务还没有执行之后，把这些事务串行地一个一个地去重新应用（应用一个更新一次mysql.slave_relay_log_info表，为什么要串行，这是为了在恢复过程中如果再次跪了，还可以正确地恢复位置），应用完成之后清空mysql.slave_worker_info表。然后启动复制线程，继续从主库拉取最新的binlog进行数据复制。
    PS：如果在主从复制架构中，有2个以上的从库，且从库永远不做提升主库的操作时，可以使用如下方法优化从库延迟（在该场景下，从库无需担心数据丢失问题，因为有另外一个从库兜底+不做主从切换，只需要专心提供快速应用主库binlog与只读业务即可）。

关闭log_slave_updates参数，减少从库binlog写入量（如果不做级联复制甚至可以同时关闭binlog）。
设置innodb_flush_log_at_trx_commit为0或者2，减少事务提交时redo log的等待频率。
设置sync_binlog为默认值或者更大的值，减少事务提交时binlog的等待频率。
设置slave_preserve_commit_order参数为OFF（默认为OFF，设置为ON时要求开启binlog和log_slave_updates参数），减少事务严格按照主库顺序提交时的提交等待时间。