MySQL GTID主备不一致修复方案

创建或修复损坏的MySQL副本

方案一：重建 Replicas

MySQL 5.6及以上版在复制中引入了新的全局事务ID（GTID）支持。 在启用了GTID模式的情况下执行MySQL和MySQL 5.7的备份时，Percona XtraBackup会自动将GTID值存储在xtrabackup_binlog_info中。 该信息可用于创建新的（或修复损坏的）基于GTID的副本。

前提条件

  1. MySQL 机器上需要安装 percona xtrabackup

优点

  1. 比较安全，操作简单

缺点

  1. 数据量较大的时候备份所需的时间比较久
  2. 当数据库有做读写分离的时候，Slave 承担的读请求需要转移到 Master

操作步骤

Master

  1. 在 Master 上使用 xtrabackup 工具对当前的数据库进行备份，执行该命令的用户需要有读取 MySQL data 目录的权限
innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD]  /tmp
  1. 将该备份文件拷贝到 Slave 机器上

Slave

  1. 在 Slave 机器上执行该命令，准备备份文件
innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD] --apply-log /tmp/[TIMESTAMP]
  1. 备份并删除 Slave data目录
systemctl stop mysqld
mv /data/mysql{,.bak}
  1. 将备份拷贝到目标目录，并赋予相应的权限，然后重启 Slave
innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD] --copy-back /tmp/[TIMESTAMP]
chmod 750 /data/mysql
chown mysql.mysql -R /data/mysql
systemctl start mysqld
  1. 查看当前备份已经执行过的最后一个的GTID，如下示例
$ cat /tmp/[TIMESTAMP]/xtrabackup_binlog_info
mysql-bin.000002    1232        c777888a-b6df-11e2-a604-080027635ef5:1-4

这个GTID也会在 innobackupex 备份完成后打印出来

innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 1232, GTID of the last change 'c777888a-b6df-11e2-a604-080027635ef5:1-4'
  1. 使用 root 登录 MySQL，进行如下配置
NewSlave > RESET MASTER;
NewSlave > SET GLOBAL gtid_purged='c777888a-b6df-11e2-a604-080027635ef5:1-4';
NewSlave > CHANGE MASTER TO
             MASTER_HOST="$masterip",
             MASTER_USER="repl",
             MASTER_PASSWORD="$slavepass",
             MASTER_AUTO_POSITION = 1;
NewSlave > START SLAVE;
  1. 查看 Slave 的复制状态是否正常
NewSlave > SHOW SLAVE STATUS\G
         [..]
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
         [...]
         Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5
         Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5

我们可以看到副本已检索到编号为5的新事务，因此从1到5的事务已在此副本上了。这样我们就完成了一个新 replicas 的搭建。

方案二：使用percona-toolkit进行数据修复

PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。

前提条件

  1. MySQL 机器上需要安装 percona-toolkit 工具

优点

  1. 修复速度快，不需要停止从库

缺点

  1. 操作复杂，操作前最后先备份数据库
  2. 待修复的表需要具有 unique constraint

操作步骤

背景示例

IP 关系对应

|  IP  | Role  |
|  ----  | ----  |
| 192.168.100.132  | Master |
| 192.168.100.131  | Slave |

假设待恢复的表结构如下所示

mysql> show create table test.t;
+-------+-------------------------------------
| Table | Create Table                                                                                                                                  |
+-------+-------------------------------------
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `content` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------

正常主备一致的情况下，Master 和 Slave 的数据均为如下所示

mysql> select * from test.t;
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
+----+---------+
2 rows in set (0.00 sec)

在极端情况下，假如出现了如下主备不一致的情况，情形如下：

  1. Master 新增了一条 id 为 3 的记录，如下所示，但并没有同步到 Slave，同时自动 failover 到了 Slave。
  2. Old Slave 作为 New Master 在服务了一段时间后，表中增加了新的记录。

重新启动 Old Master 后，Old Master的数据如下所示:

