Not the answer you need?
Register and ask your own question!

use backup on slave to rebuild 5.7 slave ,replication report duplicate key error

Zhixin LiZhixin Li EntrantCurrent User Role Novice
Percona-Server-server-57-5.7.25-28.1.el6.x86_64
percona-xtrabackup-24-2.4.13-1.el6.x86_64
Oracle Linux Server release 6.9

M --- Master
S1 --- Slave
S2 --- New Slave

I use xtrabackup on slave S1 with backup option "--throttle=20 --user=root --port=3306 --slave-info --no-timestamp --galera-info"

After backup complete, I got following file.

#cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001919', MASTER_LOG_POS=191701740;

#cat xtrabackup_binlog_info
mysql-bin.000015 547626853 2eee2fba-3ef2-11e8-9f73-fe9af3d53fb1:1-204643393,
62cbddbf-49e8-11e9-b33f-fe33b7d746c5:1-2,
9435eeea-3ef1-11e8-ae6d-fe50755a4466:1-38243312:38243322-514881185:514923885-520478873

#cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1536697629997
last_lsn = 1537515216118
compact = 0
recover_binlog_info = 0

#cat xtrabackup_info
uuid = a073eb7a-4a2f-11e9-bac6-fe33b7d746c5
name =
tool_name = innobackupex
tool_command = --defaults-file=/tmp/3306.cnf --host=127.0.0.1 --throttle=20 --user=root --port=3306 --slave-info --no-timestamp --galera-info /home/mysql/hotbak/20190319_10.11.11.11_3306
tool_version = 2.4.13
ibbackup_version = 2.4.13
server_version = 5.7.25-28-log
start_time = 2019-03-19 17:20:07
end_time = 2019-03-19 18:13:22
lock_time = 0
binlog_pos = filename 'mysql-bin.000015', position '547626853', GTID of the last change '2eee2fba-3ef2-11e8-9f73-fe9af3d53fb1:1-204643393,
62cbddbf-49e8-11e9-b33f-fe33b7d746c5:1-2,
9435eeea-3ef1-11e8-ae6d-fe50755a4466:1-38243312:38243322-514881185:514923885-520478873'
innodb_from_lsn = 0
innodb_to_lsn = 1536697629997
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N


And i use this backup to restore a new slave S2, and reconfigure replication (got postion from xtrabackup_slave_info), but when i start slave i got "Duplicate entry Error"

Duplicate entry '135778308' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

I check the data with new slave, the duplicate data is exists.

and I use mysqlbinlog to analyze Master's binlog and slave S1's binlog。Both S1 and S2 are log_slave_update=on.

the result is: the binlog position with data record in xtrabackup_slave_info is elder than the binlog position with data record in xtrabackup_binlog_info.
The master writes and updates are frequent.


I also use xtrabackup on Percona5.5 slave with --slave-info , and rebuild slave ,it's ok。
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.