MySQL Forums
Forum List  »  Replication

MySQL replication slave server re-sync
Posted by: Pui Kee Leong
Date: February 23, 2022 06:51AM

Hi Im Newbie of MySQL user,

Currently im encounter MySQL replication slave server fail sync very frequently like almost happen every month due to slave server power shutdown unexpectedly ,
then as i know if want re-sync replication slave , need stop slave then backup & restore to slave and then start slave again like step below.

1) login mysql replication slave server, then i run Stop Slave for channel 'DBname-01';

2) at master server need backup like example below
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -h 192.168.1.1 -P3306 -uroot -ppassxxxx DBname > C:\Temp\DBname_22022022.dump

3) then restore db to slave server
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.1.2 -P3306 -uroot -ppassxxxx DBname < C:\Temp\DBname_22022022.dump

4) at slave server,i change master_log_file & master_log_pos, then i run
change master to master_log_file='mysql-bin.000002',master_log_pos=123456 for CHANNEL 'DBname-01';

5) login mysql replication slave server, then i run Start Slave for channel 'DBname-01';


* But is very time consuming and somemore only can do non-operation hours especially do backup and restore part due to file size.
Is it possible any "shortcut way" can skip the database backup & restore to replication slave server step (which is item 2 and 3 )?

2) at master server need backup like example below (can skip this step ?)
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -h 192.168.1.1 -P3306 -uroot -ppassxxxx DBname > C:\Temp\DBname_22022022.dump

3) then restore db to slave server (can skip this step too ?)
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.1.2 -P3306 -uroot -ppassxxxx DBname < C:\Temp\DBname_22022022.dump

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL replication slave server re-sync
123
February 23, 2022 06:51AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.