MySQL manual version 5.0.20-mysql sync (iv)

Mysql

6.8 Sync startup options

Whether it's master or slave, set the Server-id option to make sure they all have their own unique sync IDs. You must select a positive integer between 1 and 2^32-1. For example: server-id=3.

For a detailed description of the options available on the master server to control binary logging, see "5.9.4 the Binary log".

The following table describes the options available for slave synchronization, which you can set in the command line or in a configuration file.

Some slave synchronization options are handled in a specific way, and slave will skip them if there is a ' master.info ' file and include these options when Slave is started. These options are as follows:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

Starting with the MySQL 4.1.1, the options are handled in the same way:

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

In the MySQL 4.1.1, the format of the ' Master.info ' file changed to include the appropriate SSL option. In addition, the MySQL 4.1.1 file format also includes the total number of rows in the first line of the file. If you upgrade from an older version to 4.1.1, the server automatically upgrades ' Master.info ' for the new format when it starts. However, if you demote from 4.1.1 to an older version, you need to manually delete the first line of the file when the system first starts. Note that in this case, the degraded server will no longer be able to connect to master with the SSL option.

If the ' master.info ' file is not present at slave startup, it is started using the parameter values specified in the command line or in the configuration file. This is true every time you start the SLAVE server for the first time, or after the reset SLAVE statement is closed and the SLAVE is restarted.

If the ' master.info ' file is present when the slave is started, it skips the options and reads the value directly from the ' Master.info ' file.

If the option value used when restarting salve is not the same as in ' Master.info ', the new value will not take effect because the slave server still reads only the ' master.info ' file. To use a different option value, you can restart the slave after deleting ' master.info ' or use the Change master to statement (recommended) To reset the option value.

Suppose the following option values are set in ' my.cnf ':

[Mysqld]

Master-host=some_host

The first time you start slave, it reads the option values from ' my.cnf ' and then saves them in ' Master.info '. The next time you restart Slave, it will only read the contents of ' Master.info ' and skip the option value in ' my.cnf '. Attempting to modify ' my.cnf ' to alter the sync option is not feasible, but it can be done by executing the change MASTER to statement:

Because the server considers ' Master.info ' to have a higher priority than the configuration file, it is recommended that you do not attach synchronization options at startup, but only the change master to statement. Please see "14.6.2.1 change MASTER to Syntax" for details.

The following example shows some extended options for configuring slave:

