Replication Startup Options

On both the master and the slave, you must use the server-id option to establish a unique replication ID for each server. You should pick a unique positive integer in the range from 1 to 2^32 − 1 for each master and slave. Example: server-id=3

The options that you can use on the master server for controlling binary logging are described in the section called “The Binary Log”.

The following table describes the options you can use on slave replication servers. You can specify them on the command line or in an option file.

Some slave server replication options are handled in a special way, in the sense that they are ignored if a master.info file exists when the slave starts and contains values for the options. The following options are handled this way:

As of MySQL 4.1.1, the following options also are handled specially:

The master.info file format in 4.1.1 changed to include values corresponding to the SSL options. In addition, the 4.1.1 file format includes as its first line the number of lines in the file. If you upgrade an older server to 4.1.1, the new server upgrades the master.info file to the new format automatically when it starts. However, if you downgrade a 4.1.1 or newer server to a version older than 4.1.1, you should manually remove the first line before starting the older server for the first time. Note that, in this case, the downgraded server no longer can use an SSL connection to communicate with the master.

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

If the master.info file exists when the slave server starts, the server ignores those options. Instead, it uses the values found in the master.info file.

If you restart the slave server with different values of the startup options that correspond to values in the master.info file, the different values have no effect, because the server continues to use the master.info file. To use different values, you must either restart after removing the master.info file or (preferably) use the CHANGE MASTER TO statement to reset the values while the slave is running.

Suppose that you specify this option in your my.cnf file:

