Replication Mysql

1. SQL Statements for Controlling Master Servers
Replication can be controlled through the SQL interface. This section discusses statements for managing master replication servers. Section 12.6.2, “SQL Statements for Controlling Slave Servers”, discusses statements for managing slave servers

PURGE MASTER LOGS Syntax

PURGE {MASTER BINARY} LOGS TO 'log_name'
PURGE {MASTER BINARY} LOGS BEFORE 'date'

Deletes all the binary logs listed in the log index prior to the specified log or date. The logs also are removed from the list recorded in the log index file, so that the given log becomes the first.

Example:

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

The BEFORE variant is available as of MySQL 4.1. Its date argument can be in 'YYYY-MM-DD hh:mm:ss' format. MASTER and BINARY are synonyms, but BINARY can be used only as of MySQL 4.1.1.

This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it has yet to read, the slave will be unable to replicate after it comes up.

To safely purge logs, follow this procedure:

On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

Obtain a listing of the binary logs on the master server with SHOW BINARY LOGS.

Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this is the last log on the list.

Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)

Purge all logs up to but not including the target log.

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see Section 5.1.3, “System Variables”). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

RESET MASTER Syntax

RESET MASTER

Deletes all binary logs listed in the index file, resets the binary log index file to be empty, and creates a new binary log file.

This statement was named FLUSH MASTER before MySQL 3.23.26.
SET SQL_LOG_BIN = {01}

Disables or enables binary logging for the current connection (SQL_LOG_BIN is a session variable) if the client that has the SUPER privilege. The statement is refused with an error if the client does not have that privilege. (Before MySQL 4.1.2, the statement was simply ignored in that case.)

SHOW BINLOG EVENTS Syntax

SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 12.2.7, “SELECT Syntax”.

This statement is available as of MySQL 4.0.
Note

Issuing a SHOW BINLOG EVENTS with no LIMIT clause could start a very time- and resource-consuming process because the server returns to the client the complete contents of the binary log (which includes all statements executed by the server that modify data). As an alternative to SHOW BINLOG EVENTS, use the mysqlbinlog utility to save the binary log to a text file for later examination and analysis. See Section 4.6.6, “mysqlbinlog — Utility for Processing Binary Log Files”.
Note

Events relating to the setting of variables is not included in the output generated when calling SHOW BINLOG EVENTS. To get complete coverage of events within a binary log, use mysqlbinlog.

HOW BINARY LOGS Syntax

SHOW BINARY LOGS
SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as part of the procedure described in Section 12.6.1.1, “PURGE MASTER LOGS Syntax”, that shows how to determine which logs can be purged.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
binlog.000015 724935
binlog.000016 733481
+---------------+-----------+

SHOW MASTER LOGS was added in MySQL 3.23.38. As of MySQL 4.1.1, you can also use SHOW BINARY LOGS, which is equivalent. The File_size column is displayed as of MySQL 5.0.7.
SHOW MASTER STATUS

Provides status information about the binary log files of the master. Example:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+---------------+----------+--------------+------------------+
mysql-bin.003 73 test manual,mysql
+---------------+----------+--------------+------------------+

SHOW SLAVE HOSTS

Displays a list of replication slaves currently registered with the master. Only slaves started with the --report-host=slave_name option are visible in this list.

The list is displayed on any server (not just the master server). The output looks like this:

mysql> SHOW SLAVE HOSTS;
+------------+-----------+------+-----------+
Server_id Host Port Master_id
+------------+-----------+------+-----------+
192168010 iconnect2 3306 192168011
1921680101 athena 3306 192168011
+------------+-----------+------+-----------+

Server_id: The unique server ID of the slave server, as configured in the server's option file, or on the command line with --server-id=value .

Host: The host name of the slave server, as configured in the server's option file, or on the command line with --report-host=value. Note that this can differ from the machine name as configured in the operating system.

Port: The port the slave server is listening on.

Master_id: The unique server ID of the master server that the slave server is replicating from.

Some MySQL versions report another variable, Rpl_recovery_rank. This variable was never used, and was eventually removed.

