MySQL基于GTID主从复制的杂谈

前言

系列文章：
1.MySQL主从复制
2.OneProxy实现MySQL读写分离
3.MySQL数据库结构设计
4.MySQL基于GTID主从复制的杂谈

先来回顾一下MySQL的二进制知识点。基于Row格式的日志可以避免MySQL主从复制中出现的主从不一致问题。在一个sql语句修改了1000条数据的情况下，基于段的日志格式只会记录这个sql语句。而基于row的日志格式会有1000条记录来记录每一行的数据修改。

MySQL官方推荐基于row的日志格式，优点如下：
1.使MySQL主从复制 更加安全。
2.对每一行数据的修改比基于段的复制更加高效。

有时候我们进行误操作而修改了数据库中的数据，同时又没有备份可以恢复时，我们可以通过分析binlog，对日志中记录的数据修改操作做反向处理来达到恢复数据的目的。

基于row格式的日志量会比较大，我们可以通过
配置binlog_row_image=full|minimal|noblob。full模式会记录所有的列。minimal模式智慧记录被修改的列。noblob模式在没有对text、blob列进行修改时不会记录text、blob列。

推荐日志采用mixed格式，它具有以下优点：
1.根据sql语句，系统决定在基于段和基于行的日志格式中进行选择。
2.数据量的大小由所执行的sql语句决定。

由于日志的格式原因，复制又分为：
基于sql语句的复制(SBR)：二进制日志格式使用的是statement格式。
基于行的复制(RBR):二进制日志格式使用的是基于行的日志格式。
混合模式：根据实际内容在以上两者进行切换。

SBR的优点：
1.生成的日志量较少，节约网络传输IO。
2.并不强制要求主从数据库的表定义完全相同。
3.相比于基于row的复制模式更加灵活。

SBR的缺点：
1.对非确定性时间，无法保证主从复制数据的一致性，比如uuid()。从而导致主从数据一致性，造成复制链路中断。
2.对于存储过程，触发器，自定义函数进行的修改也可能造成数据不一致。
3.相比基于行的复制方式在执行时需要更多的行锁。

RBR的优点：
1.可以应用任何sql的复制包括非确定函数,存储过程。
2.可以减少数据库锁的使用。

RBR的缺点：
1.要求主从数据库的表结构相同，否则可能会发生复制中断。
2.无法再slave上单独执行触发器。

• 基于sql段的日志是slave上重新执行binlog记录的sql。
• 基于row的日志则是在slave上直接应用对数据库的修改。

在slave配置中继日志的时候，如果不加mysql前缀的话，默认生成的中继日志格式是本机ip-mysql-replay-bin.000001。最好还是指定前缀。

relay_log=mysql-relay-bin

把中继日志的内容加入到slave的binlog中。也就是说slave的binlog会记录master同步的操作日志。

log-slave-updates=on

对数据库进行备份

mysqldump -utest -ptest rap_test > rap_test.dump

master-data=1或者2，其实没有什么区别。只是master-data=2,把change master注释掉了。指定single-transaction，备份时是不会加锁的，也不会阻止任何的事务，保证备份时数据一致性。想要实现在线备份，可以用xtrabackup工具。

mysqldump --single-transaction --master-data --triggers --routines --all-databases -utest -ptest > db.dump

image.png

下面来说说基于日志点复制和基于GTID复制的优缺点把。

基于日志点复制的优点：
1.MySQL最早支持的复制技术，BUG相对较少。
2.对sql查询没有什么限制。
3.故障处理比较容易。

基于日志点复制的缺点：
1.故障转移时重新获取master的日志点信息比较困难。基于日志点复制是从master的binlog的偏移量进行增量同步。如果指定错误会造成遗漏或者重复，造成主从不一致。

GTID是全局事务ID，其保证为每个在master上提交的事务在复制集群中可以生产一个唯一ID。GTID的生成策略是source_id(也就是server的uuid，在auto.conf文件里面可以看到):transaction_id(自增序列)。

[auto]
server-uuid=67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026

在基于GTID的复制中，slave会告诉master，slave已经执行事务的 GTID，master也会告诉slave，slave未执行事务的GTID。同一个事务只会在指定的从库执行一次。

基于GTID复制的优点是：

