16.1.3.3 Replication Slave Options and Variables

This section describes the server options and system variables that apply to slave replication servers. You can specify the options either on the command line or in an option file. Many of the options can be set while the server is running by using the CHANGE MASTER TO statement. You can specify system variable values using SET.

Server ID.  On the master and each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 232 – 1. Unique means that each ID must be different from every other ID in use by any other replication master or slave. Example my.cnf file:

[mysqld]
server-id=3
Important

Certain --master-xxx options are handled in a special way to ensure that the active replication configuration is not inadvertently altered or affected:

Before MySQL 5.1.17, these options are silently ignored if given unless there is no master.info file. If that file exists, the MySQL server has already previously been configured for replication, so the information in the file is used instead. Because the server gives an existing master.info file precedence over the startup options just described, you might elect not to use startup options for these values at all, and instead to specify the replication parameters associated with them by using the CHANGE MASTER TO statement. See Section 13.4.2.1, “CHANGE MASTER TO Syntax”.

Beginning with MySQL 5.1.17, these options are deprecated and have no effect when mysqld is started. If they are used, an appropriate warning is written to the error log. Instead, you must use CHANGE MASTER TO to set the values corresponding to the deprecated options. These options are removed in MySQL 5.5.

The master.info file format in MySQL 5.1 includes as its first line the number of lines in the file. (See Section 16.2.2, “Replication Relay and Status Logs”.) If you upgrade an older server (before MySQL 4.1.1) to a newer version, the new server upgrades the master.info file to the new format automatically when it starts. However, if you downgrade a newer server to an older version, you should remove the first line manually before starting the older server for the first time.

If no master.info file exists when the slave server starts, it uses the values for those options that are specified in option files or on the command line. This occurs when you start the server as a replication slave for the very first time, when you use CHANGE MASTER TO, or when you have run RESET SLAVE and then have shut down and restarted the slave.

Because the server uses master.info file contents and ignores any startup options that correspond to the values listed in the file, if you start the slave server with different values of the startup options that correspond to values in the file, the different values have no effect. To use different values, the preferred method is to use the CHANGE MASTER TO statement to reset the values while the slave is running. Alternatively, you can stop the server, remove the master.info file, and restart the server with different option values.

Suppose that you specify this option in your my.cnf file to configure a pre-5.1.17 slave server:

[mysqld]
master-host=some_host

The first time you start the server as a replication slave, it reads and uses that option from the my.cnf file. The server then records the value in the master.info file. The next time you start the server, it reads the master host value from the master.info file only and ignores the value in the option file. If you modify the my.cnf file to specify a different master host value of some_other_host, the change has no effect. Instead, use CHANGE MASTER TO to change the value.

This example shows a more extensive use of startup options to configure a pre-5.1.17 slave server:

[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.com

As of 5.1.17, the master-xxx options are ignored and result in warnings in the error log.

Startup options for replication slaves.  The following list describes startup options for controlling replication slave servers. Many of these options can be set while the server is running by using the CHANGE MASTER TO statement. Others, such as the --replicate-* options, can be set only when the slave server starts. Replication-related system variables are discussed later in this section.

System variables used on replication slaves.  The following list describes system variables for controlling replication slave servers. They can be set at server startup and some of them can be changed at runtime using SET. Server options used with replication slaves are listed earlier in this section.