I had an issue with my MySQL replication from Master to Slave. An App we had by mistake wrote to the Slave and deleted some data as well, causing errors during the replication (delete errors, primary key not found errors etc.- error codes 1032, 1062 etc.).

Skipping all the errors didn't work as it seemed the data was beyond repair and once all the errors from multiple tables were skipped the replication continued failing as above. To this end we exported the data from the Master using MySQL Workbench (after following all the steps of stopping, granting permissions, flushing privileges, locking the master and then exporting the data through MySQL Workbench and unlocking the databases).

https://www.lexiconn.com/blog/2014/04/how-to-set-up-selective-master-slave-replication-in-mysql/ (example guide)

The .sql files were ported to the Slave, Slave was stopped, changed the MASTER to MASTER_HOST and imported all the data through the MySQL Workbench on the Slave. The database is rather large (30 odd Gigs) so this process takes over 24 hours. Once the Slave is then Started, again the errors appear (Duplication errors, cannot delete errors, Primary Key errors etc.).

Initially we used mysqldump to extract the data and MySQL to to re-import the data but this failed on the MySql importing complaining of errors in the SQL files (which are too large to open in any notepad and investigate). So we went for the WorkBench as an export and import (which seems to both function without any errors).

I cannot find any research online that deals with something like this, a failure of replication after clean export and import or where to go from here. The .sql files created by the WorkBench do all have Drop Tables commands before create tables, so surely the tables are deleted and created fresh so should not have any Primary Key concerns etc.

Does anyone have any guidance for this as any test we do has to run for 24 hours before we see the failure.

Much appreciated.

  • Have you tried how to remove replication setup? Once done do a fresh install. refer here
    – James
    Oct 18, 2019 at 6:43
  • First establish a testing setup in local, once succeed then move on.
    – James
    Oct 18, 2019 at 6:48
  • To setup refer few links link1,link2
    – James
    Oct 18, 2019 at 6:50
  • Did you reset the binlog position on the slave server? If not, the slave will just continue where it was
    – verhie
    Oct 18, 2019 at 8:10
  • When doing the CHANGE MASTER, include the binlog name and position that you captured when doing the dump.
    – Rick James
    Oct 21, 2019 at 1:21

1 Answer 1

  1. Take a full dump again on the master node with below command shell> mysqldump --single-transaction --master-data=2 --routines --triggers your_database_name > file.sql

    --master-data=2 parameter will provide master's binary log position and file in file.sql

  2. Restore the dump from master node in Slave node

  3. login to slave and execute below mysql> STOP SLAVE; RESET SLAVE ALL; mysql> CHANGE MASTER TO MASTER_HOST='Master Node IP', MASTER_USER='replication_user', MASTER_PASSWORD='replication_user_password', MASTER_LOG_FILE='binary_log_file', MASTER_LOG_POS=binary_log_file_position;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.