Once replication failure has been detected, replication must be repaired before any users can access that server. The repair should be run after business hours if possible. Also see Replication Troubleshooting.
Make sure to complete each numbered step before continuing on to the next step, or else replication will fail. For the steps that say "on all replication servers..." you must perform the step on all replication servers (master and slaves) before continuing on to the next step.
- (Recommended) On all replication servers: Block the MySQL service network port (typically 3306) in the windows Firewall for both incoming and outgoing traffic. This step will prevent any client machines from connecting to any database while the repair is in progress.
- Pick a database from one replication server that you think is the most up-to-date database. We will refer to the server where this database is located the 'master' and all other replication servers 'slave' in the text below.
- On the master server, in the Main Menu, click Setup, Advanced Setup, Replication. Click Clear at the lower right. If this button is not visible, the replication failure was not detected in the database, so skip this step and proceed to step 4.
- On all replication servers:
a. Run the following SQL statement: STOP SLAVE; (This stops the replication thread within MySQL.)
b. Stop the MySQL service.
c. Backup and remove all files in the mysql data directory which do not reside in a subfolder, except for any files beginning with "ib" (e.g. iblog_file0, ib_logfile1, ibdata1).
- On all slave servers (not the master): Backup and remove the current replicated database (typically named 'opendental') from the data directory.
You must fully complete the above steps on all replication servers before proceeding to step 6 or replication will fail.
- Copy the database folder on the master to the data directory on all slaves.
- On all replication servers: Repeat steps 6 - 10 from setup (See One Way Replication Setup or Daisy Chain Replication Setup).
- On all replication servers: Verify that replication is running successfully by executing the following SQL statement and checking for the Slave_IO_Running and Slave_SQL_Running columns to both say "Yes": SHOW SLAVE STATUS;
- On all replication servers: If you blocked the MySQL service network port in step 1, unblock it, making sure to unblock incoming and outgoing.
If the database is very large, then the above repair scenario may not be feasible. If you have a deeper understanding of replication and expertise in restarting failed replication, you might consider restarting replication and running all the queries below the failure point in the log.
MySQL also provides an enterprise monitor software that will graphically
show your replication loop and will let you monitor the status of each
server and how many seconds each one is behind its master. While this is a very useful tool, it is also expensive.