MySQL synchronization (3) different versions

6.5 synchronization compatibility between different MySQL versions

The earliest binary format was developed in MySQL 3.23. Improved in MySQL 4.0 and MySQL 5.0. The causal relationship between the servers to be upgraded during synchronization configuration is described in "6.6 upgrading a replication setup.

If you only care about synchronization, any MySQL 4.1.x version is the same as MySQL 4.0.x because they all use binary logs of the same format. Therefore, these versions are mutually compatible and can run and synchronize seamlessly between them. One exception is that MySQL 4.0.0 to 4.0.2 cannot be compatible with later versions because of earlier development, so do not use them (they are Alpha series of Version 4.0. The compatibility between them is documented in the release package Manual ).

The following table shows the Master/Slave synchronization compatibility between different versions of MySQL.

		Master	Master	Master
		3.23.33 and up	4.0.3 and up or any 4.1.x	5.0.0
Slave	3.23.33 and up	Yes	No	No
Slave	4.0.3 and up	Yes	Yes	No
Slave	5.0.0	Yes	Yes	Yes

A common rule is that we recommend that you use the latest MySQL version because synchronization compatibility has been improved. We also recommend that both master and slave use the same version.

6.6 upgrade Synchronization

If configuration synchronization is involved during server upgrade, the steps for configuration upgrade are different from those of the current version and the upgraded version.

6.6.1 upgrade to 4.0 or 4.1

This section applies when you upgrade from MySQL 3.23 to MySQL 4.0 or 4.1. 4.0 of servers must be 4.0.3 or higher, as mentioned in "6.5 replication compatibility between MySQL versions.

When upgrading the master from MySQL 3.23 to 4.0 or 4.1, you must first confirm that all the Server Load balancer instances of the Master are 4.0 or 4.1. Otherwise, you must first upgrade the Server Load balancer: one by one, upgrade, restart, and restart synchronization.

Perform the following steps to perform a secure upgrade. Assume that the master is upgraded to 3.23, And the slave is already 4.0 or 4.1. Note: after the master is upgraded, do not restart the synchronization of any old binary logs because it will interfere with the synchronization to 4.0 or 4.1 slave.

Execute the flush tables with read lock Statement on the master to stop all updates.

Wait until all the Server Load balancer instances keep up with the master data updates. Run the show Master Status Statement on the master to obtain binary logs and the offset position. Then, slave uses these values to execute the select master_pos_wait () Statement, which will block synchronization on the slave and return its synchronized offset position. Then, execute the stop slave Statement on the slave.

Shut down the master and upgrade it to MySQL 4.0 or 4.1.

Restart the master and write down its new binary file name. You can run the show Master Status Statement on the master to obtain the information. Then execute the following statement on each slave:

Mysql> change master to master_log_file = 'binary _ log_name ',

-> Master_log_pos = 4;

Mysql> Start slave;

6.6.2 upgrade synchronization to 5.0

This section applies when you upgrade from MySQL 3.23, 4.0, or 4.1 to 5.0. 4.0 of servers must be 4.0.3 or higher, as mentioned in "6.5 replication compatibility between MySQL versions.

First, we noticed that MySQL 5.0 is still the alpha release series. It is better than the old version in various aspects (it is easier to upgrade some important session variables in synchronization, such as SQL _mode; For details, see "c.1.3 changes in release 5.0.0 (22 Dec 2003: Alpha "). However, it has not been extensively tested. Since the alpha version is used, we do not recommend that you use it in any production environment (now it can be used for production ).

When upgrading the master from MySQL 3.23, 4.0, or 4.1 to 5.0.0, you must first confirm that all the Server Load balancer instances of the Master are 5.0.0. Otherwise, you must first upgrade the Server Load balancer: one by one, upgrade, restart, and restart synchronization. 5.0.0 slave can read the relay logs of the execution statements written before the upgrade. The relay log created by the upgraded slave is in the 5.0 format.

When all slave is upgraded, shut down the master, upgrade to 5.0.0, and restart. The master of 5.0.0 can also read binary logs in the old format. Slave can recognize old formats and handle them properly. The binary logs created on the master are in the 5.0.0 format. Slave can also recognize this format.

In other words, there is no need for special rules when upgrading to 5.0.0, unless the slave must use the old version before upgrading the master to 5.0.0. Note: downgrading 5.0.0 to the old version cannot be performed automatically: You must ensure that all the binary logs in 5.0.0 format and relay logs have been processed before deleting and downgrading them.

6.7 synchronization features and known issues

The following lists what synchronization supports and what is not supported. For more information about InnoDB and synchronization, see "16.7.5 InnoDB and MySQL replication ".

Auto_increment, last_insert_id (), and timestamp values can be synchronized normally.

