MySQL synchronization (4) startup options

6.8 synchronization startup options

You must set the server-ID Option for both master and slave to ensure that each of them has a unique synchronization ID. You must select a positive integer between 1 and 2 ^ 32-1. For example, server-id = 3.

For details about the options that can be used to control binary log records on the master server, see "5.9.4 the binary log ".

The following table describes the options available for slave synchronization. You can set them in the command line or configuration file.

Some slave synchronization options are processed in a specific way. When slave is started, if the 'master. info' file exists and contains these options, slave will skip them. These options are as follows:

-- Master-host

-- Master-user

-- Master-Password

-- Master-Port

-- Master-connect-retry

Starting from MySQL 4.1.1, the following options are also handled in special ways:

-- Master-SSL

-- Master-SSL-ca

-- Master-SSL-capath

-- Master-SSL-Cert

-- Master-SSL-Cipher

-- Master-SSL-Key

In MySQL 4.1.1, the format of the 'master. info' file is changed to include the corresponding SSL options. In addition, the MySQL 4.1.1 file format also includes the total number of file lines in the first line. If you upgrade from the old version to 4.1.1, The 'master. info' is automatically upgraded to the new format when the server is started. However, if you downgrade from 4.1.1 to the old version, you need to manually delete the first line of the file when the system starts for the first time. Note: In this case, the degraded server cannot use the SSL option to connect to the master.

If the 'master. info' file does not exist during slave startup, it will start with the parameter value specified in the command line or configuration file. This is the case when the slave server is started for the first time, or after the reset slave statement is executed and the slave is restarted.

If the 'master. info' file exists during slave startup, it skips these options and directly reads the values in the 'master. info' file.

If the option value used when you restart salve is different from that in 'master. info', the new value will not take effect because the slave server still only reads the 'master. info' file. To use different option values, you can restart slave after deleting 'master. info' or use the change master to Statement (recommended) to reset the option values.

Assume that the following option values are set in 'my. CNF:

[Mysqld]

Master-host = some_host

When slave is started for the first time, it reads option values from 'my. CNF 'and saves them in 'master. info. The next time you restart slave, it will only read the content of 'master. info' and skipped the option value in 'my. CNF. It is not feasible to change the synchronization option by modifying 'my. CNF '. However, you can execute the change master to statement:

Because the server thinks that the priority of 'master. info' is higher than that of the configuration file, we recommend that you do not add synchronization options at startup, but only use the change master to statement. For details, see "14.6.2.1 change master to Syntax ".

The following example shows some slave configuration extension options:

