问题:PXB初始化从库,需要跳过多个事务如何解决(使用GTID搭建从库)?
解决方案:
可以批量来跳过事务,关键在于找到最后需要的跳过的事务.
主库使用PXB全备前,记录主库GTID,从库使用PXB恢复后跳过事务到前面记录的GTID
主库
记录PXB全备前GTID
mysql[(none)]> show master status\G;
*************************** 1. row ***************************
File: lissen-mha-master-bin.000002
Position: 3534
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: e48b6f50-44e3-11ec-910d-0242ac110002:1-15
1 row in set (0.00 sec)
ERROR:
No query specified
主库全备
xtrabackup -uroot -proot -H192.168.59.3 -P33131 --backup --target-dir=/pxb_bak/full
scp -r * root@192.168.68.132:/pxb_bak/full/
创建相关对象
create database db3 charset utf8mb4;
use db3;
create table t1(id int,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(3,'c');
配置从库并进行初始化
xtrabackup --prepare --target-dir=/pxb_bak/full
systemctl stop systemctl
mv /usr/local/mysql/data /usr/local/mysql/data_bak
xtrabackup --copy-back --target-dir=/pxb_bak/full --datadir=/usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/data
systemctl start mysqld
change master to
master_host = '192.168.68.131',
master_port = 3306,
master_user = 'repl',
master_password = 'repl',
master_auto_position = 1;
计算跳过的GTID
根据前面记录的主库PXB全备前GTID为15
所以要跳过的事务为2-15
批量跳过事务脚本
cat skip_gtid.sh
#!/bin/bash
for i in $(seq 2 15)
do
mysql -uroot -proot -h192.168.68.132 -P3306 -e "set gtid_next = 'e48b6f50-44e3-11ec-910d-0242ac110002:$i';begin;commit;"
done
mysql -uroot -proot -h192.168.68.132 -P3306 -e "set gtid_next = 'automatic';"
-- mysql -e 中执行多条命令以;分开
mysql -e ";;;"
跳过事务后启动slave sql_thread
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 573333131
Master_UUID: e48b6f50-44e3-11ec-910d-0242ac110002
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: e48b6f50-44e3-11ec-910d-0242ac110002:1-62
Executed_Gtid_Set: e48b6f50-44e3-11ec-910d-0242ac110002:1-62
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)