[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 startup options described below are used to control synchronization: Most of them can be changed at runtime with the change MASTER to statement. Other, such as--replicate-*, can only be specified when the salve is started. We intend to solve this problem in the future.

--log-slave-updates

Typically, slave receives update operations from master and does not record them in binary logs. This option tells the slave SQL thread to record these update operations in the binary log. If you want this option to work, you need to enable the--log-bin option at the same time to enable binary logging. When using the synchronization chain mechanism, you need to use the--log-slave-updates option. For example, you might need to set the following synchronization relationships:

A-> B-> C

Here, a master,b as B is the Master of C. B is also slave and master, the--log-bin option needs to be enabled on both A and B, and the--log-slave-updates option needs to be enabled on B.

--log-warnings

Let slave log more error logs when performing a synchronization. For example, it notifies you of a successful reconnection after a network/connection failure, and informs how each slave thread starts. This option is enabled by default after MySQL 4.0.19 and 4.1.12, and can be disabled with--skip-log-warnings. Starting with MySQL 4.0.21 and MySQL 4.1.3, the discarded connection is no longer logged in the error log unless the value of this option is greater than 1. This option is not just for limiting synchronization, it produces warnings that span most operations.

--master-connect-retry=seconds

The number of seconds that slave sleeps before reconnecting to master after the master computer or network connection is interrupted. If it exists in the ' master.info ' file, use it preferentially. If not set, the default is 60.

--master-host=host

Host name or IP address of the master server. If it is not set, the slave thread cannot start. If present, the option value in the ' Master.info ' file is first read.

--master-info-file=file_name

Slave the name of the file that records master information. The default name is ' Master.info ', placed in the data file directory.

--master-password=password

The password for the account that is authorized to connect to run synchronization on master. If present, the option value in the ' Master.info ' file is first read. If it is not set, it is considered a blank password.

--master-port=port_number

The TCP/IP port number that is listening on master. If present, the option value in the ' Master.info ' file is first read. If it is not set, it is considered a precompiled setting. If the Configure option parameter is not modified, then it is 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

Use to set the option to securely connect to master with SSL. Their meaning corresponds to the--ssl,--ssl-ca,--ssl-capath,--ssl-cert,--ssl-cipher,--ssl-key options mentioned in "5.6.7.5 SSL command-line Options." If present, the option value in the ' Master.info ' file is first read. These options are available from the MySQL 4.1.1.

--master-user=username

An account that is authorized to connect to run synchronization on master. This account must have REPLICATION SLAVE permissions (before MySQL 4.0.2, FILE permissions). If present, the option value in the ' Master.info ' file is first read. If it is not set, it is considered test.

--max-relay-log-size=#

The size of the automatic rotation (rotate) of the relay log. For more information, see "5.2.3 Server System Variables". This option is available after MySQL 4.0.14.

--read-only

This option makes slave unable to update data except slave threads or users with SUPER privileges. This ensures that slave does not accept updates from other clients. This option is available from the MySQL 4.0.14.

--relay-log=file_name

The name of the relay log. The default name is Host_name-relay-bin.nnn,host_name is the host name of the slave server, nnn refers to the sequence number of the relay log. You can use this option to create relay logs that do not rely on host names, or to increase the speed by increasing the size of the relay log (you do not want to lower the value of the max_relay_log_size) and by placing them in a non-data file directory, or by using load balancing between disks.

--relay-log-index=file_name

The location and file name of the relay log index file. Its default value is Host_name-relay-bin.index,host_name is the slave server host name.

--relay-log-info-file=file_name

Slave the file name on which the relay log information is logged. The default is ' Relay-log.info ' in the data file directory.

--RELAY-LOG-PURGE={0|1}

Disable or enable automatic scavenging when you do not need a relay log. The default value is 1 (enabled). This is a global variable that can be dynamically changed with the SET global Relay_log_purge. This option can be used from the MySQL 4.1.1 start.

--relay-log-space-limit=#

Used to increase the total size of the relay log on the slave (if the value is 0 for "infinite"). This is useful when the slave host has only limited space. When this limit is reached, the I/O thread continues to read the binary log events from master until the SQL thread deletes some unwanted relay logs. Note that this restriction is not absolute: SQL threads may need more binary log events before deleting the relay log. In this case, the I/O thread goes beyond this limit until the SQL thread deletes some of the relay logs, because if not, it can lead to deadlocks (as was the case before MySQL 4.0.13). Do not set the value of the--relay-log-space-limit value to be less than twice times--max-relay-log-size (--max-binlog-size if the--max-relay-log-size value is 0). In this case, the I/O thread needs to wait for more space because it has exceeded--relay-log-space-limit, but the SQL thread does not have a relay log that can be deleted to satisfy the I/O thread requirements. This forces the I/O thread to temporarily ignore the--relay-log-space-limit limit.

--replicate-do-db=db_name

Tells Slave to synchronize only those statements where the default database is Db_name (that is, the use is selected). To specify more databases, you can specify one database at a time by using this option multiple times. Note that cross-Library action statements such as UPDATE some_db.some_table SET foo= ' bar ' and operations that do not have the selected database are not synchronized. If you must use a cross library operation, be sure to use MySQL 3.23.28 or higher, and use the--replicate-wild-do-table=db_name.% option. Please read the following notes carefully.

Here's an example of a failure to work as expected: If you start slave with the--replicate-do-db=sales option and execute the following statement on master, the UPDATE statement will not be synchronized:

Use prices;

UPDATE sales.january SET amount=amount+1000;

If you need to synchronize a cross library operation, simply use the--replicate-wild-do-table=db_name.% option. The main reason for this "check default database Only" feature is that it is difficult to determine whether you want to be synchronized in a single statement (for example, by using multiple table DELETE or UPDATE, which spans the library). However, it is very quick to check whether it is the default database.

--replicate-do-table=db_name.tbl_name

Tells Slave to synchronize only those specified data tables. To specify more data tables, use this option multiple times to specify one datasheet at a time. This option supports cross library updates, as opposed to the--REPLICATE-DO-DB option. Please read the following notes carefully.

--replicate-ignore-db=db_name

Tell slave not to sync those statements where the default database is Db_name (that is, the use is selected). To specify more databases, you can specify one database at a time by using this option multiple times. Do not use this option if you have a cross library operation and you want these operations to be synchronized. Please read the following notes carefully.

Here's an example of a failure to work as expected: If you start slave with the--replicate-ignore-db=sales option and execute the following statement on master, the UPDATE statement will not be synchronized:

Use prices;

UPDATE sales.january SET amount=amount+1000;

To allow cross library operations to be synchronized properly, simply use the--replicate-wild-ignore-table=db_name.% option.

--replicate-ignore-table=db_name.tbl_name

Tells slave not to synchronize any update statements (even other tables that are updated with the same statement) for the specified data table. To specify more data tables, use this option multiple times to specify one datasheet at a time. This option supports cross library updates, as opposed to the--REPLICATE-IGNORE-DB option. Please read the following notes carefully.

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

Limit slave only those data tables that match the specified pattern are synchronized. The pattern can contain wildcard characters '% ' and ' _ ', and they have the same meaning as like mode. To specify more data tables, use this option multiple times to specify one datasheet at a time. Please read the following notes carefully.

For example,--replicate-wild-do-table=foo%.bar% synchronizes the update operations on a datasheet that starts with bar in all databases that start with Foo.

If the match pattern is%, all table names are matched and applied to database-level statements (CREATE database, DROP database, and ALTER database). For example, with the--replicate-wild-do-table=foo%.% option, all database-level operations that match the foo% pattern are synchronized.

If you want to include the literal wildcard characters in the database/table schema, you need to escape them with backslashes. For example, if you want to synchronize all the tables under the MY_OWN%DB database, but do not want to synchronize the tables under the MY1OWNAABCDB database, you need to escape the character ' _ ':--replicate-wild-do-table=my\_own\%db. If you use this option on the command line, you may need two backslashes to escape, which depends on the command line interpreter. For example, under the bash shell, you would need to enter:--replicate-wild-do-table=my\\_own\\%db.

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

Limit slave The data tables that match the specified pattern, in different steps. To specify more data tables, use this option multiple times to specify one datasheet at a time. Please read the following notes carefully.

For example,--replicate-wild-ignore-table=foo%.bar% will not synchronize the update operations on a datasheet that starts with bar in all databases that start with Foo.

To see how matching patterns work, look at the specific description of the--replicate-wild-ignore-table option. The pattern contains the same rules as the--replicate-wild-ignore-table option that contain the literal wildcard characters.

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

Tell slave to convert the default database on Master From_name (only with use) to To_name. Only statements involving data tables (excluding similar CREATE database, DROP database, and ALTER database) are synchronized and are only from_name for the default database on master. This option does not support cross library operations. Note that the database name conversion precedes the--replicate-* rule before the test. If you use this option on the command line, you need to quote the ' > ' character in quotes. For example:

shell> mysqld--replicate-rewrite-db= "olddb->newdb"

--replicate-same-server-id

This option is used on top of slave. It is typically used with its default value of 0 to avoid an infinite synchronization loop. If set to 1, then slave will not ignore the update log that has the same server number as it is, and it is typically used only in relatively rare configurations. If the--log-slave-updates option is enabled, it cannot be set to 1. Note that, starting with MySQL 4.1, the slave I/O thread does not write the binary log of the server number containing the slave to the relay log by default (compared to 4.0 this saves disk use). So you want to use the--replicate-same-server-id option in 4.1 to use this option when slave reads your update event to make sure that the SQL thread is starting the slave.

--report-host=host

The master hostname or IP address to report when Salve is registered. The show SLAVE HOSTS statement is displayed on master when it is executed. You do not need to set this option if you do not want slave to be registered to master. Note that after the slave is connected to master, the slave IP address is not read directly from the TCP/IP socket based on this configuration master. Because of the presence of NAT or other routing mechanisms, this IP information is not sufficient to connect to slave on master or other hosts. This option is available from the MySQL 4.0.0 start.

--report-port=port_number

Connect to the Slave TCP/IP port, which is used when slave is registered to master. You do not need to set this value unless slave is listening on a non-default port or from master or other clients to a specific tunnel used to connect to slave. If you are unsure, do not set it. This option is available from the MySQL 4.0.0 start.

--skip-slave-start

Tell the slave server not to run the slave thread when it starts. Simply use the start SLAVE statement to start the SLAVE thread.

--SLAVE_COMPRESSED_PROTOCOL={0|1}

If it is set to 1 and is supported by Maste/slave, the data is transmitted using a compression protocol.

--slave-load-tmpdir=file_name

Slave the directory where temporary files are created. This option value defaults to the same value as the system variable TMPDIR. When the slave SQL thread synchronizes the load DATA INFILE statement, it extracts the files to be loaded from the relay log into the temporary file and loads them into the slave. If the files loaded on master are large, the temporary files on the slave will also be large. It is therefore recommended that you specify this option on the slave to place it on a file system with a relatively higher number of remaining space. This is, preferably, also specify--relay-log to that filesystem, because the relay log can also be very large. --slave-load-tmpdir must point to a disk-based file system, not a memory-based filesystem: Slave may need this temporary file to synchronize the load DATA INFILE statement after the machine restarts. This directory also cannot be in the directory that will be purged by the operating system's startup process.

--slave-net-timeout=seconds

Salve the number of seconds to wait to read more data from Master after abandoning the read, taking into account the disconnect and trying to reconnect. The first retry is performed immediately after the timeout. The time interval for retries is controlled by the option--master-connect-retry.

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

Typically, synchronization stops when an error occurs to provide manual resolution of data inconsistencies. This option tells the slave SQL thread to continue running when the error code returned when executing the statement is in the list.

Do not set this option unless you really understand why the error occurred. If synchronization settings and client programs do not have bugs and are not MySQL's own bugs, then there is no error to stop the synchronization. Using this option without discrimination can cause slave to hopelessly deviate from Master's sync, and you don't know why.

You can see the error code in the result of the SLAVE error log and the show SLAVE STATUS statement on SLAVE. Server error code details please see "Error Handling in MySQL".

You can also (preferably not) use a very deprecated value of all, which ignores all error messages and continues to be synchronized, regardless of the situation. Needless to say, if you use this option value, you cannot expect the integrity of the data. In this case, you can't complain that the slave data is not close to master anywhere. I've already warned you. For example:

--slave-skip-errors=1062,1053

--slave-skip-errors=all

The--replicate-* mode determines whether a statement is to be executed or ignored according to the following rules:

Do you have--replicate-do-db or--replicate-ignore-db rules?

Yes: Test the--BINLOG-DO-DB and--BINLOG-IGNORE-DB options (see "5.9.4 the Binary Log" for details). Test results?

Ignore: Ignore and exit.

Execution: Do not execute immediately, postpone judgment, to the next step.

No: To the next step.

Is there a--replicate-*-table rule?

No: Execute and exit.

Yes: Go to the next step. Only tables to be updated are compared to this rule (INSERT into sales SELECT * FROM prices: Only sales and rule comparisons). If several tables are updated together (multiple-table statements), the First matching table (matching ' do ' or ' ignore ') wins. In other words, only the first table and the rules are compared. Then, if no decision has been made, compare the second table, and so on.

Is there a--replicate-do-table rule?

Yes: does this table match these rules?

Yes: Execute and exit.

No: Go to the next step.

No: Go to the next step.

Is there a--replicate-ignore-table rule?

Yes: does this table match these rules?

Yes: Ignore and exit.

No: Go to the next step.

No: Go to the next step.

Is there a--replicate-wild-do-table rule?

Yes: does this table match these rules?

Yes: Execute and exit.

No: Go to the next step.

No: Go to the next step.

Is there a--replicate-wild-ignore-table rule?

Yes: does this table match these rules?

Yes: Ignore and exit.

No: Go to the next step.

No: Go to the next step.

No matching--replicate-*-table rule. Are there other tables that match these rules?

Yes: Loop lookup match.

No: All tables to be updated have been tested and no matching rules found. Do you have--replicate-do-table or--replicate-wild-do-table rules?

Yes: Ignore and exit.

No: Execute and exit.