Table of Contents for

MySQL in a Nutshell, 2nd Edition

After you create the replication user accounts, configure the servers properly, and load the backed-up databases onto the slave server, you’re ready to begin replication. Execute the following SQL statement while logged in as root or a user with SUPER privileges on the slave:

START SLAVE;

After this statement is run, the slave should connect to the master and get the changes it missed since the backup. From there, it should stay current by continuously interacting with the master, as outlined in the Replication Process” section earlier in this chapter.

If everything is configured correctly on the slave, it will most likely start without a problem and return no message when START SLAVE is executed. However, when the slave tries to connect to the master, the connection may fail. Or when the SQL thread begins processing entries received from the master, it may fail. For whatever reason, if a slave fails after it is started, the client that started the slave will not be informed of the failure, nor will it be informed of the subsequent termination of the slave thread. For that information, you will have to read the slave’s error logs. To confirm a slave is running, you can execute the SHOW SLAVE STATUS statement and check the results to see what state the slave is in, if any. We will describe the various slave states later in this chapter.

By default, the START SLAVE statement starts both the I/O thread and the execution thread as described earlier in the Replication Process” section. You can specify which slave thread to start if you don’t want to start both. You can also specify a particular master binary log file and the position in the log in which to stop replicating. You shouldn’t need to make these distinctions when first starting a slave. These extra options for START SLAVE are useful when debugging a problem with a slave log, and especially when attempting to restore data to a particular position in the log because a user entered an erroneous statement and you want to revert to an earlier point in the database.

Here is an example of these possibilities:

START SLAVE SQL_THREAD
UNTIL MASTER_LOG_FILE = 'relay.0000052',
MASTER_LOG_POS = 254;

You can also control the processing of the relay log file with this syntax, but using the RELAY_LOG_FILE and the RELAY_LOG_POS parameters. You cannot specify a master log position and a relay log position in the same statement, though.

The UNTIL clause will be ignored if the SQL thread is already running. It will also be ignored if a slave already doing replication is shut down and restarted, or if the STOP SLAVE statement is executed followed by a START SLAVE statement without the UNTIL clause. Therefore, to use these options for fine-grained control, restart the slave server with the --skip-slave-start option in the configuration file.