gtid主从复制原理和报错解决

  1. 云栖社区>
  2. 博客>
  3. 正文

gtid主从复制原理和报错解决

JAAT9999 2019-03-12 16:42:59 浏览761
展开阅读全文
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)

image

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 status\G;

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

报错:
image

解决办法:在主库上找到更新前的数据,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

image

注意: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)

报错:
image

解决办法:跳过该事务

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)

image

解决办法:
登录主库执行,查看被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 status\G;

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

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

5,主库执行了reset master命令后插入了新数据(error 1236)

image

解决办法: 重新搭建从库,让从库的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 status\G;

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

image

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表

登录主库查看校验表:
image

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

登录从库查看校验表:
image

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);

网友评论

登录后评论
0/500
评论
JAAT9999
+ 关注