[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 startup options are used to control synchronization: most of them can be changed by the change master to statement at runtime. Others, such as -- replicate-*, can only be specified when salve is started. We plan to solve this problem in the future.

-- Log-slave-Updates

Generally, slave does not record updates in binary logs after receiving updates from the master. This option tells the slave SQL thread to record these update operations in the binary log. To enable this option, you must enable the -- log-bin option at the same time to enable binary logs. When using the synchronization chain mechanism, you must use the -- log-slave-Updates option. For example, you may need to set the following synchronization relationship:

A-> B-> C

Here, a acts as the master of B and B acts as the master of C. B is both a Server Load balancer and a master. You must enable the -- log-bin option on both A and B, and enable the -- log-slave-Updates option on B.

-- Log-Warnings

Let slave record more error logs during synchronization. For example, it will notify you that the reconnection is successful after the Network/connection fails and how each slave thread starts. This option is enabled by default after MySQL 4.0.19 and 4.1.12. You can disable it with -- skip-log-warnings. Starting from MySQL 4.0.21 and MySQL 4.1.3, dropped connections are no longer recorded in error logs unless the value of this option is greater than 1. This option is not only used to limit synchronization, but also generates warnings that span most operations.

-- Master-connect-retry = seconds

The number of seconds before slave reconnects to the master when the master machine or network connection is interrupted. If the 'master. info' file exists, use it first. If no value is set, the default value is 60.

-- Master-host = Host

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

-- Master-Info-file = file_name

The name of the file where slave records master information. The default name is 'master. info', which is placed in the data file directory.

-- Master-Password = Password

The account password used to be authorized to connect to the master to run synchronization. If yes, the option value in the 'master. info' file is read first. If no password is set, it is treated as a blank password.

-- Master-Port = port_number

The TCP/IP Port Number listened on the master. If yes, the option value in the 'master. info' file is read first. If this parameter is not set, it is used as the pre-compiled setting. If the configure option parameter is not modified, 3306 is used.

-- 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

Used to set the option to connect to the master with SSL security. Their meanings correspond to -- SSL, -- SSL-Ca, -- SSL-capath, -- SSL-cert, -- SSL-Cipher, -- SSL-key option. If yes, the option value in the 'master. info' file is read first. These options can be used since MySQL 4.1.1.

-- Master-user = Username

Account authorized to connect to the master to run synchronization. This account must have the replication slave permission (File Permission before MySQL 4.0.2 ). If yes, the option value in the 'master. info' file is read first. If it is not set, it is treated as test.

-- Max-relay-log-size = #

The size of the automatically rotated relay log. For more information, see "5.2.3 server system variables ". This option is available only after MySQL 4.0.14.

-- Read-only

This option does not allow slave threads or users with super permissions to update data. This ensures that slave does not accept updates from other clients. This option is available since 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, and NNN is the sequence number of the relay log. You can use this option to create a relay log that does not depend on the host name, or the relay log is getting bigger (you do not want to reduce the value of max_relay_log_size) and put them in a non-data file directory, or you want to use Server Load balancer between disks to increase the speed.

-- 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, and host_name is the slave server host name.

-- Relay-log-Info-file = file_name

The name of the file that records the relay log information on the slave. The default value is 'relay -log.info 'in the data file directory '.

-- Relay-log-Purge = {0 | 1}

Disable or enable automatic clearing when no relay logs are required. The default value is 1 (Enabled ). This is a global variable and can be dynamically changed using set global relay_log_purge. This option can be used from MySQL 4.1.1.

-- Relay-log-space-Limit = #

Used to increase the total size of the relay log on the slave (if the value is 0, it indicates "unlimited "). This is useful when the slave host has only limited space. When this limit is reached, the I/o thread will not continue to read binary log events from the master until the SQL thread deletes some useless relay logs. Note that this restriction is not absolute: before deleting the relay log, the SQL thread may need more binary log events. In this case, the I/O thread will go beyond this limit until the SQL thread deletes some relay logs, because otherwise it will lead to a deadlock (this was done before MySQL 4.0.13 ). Do not set the value of -- relay-log-space-limit to less than twice -- Max-relay-log-size (if the value of -- Max-relay-log-size is 0, it is the value of -- Max-BINLOG-size. In this case, because the value of -- relay-log-space-limit is exceeded, the I/O thread needs to wait for more space, however, SQL threads do not have relay logs that can be deleted to meet the I/O thread requirements. This forces the I/O thread to temporarily ignore the -- relay-log-space-limit restriction.

-- Replicate-do-DB = db_name

Tell slave to synchronize only the statements whose default database is db_name (that is, selected with use. To specify more databases, you only need to use this option multiple times to specify one database at a time. Note that cross-database operation statements such as update some_db.some_table set Foo = 'bar' and operations without a selected database will not be synchronized. If cross-database operations are required, make sure that MySQL 3.23.28 or higher is used and the -- replicate-wild-do-table = db_name. % option is used. Read the last note carefully.

The following is an example of failure to work as expected: If the -- replicate-do-DB = Sales option is used when slave is started and the following statement is executed on the master, the update statement will not be synchronized:

Use prices;

Update sales. January set amount = Amount + 1000;

To synchronize cross-database operations, you only need to use the -- replicate-wild-do-table = db_name. % option. The main reason for this "check only the default database" feature is that it is difficult to determine whether to be synchronized from a single statement (for example, using multi-table delete or update, this is cross-database ). However, it is fast to check whether the database is the default database.

-- Replicate-do-table = db_name.tbl_name

Tell slave to synchronize only the specified data tables. To specify more data tables, you only need to use this option multiple times to specify a data table at a time. This option supports cross-database update, which is opposite to the -- replicate-do-DB option. Read the last note carefully.

-- Replicate-ignore-DB = db_name

Tell slave not to synchronize the statements whose default database is db_name (that is, the statement selected with use. To specify more databases, you only need to use this option multiple times to specify one database at a time. If there are cross-database operations and you want these operations to be synchronized, do not use this option. Read the last note carefully.

The following is an example of failure to work as expected: If the -- replicate-ignore-DB = Sales option is used when slave is started and the following statement is executed on the master, the update statement will not be synchronized:

Use prices;

Update sales. January set amount = Amount + 1000;

To ensure normal synchronization of cross-database operations, you only need to use the -- replicate-wild-ignore-table = db_name. % option.

-- Replicate-ignore-table = db_name.tbl_name

Tell slave not to synchronize any update statements of the specified data table (or even other tables updated using the same statement ). To specify more data tables, you only need to use this option multiple times to specify a data table at a time. This option supports cross-database update, which is opposite to the -- replicate-ignore-DB option. Read the last note carefully.

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

Restrict slave to synchronize only data tables that match the specified mode. The pattern can contain wildcards '%' and '_', which have the same meaning as the like pattern. To specify more data tables, you only need to use this option multiple times to specify a data table at a time. Read the last note carefully.

For example, -- replicate-wild-do-table = Foo %. Bar % Will Synchronize all update operations on Data Tables starting with bar in the database that starts with Foo.

If the matching mode is %, all table names are matched and applied to database-level statements (create database, drop database, and alter database ). For example, if you use the -- replicate-wild-do-table = Foo %. % option, all database-level operations that match the foo % mode will be synchronized.

To include the original wildcard characters in database/table mode, use a backslash to escape them. For example, If You Want To synchronize all tables in the my_own % DB database but do not want to synchronize tables in the my1ownaabcdb database, you need to escape the character '_': -- replicate-wild-do-table = My \ _ own \ % db. If this option is used in the command line, two backslashes may be required for escape, which depends on the command line interpreter. For example, in bash shell, enter -- replicate-wild-do-table = My \ _ own \ % db.

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

Restrict slave from synchronizing data tables that match the specified mode. To specify more data tables, you only need to use this option multiple times to specify a data table at a time. Read the last note carefully.

For example, -- replicate-wild-ignore-table = Foo %. Bar % will not synchronize all update operations on Data Tables starting with bar in a database starting with Foo.

To learn how the matching mode works, see the specific description of the -- replicate-wild-ignore-Table option. The rules for a mode that contains the original-defined wildcard are the same as those for the -- replicate-wild-ignore-Table option.

-- Replicate-rewrite-DB = from_name-> to_name

Tell slave to convert the default database from_name (only selected by use) on the master to to_name. Only statements related to data tables (excluding statements similar to create database, drop database, and alter database) will be synchronized, and the default database on the master is from_name. This option does not support cross-database operations. Note that database name conversion is prior to the -- replicate-* Rule. If you use this option in the command line, You need to quote the '>' character with quotation marks. For example:

Shell> mysqld -- replicate-rewrite-DB = "olddb-> newdb"

-- Replicate-same-server-ID

This option is used on slave. The default value 0 is usually used to avoid infinite synchronization loops. If it is set to 1, slave will not ignore the update logs with the same server number as it; Generally, it is only used in rare configurations. If the -- log-slave-Updates option is enabled, it cannot be set to 1. Note: From MySQL 4.1, the slave I/O thread does not write binary logs containing the server number of the slave to the relay log by default (this can save disk usage compared with 4.0 ). Therefore, to use the -- replicate-same-server-ID Option in 4.1, make sure that this option is used when slave is started before the SQL thread reads its own update event.

-- Report-host = Host

Master host name or IP address to be reported during salve registration. The show slave hosts statement is displayed on the master node. If you do not want the Server Load balancer to register with the master node, you do not need to set this option. Note: After the slave is connected to the master, the Master cannot directly read the slave IP address from the TCP/IP socket based on this configuration. Due to the existence of NAT or other routing mechanisms, this IP address information is not enough to connect to the slave on the master or other hosts. This option is available from MySQL 4.0.0.

-- Report-Port = port_number

Connect to the slave TCP/IP Port and use it when the slave registers to the master. Unless slave listens on non-default ports or connects to slave from the master or other clients, this value is not required. If you are not sure, do not set it. This option is available from MySQL 4.0.0.

-- Skip-slave-start

Tell the slave server not to run the slave thread at startup. You only need to use the start slave statement to start the slave thread.

-- Slave_compressed_protocol = {0 | 1}

If it is set to 1 and both maste and slave are supported, data is transmitted using the compression protocol.

-- Slave-load-tmpdir = file_name

The directory where slave creates temporary files. The value of this option is the same as the value of the system variable tmpdir by default. When the SQL thread of slave synchronizes the load data infile statement, it extracts the files to be loaded from the relay log and puts them into the temporary files, and then loads them into slave. If the files loaded on the master node are large, the temporary files on the slave will also be large. Therefore, we recommend that you set this option on slave to a file system with more space. This is, it is best to specify -- relay-log to that file system, because the relay log may be large. -- Slave-load-tmpdir must point to a disk-based file system instead of a memory-based file system: slave may need to use this temporary file when synchronizing the load data infile statement after the machine restarts. This directory cannot be cleared by the Startup Process of the operating system.

-- Slave-net-Timeout = seconds

After giving up reading, salve waits for several seconds to read more data from the master, considering the disconnection and re-connection attempts. The first retry will be executed immediately after the timeout. The Retry Interval is controlled by the master-connect-retry option.

-- Slave-Skip-errors = [err_code1, err_code2,... | all]

Generally, synchronization stops when an error occurs to solve data inconsistency manually. This option tells the slave SQL thread to return an error when executing the statement.CodeContinue running in this list.

Do not set this option unless you really understand why an error occurs. If the synchronization settings and clientProgramThere are no bugs, and it is not a MySQL bug, so there will be no errors that will stop synchronization. Using this option without partitioning will cause slave to be irretrievably deviated from Master synchronization, and you do not know why.

You can see the error code in the slave error log and the result of executing the show slave status Statement on slave. For details about the server error code, see "22 error handling in MySQL ".

You can also (preferably not) use the unrecommended value all, which can ignore all error messages and keep them synchronized in any case. If this option value is used, data integrity cannot be expected. In this case, you cannot complain that the slave data is not close to the master wherever it is. You have been warned. For example:

-- Slave-Skip-errors = Route 2, 1053

-- Slave-Skip-errors = all

-- Replicate-* mode determines whether a statement is to be executed or ignored based on the following rules:

Is there a -- replicate-do-db or -- replicate-ignore-DB rule?

Yes: test the -- BINLOG-do-DB and -- BINLOG-ignore-DB options (for details, see "5.9.4 the binary log "). Test results?

Ignore: Ignore and exit.

Execution: do not execute immediately. Postpone the judgment and proceed to the next step.

No: Go to the next step.

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

No: Execution and exit.

Yes: Go to the next step. Only the table to be updated can be compared with this rule (insert into sales select * From Prices: only sales and rule comparison are performed ). If several tables are updated together (Multi-Table statements), the first matched table (matching 'do 'or 'ignore') wins. That is to say, only the first table is compared with the rule. Then, if no decision is made, compare the second table, and so on.

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

Yes: Does the 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 the 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 the 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 the table match these rules?

Yes: Ignore and exit.

No: Go to the next step.

No: Go to the next step.

The -- replicate-*-Table rule is not matched. Are there other tables that match these rules?

Yes: Search and match cyclically.

No: All tables to be updated have been tested and no matching rules can be found. Is there a -- replicate-do-table or -- replicate-wild-do-Table rule?

Yes: Ignore and exit.

No: Execution and exit.
Reference:
Http://database.ccidnet.com/art/1105/20060815/807451_1.html