old_master> select * from test.t;
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
|  3 | c       |
+----+---------+
3 rows in set (0.00 sec)

New Master 的数据如下所示:

new_master> select * from test.t;
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
|  3 | cc      |
|  4 | dd      |
+----+---------+
4 rows in set (0.00 sec)

此时如果将 old master 配置为 new master 的slave，则会报错，比如出现如下报错

...Last_IO_Error: binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID.

可以看到 Old Master 的 GTID 已到 255

Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-10,
60d082ee-86c2-11eb-a9df-000c2988edab:1-255

而 New Master 的 GTID才到254

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 4062
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-2,
60d082ee-86c2-11eb-a9df-000c2988edab:1-254
1 row in set (0.00 sec)

此时我们配置 Old Master 跳过错误，将 Old Master 恢复成可以正常从 New Master 复制的状态

old_master> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

old_master> set gtid_next='60d082ee-86c2-11eb-a9df-000c2988edab:254';  --Specify the version of the next transaction,the GTID you want to skip
Query OK, 0 rows affected (0.00 sec)

old_master> begin;
Query OK, 0 rows affected (0.00 sec)

old_master> commit;                                                   -- Inject an empty transaction
Query OK, 0 rows affected (0.00 sec)

old_master> set gtid_next='AUTOMATIC'；   -- Restore to automaic GTID
Query OK, 0 rows affected (0.00 sec)

old_master> start slave;
Query OK, 0 rows affected (0.13 sec)

然后我们在 Old Master 上可以看到复制在正常进行

mysql> show slave status\G
            ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-10,
60d082ee-86c2-11eb-a9df-000c2988edab:1-255
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

最后我们在 New Master 上清除 slave_master_info

new_master> reset slave all for channel '';
Query OK, 0 rows affected (0.00 sec)

new_master> show slave status\G;
Empty set (0.01 sec)

校验一致性

接下来我们要校验主从一致性，在 New Master上执行 pt-table-checksum，ROWS为4，存在一条DIFFS

[root@localhost ~]# pt-table-checksum h='127.0.0.1',u='mha',p='[PASSWORD]',P=3306 --no-check-binlog-format --databases test
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
03-29T19:24:18      0      1        4          1       1       0   0.322 test.t

双向同步（同步操作会修改数据，操作前进行数据备份）

在同步过程中，pt-table-sync 会在 Master 上进行数据修改，pt-table-sync的参数作用如下

pt-table-sync --databases test --bidirectional --conflict-column='*' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --print
--database                指定待执行的数据库
--bidirectional           为双向同步
--conflict-column         对比该列当冲突发生时
--conflict-comparison     冲突对比策略
--print                   输出对比结果
--dry-run                 测试运行
--execute                 执行测试

# 左边的DSN为 Slave
# 右边的DSN为 Master

这里我们指定—conflict-name='content'作为对比列，一般使用业务主键作为该列。可以看到打印出了待执行的语句

[root@localhost ~]# pt-table-sync --databases test --bidirectional  --conflict-column='content' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --print
/*192.168.100.132:3306*/ UPDATE `test`.`t` SET `content`='cc' WHERE `id`='3' LIMIT 1;
/*192.168.100.132:3306*/ INSERT INTO `test`.`t`(`id`, `content`) VALUES ('4', 'dd');

接下来执行语句

[root@localhost ~]# pt-table-sync --databases test --bidirectional  --conflict-column='content' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --execute

然后在 Master 上再次执行数据对比，可以看到数据正常了

[root@localhost ~]# pt-table-checksum h='127.0.0.1',u='mha',p='[PASSWORD]',P=3306 --no-check-binlog-format --databases test
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
03-30T12:09:57      0      0        4          0       1       0   0.330 test.t

参考文档

https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_gtid.html

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-checksum.html

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-sync.html

https://developer.aliyun.com/article/708726

https://tech.meituan.com/2017/11/17/mysql-flashback.html