The user (), UUID (), and load_file () functions are completely synchronized to the slave, which may be unreliable. The same is true for the connection_id () function in MySQL versions earlier than MySQL 4.1.1. After MySQL 4.1.1 and higher, the new password () function can be synchronized normally. Of course, slave must be 4.1.1 or higher or not synchronized. If the slave of the old version must synchronize the password () function, the -- old-password option must be added when the master starts, so that the old method is used to implement password () on the master () (note that the implementation of the password () function of MySQL 4.1.0 is different from that of other versions. It is best not to synchronize the password with MySQL 4.1.0 ).

Synchronize the foreign_key_checks variable from MySQL 4.0.14. Synchronize SQL _mode, unique_checks, and SQL _auto_is_null variables from 5.0.0. The SQL _select_limit and table_type variables cannot be synchronized yet.

Now we will discuss how to synchronize MySQL servers with different character sets.

First, the master and slave must always use the same global character set and verification character set (-- default-character-set, -- default-collation are related global variables ). Otherwise, a duplicate-key error may occur on the slave, because the master Character Set considers the key to be unique, but it is not necessary to use the slave character set.

Second, if the master must be lower than MySQL 4.1.3, the session character set must be the same as the global value (that is, the set names and set character set statements cannot be executed ), these modifications to the character set are not recognized by slave. If the master is 4.1.3 or higher and the slave is the same, the session character set can be modified (execute names, Character Set, collation_client, collation_server, and so on ), all these modifications will be recorded in the binary log and synchronized to the slave to know how to do it. This session also prevents attempts to modify these global variables. As mentioned above, the master and slave must use the same global character set.

