5.10 Troubleshooting Replication

If you have followed the instructions, and your replication setup is not working, first check the following:

• Check the error log for messages . Many users have lost time by not doing this early enough.

• Is the master logging to the binary log? Check with SHOW MASTER STATUS . If it is, Position will be non-zero . If not, verify that you are running the master with the log-bin and server-id options.

• Is the slave running? Use SHOW SLAVE STATUS to check whether the Slave_IO_Running and Slave_SQL_Running values are both Yes . If not, verify the options that were used when starting the slave server.

• If the slave is running, did it establish a connection to the master? Use SHOW PROCESSLIST , find the I/O and SQL threads and check their State column to see how they display. See Section 5.3, "Replication Implementation Details." If the I/O thread state says Connecting to master , verify the privileges for the replication user on the master, master hostname, your DNS setup, whether the master is actually running, and whether it is reachable from the slave.

• If the slave was running before but now has stopped , the reason usually is that some statement that succeeded on the master failed on the slave. This should never happen if you have taken a proper snapshot of the master, and never modify the data on the slave outside of the slave thread. If it does, it is a bug or you have encountered one of the known replication limitations described in Section 5.7, "Replication Features and Known Problems." If it is a bug, see Section 5.11, "Reporting Replication Bugs," for instructions on how to report it.

• If a statement that succeeded on the master refuses to run on the slave, and it is not feasible to do a full database resynchronization (that is, to delete the slave's database and copy a new snapshot from the master), try the following:

  1. Determine whether the slave's table is different from the master's. Try to understand how this happened . Then make the slave's table identical to the master's and run START SLAVE .
     
     
  2. If the preceding step does not work or does not apply, try to understand whether it would be safe to make the update manually (if needed) and then ignore the next statement from the master.
     
     
  3. If you decide that you can skip the next statement from the master, issue the following statements:
     
     

     
     

      mysql>  SET GLOBAL SQL_SLAVE_SKIP_COUNTER =   n   ;  mysql>  START SLAVE;  

     
     The value of n should be 1 if the next statement from the master does not use AUTO_INCREMENT or LAST_INSERT_ID() . Otherwise, the value should be 2. The reason for using a value of 2 for statements that use AUTO_INCREMENT or LAST_INSERT_ID() is that they take two events in the binary log of the master.
     
     
  4. If you are sure that the slave started out perfectly synchronized with the master, and no one has updated the tables involved outside of slave thread, then presumably the discrepancy is the result of a bug. If you are running the most recent version, please report the problem. If you are running an older version of MySQL, try upgrading.