(mysql.info) replication-problems

 
 6.11 Troubleshooting Replication
 ================================
 
 If you have followed the instructions, and your replication setup is
 not working, the first thing to do is _check the error log for
 messages_. Many users have lost time by not doing this soon enough
 after encountering problems.
 
 If you cannot tell from the error log what the problem was, try the
 following techniques:
 
    * Verify that the master has binary logging enabled by issuing a
      `SHOW MASTER STATUS' statement. If logging is enabled, `Position'
      is non-zero. If binary logging is not enabled, verify that you are
      running the master with the -log-bin and -server-id options.
 
    * Verify that the slave is 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. For example, -skip-slave-start prevents
      the slave threads from starting until you issue a `START SLAVE'
      statement.
 
    * If the slave is running, check whether it established a connection
      to the master. Use `SHOW PROCESSLIST', find the I/O and SQL
      threads and check their `State' column to see what they display.
      See  replication-implementation-details. If the I/O thread
      state says `Connecting to master', verify the privileges for the
      replication user on the master, the master hostname, your DNS
      setup, whether the master is actually running, and whether it is
      reachable from the slave.
 
    * If the slave was running previously but 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 modified the data on the slave
      outside of the slave thread. If the slave stops unexpectedly, it
      is a bug or you have encountered one of the known replication
      limitations described in  replication-features. If it is a
      bug, see  replication-bugs, for instructions on how to
      report it.
 
    * If a statement that succeeded on the master refuses to run on the
      slave, try the following procedure if it is not feasible to do a
      full database resynchronization by deleting the slave's databases
      and copying a new snapshot from the master:
 
        1. Determine whether the affected table on the slave is
           different from the master table. 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 that no one has updated the
           tables involved outside of the slave thread, then presumably
           the discrepancy is the result of a bug. If you are running
           the most recent version of MySQL, please report the problem.
           If you are running an older version, try upgrading to the
           latest production release to determine whether the problem
           persists.
Info Catalog
(mysql.info) replication-faq
(mysql.info) replication
(mysql.info) replication-bugs
automatically generated byinfo2html