If the master database has a character set that is different from the global variable collation_server, you must design the create table statement so that the data table does not implicitly use the default Character Set of the database, this is still a bug (bug #2326). A work und is to explicitly declare the character set of the data table and verify the character set in the create table statement.

Sometimes the transaction table on the master may be synchronized to the slave and then become a non-transaction table. For example, the InnoDB table of the master can be treated as a MyISAM table on the slave. However, slave stops in a begin/commit block, because slave starts from begin again. This problem has been put into todo and will be fixed soon.

If User-Defined variables (such as the variable @ var_name) are used in the update statement, MySQL 3.23 and 4.0 cannot be correctly synchronized. This is fixed in 4.1. Note: User variables are case insensitive since MySQL 5.0. This issue must be taken into account during synchronization between MySQL 5.0 and earlier versions.

In versions 4.1.1 and later, slave can be connected to the master using SSL.

If the create table statement executed on the master includes the data directory or index directory clause, it will also be applied to slave. If the corresponding directory does not exist on the slave or there is no permission, the problem may occur. From MySQL 4.0.15, there is a SQL _mode option named no_dir_in_create. If slave's SQL mode contains this option, it ignores the two clauses mentioned above before synchronizing the CREATE TABLE statement. The result is that MyISAM data and index files can only be stored in the database directory of the table.

Although I have never heard of a similar situation, there is a possibility in theory: If a query is designed to modify data in an uncertain way, it may lead to data inconsistency between the master and slave. Then, let's hand over the decision power to the query optimizer. (This is not a good practice, or even out of the scope of synchronization. For details, see "1.8.7.3 open bugs and Design deficiencies in MySQL ")

Before MySQL 4.1.1, the flush, analyze table, optimize table, and repair table statements are not written to binary logs, and therefore are not synchronized to slave. This usually does not cause problems because they do not modify data. However, it may cause problems under specific circumstances. If the permission table in the MySQL database is synchronized and the grant statement is not used during the update, the flush privileges statement must be executed on the slave for the update to take effect. Similarly, if another MyISAM table is part of the MERGE table, you must manually execute the flush tables Statement on the slave. These statements have been written to binary logs since MySQL 4.1.1 (unless no_write_to_binlog or its option with the same name is specified ). Some exceptions are flush logs, flush slave, and flush tables with read lock (any synchronization from one of them to slave may cause problems ). The example shows "14.5.4.2 flush Syntax ".

MySQL only supports multiple Server Load balancer instances of one master node. We will add a vote laterAlgorithmIf the current master has a problem, it can automatically switch. A "proxy" process will also be introduced to help send select queries to different slave for load balancing.

When the server is shut down and restarted, all memory (HEAP) tables are cleared. Starting from MySQL 4.0.18, the master synchronizes them in the following way: Once the master starts to use a memory table, after these tables are used up, it will write a delete from statement in the binary log to tell slave to delete them. For details, see "15.3 The memory (HEAP) storage engine ".

Unless slave is disabled (only the slave thread is closed), the temporary table is synchronized, and some update statements for the temporary table are recorded on the slave. After the slave is disabled and restarted, the temporary table required for updating will no longer exist. To avoid this problem, do not disable slave when there is a temporary table. Alternatively, follow these steps:

Submit a stop slave statement. Use the show status statement to check the value of the variable slave_open_temp_tables. If the value is 0, run the mysqladmin shutdown command to disable slave. If the value is not 0, use the start slave statement to restart the slave thread. If you have such good luck, perform the same step again. Pai_^ We will solve this problem as soon as possible. If you specify the -- log-slave-Updates option in a cyclic Master/Slave synchronization relationship, you can Securely connect to each server. Note that many statements may not work normally in this setting environment unlessProgramYou have already paid special attention to avoiding potential problems when updating, because the update may occur in different order on different servers. This means that the following loop can be set: A-> B-> C-> Server IDs have been encoded into binary logs, So server a knows the logs created by itself, therefore, they will not be executed (unless the -- replicate-same-server-ID Option is added when server a is started, this option only makes sense in rare cases ). Therefore, there will be no infinite loop. However, this loop does not cause any conflict only when the table is updated. In other words, if a record is inserted in both A and C, data may not be inserted in a because its key may conflict with the c key. Similarly, the same record cannot be updated on two servers, unless there is a sufficient interval between two update operations. If an error occurs after an SQL statement is executed on slave, the SQL thread of slave is terminated and a message is written into the error log. You can connect to slave to solve the problem (for example, a table does not exist) and run the start slave statement to restart it. You can safely shut down the master (clean) and then restart it. If the slave to master is disconnected, it will immediately reconnect. If it fails, slave will retry regularly (the default is to retry once every 60 seconds, you can use the -- master-connect-retry option to modify ). Slave also handles network disconnection. However, slave will be treated as a network disconnection if it does not receive data from the master after slave_net_timeout seconds. If the disconnection time is not long, you can reduce the value of slave_net_timeout. For more information, see "5.2.3 server system variables ". You can also safely close the slave (clean), which records the stopped place. Otherwise, disabling slave may cause problems, especially when the system is disabled but the cache has not been refreshed to the disk. Uninterrupted power supply can be provided to improve system fault tolerance. If the master is not clean, the table and binary content may be inconsistent. If it is an InnoDB table, use the -- InnoDB-safe-BINLOG option on the master to avoid this problem. For details, see "5.9.4 the binary log ". Because the non-transactional nature of the MyISAM Table may cause an error when only some tables are updated by a statement.Code. For example, in a multi-insert statement, one record violates the constraint key rules. An update statement is killed after updating some records. If this happens on the master, the slave thread will be released, waiting for the database administrator to decide what to do, unless the error code is valid and the execution result of this statement is the same. There is no detailed description about whether the error code is legal. Some error codes can be blocked using the -- slave-Skip-errors option. This option can be used from MySQL 3.23.47. If the data table is updated in the begin/commit segment when a non-transaction table is synchronized to the transaction table, if other threads update the table before the non-transaction table is committed, then this update operation will not be correctly synchronized to the binary log. This is because binary logs are written only when the entire transaction is successfully committed. Before 4.0.15, any update operation on a non-transaction table will be immediately written to the binary log when it is executed. However, the update of the transaction table is only written after commit, rollback will not be written. Therefore, you need to consider this situation when updating the transaction table or non-transaction table in some transactions (not only this problem occurs during synchronization, but also when you want to use the binary log as a backup ). In MySQL 4.0.15, we have modified the log record behavior when the transaction and non-transaction tables are mixed, it solves this problem (for Binary logs, it is a good practice to record statements in sequence. All required statements are written in, and the same is true for rollback ). When the second connection updates a non-transaction table and the transaction of the first connection has not ended, the same problem will occur; the order of statements recorded will still be incorrect, because the second connection will be written to the log immediately after the update is complete. When the slave of 4.x synchronizes load data infile from the master of 3.23, the values of the exec_master_log_pos and relay_log_space fields in show slave status are incorrect. If the exec_master_log_pos value is incorrect, it will cause problems after the slave is restarted. Therefore, it is best to modify this value before the restart and simply run flush logs on the master. This bug has been fixed in MySQL 5.0.0 slave. The following table lists the issues that may occur during MySQL 3.23 synchronization. They have been resolved in MySQL 4.0: Load data infile can be correctly processed, as long as the data file still exists on the master at the beginning of the update. Load data local infile is no longer skipped as in the previous 3.23. In 3.23, the Rand () Update synchronization is not normal. Therefore, Rand (some_non_rand_expr) format is used when rand () is updated. For example, you can use unix_timestamp () as the rand () parameter.

Reference:
Http://database.ccidnet.com/art/1105/20060814/803053_1.html
Http://database.ccidnet.com/art/1105/20060814/803053_2.html
Http://database.ccidnet.com/art/1105/20060814/803053_3.html