CHANGE MASTER TO

This MySQL statement changes the settings on a slave server related to the master server and replication.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CHANGE MASTER TO 
[MASTER_HOST = 'host' |
MASTER_USER = 'user' |
MASTER_PASSWORD = 'password' |
MASTER_PORT = port |
MASTER_CONNECT_RETRY = count |
MASTER_LOG_FILE = 'filename' |
MASTER_LOG_POS = position |
RELAY_LOG_FILE = 'filename' |
RELAY_LOG_POS = position |
MASTER_SSL = {0|1} |
MASTER_SSL_CA = 'filename' |
MASTER_SSL_CAPATH = 'path' |
MASTER_SSL_CERT = 'filename' |
MASTER_SSL_KEY = 'filename' |
MASTER_SSL_CIPHER = 'list' |
MASTER_SSL_VERIFY_SERVER_CERT = {0|1}], [,...]

Explanation

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.

Examples

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 secure socket layer (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 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.