2. SQL Statements for Controlling Slave Servers

CHANGE MASTER TO Syntax

CHANGE MASTER TO master_def [, master_def] ...
master_def:
MASTER_HOST = 'host_name'
MASTER_USER = 'user_name'
MASTER_PASSWORD = 'password'
MASTER_PORT = port_num
MASTER_CONNECT_RETRY = count
MASTER_LOG_FILE = 'master_log_name'
MASTER_LOG_POS = master_log_pos
RELAY_LOG_FILE = 'relay_log_name'
RELAY_LOG_POS = relay_log_pos
MASTER_SSL = {01}
MASTER_SSL_CA = 'ca_file_name'
MASTER_SSL_CAPATH = 'ca_directory_name'
MASTER_SSL_CERT = 'cert_file_name'
MASTER_SSL_KEY = 'key_file_name'
MASTER_SSL_CIPHER = 'cipher_list'

CHANGE MASTER TO changes the parameters that the slave server uses for connecting to and communicating with the master server. It also updates the contents of the master.info and relay-log.info files.

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER provide information to the slave about how to connect to its master.

The relay log options (RELAY_LOG_FILE and RELAY_LOG_POS) are available beginning with MySQL 4.0.

The SSL options (MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER) are available beginning with MySQL 4.1.1. You can change these options even on slaves that are compiled without SSL support. They are saved to the master.info file, but are ignored unless you use a server that has SSL support enabled.

If you don't specify a given parameter, it keeps its old value, except as indicated in the following discussion. For example, if the password to connect to your MySQL master has changed, you just need to issue these statements to tell the slave about the new password:

STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication

There is no need to specify the parameters that do not change (host, port, user, and so forth).

MASTER_HOST and MASTER_PORT are the hostname (or IP address) of the master host and its TCP/IP port. Note that if MASTER_HOST is equal to localhost, then, like in other parts of MySQL, the port number might be ignored.
Note

Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

If you specify MASTER_HOST or MASTER_PORT, the slave assumes that the master server is different from before (even if you specify a host or port value that is the same as the current value.) In this case, the old values for the master binary log name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave I/O thread should begin reading from the master the next time the thread starts. If you specify either of them, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS. If neither of MASTER_LOG_FILE or MASTER_LOG_POS are specified, the slave uses the last coordinates of the slave SQL thread before CHANGE MASTER was issued. This ensures that replication has no discontinuity, even if the slave SQL thread was late compared to the slave I/O thread, when you just want to change, say, the password to use. This safe behavior was introduced starting from MySQL 4.0.17 and 4.1.1. (Before these versions, the coordinates used were the last coordinates of the slave I/O thread before CHANGE MASTER was issued. This caused the SQL thread to possibly lose some events from the master, thus breaking replication.)

CHANGE MASTER deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay logs are kept; as of MySQL 4.1.1, the relay_log_purge global variable is set silently to 0.

CHANGE MASTER is useful for setting up a slave when you have the snapshot of the master and have recorded the log and the offset corresponding to it. After loading the snapshot into the slave, you can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master on the slave.

The following example changes the master and master's binary log coordinates. This is used when you want to set up the slave to replicate the master:

CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

The next example shows an operation that is less frequently employed. It is used when the slave has relay logs that you want it to execute again for some reason. To do this, the master need not be reachable. You need only use CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD):

CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;

You can even use the second operation in a non-replication setup with a standalone, non-slave server for recovery following a crash. Suppose that your server has crashed and you have restored a backup. You want to replay the server's own binary logs (not relay logs, but regular binary logs), named (for example) myhost-bin.*. First, make a backup copy of these binary logs in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binary logs. If using MySQL 4.1.1 or newer, use SET GLOBAL relay_log_purge=0 for additional safety. Then start the server without the --log-bin option. Before MySQL 4.0.19, start it with a new server ID; in newer versions there is no need; simply use the --replicate-same-server-id option. Start it with --relay-log=myhost-bin (to make the server believe that these regular binary logs are relay logs) and --skip-slave-start options. After the server starts, issue these statements:

