Some "pitfalls" of MySQL database upgrade

For commercial databases, database upgrade is a high priority. There is a version upgrade roadmap, corresponding patches, and a series of drills for the plan. It is obviously a tough battle. In the MySQL direction, the upgrade issue has been downplayed a lot, as if it can only prove its existence. Of course, it is precisely because of this lack of attention that I have taken a lot of detours today.

Generally speaking, there are two feasible solutions for upgrading MySQL. One is to directly upgrade the data dictionary, which is completed on the local machine. The whole process involves offline operations and will interrupt the business. The second is to achieve smooth switching through high availability. The principle is to establish a data replication relationship from a low version to a high version. This solution has obvious advantages, is the least invasive to the business, and can be verified in advance, and can even achieve smooth rollback. Of course, the second solution requires a lot of preliminary preparation.

The environment we are dealing with today uses the first method based on factors such as storage and duration. The entire process is as follows:

1) Use mysqldump to back up the database. The backup file is about 120G.

2) Stop the MySQL 5.5 database

3) Modify the database port and restart the database, for example, change it from 4308 to 4318 to avoid the impact of other business connections during the migration process. After verification, stop the database.

4) Change the mysql_base path to version 5.7 and modify the environment variable configuration such as /usr/bin/mysql

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

6) Use the upgrade mode to upgrade the data dictionary. The command is as follows:

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

7) Inspection and Review

The whole process looks OK, but it is full of loopholes in actual operation.

1) Use mysqldump to back up the database. The backup file is about 120G. For fast online backup, mysqldump is used. However, the recovery efficiency is impaired under abnormal conditions. Therefore, it is not recommended to use mysqldump for backup. Instead, it is recommended to use physical backup. If conditions permit, use cold backup mode directly.

2) Stop the MySQL 5.5 database

3) Modify the database port and restart the database, for example, change it from 4308 to 4318 to avoid the impact of other business connections during the migration process. After verification, stop the database.

4) Change the mysql_base path to version 5.7 and modify the environment variable configuration such as /usr/bin/mysql

5) Replace the configuration file with version 5.7 and start the database in 5.7 mode. I didn’t pay attention to the configuration of ibdata. Unfortunately, I encountered a strange configuration as follows:

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

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

innodb_data_file_path = ibdata1:1G:autoextend,

This causes an error message when the database is started, indicating that the ibdata file has been damaged.

6) Use the upgrade mode to upgrade the data dictionary. The command is as follows:

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

The implementation prompt of the upgrade command was not friendly enough and threw out a lot of errors, but in the end it actually comforted me and said that the upgrade was successful. When the problem reached this stage, it was actually difficult to solve. Because the data dictionary file was damaged, it was impossible to upgrade the data dictionary. Now the database could not even desc the tables in it.

7) Inspection and verification. The verification work that was originally completed easily has now become an urgent repair work.

The first wave of remedial measures that followed were as follows:

8) It took about an hour to restore data using the existing physical backup taken in the early morning. I gave up on restoring with mysqldump and I remember it took at least 6 hours.

9) Back up the current database using physical backup mode

10) Re-upgrade the database, paying special attention to the configuration of ibdata. If the upgrade fails, use physical backup to quickly roll back.

11) The upgrade process was blocked again, this time with sql_mode. The system data dictionary was successfully upgraded, but during the database table detection, the format verification of many data tables failed, mainly due to the data format verification of sql_mode. A reconstruction operation such as alter table test.xxxxx force was required.

12) Due to unknown reasons during the recovery process, InnoDB's redo log was also affected and the log began to throw errors. Therefore, even if the dictionary of the currently restored database is successfully upgraded, it still has some defects.

The subsequent 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 the permissions separately.

14) Deploy an instance of MySQL 5.7, using a different port, such as port 4390

15) sql_mode is wildcarded with version 5.5, and other parameters are modified.

16) Import mysqldump data to 4390 5.7 instance

17) Establish a master-slave replication relationship

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

The whole process was full of twists and turns. I tried to deal with each challenge with my own strategy, and tried to take shortcuts, but in the end I found that I had not fallen into any trap. This also taught me a profound lesson: never take it lightly and never deal with problems with a try-your-luck attitude.

The above are the details of some "traps" in MySQL database upgrades. For more information about MySQL database upgrades, please pay attention to other related articles on 123WORDPRESS.COM!