Mysql8.0.22主备GTID Replication中的那些坑

导读

作者:杨漆
16年关系型数据库管理,从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM;运维路上不平坦,跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿,希望帮到大家少走弯路、少熬夜。

Mysql 中的GTID是什么 ?
答:全局事务ID,为每一个在Master上提交的事务在集群内Replication时只生成一个唯一的ID,为规避冗余和错误提供了有力保障。
一条GITD信息由两部分组成,即source_id + transaction_id,GTID=source_id:transaction_id,其中source_id是执行事务的主库server-uuid值,在mysql首次启动时生成,保存在DB的数据目录中auto.conf文件里,存放server-uuid的值(唯一性)。transaction_id是从1开始自增的序列,记录在主库上执行的第几个事务,Mysql会保证这个事务和GTID是唯一的(一比一的关系)。

为何要使用GTID Replication ?
答:GTID Replication是从Mysql5.6开始支持的一种新的复制方式,与传统基于日志的方式存在着很大的差异。旧有基于日志的复制中,Slave端连接到Master端并告诉Master 从哪个二进制日志的偏移量开始执行增量同步,此刻若指定的日志偏移量不对,就会造成Master–>Slave数据的不一致,而基于GTID的复制不会出现此类错误。
GTID Replication中Slave会将执行完的事务的GTID值告知Master,之后Master把所有没在Slave端执行过的事务发送到Slave端并执行。GTID Replication可以保证同一个事务在指定的Slave端只执行一次,规避了由于偏移量的问题造成的数据不一致风险。
GTID Replication优点:
1、故障转移更为方便(因GTID是全局唯一的标识符,能更简单的知道哪些事务在Slave端没有执行,在多Slave场景下使用master_auto_position=1省去了多个日志偏移量配置的麻烦)
2、Slave和Master的data一致性。
GTID Replication缺点:
1、当故障发生时,处理起来比传统日志模式复杂些。
2、执行语句的一些限制会导致Slave端停止。

以下整理出几个常见的坑,以供参考:
故障1、在Master端创建函数时,Slave端报出如下错误:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

解决方案:
set global log_bin_trust_function_creators=TRUE;
Mysql8.0.22主备GTID Replication中的那些坑
Start slaveMysql8.0.22主备GTID Replication中的那些坑
如果Slave端与Master端数据差异量太大,为快速恢复可从Master端重新拉备份传到Slave端进行恢复。
之后执行如下命令:
mysql> reset slave all ; reset master;
Query OK, 0 rows affected, 1 warning (0.19 sec)
Query OK, 0 rows affected (0.03 sec)

mysql> change master to
-> master_host=‘192.10.10.6’,
-> master_port=3306,
-> master_user=‘repl’,
-> master_password=‘replxxx’,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

故障2:
Last_SQL_Errno: 1050
主库上create table,从库上存在。
报错信息如下所示:
Last_SQL_Errno: 1050
Last_SQL_Error: Error ‘Table ‘test’ already exists’ on query. Default database: ‘dbtest’. Query: ‘create table test(id int,name varchar(20))’

处理的原则:以主库的为准。
处理方法:在从库上drop这张表,在salve上执行:
set sql_log_bin=0;
drop table dbtest.test;
set sql_log_bin=1;
stop slave sql_thread;
start slave sql_thread;

故障3:
Last_Errno: 1396
Last_Error: Error ‘Operation CREATE USER failed for ‘dba_mha’@‘192.10.10.%’’ on query. Default database: ‘’. Query: ‘CREATE USER ‘dba_mha’@‘192.10.10.%’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*74180F4C728E45A96199206F9F799F5315310896’’

Alter user dba_mha@‘192.10.10.%’ identified WITH mysql_native_password by ‘mha123’;

show grants for ‘dba_mha’@‘192.10.10.%’;
经查询权限已获取,故障解决。

故障4:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table etl_db.sys_user; Can’t find record in ‘sys_user’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log master-binlog.000001, end_log_pos 3042765

解决方法:(处理此种错误一般有两种思路)
方法1、直接跳过错误执行语句

–传统模式
mysql> stop slave;
#表示跳过一步错误,后面的数字可变
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;

–GTID模式
mysql> stop slave;

通过show slave status\G;
找到Retrieved_Gtid_Set:3e7c7be8-5c68-11eb-ac69-525402ff357a:3-124417

mysql> set GTID_NEXT='3e7c7be8-5c68-11eb-ac69-525402ff357a:3-124417 ’

mysql> begin;commit;
mysql> set GTID_NEXT=‘AUTOMATIC’;
mysql> start slave;

方法2、找到错误执行语句,修复从库数据

第一种解决方案会有造成主从不一致的隐患(delete语句可以跳过),第二种是从根本上解决问题比较推荐

本列采用治本方法,根据Last_Error中提示的master log和end_log_pos的位置查找这条从库上缺失的数据
在主库执行:
mysqlbinlog -v --base64-output=decode-rows --stop-position=3042765 /data/mysql/data/master-binlog.000001 | tail -20
(备注:当调用mysqlbinlog这个工具无法识别binlog中配置中的default-character-set=utf8mb4这个指令时,参考故障5排除法)

从binlog中查出缺失的数据如下:Mysql8.0.22主备GTID Replication中的那些坑
根据查询结果在slave端执行:Mysql8.0.22主备GTID Replication中的那些坑
start slave;
show slave status \G;Mysql8.0.22主备GTID Replication中的那些坑
主备关系修复成功。

故障5:
执行 mysqlbinlog -v --base64-output=decode-rows --stop-position=3042765 /data/mysql/data/master-binlog.000001 | tail -100 时报如下错误:
mysqlbinlog: [ERROR] unknown variable ‘default-character-set=utf8mb4’

原因:
mysqlbinlog工具无法识别binlog中配置的default-character-set=utf8mb4这个指令

解决方法:
使用:–no-defaults参数即可

mysqlbinlog --no-defaults -v --base64-output=decode-rows --stop-position=3042765 /data/mysql/data/master-binlog.000001 | tail -100Mysql8.0.22主备GTID Replication中的那些坑

上一篇:MySQL的SQL语句 -复制语句(15)- 控制组复制的 SQL 语句 -


下一篇:阿里云服务器搭建redis主从遇到的坑