CHANGE MASTER TO
RELAY_LOG_FILE='myhost-bin.153',
RELAY_LOG_POS=410,
MASTER_HOST='some_dummy_string';
START SLAVE SQL_THREAD;

The server reads and executes its own binary logs, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shut down the server, delete the master.info and relay-log.info files, and restart the server with its original options.

Specifying the MASTER_HOST option (even with a dummy value) is required to make the server think it is a slave.
LOAD DATA FROM MASTER Syntax

LOAD DATA FROM MASTER

This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL.

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1 of MySQL and above. We will introduce a more advanced technique (called “online backup”) in a future version. That technique will have the additional advantage of working with more storage engines.

For MySQL 5.1 and earlier, the recommended alternative solution to using LOAD DATA FROM MASTER or LOAD TABLE FROM MASTER is using mysqldump or mysqlhotcopy. The latter requires Perl and two Perl modules (DBI and DBD:mysql) and works for MyISAM and ARCHIVE tables only. With mysqldump, you can create SQL dumps on the master and pipe (or copy) these to a mysql client on the slave. This has the advantage of working for all storage engines, but can be quite slow, since it works using SELECT.

This statement takes a snapshot of the master and copies it to the slave. It updates the values of MASTER_LOG_FILE and MASTER_LOG_POS so that the slave starts replicating from the correct position. Any table and database exclusion rules specified with the --replicate-*-do-* and --replicate-*-ignore-* options are honored. --replicate-rewrite-db is not taken into account because a user could use this option to set up a non-unique mapping such as --replicate-rewrite-db="db1->db3" and --replicate-rewrite-db="db2->db3", which would confuse the slave when loading tables from the master.

Use of this statement is subject to the following conditions:

It works only for MyISAM tables. Attempting to load a non-MyISAM table results in the following error:

ERROR 1189 (08S01): Net error reading from master

It acquires a global read lock on the master while taking the snapshot, which prevents updates on the master during the load operation.

If you are loading large tables, you might have to increase the values of net_read_timeout and net_write_timeout on both the master and slave servers. See Section 5.1.3, “System Variables”.

Note that LOAD DATA FROM MASTER does not copy any tables from the mysql database. This makes it easy to have different users and privileges on the master and the slave.

To use LOAD DATA FROM MASTER, the replication account that is used to connect to the master must have the RELOAD and SUPER privileges on the master and the SELECT privilege for all master tables you want to load. All master tables for which the user does not have the SELECT privilege are ignored by LOAD DATA FROM MASTER. This is because the master hides them from the user: LOAD DATA FROM MASTER calls SHOW DATABASES to know the master databases to load, but SHOW DATABASES returns only databases for which the user has some privilege. See Section 12.5.4.6, “SHOW DATABASES Syntax”. On the slave side, the user that issues LOAD DATA FROM MASTER must have privileges for dropping and creating the databases and tables that are copied.
LOAD TABLE tbl_name FROM MASTER

This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL.

Since the current implementation of LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER is very limited, these statements are deprecated in versions 4.1 of MySQL and above. We will introduce a more advanced technique (called “online backup”) in a future version. That technique will have the additional advantage of working with more storage engines.

For MySQL 5.1 and earlier, the recommended alternative solution to using LOAD DATA FROM MASTER or LOAD TABLE FROM MASTER is using mysqldump or mysqlhotcopy. The latter requires Perl and two Perl modules (DBI and DBD:mysql) and works for MyISAM and ARCHIVE tables only. With mysqldump, you can create SQL dumps on the master and pipe (or copy) these to a mysql client on the slave. This has the advantage of working for all storage engines, but can be quite slow, since it works using SELECT.

Transfers a copy of the table from the master to the slave. This statement is implemented mainly debugging LOAD DATA FROM MASTER operations. To use LOAD TABLE, the account used for connecting to the master server must have the RELOAD and SUPER privileges on the master and the SELECT privilege for the master table to load. On the slave side, the user that issues LOAD TABLE FROM MASTER must have privileges for dropping and creating the table.

