Mysql 5.7 replication failure

MySQL & MariaDB
1

Hi everyone,

I got an issue in mysql 5.7 master slave replication error.

Can you please help in resolving this.

Steps I followed o resetup of slave with master data.

  1. I took dump of master data with mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers --events > dump.sql
  2. Imported the dump file in slave
  3. Reset slave all; on slave machine I did
  4. Got the new master file and position from dump file using
    head -28 dump.sql
  5. Change master to master_host=xxx, master_port=xxx,master_user=xxx,master_password=xxx, master_delay=86400, master_log_file=xxx, master_log_position=xxx;
  6. Start slave ; on slave machine

But the replication is failed with 1062 duplicate error.
I Cross checked the master log file and position from dump file, I verified the error logs.

Nothing helped.

Can you please help me to resolve this.

Thanks
Kumar

2

Have you tried this without the master_delay option?

1 Like
3

Hi mattheab,

No I didn’t tried without master_delay, can you please help me how it different.

Thanks
Kumar

1 Like
4

Run the same change master command as you have, but remove the master_delay parameter. Simple. Also, please confirm that all of your tables are InnoDB engine?

1 Like
5

Hey, bKumar

I experienced the same error you faced, the reason is that there is a few myisam engine tables on the master,so please check the error message(run the show slave status on the slave can display the error message)to make sure if the error is associated with myisam table.making all application tables are InnoDB engine and resetup a slave

1 Like
6

Hi Matthewb,

No some of the tables are MyISAM and Memory tables were there.

Thanks
Kumar

1 Like
7

Hi zhouke,

Yes there were some myisam and memory tables, but duplicate issue came on innodb table

Thanks
Kumar

1 Like
8

Hi,bKumar
at first, you can use Xtrabackup to resetup a slave instead of mysqldump .in fact I also believe that using mysqldump with all innnodb application tables to resetup a slave is feasible way.

1 Like
9

Hi, how are you?, Could you run pt-table-checksum to check the integrity of your databases?, If there Is unconsistency, you should run pt-sync to avoid these unconsistencies AND you make sure in the databases everything Is ok. Besides you could also check if there are autoincremental fields or not. Besides this theory Is related with the previous the triggers could be affecting also

1 Like