0

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

  • Is your question about the safeness of using RESET MASTER/SLAVE or about a replication failure? – Jesus Uzcanga Jan 23 '20 at 18:18
  • I assume the question would be is it safe to use: mysql> RESET MASTER, mysql> RESET SLAVE in the context of fixing replication – user2872898 Jan 23 '20 at 20:13
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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