Restore MySQL Replication

an introduction is needed before diving into the procedures…

1. First you need to stop Apache service on web server, or any other measure to prevent other environments from writing to your DBs:
   :~$ sudo service apache2 stop
2. flush / reset queries & flush tables with read lock and disable keys on MASTER:
   mysql> FLUSH PRIVILEGES;
   mysql> FLUSH QUERY CACHE;
   mysql> RESET QUERY CACHE;
   mysql> DISABLE KEYS;
   mysql> FLUSH TABLES WITH READ LOCK;
   
3. take a copy of the ALL the DB’s from MASTER server:
   TIP: if possible, you may want to use an IDE to export / dump since it’s easier and more user friendly
4. make a note of the logs on MASTER, ie mysql-bin.XXXXXX and MASTER_LOG_POS by checking the status of your master:
   mysql> SHOW MASTER STATUS \G;
5. stop and reset slave on your SLAVE(s) server:
   mysql> STOP SLAVE;
   mysql> RESET SLAVE;
   
6. on SLAVE drop DBs and import the new DBs from MASTER:
   TIP: if possible, use an IDE to import ALL DB’s as it is more user friendly
7. reset the SLAVE and update it with master details (see step #4):
   mysql> CHANGE MASTER TO MASTER_HOST=’192.168.174.1‘, MASTER_USER=’your_replication_user‘, MASTER_PASSWORD=’your_replication_password‘, MASTER_LOG_FILE=’mysql-bin.<MASTER_LOG_FILE>‘, MASTER_LOG_POS=<MASTER_LOG_POS>;
   
8. start SLAVE and verify replication is working:
   mysql> START SLAVE;
   mysql> SHOW SLAVE STATUS \G;
   
9. unlock tables and enable keys on MASTER (apgmysql02 / apgmysql05)
   mysql> UNLOCK TABLES;
   mysql> ENABLE KEYS;
   
10. on MASTER test if replication still works:
    mysql> SHOW MASTER STATUS \G;
11. on SLAVE test if replication still works:
    mysql> SHOW SLAVE STATUS \G;
12. start Apache service on web server (or restore any services you’ve stopped on step #1):
    :~$sudo service apache2 start
    
13. trigger some DB reads / writes in production and check replication still works (e.g. the front end of an application, or by running some maintenance tasks on master)
14. communicate results to stake holders – job well done!

Tags: HowToMySQL