近期在给 radondb/xenon 适配 percona xtrabackup 8.0时,遇到了一些问题,经过多日调研、尝试终于解决,特此分享。
版本信息:
Percona-Server 8.0.19-10
Percona-Xtrabackup 8.0.13
版本各字段含义参考 https://www.percona.com/blog/2020/08/18/aligning-percona-xtrabackup-versions-with-percona-server-for-mysql/
适配过程中遇到的坑
一、MySQL 8.0 + Semi-Sync + 持续写入数据期间执行重建后,change master to && start slave 报错:
Last_Error: Could not execute Write_rows event on table db1.t1; Duplicate entry ‘28646’ for key ‘t1.PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000052, end_log_pos 437
二、MySQL 8.0 + Group Replication + 持续写入数据期间执行重建后,change master to && start group_replication 报错:
2020-08-21T14:51:09.977606+08:00 61 [System] [MY-010597] [Repl] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_applier’ executed’. Previous state master_host=’’, master_port= 0, master_log_file=’’, master_log_pos= 4, master_bind=’’. New state master_host=’’, master_port= 0, master_log_file=’’, master_log_pos= 4, master_bind=’’.
2020-08-21T14:51:09.987494+08:00 61 [ERROR] [MY-013124] [Repl] Slave SQL for channel ‘group_replication_applier’: Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124
2020-08-21T14:51:09.987542+08:00 61 [ERROR] [MY-011534] [Repl] Plugin group_replication reported: ‘Error while starting the group replication applier thread’
2020-08-21T14:51:09.987651+08:00 7 [ERROR] [MY-011669] [Repl] Plugin group_replication reported: ‘Unable to initialize the Group Replication applier module.’
2020-08-21T14:51:09.987831+08:00 7 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] The member is leaving a group without being on one.’
要解释这个问题,首先要弄清楚xtrabackup 2.4和8.0的区别。
xtrabackup 2.4和8.0区别
google查到xtrabackup 8.0与2.4版本行为有所不同:
Xtrabackup 2.4 备份后生成的 xtrabackup_binlog_info 文件记录的 GTID 信息是准确的,但是备份恢复后 show master status 显示的 GTID 是不准确的。
Xtrabackup 8.0 在备份只有 InnoDB 表的实例时,xtrabackup_binlog_info 文件记录的 GTID 信息不一定是准确的,但是备份恢复后 show master status 显示的 GTID 是准确的。
Xtrabackup 8.0 在备份有非 InnoDB 表格的实例时,xtrabackup_binlog_info 文件记录的 GTID 信息是准确的,备份恢复后 show master status 显示的 GTID 也是准确的。
之前研究过 xtrabackup 2.4 ,其过程大致如下:
start backup
copy ibdata1 / copy .ibd file
excuted FTWRL
backup non-InnoDB tables and files
writing xtrabackup_binlog_info
executed FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
executed UNLOCK TABLES
copying ib_buffer_pool
completed OK!
问题1:xtrabackup 8.0 的执行过程是什么样?
首先,查看重建期间的general log:
2020-08-26T16:20:18.136376+08:00 170 Query SET SESSION wait_timeout=2147483
2020-08-26T16:20:18.136439+08:00 170 Query SET SESSION autocommit=1
2020-08-26T16:20:18.136523+08:00 170 Query SET NAMES utf8
2020-08-26T16:20:18.136595+08:00 170 Query SHOW VARIABLES
2020-08-26T16:20:18.138840+08:00 170 Query SELECT COUNT(*) FROM information_schema.tables WHERE engine = ‘MyISAM’ OR engine = ‘RocksDB’
2020-08-26T16:20:18.140203+08:00 170 Query SHOW ENGINES
2020-08-26T16:20:18.140407+08:00 170 Query SHOW ENGINE INNODB STATUS
2020-08-