gtid主从复制原理和报错解决-云栖社区-阿里云

2019年05月11日原文

mysql，gtid，主从复制

IP	版本	节点
10.10.10.100	centos7.2 + mysql5.6	master
10.10.10.200	centos7.2 + mysql5.6	slave

二、GTID的组成部分：

前面是server_uuid：后面是一个序列号
UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。
Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

三、GTID比传统复制的优势：

1、更简单的实现failover，不用以前那样在需要找log_file和log_Pos。
2、更简单的搭建主从复制。
3、比传统复制更加安全。
4、GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。
5、多线程复制

四、GTID的工作原理：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

五、相关变量

GTID_PURGED ：已经执行完被删除的binlog的事务，它是GTID_EXECUTED的子集，从MySQL5.6.9开始，该变量无法被设置。
GTID_OWNED : 表示正在执行的事务的gtid以及对应的线程ID。
GTID_EXECUTED：表示已经在该实例上执行过的事务；执行RESET MASTER 会将该变量置空; 我们还可以通过设置GTID_NEXT执行一个空事务，来影响GTID_EXECUTED
GTID_NEXT：是SESSION级别变量，表示下一个将被使用的GTID
在内存中也维护了与GTID_PURGED， GTID_OWNED, GTID_EXECUTED相对应的全局对象gtid_state。
gtid_state中维护了三个集合，其中logged_gtids对应GTID_EXECUTED， lost_gtids对应GTID_PURGED，owned_gtids对应GTID_OWNED

六、要点

slave在接受master的binlog时，会校验master的GTID是否已经执行过（一个服务器只能执行一次）。为了保证主从数据的一致性，多线程只能同时执行一个GTID。

七，GTID主从复制搭建：

1，两台机器各自安装好mysql

2，两台服务器各自安装好mysql，修改root密码，启动mysql数据库
在主库上导出数据：

mysql> flush tables with read lock;               
# mysqldump -uroot -p --default-character-set=utf8  --single-transaction --flush-logs --set-gtid-purged=OFF  wbdb > /tmp/wbdb.sql      
mysql> unlock tables;             
# mysql -uroot -p  --default-character-set=utf8 < database.sql

3,配置my.cnf参数，加入以下项目：
主：

server-id       = 1
log-bin=mysql-bin
binlog_format=row    
#当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW
#添加以下这些选项
log-slave-updates=true           #slave更新是否记入日志
lower_case_table_names =1        #大小写不敏感
gtid-mode=on                 # 启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true     #强制GTID的一致性
master-info-repository=TABLE     #主服信息记录库=表/文件
relay-log-info-repository=TABLE    #中继日志信息记录库
sync-master-info=1                #同步主库信息
slave-parallel-workers=4            #从服务器的SQL线程数，要复制库数目相同
binlog-checksum=CRC32            #校验码
master-verify-checksum=1          #主服校验
slave-sql-verify-checksum=1        #从服校验

从：

server-id       = 2
log-bin=mysql-bin
binlog_format=row
log-slave-updates=true    
lower_case_table_names =1    
gtid-mode=on    
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
slave-parallel-workers=4
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
replicate-do-db=jaat                  // 设定需要复制的数据库（多数据库使用逗号，隔开）

4，登录master服务器：

# mysql -uroot -p123456 -e "show global variables like '%uuid%';"
+---------------+--------------------------------------+
|Variable_name | Value                                |
+---------------+--------------------------------------+
|server_uuid   |3eda76df-e355-11e3-8d42-000c294698bf |
+---------------+--------------------------------------+
mysql>grant replication slave on *.* to replyuser@'10.10.10.%'identified by 'replypass';        
mysql>flush privileges;

登录slave服务器：

# mysql -e "show global variables like '%uuid%';"
+---------------+--------------------------------------+
|Variable_name | Value                                |
+---------------+--------------------------------------+
|server_uuid   |08c840ad-e35c-11e3-8d6f-000c29ed6c68 |
+---------------+--------------------------------------+
mysql>change master to master_host='10.10.10.100', master_user='replyuser',master_password='replypass', master_auto_position=1;
mysql>start slave;

八，GTID事务常见报错:（参考文档：MySQL GTID 错误处理汇总）

主库新增记录，从库提示主键冲突
主库对象可更新，从库无对应的对象可更新
主库对象可删除，从库无对应的对象可删除
主库日志被purged的情形(即在主库执行了reset master)

1，从库报主键冲突：(Errno: 1062)

Retrieved_Gtid_Set项：记录了relay日志从Master获取了binlog日志的位置
Executed_Gtid_Set项：记录本机执行的binlog日志位置（如果是从机，包括Master的binlog日志位置和slave本身的binlog日志位置）

解决办法：在从库上删除重复的记录

mysql> stop slave;
mysql> delete from jaat.test where id=2;
mysql> start slave;
mysql> show slave statusG;

2，主库对象可更新，从库无对应的对象可更新(Errno: 1032)

报错：

解决办法：在主库上找到更新前的数据，insert到从库里去：
根据上面报错是在mysql-bin.000001 日志文件的2502位置处发生的错误，在主库上找到2502位置对应的sql语句：

