Table of Contents for

MySQL in a Nutshell, 2nd Edition

This statement changes the settings on a slave server related to the master server and replication. Some of the variables relate to connecting to the master server, and some relate to master log files and the current position in the log files. This statement is run from the slave.

If the slave is engaging in replication, it may be necessary to use the STOP SLAVE statement before using this statement and the START SLAVE statement afterward. These options can be set from the server’s options file, but it’s much better to use this SQL statement to set replication options. MASTER_SSL_VERIFY_SERVER_CERT is available as of version 5.1.18 of MySQL and is comparable to the --ssl-verify-server-cert option. See Chapter 15 for more information on this client option.

Multiple option and value pairs may be given in one CHANGE MASTER TO statement, as long as the pairs are separated by commas. For example, the following SQL statement sets several properties for this slave:

CHANGE MASTER TO
   MASTER_HOST='mysql.company.com',
   MASTER_PORT=3306,
   MASTER_USER='slave_server',
   MASTER_PASSWORD='password',
   MASTER_CONNECT_RETRY=5;

The clauses related to log files name the master log files and provide the slave with the current position of the master log files. This may be necessary when first setting up a new slave or when a slave has been disabled for a while. Use the SHOW MASTER STATUS statement to determine the current position of the master log files, and the SHOW SLAVE STATUS statement to confirm a slave’s position for the related files. Here is an example using the clauses related to log files:

CHANGE MASTER TO
   MASTER_LOG_FILE= 'log-bin.000153',
   MASTER_LOG_POS = 79,
   RELAY_LOG_FILE = 'log-relay.000153',
   RELAY_LOG_POS = 112;

The remaining clauses set various SSL variables. These values are saved to the master.info file. To see the current values for these options, use the SHOW SLAVE STATUS statement.

Relay log options are available as of version 4.1.1 of MySQL. The MASTER_SSL variable is set to 0 if the master does not allow SSL connections, and 1 if it does. The MASTER_SSL_CA variable holds the name of the file that contains a list of trusted certificate authorities (CAs). MASTER_SSL_CAPATH contains the absolute path to that file. The MASTER_SSL_CERT variable specifies the name of the SSL certificate file for secure connections, and MASTER_SSL_KEY specifies the SSL key file used to negotiate secure connections. Finally, MASTER_SSL_CIPHER provides a list of acceptable cipher methods for encryption.