MySql Replication Failure on primary secondary

mysql-5.7replication

We have experienced replication failure between primary – secondary databases.

I have tried various scenarios to get replication to work again, which included deleting secondary database and restoring it from primary backup. However, replication still fails.

Would it be best to follow steps:

On Slave

mysql> STOP SLAVE;

On Master

mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p mydb > mydb-dump.sql
mysql> UNLOCK TABLES;

On Slave

mysql -u root -p mydb < mydb-dump.sql
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
mysql> START SLAVE;
mysql> show slave status\G

Is it safe to execute 

mysql> RESET MASTER;
mysql> RESET SLAVE;

as those commands deletes the binary log files. Or would it be better to use

mysql> PURGE BINARY LOGS

Any thoughts would be greatly appreciated.

Cheers,
Roland

Best Answer

In the master server, you don't need to issue either RESET MASTER nor PURGE BINARY LOGS to extract a DB copy... when you need to extract a master copy to create a slave, just do it with the current binary log file and position.

Since you will need the current binary log file and position to start replication on the slave, just include the --master-data (more info) option to mysqldump which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process. (Just open the mydb-dump.sql file after backup is completed and copy the "CHANGE MASTER TO" info.

In slave is perfectly safe to use RESET SLAVE after a backup restoration to start the replication.

Related Question