MySQL GTID主备不一致的修复方案
程序员文章站
2022-06-19 15:22:47
方案一：重建 replicasmysql 5.6及以上版在复制中引入了新的全局事务id（gtid）支持。 在启用了gtid模式的情况下执行mysql和mysql 5.7的备份时，percona xtr...
方案一：重建 replicas
mysql 5.6及以上版在复制中引入了新的全局事务id（gtid）支持。 在启用了gtid模式的情况下执行mysql和mysql 5.7的备份时，percona xtrabackup会自动将gtid值存储在xtrabackup_binlog_info中。 该信息可用于创建新的（或修复损坏的）基于gtid的副本。
前提条件
mysql 机器上需要安装 percona xtrabackup
优点
比较安全，操作简单
缺点
- 数据量较大的时候备份所需的时间比较久
- 当数据库有做读写分离的时候，slave 承担的读请求需要转移到 master
操作步骤
master
在 master 上使用 xtrabackup 工具对当前的数据库进行备份，执行该命令的用户需要有读取 mysql data 目录的权限
innobackupex --default-file=/etc/my.cnf --user=root -h 127.0.0.1 --password=[password] /tmp
将该备份文件拷贝到 slave 机器上
slave
在 slave 机器上执行该命令，准备备份文件
innobackupex --default-file=/etc/my.cnf --user=root -h 127.0.0.1 --password=[password] --apply-log /tmp/[timestamp]
备份并删除 slave data目录
systemctl stop mysqld
mv /data/mysql{,.bak}
将备份拷贝到目标目录，并赋予相应的权限，然后重启 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
查看当前备份已经执行过的最后一个的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'
使用 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;
查看 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两个工具，主要用于检测主从是否一致以及修复数据不一致情况。
前提条件
mysql 机器上需要安装 percona-toolkit 工具
优点
修复速度快，不需要停止从库
缺点
操作复杂，操作前最后先备份数据库
待修复的表需要具有 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)
在极端情况下，假如出现了如下主备不一致的情况，情形如下：
- master 新增了一条 id 为 3 的记录，如下所示，但并没有同步到 slave，同时自动 failover 到了 slave。
- 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
以上就是mysql gtid主备不一致的修复方案的详细内容，更多关于mysql gtid主备不一致修复的资料请关注其它相关文章！