Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MySQL replication slave server re-sync issue

Status
Not open for further replies.

soushi01

Technical User
Dec 27, 2010
31
0
0
MY
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_21022022.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_21022022.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 )?[bow] :'(

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_21022022.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_21022022.dump


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top