MySQL replication still failing after new import

Question

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.

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;