{"id":2941,"date":"2018-12-06T16:51:41","date_gmt":"2018-12-06T16:51:41","guid":{"rendered":"http:\/\/blog.designed79.co.uk\/?p=2941"},"modified":"2018-12-06T16:54:50","modified_gmt":"2018-12-06T16:54:50","slug":"resync","status":"publish","type":"post","link":"https:\/\/blog.designed79.co.uk\/?p=2941","title":{"rendered":"Resync mysql master and slave without master downtime"},"content":{"rendered":"

On the Master server dump a backup of the database you want to resync<\/p>\n

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

Move compress backup to Slave server<\/p>\n

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

On the Slave server decompress the backup<\/p>\n

gunzip ~\/dump.sql.gz<\/div><\/div>\n

Get the Master Log Name and Position from the dump, Make a note of the MASTER_LOG_FILE & MASTER_LOG_POS values<\/p>\n

head dump.sql -n80 | grep "MASTER_LOG_POS"<\/div><\/div>\n

Stop mySQL\/MariaDB<\/p>\n

service mysql stop<\/div><\/div>\n

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<\/p>\n

skip-slave-start<\/div><\/div>\n

Start mySQL\/MariaDB<\/p>\n

service mysql start<\/div><\/div>\n

Log into mySQL and check the Slave is off<\/p>\n

mysql -u root -p
\nSHOW SLAVE STATUS \\G;
\nexit;<\/div><\/div>\n

Load backup into Slave server<\/p>\n

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

Log into mySQL and set Master Settings<\/p>\n

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>>;<\/div><\/div>\n

Stop mySQL\/MariaDB<\/p>\n

service mysql stop<\/div><\/div>\n

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<\/p>\n

skip-slave-start<\/div><\/div>\n

Start mySQL\/MariaDB<\/p>\n

service mysql start<\/div><\/div>\n

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.<\/p>\n

mysql -u root -p
\nSHOW SLAVE STATUS \\G;
\nexit;<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

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>> […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2941","post","type-post","status-publish","format-standard","hentry","category-info-on-tech"],"_links":{"self":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2941"}],"version-history":[{"count":0,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2941\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}