Resync mysql master and slave without master downtime

By LotusshaneyTech

On the Master server dump a backup of the database you want to resync

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -q <<dbname>>  | gzip -c > ~/dump.sql.gz

Move compress backup to Slave server

scp ~/dump.sql.gz <<user>>@<<slave-server-ip>>:~/

On the Slave server decompress the backup

gunzip ~/dump.sql.gz

Get the Master Log Name and Position from the dump, Make a note of the MASTER_LOG_FILE & MASTER_LOG_POS values

head dump.sql -n80 | grep "MASTER_LOG_POS"

Stop mySQL/MariaDB

service mysql stop

Put the following option in your my.conf config file (most probably under /etc/my.conf) under the [mysqld] section to disable the slave from auto starting

skip-slave-start

Start mySQL/MariaDB

service mysql start

Log into mySQL and check the Slave is off

mysql -u root -p
SHOW SLAVE STATUS \G;
exit;

Load backup into Slave server

mysql -u root -p <<dbname>> < ~/dump.sql

Log into mySQL and set Master Settings

CHANGE MASTER TO MASTER_HOST='<<master-server-ip>>',MASTER_USER='<<replicant_user>>',MASTER_PASSWORD='<<password>>', MASTER_LOG_FILE='<<value from step 4>>', MASTER_LOG_POS=<<value from step 4>>;

Stop mySQL/MariaDB

service mysql stop

Remove the following option in your my.conf config file (most probably under /etc/my.conf) under the [mysqld] section to enable the slave for auto starting

skip-slave-start

Start mySQL/MariaDB

service mysql start

Log into mySQL and check the Slave is On. Take Note of the Seconds_Behind_Master . This will decrease as the Slave server catches up with the Master server.

mysql -u root -p
SHOW SLAVE STATUS \G;
exit;