Resync mysql master and slave without master downtime

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

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

Move compress backup to Slave server

[cc]scp ~/dump.sql.gz <>@<>:~/[/cc]

On the Slave server decompress the backup

[cc]gunzip ~/dump.sql.gz[/cc]

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

[cc]head dump.sql -n80 | grep “MASTER_LOG_POS”[/cc]

Stop mySQL/MariaDB

[cc]service mysql stop[/cc]

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

[cc]skip-slave-start[/cc]

Start mySQL/MariaDB

[cc]service mysql start[/cc]

Log into mySQL and check the Slave is off

[cc]mysql -u root -p
SHOW SLAVE STATUS \G;
exit;[/cc]

Load backup into Slave server

[cc]mysql -u root -p <> < ~/dump.sql[/cc] Log into mySQL and set Master Settings [cc]CHANGE MASTER TO MASTER_HOST='<>’,MASTER_USER=’<>’,MASTER_PASSWORD=’<>’, MASTER_LOG_FILE=’<>’, MASTER_LOG_POS=<>;[/cc]