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

Deletes all the binary logs listed in the log index that are strictly 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.

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 wants to read, the slave will be unable to replicate once it comes up. The statement is safe to run while slaves are replicating. You do not need to stop them.

To purge logs, follow this procedure:

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 = {0|1}

Disables or enables binary logging for the current connection (SQL_LOG_BIN is a session variable) if the client connects using an account 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
   [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 will be displayed. The LIMIT clause has the same syntax as for the SELECT statement. See SELECT.

This statement is available as of MySQL 4.0.

SHOW MASTER LOGS
SHOW BINARY LOGS

Lists the binary log files on the server. This statement is used as part of the procedure described in the section called “PURGE MASTER LOGS Syntax” for determining which logs can be purged. 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.

SHOW MASTER STATUS

Provides status information on the binary log files of the master.

SHOW SLAVE HOSTS

Displays a list of slaves currently registered with the master. Any slave not started with the --report-host=slave_name option will not be visible in that list.

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 = {0|1}
    | 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'

Changes the parameters that the slave server uses for connecting to and communicating with the master server.

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER provide information for 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 is ignored until 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:

mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> 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 may be ignored (if Unix socket files can be used, for example).

If you specify MASTER_HOST or MASTER_PORT, the slave assumes that the master server is different than 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 can't 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 TO updates the contents of the master.info and relay-log.info files.

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.

Examples:

mysql> 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;

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

The first 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.

The second example shows an operation that is less frequently used. It is done 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 just have to use CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD).

You can even use the second operation in a non-replication setup with a standalone, non-slave server, to recover after 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), supposedly named 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 (different from before) server id; in newer versions there is no need, just 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 with --skip-slave-start. After the server starts, issue these statements:

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

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

For the moment, specifying MASTER_HOST (even with a dummy value) is required to make the server think it is a slave. In the future, we plan to add options to get rid of these small constraints.

LOAD DATA FROM MASTER

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 will start 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 one user could, with this option, 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 it loads the master's tables).

Use of this statement is subject to the following conditions:

In the future, it is planned to make this statement work with InnoDB tables and to remove the need for a global read lock by using non-blocking online backup.

If you are loading big tables, you might have to increase the values of net_read_timeout and net_write_timeout on both your master and slave servers. See the section called “Server 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.

The LOAD DATA FROM MASTER statement requires the replication account that is used to connect to the master to 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 will hide 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 SHOW DATABASES. On the slave's side, the user that issues LOAD DATA FROM MASTER should have grants to drop and create the databases and tables that are copied.

LOAD TABLE tbl_name FROM MASTER

Transfers a copy of the table from master to the slave. This statement is implemented mainly for debugging of LOAD DATA FROM MASTER. It requires that the account used for connecting to the master server has the RELOAD and SUPER privileges on the master and the SELECT privilege on the master table to load. On the slave side, the user that issues LOAD TABLE FROM MASTER should have privileges to drop and create the table.

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

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)

This is 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 the section called “Miscellaneous Functions” for a full description.

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 totally 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 = n

Skip 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

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 semicolon to get 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:

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 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 don't manage to connect to the master or read its binary logs, or some other problem). START SLAVE will not warn you about this. You must check your slave's error log for error messages generated by the slave threads, or check that they are running fine 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 already running, the UNTIL clause is ignored and a warning is issued.

With 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. For the moment, SLAVE START is still accepted for backward compatibility, but is deprecated.

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 stop.

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