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；

Start slave

如果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中查出缺失的数据如下：

根据查询结果在slave端执行：

start slave;

show slave status \G;

主备关系修复成功。

故障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 -100