Best Practices for MySQL Upgrades

MySQL 5.7 adds many new features, such as: Online DDL, multi-source replication, enhanced semi-synchronization, tablespace transfer, sys library, Group Replication, etc. I finally got a chance to upgrade MySQL to 5.7 recently, and I'm very excited.

Overview of MySQL Upgrades

The essence of MySQL upgrade:

Upgrading the data dictionary

The data dictionaries are: mysql, information_schema, performance_schema, sys schema.

There are two ways to upgrade MySQL:

in-place upgrade:

Suitable for minor version upgrades.

That is, shut down the current MySQL, replace the current binary file or package, restart MySQL in the existing data directory, and run mysql_upgrade.

Features: No changes to data files, fast upgrade speed; however, cannot cross operating systems, cannot cross major versions (5.5—>5.7).

logical upgrade:

Suitable for MySQL upgrades on different operating systems and upgrades between major versions.

That is: use mysqldump or mydumper to import and export data to achieve version upgrade.

Features: It can be used across operating systems and major versions; however, the upgrade speed is slow and garbled characters and other problems may occur easily.

Preparation before upgrading:

Make backups in advance.

Learn about new version changes (which ones are no longer compatible, which features are no longer supported)

In the general information of the official website—>what is new in MySQL 5.7

Notes on upgrading:

Check if there are any major changes in the new version

Note the change in SQL mode

For example, the SQL mode has changed in MySQL 5.7. Some SQL statements will not work for the SQL mode that is no longer supported. In this case, you can clear the SQL mode and then set the SQL mode after the operation is completed.

After the upgrade is successful, confirm whether the business SQL can run smoothly

Is the program layer normal?

Sometimes parts of the original programming language are not supported by the new version of the database. For example, once I used PHP4.0 in 5.1, but when I upgraded to 5.6, some PHP functions were not supported.

After the upgrade is complete, be sure to use the same program as the online version when testing to test whether there are any problems.

Storage engine changes

For example: In the future version 5.8, the myisam engine will no longer be supported.

Pay attention to the garbled character set problem

Next, use the in-place upgrade method to upgrade MySQL 5.6 to MySQL 5.7.

In-place upgrade

environment:

5.6.15 —>5.7.20

Preparation before upgrading:

Backup + pay attention to the changes in the new version Upgrade operation:

1. Download and unzip the 5.7 software package

# tar -xzvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
# ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql5.7

2. Close the current MySQL (5.6)

# mysql -u root -p -S /data/mysql3308/mysql3308.sock --execute="SET GLOBAL innodb_fast_shutdown=0"
# mysqladmin -u root -p -S /data/mysql3308/mysql3308.sock shutdown

3. Replace binary files (5.7 replaces 5.6)

# cd /usr/local
# mv mysql mysql5.6
# mv mysql5.7 mysql

4. Start MySQL using the existing data directory

# mysqld_safe --user=mysql --socket=/data/mysql3308/mysql3308.sock -p --skip-grant-tables --datadir=/data/mysql3308/data

5. Check whether all tables are compatible with the current version and update the system library

# mysql_upgrade -uroot -p -S /data/mysql3308/mysql3308.sock
Note: The function of mysql_upgrade is to check whether all tables in all libraries are compatible with the current new version and update the system library.

6. Restart to ensure that the changes made to the system table take effect

# mysqld --defaults-file=/data/mysql3308/my3308.cnf &
# mysql -uroot -p -S /data/mysql3308/mysql3308.sock

At this point, the upgrade is complete.

Question: What should I do if the upgrade of MySQL fails?

When upgrading, a slave library is generally created for upgrading. If the upgrade fails, it will not affect the master library. If the upgrade is successful and the test is also successful, other slave libraries will be gradually upgraded to the new version. Finally, the master library will be taken offline, a slave library will be promoted to the new master library, and the old master library will be upgraded.