[root@master bin]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/mysql-bin.000001  |grep -A '10' 2502

注意：where后面的部分为更新前的数据，set部分为更新后的数据，@1表示第一个列，@2表示第二列。因此可以将更新前的数据插入到从库，在从库上执行：

slave> stop slave sql_thread;;
slave> insert into jaat.test values(2,'mm')
slave> start slave sql_thread;

3，主库对象可删除，从库无对应的对象可删除(Errno: 1032)

报错：

解决办法：跳过该事务

mysql> stop slave sql_thread;
mysql> set gtid_next='0dbdf52e-0b1e-11e7-a0af-000c29df5573:9';                ////在执行GTID(Executed_Gtid_Set)为7(0dbdf52e-0b1e-11e7-a0af-000c29df5573:1-8)的事物报错，所以跳过这个事物执行GTID为9的事物。
mysql> begin;commit;
mysql> set gtid_next='AUTOMATIC';
mysql> start slave sql_thread;

4，主库日志被purged的情形(即在主库执行了purge binary logs to 'mysql-bin.xxxx';)（error 1236）

解决办法：
登录主库执行，查看被purge的gtid（已经执行完被删除的binlog的事务）：

master> show variables like '%gtid_purged%';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| gtid_purged   | 0dbdf52e-0b1e-11e7-a0af-000c29df5573:1-5 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

登录从库执行：
mysql> stop slave;
mysql> reset mater;
mysql> set global gtid_purged = '0dbdf52e-0b1e-11e7-a0af-000c29df5573:1-5';        //这个是上面主库查出来的值告诉SLAVE要主动抛弃掉 MASTER 上传输过来的1-5区间的事务，也就是在 GTID 从6开始，又会继续应用 RELAY LOG 了。
mysql> start slave;
mysql> show slave statusG;

如果后面有主键重复的报错，删除对应的重复记录，然后重启启动主库查看即可(是由于我们在从库停止期间delete这个事务没有被从库的relay log接受到,其次主从的binlog又被purged，而且从库启动后，执行了gtid_purged，因此主库上新增的记录在从库上提示主键重复)。

注意：事实上，主从的数据已经不一致了，应根据实际的需要考虑是否进行相应的修复

5，主库执行了reset master命令后插入了新数据（error 1236）

解决办法：重新搭建从库，让从库的gtid也从1开始，最后用pt工具修复不一致。

mysql> stop slave;
mysql> reset slave all;
mysql> reset master;
mysql> change master to master_host='192.168.225.128', master_user='replyuser',master_password='replypass', master_auto_position=1;
mysql> start slave;
mysql> show slave statusG;

6，终极解决办法：忽略所有报错，直到同步成功后，用pt工具进行数据校验和修复

在主库上执行以下操作，pt工具会根据主库中的复制关系自动去找从库：

[root@master ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=jaat.checksum --databases=jaat --tables=test h=192.168.225.128,u=root,p=123456 --empty-replicate-table --create-replicate-table

DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
参数意义：
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式，要是binlog为row模式下必须要加上该项。
--replicate-check-only : 只显示不同步的信息。
--replicate= ：把checksum的信息写入到指定表中，该实例中是写入到(jaat.checksum表中)
--databases= ：指定需要被检查的数据库，多个则用逗号隔开。
--tables= ：指定需要被检查的表，多个用逗号隔开
--empty-replicate-table ：清空checksum这个表
--create-replicate-table：创建checksum表

登录主库查看校验表：

this_crc: 13fa7d9d #从的校验值 this_cnt: 4 #从的行数
master_crc: aa7a56c3 #主的校验值 master_cnt: 5 #主的行数

登录从库查看校验表：

pt-table-sync修复从库不一致的数据：他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。以下所有操作都要在主库上进行：
对从库数据的修复通常是在主库执行sql来同步到从库。因此，在有多个从库时，修复某个从库的数据实际会把修复语句同步到所有从库。

1，在主库上执行以下操作，pt工具会根据主库中的复制关系自动去找从库：（--tables和--databases参数可以不要，如果加上了这两个参数，下面同步会自动同步单个库的单个或者多个表，不加这两个参数，下面的pt-table-sync表示同步所有数据)

[root@master ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=jaat.checksum --databases=jaat --tables=test h=192.168.225.128,u=root,p=123456 --empty-replicate-table --create-replicate-table

2，打印出不同的数据，先写master的ip，再写slave的ip：

[root@master ~]# pt-table-sync --print --replicate=jaat.checksum h=192.168.225.128,u=root,p=123456,P=3306 h=192.168.225.129,u=root,p=123456,P=3306

--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库，多个用逗号隔开。

3，开始同步数据：

[root@master ~]# pt-table-sync --replicate=jaat.checksums h=192.168.225.128,u=root,p=123456,P=3306 h=192.168.225.129,u=root,p=123456,P=3306 --execute

4，同步完成后再进行校验：

[root@master ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=jaat.checksum --databases=jaat --tables=test h=192.168.225.128,u=root,p=123456 --empty-replicate-table --create-replicate-table

5，验证校验结果，执行以下语句若返回为空则说明修复成功：

select * from wbdb.checksum where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);