Shulou(Shulou.com)06/01 Report--

Recently, there is a need to upgrade MySQL version 5.5 to MySQL 5.7. to this end, we have come up with some solutions, such as MySQL cascade replication upgrades, which are mainly based on version differences and remain compatible as much as possible.

There are also logical backup recovery, physical backup recovery solutions, of course, no matter how to reflect the business value in order to make technical value more meaningful. So we hope to upgrade the version to unify the online version as much as possible, at the same time, the major benefits to the business and DBA are online DDL, reduced data latency and enhanced optimizer.

Of course, whether it can be upgraded or not is also a clap to the head. In principle, it is possible, but in fact, there is no proof of how effective it is. What has been done before is more mobile upgrade, through the way of logical backup and recovery, in the case of a large amount of data, that way is a bit difficult.

So I built three sets of MySQL environments according to the version of 5.5 and 5.6, and then cascaded replication based on these three environments. See if you can achieve a smooth database upgrade.

The database version is 5.5.19,5.6.14,5.7.19

In order to maintain compatibility and better functionality as much as possible, I plan to use the following methods.

Upgrade from MySQL 5.5to MySQL 5.6Sync using offsets

Upgrade from MySQL 5.6to MySQL 5.7Use GTID to synchronize

Then just do it. In fact, initializing the environment is mainly about the compatibility of parameters.

For example, the following parameters do not exist in version 5.5, but do exist in 5.6Gore 5.7, so you need to make a choice according to the requirements.

171019 9:47:53 [ERROR] / usr/local/mysql_5.5/bin/mysqld: unknown variable 'master_info_repository=TABLE'

171019 9:47:53 [ERROR] Aborting

171019 9:48:48 [ERROR] / usr/local/mysql_5.5/bin/mysqld: unknown variable 'relay_log_info_repository=TABLE'

171019 9:49:12 [ERROR] / usr/local/mysql_5.5/bin/mysqld: unknown variable 'binlog_checksum=NONE'

The key is the configuration of the replication relationship.

I first verified the configuration relationship between 5.6 and 5.7, but I didn't expect to see the following error after starting slave.

Last_SQL_Error: Column 1 of table 'mysql.user' cannot be converted from type' char (48 (bytes))'to type 'char (96 (bytes) utf8)'

For this type of problem, you can consider changing the parameters to set the degree of lossless replication, such as this.

Mysql > set global slave_type_conversions='ALL_LOSSY,ALL_NON_LOSSY'

Then another error was received.

Last_SQL_Error: Can't create conversion table for table 'mysql.user'

Of course, according to this idea, we can abandon the mysql library completely and copy the library where the data is located.

Then there is the environment configured from 5.5to 5.6and it is found that GTID is configured in 5.6and there is a conflict with the use of offsets.

So the trade-off is to cancel the GTID setting, uniformly use the offset, reconfigure the master-slave library, and reset it. Just re-establish the master-slave relationship.

After a simple test, it is feasible to configure the offset in the way of 5.5-> 5.6-> 5.7. there is no need to set the replication filter configuration. I have done the operation of DDL,DML and reconfigured the user, all of which are possible.

Then I went a step further and tried to configure a replication relationship of 5.5 to 5.7, which I didn't expect to be possible.

So the simple attempt above gives me a new understanding of replication, at least at this point, the data can be completely synchronized, and more supplementary tests will be done for more complex scenarios.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.