The conditions for LOAD DATA FROM MASTER apply here as well. For example, LOAD TABLE FROM MASTER works only for MyISAM tables. The timeout notes for LOAD DATA FROM MASTER apply as well.
MASTER_POS_WAIT() Syntax

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)

This is actually a function, not a statement. It is used to ensure that the slave has read and executed events up to a given position in the master's binary log. See Section 11.10.4, “Miscellaneous Functions”, for a full description.
RESET SLAVE Syntax

RESET SLAVE

RESET SLAVE makes the slave forget its replication position in the master's binary logs. This statement is meant to be used for a clean start: It deletes the master.info and relay-log.info files, all the relay logs, and starts a new relay log.
Note

All relay logs are deleted, even if they have not been completely executed by the slave SQL thread. (This is a condition likely to exist on a replication slave if you have issued a STOP SLAVE statement or if the slave is highly loaded.)

Connection information stored in the master.info file is immediately reset using any values specified in the corresponding startup options. This information includes values such as master host, master port, master user, and master password. If the slave SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET SLAVE is issued, these replicated temporary tables are deleted on the slave.

This statement was named FLUSH SLAVE before MySQL 3.23.26.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER Syntax

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

Before MySQL 4.0, omit the GLOBAL keyword from the statement.

SHOW SLAVE STATUS Syntax

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave threads. If you issue this statement using the mysql client, you can use a \G statement terminator rather than a semicolon to obtain a more readable vertical layout:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: gbichot-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: gbichot-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 8

Depending on your version of MySQL, you may not see all the fields just shown. In particular, several fields are present only as of MySQL 4.1.1.

SHOW SLAVE STATUS returns the following fields:

Slave_IO_State

A copy of the State field of the output of SHOW PROCESSLIST for the slave I/O thread. This tells you what the thread is doing: trying to connect to the master, waiting for events from the master, reconnecting to the master, and so on. Possible states are listed in Section 6.3, “Replication Implementation Details”. It is necessary to check this field for older versions of MySQL (prior to 4.1.14) because in these versions the thread could be running while unsuccessfully trying to connect to the master; only this field makes you aware of the connection problem. The state of the SQL thread is not copied because it is simpler. If it is running, there is no problem; if it is not, you can find the error in the Last_Error field (described below).

This field is present beginning with MySQL 4.1.1.

Master_Host

The current master host.

Master_User

The current user used to connect to the master.

Master_Port

The current master port.

Connect_Retry

The current value of the --master-connect-retry option.

Master_Log_File

The name of the master binary log file from which the I/O thread is currently reading.

Read_Master_Log_Pos

The position up to which the I/O thread has read in the current master binary log.

Relay_Log_File

The name of the relay log file from which the SQL thread is currently reading and executing.

Relay_Log_Pos

The position up to which the SQL thread has read and executed in the current relay log.

Relay_Master_Log_File

The name of the master binary log file containing the most recent event executed by the SQL thread.

Slave_IO_Running

Whether the I/O thread is started and has connected successfully to the master. For older versions of MySQL (prior to 4.1.14 and 5.0.12) Slave_IO_Running is YES if the I/O thread is started, even if the slave hasn't connected to the master yet.

Slave_SQL_Running

Whether the SQL thread is started.

Replicate_Do_DB, Replicate_Ignore_DB

The lists of databases that were specified with the --replicate-do-db and --replicate-ignore-db options, if any.

These fields are present beginning with MySQL 4.1.1.

Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

The lists of tables that were specified with the --replicate-do-table, --replicate-ignore-table, --replicate-wild-do-table, and --replicate-wild-ignore_table options, if any.

These fields are present beginning with MySQL 4.1.1.

Last_Errno, Last_Error

The error number and error message returned by the most recently executed query. An error number of 0 and message of the empty string mean “no error.” If the Last_Error value is not empty, it also appears as a message in the slave's error log. For example:

Last_Errno: 1051
Last_Error: error 'Unknown table 'z'' on query 'drop table z'

The message indicates that the table z existed on the master and was dropped there, but it did not exist on the slave, so DROP TABLE failed on the slave. (This might occur, for example, if you forget to copy the table to the slave when setting up replication.)

