13.6. Replication Statements

MySQL 5.0

previous page next page

13.6. Replication Statements

This section describes SQL statements related to replication. One group of statements is used for controlling master servers. The other is used for controlling slave servers.

13.6.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 13.6.2, “SQL Statements for Controlling Slave Servers”, discusses statements for managing slave servers.

13.6.1.1.  Syntax

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

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 variant's argument can be in format. and are synonyms.

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:

  1. On each slave server, use to check which log it is reading.

  2. Obtain a listing of the binary logs on the master server with .

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

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

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

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

13.6.1.3.  Syntax

SET SQL_LOG_BIN = {0|1}

Disables or enables binary logging for the current connection ( is a session variable) if the client has the privilege. The statement is refused with an error if the client does not have that privilege.

13.6.1.4.  Syntax

SHOW BINLOG EVENTS
   [IN ''] [FROM ] [LIMIT [,] ]

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

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

Note: Issuing a with no 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 , use the mysqlbinlog utility to save the binary log to a text file for later examination and analysis. See Section 8.10, “mysqlbinlog — Utility for Processing Binary Log Files”.

13.6.1.5.  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 13.6.1.1, “ Syntax”, that shows how to determine which logs can be purged. 

mysql> 
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

is equivalent to . The column is displayed as of MySQL 5.0.7.

13.6.1.6.  Syntax

SHOW MASTER STATUS

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

mysql > 
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

13.6.1.7.  Syntax

SHOW SLAVE HOSTS

Displays a list of replication slaves currently registered with the master. Any slave not started with the option is not visible in this list.

13.6.2. SQL Statements for Controlling Slave Servers

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

13.6.2.1.  Syntax

CHANGE MASTER TO  [, ] ...

:
    MASTER_HOST = ''
  | MASTER_USER = ''
  | MASTER_PASSWORD = ''
  | MASTER_PORT = 
  | MASTER_CONNECT_RETRY = 
  | MASTER_LOG_FILE = ''
  | MASTER_LOG_POS = 
  | RELAY_LOG_FILE = ''
  | RELAY_LOG_POS = 
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = ''
  | MASTER_SSL_CAPATH = ''
  | MASTER_SSL_CERT = ''
  | MASTER_SSL_KEY = ''
  | MASTER_SSL_CIPHER = ''

changes the parameters that the slave server uses for connecting to and communicating with the master server. It also updates the contents of the and files.

, , , , , , , and provide information to the slave about how to connect to its master.

The SSL options (, , , , , and ) can be changed even on slaves that are compiled without SSL support. They are saved to the 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).

and are the hostname (or IP address) of the master host and its TCP/IP port. Note that if is equal to , then, like in other parts of MySQL, the port number might be ignored (if Unix socket files can be used, for example).

If you specify or , 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 and in the statement, and are silently appended to it.

and 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 or . If neither of or are specified, the slave uses the last coordinates of the slave SQL thread before was issued. This ensures that there is no discontinuity in replication, even if the slave SQL thread was late compared to the slave I/O thread, when you merely want to change, say, the password to use.

deletes all relay log files and starts a new one, unless you specify or . In that case, relay logs are kept; the global variable is set silently to 0.

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 ', MASTER_LOG_POS= 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 and start the 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) . 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. Use for additional safety. Then start the server without the option, Instead, use the , (to make the server believe that these regular binary logs are relay logs) and 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 , shut down the server, delete the and files, and restart the server with its original options.

Specifying the option (even with a dummy value) is required to make the server think it is a slave.

13.6.2.2.  Syntax

LOAD DATA FROM MASTER

This statement takes a snapshot of the master and copies it to the slave. It updates the values of and so that the slave starts replicating from the correct position. Any table and database exclusion rules specified with the and options are honored. is not taken into account because a user could use this option to set up a non-unique mapping such as and , 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 tables. Attempting to load a non- 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 and on both the master and slave servers. See Section 5.2.2, “Server System Variables”.

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

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

13.6.2.3.  FROM MASTER Syntax

LOAD TABLE  FROM MASTER

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

The conditions for apply here as well. For example, works only for tables. The timeout notes for apply as well.

13.6.2.4.  Syntax

SELECT MASTER_POS_WAIT('', )

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 12.9.4, “Miscellaneous Functions”, for a full description.

13.6.2.5.  Syntax

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 and 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 statement or if the slave is highly loaded.)

Connection information stored in the 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 is issued, these replicated temporary tables are deleted on the slave.

13.6.2.6.  Syntax

SET GLOBAL SQL_SLAVE_SKIP_COUNTER =

This statement skips the next 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.

13.6.2.7.  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 statement terminator rather than a semicolon to obtain a more readable vertical layout: 

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

returns the following fields:

  • A copy of the field of the output of 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 5.0.12) 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 field (described below).

  • The current master host.

  • The current user used to connect to the master.

  • The current master port.

  • The current value of the option.

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

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

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

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

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

  • 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) is if the I/O thread is started, even if the slave hasn't connected to the master yet.

  • Whether the SQL thread is started.

  • ,

    The lists of databases that were specified with the and options, if any.

  • , , ,

    The lists of tables that were specified with the , , , and options, if any.

  • ,

    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 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 existed on the master and was dropped there, but it did not exist on the slave, so failed on the slave. (This might occur, for example, if you forget to copy the table to the slave when setting up replication.)

  • The most recently used value for .

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

  • The total combined size of all existing relay logs.

  • , ,

    The values specified in the clause of the statement.

    has these values:

    • if no clause was specified

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

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

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

  • , , , , ,

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

    has these values:

    • if an SSL connection to the master is permitted

    • if an SSL connection to the master is not permitted

    • 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 , , , , and options.

  • 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 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 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). is (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 option before reconnecting, 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 successfully. However, the drawback for 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.

13.6.2.8.  Syntax

START SLAVE [ [, ] ... ]
START SLAVE [SQL_THREAD] UNTIL
    MASTER_LOG_FILE = '', MASTER_LOG_POS = 
START SLAVE [SQL_THREAD] UNTIL
    RELAY_LOG_FILE = '', RELAY_LOG_POS = 

: IO_THREAD | SQL_THREAD

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. requires the privilege.

If 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). 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 .

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

An 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 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 clause is ignored and a warning is issued.

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

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

The 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 statement was executed on the master, you can use 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 statement.

If you are using to have the slave process replicated queries in sections, it is recommended that you start the slave with the 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 statement includes output fields that display the current values of the condition.

In old versions of MySQL (before 4.0.5), this statement was called . This usage is still accepted in MySQL 5.0 for backward compatibility, but is deprecated.

13.6.2.9.  Syntax

STOP SLAVE [ [, ] ... ]

: IO_THREAD | SQL_THREAD

Stops the slave threads. requires the privilege.

Like , this statement may be used with the and options to name the thread or threads to be stopped.

In old versions of MySQL (before 4.0.5), this statement was called . This usage is still accepted in MySQL 5.0 for backward compatibility, but is deprecated.

previous page start next page