1.可以很方便的进行故障转移，记录master最后事务的GTID值。比如master：A，slave：B,C。当A挂了后，B执行了所有A传过来的事务。当C连接到B后，在自己的binlog找到最后一次A传过来的GTID。然后C将这个GTID发送给B，B获取到这个GTID，就开始从这个GTID的下一个GTID开始发送事务给C。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。

2.slave不会丢失master的任何修改(开启了log_slave_updates)

基于GTID复制的缺点：
1.不支持非事务引擎。
2.故障处理比较复杂，需要注入空事务。
3.不支持sql_slave_skip_counter(一般用这个来跳过基于binlog主从复制出现的问题。)
4.对执行的sql有一定的限制。
5.为了保证事务的安全性，create table ... select无法使用。不能使用create temporary table创建临时表。不能使用关联更新事务表和非事务表。

废话不BB了，直接进入正题。

这是我从网上copy的一张图。一般主从复制有3个线程参与，binlog dump(master),IO Thread(slave),sql Thread(slave)

image.png

基于GTID主从复制的步骤：
1.master数据改变时，会在事务前产生一个GTID，通过binlog dump记录到master的binlog中。
2.slave通过IO Thread将binlog中变更的数据，写入到slave的relay log中(中继日志)。
3.slave通过sql Thread读取relay log中的GTID，然后对比slave的binlog是否有此记录。
4.如果slave的binlog存在该GTID的记录，那么不好意思，忽略掉了。
5.如果slave的binlog不存在该GTID的记录，那么就执行该GTID事务，并一同记录到slave的binlog中。

slave配置

server-id=6
log-bin=mysql-bin
binlog_format=mixed
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on
#read_only=on
#innodb_read_only=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mysql-relay-bin
binlog_row_image=minimal
relay_log_recovery=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

master配置

# Binary Logging.
sync_binlog=1
log-bin=mysql-bin
binlog_format=mixed
binlog_row_image=minimal
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on

# Error Logging.
log-error="cmazxiaoma-mysql.err"

# Server Id.
server-id=21

在slave配置master相关的信息。

mysql> change master to 
    -> master_host="192.168.10.21",
    -> master_user="gtid",
    -> master_password="gtid",
    -> master_auto_position=0\g
Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.10.21
                  Master_User: gtid
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 21
                  Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 181108 11:26:16
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1-42135,
803785ec-db4e-11e8-987c-000c29c74079:1-6139
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

之前我在master上执行了reset master的命令，清除了binlog。首先IO线程读取主库的binlog将master数据变化写入到slave的relay log。然后sql线程从relay log获取gtid，与slave的binlog对比，发现slave比master的gtid数量还要多，所以抛出了1236错误。

我们在master执行SHOW GLOBAL VARIABLES LIKE '%gtid%'，发现gtid_executed和gtid_purged都为空。

image.png

我们在slave也同样执行SHOW GLOBAL VARIABLES LIKE '%gtid%'，发现gtid_executed为67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1-42135, 803785ec-db4e-11e8-987c-000c29c74079:1-6139

image.png

我们在slave执行reset master，再来看看gtid_executed的值，发现它清空了。

mysql> show global variables like '%gtid%'\G
*************************** 1. row ***************************
Variable_name: binlog_gtid_simple_recovery
        Value: ON
*************************** 2. row ***************************
Variable_name: enforce_gtid_consistency
        Value: ON
*************************** 3. row ***************************
Variable_name: gtid_executed
        Value: 
*************************** 4. row ***************************
Variable_name: gtid_executed_compression_period
        Value: 1000
*************************** 5. row ***************************
Variable_name: gtid_mode
        Value: ON
*************************** 6. row ***************************
Variable_name: gtid_owned
        Value: 
*************************** 7. row ***************************
Variable_name: gtid_purged
        Value: 
*************************** 8. row ***************************
Variable_name: session_track_gtids
        Value: OFF
8 rows in set (0.00 sec)

运行slave，查看slave的状态，一切正常。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.21
                  Master_User: gtid
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 751
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 21
                  Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