[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 of some_other_host, the change still will have no effect. You should use CHANGE MASTER TO instead.

Because the server gives an existing master.info file precedence over the startup options just described, you might prefer not to use startup options for these values at all, and instead specify them by using the CHANGE MASTER TO statement. See CHANGE MASTER TO.

This example shows a more extensive use of startup options to configure a 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

The following list describes startup options for controlling replication: Many of these options can be reset 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. We plan to fix this.

--log-slave-updates

Normally, updates received from a master server by a slave are not logged to its binary log. This option tells the slave to log the updates performed by its SQL thread to the slave's own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want a setup like this:

A -> B -> C

That is, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option.

--log-warnings

Makes the slave print more messages to the error log about what it is doing. For example, it will warn you that it succeeded in reconnecting after a network/connection failure, and warn you about how each slave thread started. This option is enabled by default as of MySQL 4.0.19 and 4.1.2; to disable it, use --skip-log-warnings. As of MySQL 4.0.21 and 4.1.3, aborted connections are not logged to the error log unless the value is greater than 1.

This option is not limited to replication use only. It produces warnings across a spectrum of server activities.

--master-connect-retry=seconds

The number of seconds the slave thread sleeps before retrying to connect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60.

--master-host=host

The hostname or IP number of the master replication server. If this option is not given, the slave thread will not be started. The value in master.info takes precedence if it can be read.

--master-info-file=file_name

The name to use for the file in which the slave records information about the master. The default name is mysql.info in the data directory.

--master-password=password

The password of the account that the slave thread uses for authentication when connecting to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

--master-port=port_number

The TCP/IP port the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed. If you have not tinkered with configure options, this should be 3306.

--master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name,

These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options described in the section called “SSL Command-Line Options”. The values in the master.info file take precedence if they can be read.

These options are operational as of MySQL 4.1.1.

--master-user=username

The username of the account that the slave thread uses for authentication when connecting to the master. The account must have the REPLICATION SLAVE privilege. (Prior to MySQL 4.0.2, it must have the FILE privilege instead.) The value in the master.info file takes precedence if it can be read. If the master user is not set, user test is assumed.

--max-relay-log-size=#

To rotate the relay log automatically. See the section called “Server System Variables”.

This option is available as of MySQL 4.0.14.

--read-only

This option causes the slave to allow no updates except from slave threads or from users with the SUPER privilege. This can be useful to ensure that a slave server accepts no updates from clients.

This option is available as of MySQL 4.0.14.

--relay-log=file_name

The name for the relay log. The default name is host_name-relay-bin.nnn, where host_name is the name of the slave server host and nnn indicates that relay logs are created in numbered sequence. You can specify the option to create hostname-independent relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them in some area different from the data directory, or if you want to increase speed by balancing load between disks.

--relay-log-index=file_name

The location and name that should be used for the relay log index file. The default name is host_name-relay-bin.index, where host_name is the name of the slave server.

--relay-log-info-file=file_name

The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.

--relay-log-purge={0|1}

Disables or enables automatic purging of relay logs as soon as they are not needed any more. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge.

This option is available as of MySQL 4.1.1.

--relay-log-space-limit=#

Places an upper limit on the total size of all relay logs on the slave (a value of 0 means “unlimited”). This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some now unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread will exceed the limit until it becomes possible for the SQL thread to delete some relay logs. Not doing so would cause a deadlock (which is what happens before MySQL 4.0.13). You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread will wait for free space because --relay-log-space-limit is exceeded, but the SQL thread will have no relay log to purge and be unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore --relay-log-space-limit.

--replicate-do-db=db_name

Tells the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this will not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. If you need cross-database updates to work, make sure that you have MySQL 3.23.28 or later, and use --replicate-wild-do-table=db_name.%. Please read the notes that follow this option list.

An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement will not be replicated:

USE prices;
UPDATE sales.january SET amount=amount+1000;

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead.

The main reason for this “just-check-the-default-database” behavior is that it's difficult from the statement alone to know whether or not it should be replicated (for example, if you are using multiple-table DELETE or multiple-table UPDATE statements that go across multiple databases). It's also very fast to just check the default database.

--replicate-do-table=db_name.tbl_name

Tells the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This will work for cross-database updates, in contrast to --replicate-do-db. Please read the notes that follow this option list.

--replicate-ignore-db=db_name

Tells the slave to not replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each database. You should not use this option if you are using cross-database updates and you don't want these updates to be replicated. Please read the notes that follow this option list.

An example of what does not work as you might expect: If the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master, the UPDATE statement will be replicated:

USE prices;
UPDATE sales.january SET amount=amount+1000;

If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead.

--replicate-ignore-table=db_name.tbl_name

Tells the slave thread to not replicate any statement that updates the specified table (even if any other tables might be updated by the same statement). To specify more than one table to ignore, use this option multiple times, once for each table. This will work for cross-database updates, in contrast to --replicate-ignore-db. Please read the notes that follow this option list.

--replicate-wild-do-table=db_name.tbl_name

Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the ‘%’ and ‘_’ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list.

Example: --replicate-wild-do-table=foo%.bar% will replicate only updates that use a table where the database name starts with foo and the table name starts with bar.

If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements statements are replicated if the database name matches the pattern foo%.

To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the ‘_’ and ‘%’ characters like this: --replicate-wild-do-table=my\_own\%db. If you're using the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.

--replicate-wild-ignore-table=db_name.tbl_name

Tells the slave thread to not replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list.

Example: --replicate-wild-ignore-table=foo%.bar% will not replicate updates that use a table where the database name starts with foo and the table name starts with bar.

For information about how matching works, see the description of the --replicate-wild-ignore-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.

--replicate-rewrite-db=from_name->to_name

Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name was the default database on the master. This will not work for cross-database updates. Note that the database name translation is done before --replicate-* rules are tested.

If you use this option on the command line and the ‘>’ character is special to your command interpreter, quote the option value. For example:

shell> mysqld --replicate-rewrite-db="olddb->newdb"
--replicate-same-server-id

To be used on slave servers. Usually you can should the default setting of 0, to prevent infinite loops in circular replication. If set to 1, this slave will not skip events having its own server id; normally this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. Be careful that starting from MySQL 4.1, by default the slave I/O thread does not even write binary log events to the relay log if they have the slave's server id (this optimization helps save disk usage compared to 4.0). So if you want to use --replicate-same-server-id in 4.1 versions, be sure to start the slave with this option before you make the slave read its own events which you want the slave SQL thread to execute.

--report-host=host

The hostname or IP number of the slave to be reported to the master during slave registration. This value will appear in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP number of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.

This option is available as of MySQL 4.0.0.

--report-port=port_number

The TCP/IP port for connecting to the slave, to be reported to the master during slave registration. Set it only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, leave this option unset. This option is available as of MySQL 4.0.0.

--skip-slave-start

Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.

--slave_compressed_protocol={0|1}

If this option is set to 1, use compression of the slave/master protocol if both the slave and the master support it.

--slave-load-tmpdir=file_name

The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the to-be-loaded file from the relay log into temporary files, then loads these into the table. If the file loaded on the master was huge, the temporary files on the slave will be huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some filesystem that has a lot of available space. In that case, you may also use the --relay-log option to place the relay logs in that filesystem, because the relay logs will be huge as well. --slave-load-tmpdir should point to a disk-based filesystem, not a memory-based one: The slave needs the temporary files used to replicate LOAD DATA INFILE to survive a machine's restart. The directory also should not be one that is cleared by the operating system during the system startup process.

--slave-net-timeout=seconds

The number of seconds to wait for more data from the master before aborting the read, considering the connection broken, and trying to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the --master-connect-retry option.

--slave-skip-errors= [err_code1,err_code2,... | all]

Normally, replication stops when an error occurs, which gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting the errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option will result in slaves becoming hopelessly out of sync with the master, and you will have no idea why.

For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. The server error codes are listed in Chapter 23, Error Handling in MySQL.

You can (but should not) also use the very non-recommended value of all which will ignore all error messages and keep barging along regardless of what happens. Needless to say, if you use it, we make no promises regarding your data integrity. Please do not complain if your data on the slave is not anywhere close to what it is on the master in this case. You have been warned.

Examples:

--slave-skip-errors=1062,1053
--slave-skip-errors=all

The --replicate-* rules are evaluated as follows to determine whether a statement will be executed by the slave or ignored:

  1. Are there some --replicate-do-db or --replicate-ignore-db rules?

    • Yes: Test them as for --binlog-do-db and --binlog-ignore-db (see the section called “The Binary Log”). What is the result of the test?

      • Ignore the statement: Ignore it and exit.

      • Execute the statement: Don't execute it immediately, defer the decision, go to the next step.

    • No: Go to the next step.

  2. Are there some --replicate-*-table rules?

    • No: Execute the query and exit.

    • Yes: Go to the next step. Only tables that are to be updated are compared to the rules (INSERT INTO sales SELECT * FROM prices: only sales will be compared to the rules). If several tables are to be updated (multiple-table statement), the first matching table (matching “do” or “ignore”) wins. That is, the first table is compared to the rules. Then, if no decision could be mad, the second table is compared to the rules, and so forth.

  3. Are there some --replicate-do-table rules?

    • Yes: Does the table match any of them?

      • Yes: Execute the query and exit.

      • No: Go to the next step.

    • No: Go to the next step.

  4. Are there some --replicate-ignore-table rules?

    • Yes: Does the table match any of them?

      • Yes: Ignore the query and exit.

      • No: Go to the next step.

    • No: Go to the next step.

  5. Are there some --replicate-wild-do-table rules?

    • Yes: Does the table match any of them?

      • Yes: Execute the query and exit.

      • No: Go to the next step.

    • No: Go to the next step.

  6. Are there some --replicate-wild-ignore-table rules?

    • Yes: Does the table match any of them?

      • Yes: Ignore the query and exit.

      • No: Go to the next step.

    • No: Go to the next step.

  7. No --replicate-*-table rule was matched. Is there another table to test against these rules?

    • Yes: Loop.

    • No: We have tested all tables to be updated and could not match any rule. Are there --replicate-do-table or --replicate-wild-do-table rules?

      • Yes: Ignore the query and exit.

      • No: Execute the query and exit.