Skip_Counter

The most recently used value for SQL_SLAVE_SKIP_COUNTER.

Exec_Master_Log_Pos

The position of the last event executed by the SQL thread from the master's binary log (Relay_Master_Log_File). (Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's binary log corresponds to (Relay_Log_File, Relay_Log_Pos) in the relay log.

Relay_Log_Space

The total combined size of all existing relay logs.

Until_Condition, Until_Log_File, Until_Log_Pos

The values specified in the UNTIL clause of the START SLAVE statement.

Until_Condition has these values:

None if no UNTIL clause was specified

Master if the slave is reading until a given position in the master's binary logs

Relay if the slave is reading until a given position in its relay logs

Until_Log_File and Until_Log_Pos indicate the log filename and position values that define the point at which the SQL thread stops executing.

These fields are present beginning with MySQL 4.1.1.

Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key

These fields show the SSL parameters used by the slave to connect to the master, if any.

Master_SSL_Allowed has these values:

Yes if an SSL connection to the master is permitted

No if an SSL connection to the master is not permitted

Ignored if an SSL connection is permitted but the slave server does not have SSL support enabled

The values of the other SSL-related fields correspond to the values of the MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_CIPHER, and MASTER_SSL_KEY options to the CHANGE MASTER statement. See Section 12.6.2.1, “CHANGE MASTER TO Syntax”.

These fields are present beginning with MySQL 4.1.1.

Seconds_Behind_Master

This field is present beginning with MySQL 4.1.1. It is been experimental and has been changed in MySQL 4.1.9. The following applies to slaves running MySQL 4.1.9 or newer. This field is an indication of how “late” the slave is:

When the slave SQL thread is actively running (processing updates), this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.

When the SQL thread has caught up to the slave I/O thread and goes idle waiting for more events from the I/O thread, this field is zero.

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

If the network connection between master and slave is fast, the slave I/O thread is very close to the master, so this field is a good approximation of how late the slave SQL thread is compared to the master. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.

This time difference computation works even though the master and slave do not have identical clocks (the clock difference is computed when the slave I/O thread starts, and assumed to remain constant from then on). Seconds_Behind_Master is NULL (which means “unknown”) if the slave SQL thread is not running, or if the slave I/O thread is not running or not connected to master. For example if the slave I/O thread is sleeping for the number of seconds given by the --master-connect-retry option before reconnecting, NULL is shown, as the slave cannot know what the master is doing, and so cannot say reliably how late it is.

This field has one limitation. The timestamp is preserved through replication, which means that, if a master M1 is itself a slave of M0, any event from M1's binlog which originates in replicating an event from M0's binlog has the timestamp of that event. This enables MySQL to replicate TIMESTAMP successfully. However, the drawback for Seconds_Behind_Master is that if M1 also receives direct updates from clients, the value randomly deviates, because sometimes the last M1's event is from M0 and sometimes it is the most recent timestamp from a direct update.

START SLAVE Syntax

START SLAVE [thread_type [, thread_type] ... ]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_type: IO_THREAD SQL_THREAD

START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads queries from the master server and stores them in the relay log. The SQL thread reads the relay log and executes the queries. 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 logs, 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.

As of MySQL 4.0.2, you can add IO_THREAD and SQL_THREAD options to the statement to name which of the threads to start.

As of MySQL 4.1.1, 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 logs or in the slave relay logs. 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 filename 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 a statement. 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 logs or slave relay logs, 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.

This statement is called SLAVE START before MySQL 4.0.5. SLAVE START is still accepted for backward compatibility, but is now deprecated.

STOP SLAVE Syntax

STOP SLAVE [thread_type [, thread_type] ... ]
thread_type: IO_THREAD SQL_THREAD

Stops the slave threads. STOP SLAVE requires the SUPER privilege.

Like START SLAVE, as of MySQL 4.0.2, this statement may be used with the IO_THREAD and SQL_THREAD options to name the thread or threads to be stopped.

This statement is called SLAVE STOP before MySQL 4.0.5. SLAVE STOP is still accepted for backward compatibility, but is deprecated.