后来我不小心在master上执行了一段sqlINSERT INTO groupon.date_demo VALUES(NULL,NOW(),NOW(),NOW(),NOW(),NOW())，由于master和slave上的数据不一致(master上有groupon数据库，slave没有groupon数据库)，导致slave的SQL线程执行sql语句时，挂了。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.21
                  Master_User: gtid
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 537
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1' at master log mysql-bin.000001, end_log_pos 506. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 1134
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1' at master log mysql-bin.000001, end_log_pos 506. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 21
                  Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 181108 11:57:50
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

通过看slave的状态，我们可以抓住几条关键消息，来定位错误。
Retrieved_Gtid_Set是slave收到的事务信息，Executed_Gtid_Set是slave执行的事务消息。我们发现收到的事务信息是1,但是slave却没有执行任何事务消息。所以我判断是执行67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1事务消息报错。

           Retrieved_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1
            Executed_Gtid_Set

还有一条关键信息是Last_SQL_Error，它显示执行67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1事务消息报错。证明了我们上面的想法。如果Last_SQL_Error没有显示执行错误的事务信息，那该怎么办。我们可以通过查看log的方式，定位到事务信息GTID。

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1' at master log mysql-bin.000001, end_log_pos 506. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

还有一条关键消息是Relay_Master_Log_File: mysql-bin.mysql-bin.000001和Exec_Master_Log_Pos: 154。这是相对于主库，是从库的sql线程执行到的位置。也就是SQL线程读取Relay_Master_Log_File文件，执行完position为154的位置，发生异常。为了确认我的想法是对的，执行mysqlbinlog --no-defaults -v -v --base64-output=decode-rows --stop-position=200 mysql-bin.000001查看binlog日志，发现mysql-bin.000001文件中end_log_pos:154的后面没有消息了。按理来说执行完end_log_pos:154消息，再往下执行就会报错，会输出相关执行错误的事务信息。那为什么没有相关错误的事务消息呢？脑子里面仔细回想一下GTID主从复制的流程：slave通过读取relay log文件，执行GTID的事务并记录到slave的binlog中。由于执行GTID的事务失败，那么相关信息肯定不会记录到slave的binlog中。

image.png image.png

在Relay_Master_Log_File没有找到相关信息，不要紧。我们从Relay_Log_File里面找信息。Relay_Log_File是中继日志，相对于从库，记录着从库的sql线程执行到的位置。我们可以确定从库的sql线程执完中继日志的pos为367的位置发生异常。

               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 367

执行mysqlbinlog --no-defaults -v -v --base64-output=decode-rows --stop-position=390 mysql-relay-bin.000003，我们可以看到执行到end_log_pos为219的位置发生异常，异常事务消息gtid为67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1。

image.png

我们从master查看到binlog中pos为219的消息，可以看到GTID为67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1，证明我的猜想是正确的。

image.png

那如何定位错误事务GTID已经说完了，接下来是怎么解决错误了，我们可以注入空事务的方式跳过这个错误。

mysql> stop slave\G
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set gtid_next='67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1'\G
Query OK, 0 rows affected (0.00 sec)

mysql> begin\G
Query OK, 0 rows affected (0.00 sec)

mysql> commit\G
Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next="automatic"\G
Query OK, 0 rows affected (0.00 sec)

运行slave，查看状态，可以看到Retrieved_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1和Executed_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1数据一致，证明我们注入空事务成功，nice。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.21
                  Master_User: gtid
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 537
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 537
              Relay_Log_Space: 1257
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 21
                  Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1
            Executed_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

为了测试基于GTID的主从复制是否成功，
我们在master插入一条数据INSERT INTO date_demo VALUES(NULL,NOW(),NOW(),NOW(),NOW(),NOW())，然后看slave是否有此记录。

master.png
slave.png

再看看slave的Retrieved_Gtid_Set和Executed_Gtid_Set发生了什么变化。它们从67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1转变成了67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1-2,说明slave成功执行了刚才插入sql的事务消息。

 Retrieved_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1-2
 Executed_Gtid_Set: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026:1-2

尾言

大家好，我是cmazxiaoma(寓意是沉梦昂志的小马)，感谢各位阅读本文章。
小弟不才。
如果您对这篇文章有什么意见或者错误需要改进的地方,欢迎与我讨论。
如果您觉得还不错的话,希望你们可以点个赞。
希望我的文章对你能有所帮助。
有什么意见、见解或疑惑，欢迎留言讨论。

最后送上：心之所向，素履以往。生如逆旅，一苇以航。

saoqi.png