• MySQL 5.1 Reference Manual :: 12 SQL Statement Syntax :: 12.5 Replication Statements :: 12.5.2 SQL Statements for Controlling Slave Servers :: 12.5.2.1 CHANGE MASTER TO Syntax
  • 12.5.2.1. CHANGE MASTER TO Syntax

    CHANGE MASTER TO option [, option] ...
    
    option:
        MASTER_BIND = 'interface_name'
      | MASTER_HOST = 'host_name'
      | MASTER_USER = 'user_name'
      | MASTER_PASSWORD = 'password'
      | MASTER_PORT = port_num
      | MASTER_CONNECT_RETRY = interval
      | MASTER_HEARTBEAT_PERIOD = interval
      | 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'
      | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
    

    CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master server, for reading the master binary log, and reading the slave relay log. It also updates the contents of the master.info and relay-log.info files. To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary).

    Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. 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
    

    MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the slave about how to connect to its master:

    • MASTER_HOST and MASTER_PORT are the host name (or IP address) of the master host and its TCP/IP port.

      Note

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

      If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file 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.

      Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as not setting MASTER_HOST at all. Setting this option to an empty string causes START SLAVE subsequently to fail. This issue is addressed in MySQL 5.5. (Bug#28796)

    • MASTER_USER and MASTER_PASSWORD are the user name and password of the account to use for connecting to the master.

    The MASTER_SSL_xxx options provide information about using SSL for the connection. They correspond to the --ssl-xxx options described in Section 5.5.6.3, “SSL Command Options”, and Section 16.3.7, “Setting Up Replication Using SSL”. MASTER_SSL_VERIFY_SERVER_CERT was added in MySQL 5.1.18. These options can be changed even on slaves that are compiled without SSL support. They are saved to the master.info file, but are ignored if the slave does not have SSL support enabled.

    MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60. The number of reconnection attempts is limited by the --master-retry-count server option; for more information, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.

    The next two options (MASTER_BIND and MASTER_HEARTBEAT_PERIOD) are available in MySQL Cluster NDB 6.3 and later, but are not supported in mainline MySQL 5.1:

    MASTER_BIND is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master. It is also possible to determine which network interface is to be used in such cases by starting the slave mysqld process with the --master-bind option.

    The ability to bind a replication slave to specific network interface was added in MySQL Cluster NDB 6.3.4.

    MASTER_HEARTBEAT_PERIOD is used to set the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution to hundredths of a second; the smallest nonzero value is 0.01. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.

    Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.

    Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.

    MASTER_HEARTBEAT_PERIOD was added in MySQL Cluster NDB 6.3.4.

    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. RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at which the slave SQL thread should begin reading from the relay log the next time the thread starts. If you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS. If neither of MASTER_LOG_FILE or MASTER_LOG_POS is specified, the slave uses the last coordinates of the slave SQL thread before CHANGE MASTER TO 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.

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

    Beginning with MySQL 5.1.47, invoking CHANGE MASTER TO causes the previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and MASTER_LOG_POS to be written to the error log, along with other information about the slave's state prior to execution.

    CHANGE MASTER TO is useful for setting up a slave when you have the snapshot of the master and have recorded the master binary log coordinates corresponding to the time of the snapshot. After loading the snapshot into the slave to synchronize it to the slave, you can run CHANGE MASTER TO MASTER_LOG_FILE='log_name', MASTER_LOG_POS=log_pos on the slave to specify the coodinates at which the slave should begin reading the master binary log.

    The following example changes the master server the slave uses and establishes the master binary log coordinates from which the slave begins reading. 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 log files 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 nonreplication setup with a standalone, nonslave server for recovery following a crash. Suppose that your server has crashed and you have restored it from a backup. You want to replay the server's own binary log files (not relay log files, but regular binary log files), named (for example) myhost-bin.*. First, make a backup copy of these binary log files in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binary log. Use SET GLOBAL relay_log_purge=0 for additional safety. Then start the server without the --log-bin option, Instead, use the --replicate-same-server-id, --relay-log=myhost-bin (to make the server believe that these regular binary log files are relay log files) 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 log files, 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.

    The following table shows the maximum allowable length for the string-valued options.

    Option Maximum Length
    MASTER_HOST 60
    MASTER_USER 16
    MASTER_PASSWORD 32
    MASTER_LOG_FILE 255
    RELAY_LOG_FILE 255
    MASTER_SSL_CA 255
    MASTER_SSL_CAPATH 255
    MASTER_SSL_CERT 255
    MASTER_SSL_KEY 255
    MASTER_SSL_CIPHER 511