Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual START SLAVE Syntax

START SLAVE [thread_types]

    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

    [thread_type [, thread_type] ... ]

thread_type: IO_THREAD | SQL_THREAD

START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads events from the master server and stores them in the relay log. The SQL thread reads events from the relay log and executes them. START SLAVE requires the SUPER privilege.

If START SLAVE succeeds in starting the slave threads, it returns without any error. However, even in that case, it might be that the slave threads start and then later stop (for example, because they do not manage to connect to the master or read its binary log, or some other problem). START SLAVE does not warn you about this. You must check the slave's error log for error messages generated by the slave threads, or check that they are running satisfactorily with SHOW SLAVE STATUS.

START SLAVE sends an acknowledgment to the user after both the I/O thread and the SQL thread have started. However, the I/O thread may not yet have connected. For this reason, a successful START SLAVE causes SHOW SLAVE STATUS to show Slave_SQL_Running=Yes, but this does not guarantee that Slave_IO_Running=Yes (because Slave_IO_Running=Yes only if the I/O thread is running and connected). For more information, see Section, “SHOW SLAVE STATUS Syntax”, and Section, “Checking Replication Status”.

You can add IO_THREAD and SQL_THREAD options to the statement to name which of the threads to start.

An UNTIL clause may be added to specify that the slave should start and run until the SQL thread reaches a given point in the master binary log or in the slave relay log. When the SQL thread reaches that point, it stops. If the SQL_THREAD option is specified in the statement, it starts only the SQL thread. Otherwise, it starts both slave threads. If the SQL thread is running, the UNTIL clause is ignored and a warning is issued.

For an UNTIL clause, you must specify both a log file name and position. Do not mix master and relay log options.

Any UNTIL condition is reset by a subsequent STOP SLAVE statement, a START SLAVE statement that includes no UNTIL clause, or a server restart.

The UNTIL clause can be useful for debugging replication, or to cause replication to proceed until just before the point where you want to avoid having the slave replicate an event. For example, if an unwise DROP TABLE statement was executed on the master, you can use UNTIL to tell the slave to execute up to that point but no farther. To find what the event is, use mysqlbinlog with the master binary log or slave relay log, or by using a SHOW BINLOG EVENTS statement.

If you are using UNTIL to have the slave process replicated queries in sections, it is recommended that you start the slave with the --skip-slave-start option to prevent the SQL thread from running when the slave server starts. It is probably best to use this option in an option file rather than on the command line, so that an unexpected server restart does not cause it to be forgotten.

The SHOW SLAVE STATUS statement includes output fields that display the current values of the UNTIL condition.

In old versions of MySQL (before 4.0.5), this statement was called SLAVE START. This usage is still accepted in MySQL 5.5 for backward compatibility, but is deprecated and is removed in MySQL 5.6.

Download this Manual
User Comments
  Posted by Jim Grill on February 5, 2008
Useful feature...

Sometimes it's desirable to keep a slave behind the master in the event a bad drop statement causes chaos.

For example to keep a slave behind the master at all times write a script to:

1) get the current master log file and position and save it in a file
2) use the master log file and position from the *previous* run to START SLAVE UNTIL MASTER_LOG_FILE='<log file>', MASTER_LOG_POS=<position>
3) run the script once per hour to keep the slave the behind at least an hour at all times... or every ten minutes to keep it ten minutes behind.

Be sure to add skip-slave-start to your my.cnf
  Posted by Kayra Otaner on October 16, 2009
A gotcha if you're using transactions.
You can't use just any position if you're using transactions on your master. Only position right after 'commit' can be used to start slave 'until'. If the position specified is not the 'commit' position for the transaction enclosed in binlog, MySQL replication will stop at the 'commit' position, not the position you explicitly specified.
Sign Up Login You must be logged in to post a comment.