How to re-sync the Mysql DB Replica

Scenario Mysql Server1 is running as MASTER. Mysql Server2 is running as SLAVE. Now DB replication is happening from MASTER to SLAVE. How to re-sync the DB again as after restoring DB taken from Master to Slave also doesn't solve the problem ? ---------------------------- At the master: mysql -u root -p RESET MASTER; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; And copy the values of the result of the last command somewhere. Without closing the connection to the client (I.E. in another terminal window... because it would release the read lock) issue the command to get a dump of the master: mysqldump -uroot -p --all-databases > abs-path-to/mysqldump.sql [in alternativa puoi aggiungere il flag '--master-data=1' ed evitare di eseguire a mano sullo slave il change master to master-bin-NN... vedi sotto] [oppure, in alternativa ancora, puoi aggiungere il flag '--delete-master-logs' (che implica il '--master-data=1' ed evitare di eseguire a mano sullo slave il change master to master-bin-NN... vedi sotto e resetta i bin-log del master a 1. V. man mysqldump] Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client: UNLOCK TABLES; Now copy the dump file to the slave using scp or your preferred tool. ------------------------------ At the slave: Open a connection to mysql (mysql -u root -p) and type: STOP SLAVE; Load master's data dump with this console command: mysql -u root -p < mysqldump.sql Sync slave and master logs: RESET SLAVE; [solo se sopra non hai usato uno dei due flag alternativi...] CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=NN; Where the values of the above fields are the ones you copied before. Finally type START SLAVE; And to check that everything is working again, if you type SHOW SLAVE STATUS; you should see: Slave_IO_Running: Yes Slave_SQL_Running: Yes That's it!