Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the pits of MySQL database upgrade". In the daily operation, I believe that many people have doubts about the problems of MySQL database upgrade pits. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the pits of MySQL database upgrade?" Next, please follow the editor to study!

Generally speaking, there are two feasible schemes for upgrading MySQL, one is to directly upgrade the data dictionary, which is completed on this machine, the whole process will be offline and will be interrupted to the business, and the second is realized smoothly through highly available switching. The principle is to build a data replication relationship from a low version to a high version. This scheme has obvious advantages, low intrusiveness to the business, and can be verified in advance. It can even achieve a smooth fallback, of course, the second scheme requires a lot of preparatory work.

The environment we are dealing with today uses the first approach based on factors such as storage and duration, and the whole process is as follows:

1) mysqldump backs up the database, and the backup files are about 120g

2) stop MySQL 5.5database

3) modify the database port to restart the database, for example, adjust it from 4308 to 4318, so as to avoid the impact of other business connections in the migration process, and stop the database after verification.

4) modify the mysql_base path to version 5.7, and modify the configuration of environment variables such as / usr/bin/mysql

5) replace the configuration file with version 5.7, and start the database in 5.7 mode

6) upgrade the data dictionary using upgrade mode, with the following command:

Mysql_upgrade-socket=/data/mysql_4306/tmp/mysql.sock-port=4308-uroot-pxxxx

7) check and review

The whole process seems to be OK, but it is full of loopholes in actual operation.

1) mysqldump backup database, the backup file is about 120g. Mysqldump is used for fast online backup, but the recovery efficiency in abnormal cases is hard, so mysqldump backup is not recommended here, but physical backup is recommended, even if conditions permit, use cold backup mode directly.

2) stop MySQL 5.5database

3) modify the database port to restart the database, for example, adjust it from 4308 to 4318, so as to avoid the impact of other business connections in the migration process, and stop the database after verification.

4) modify the mysql_base path to version 5.7, and modify the configuration of environment variables such as / usr/bin/mysql

5) replace the configuration file with version 5.7, and start the database in 5.7 mode. We didn't pay attention to the configuration of ibdata. Unfortunately, we ran into a strange configuration, as shown below:

Innodb_data_file_path = ibdata1:1000M;ibdata2:100M:autoextend

The original specification configuration is an ibdata file, as follows:

Innodb_data_file_path = ibdata1:1G:autoextend

Caused the database startup Times error, indicating that the ibdata file has been corrupted.

6) upgrade the data dictionary using upgrade mode, with the following command:

Mysql_upgrade-socket=/data/mysql_4306/tmp/mysql.sock-port=4308-uroot-pxxxx

The implementation of the upgrade command was unfriendly and threw a lot of errors, but in the end it comforted me that the upgrade was successful. At this stage of the problem, it is actually quite difficult to end. Because the data dictionary file is damaged, it is completely impossible to upgrade the data dictionary. Now the database cannot even desc the tables inside.

7) check and review, the verification work that had been easily completed has now become an urgent repair work.

The following first wave of remedial measures are as follows:

8) use the existing fixed physical backup in the early morning to recover data, which takes about 1 hour, and the mysqldump recovery is decisively abandoned, which takes at least 6 hours in my impression.

9) back up the current database using physical backup mode

10) re-upgrade the database, pay special attention to the configuration of ibdata, and use physical backup to roll back quickly if the upgrade fails

11) the upgrade process is blocked again, this time it is sql_mode, and the system data dictionary is upgraded successfully, but in the table detection of the database, mainly because of the data format verification of sql_mode, the format verification of many data tables fails, so it is necessary to perform reconstruction operations such as alter table test.xxxxx force.

12) due to unknown reasons during the recovery process, the redo log of InnoDB was also affected, and the log began to be thrown incorrectly, so the current restored database still has some hard injuries even if the dictionary upgrade is successful.

The follow-up second wave of remedial measures are as follows:

13) use mysqldump to back up the current database, only back up the specified database, do not use the all-databases option, and export permissions separately.

14) deploy instances of MySQL 5.7on different ports, such as port 4390

15) sql_mode and version 5.5 are compatible, and other parameters are modified.

16) Import mysqldump data to 5.7instances of 4390

17) establish a master-slave replication relationship

18) switch the database port to make the new version 5.7 service effective

At this point, the study on "what are the pitfalls for MySQL database upgrade" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.