记一次传统mysqldump恢复数据后AB主从同步失败解决办法

前提：服务器使用的是一主一从+MHA，一天因为某种异常，导致主库停止，MHA自动把请求迁移到从库，因为从库的服务器配置相对主库要低，主库通过MHA日志，找到切换时slave要执行的记录，把数据重新恢复了(CHANGE MASTER TO...命令如下)，并将主库作为从库重新加入到主从中，查看主从同步没问题，然后重启mha，并手动关闭从库，让mha重新把请求迁移回主库，最后再同理恢复从库数据。

stop slave

CHANGE MASTER TO MASTER_HOST = 'db2.xxx.com',
 MASTER_PORT = 3306,
 MASTER_LOG_FILE = 'mysql-bin.001038',
 MASTER_LOG_POS = 10270477,
 MASTER_USER = 'replication',
 MASTER_PASSWORD = 'xxx'

start slave

show slave status

问题就出现在这儿了，从库使用上面同理的方式恢复数据后，启动slave，查看SQL线程，是NO状态(Slave_SQL_Running  NO)，查看Mysql错误日志，发现如下错误提示：

2023-03-02 15:27:55 26482 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='db1.xxx.com', master_port= 3306, master_log_file='mysql-bin.001038', master_log_pos= 10549635, master_bind=''. New state master_host='db1.xxx.com', master_port= 3306, master_log_file='mysql-bin.001028', master_log_pos= 4, master_bind=''.
2023-03-02 15:27:58 26482 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2023-03-02 15:27:58 26482 [Note] Slave I/O thread: connected to master 'replication@db1.xxx.com:3306',replication started in log 'mysql-bin.001028' at position 4
2023-03-02 15:27:58 26482 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2023-03-02 15:27:58 26482 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001028' at position 4, relay log './mysql-relay-bin.000001' position: 4
2023-03-02 15:27:58 26482 [ERROR] Slave SQL: Could not execute Write_rows event on table xx_user.xx_ser_detail; Duplicate entry '2979591168' for key 'PRIMARY', Error_code: 1062; handler error xxxOUND_DUPP_KEY; the event's master log mysql-bin.001028, end_log_pos 880, Error_code: 1062
2023-03-02 15:27:58 26482 [Warning] Slave: Duplicate entry '1567996792979591168' for key 'PRIMARY' Error_code: 1062
2023-03-02 15:27:58 26482 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001028' position 557
2023-03-02 15:28:10 26482 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
2023-03-02 15:28:10 26482 [Note] Slave I/O thread killed while reading event
2023-03-02 15:28:10 26482 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001038', position 10674438

 

 

一、使用默认的方式备份还原后主从同步还是报上面的错误，下面是默认备份还原方式

1.备份主库数据库

mysqldump -uroot -p -R --opt -A --flush-logs > db_all.sql

 

2.上传到备库然后先恢复备库数据，在执行CHANGE MASTER TO...

　　1）删除原库数据库(物理/sql都可以)，防止恢复失败

　　2）删除日志文件和其他文件，执行过的命令如下：

#先停服务
rm -rf logs/*
cd /opt/mysql/data
rm -rf mysql-relay-bin.*
rm -rf ib*
rm -rf xxx_*(数据库)
rm -rf relay-log.info master.info
cd /opt/mysql/data/mysql
rm -rf innodb_index_stats*
rm -rf innodb_table_stats*
rm -rf slave_master_info*
rm -rf slave_relay_log_info*
rm -rf slave_worker_info*
#启动mysql

　　3）恢复

mysql -uroot -p < db_all.sql

　　4）执行CHANGE MASTER TO...(查看备份刷新后的新的bin-log日志)

　　5）查看同步状态，同步还是失败，查看mysql错误日志，还是报上述错误

 

二、添加适当的参数进行备份还原，同步成功(还是先执行前面的删除操作)

步骤：

1.备份

mysqldump -uroot -p --single-transaction --master-data=2 --all-databases > db_all.sql

2.恢复备份

mysql -uroot -p < db_all.sql

3.查看备份文件里面的bin-log日志记录点

cat db_all.sql | grep MASTER_LOG_FILE | grep mysql-bin
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001038', MASTER_LOG_POS=10270477;

4.重新执行如下命令(查看同步成功)

stop slave

CHANGE MASTER TO MASTER_HOST = 'db1.xxx.com',
 MASTER_PORT = 3306,
 MASTER_LOG_FILE = 'mysql-bin.001038',
 MASTER_LOG_POS = 10270477,
 MASTER_USER = 'replication',
 MASTER_PASSWORD = 'xxx'

start slave

show slave status

 

最后开启MHA

nohup masterha_manager --conf=/etc/mha_master/mha.cnf >>/etc/mha_master/manager.log 2&>1 &

 

参数说明

    --single-transaction：保证一个事务中所有相同的查询读取到同样的数据，其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，不会锁表

    --master-data=2：对于生成的sql文件产生备份时刻的mysql-bion的信息和pos信息

    --all-databases：备份全库