The best way to upgrade MySQL in actual combat

MySQL 5.7 adds many new features, such as Online DDL, multi-source replication, enhanced semi-synchronization, tablespace transfer, sys library, Group Replication and so on. Recently, I finally got an opportunity to upgrade MySQL to 5.7, and I was very excited.

Overview of MySQL Upgrade

The essence of MySQL upgrade:

Upgrade of data dictionary

Data dictionaries are: mysql, information_schema, performance_schema, sys schema.

Two ways to upgrade MySQL:

in-place upgrade:

Suitable for minor version upgrades.

That is, close the current MySQL, replace the current binary file or package, restart MySQL on the existing data directory, and run mysql_upgrade.

Features: Do not change the data file, fast upgrade speed; However, it is not allowed to cross operating systems, and it is not allowed to cross large versions (5.5- > 5.7).

logical upgrade:

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

That is, using mysqldump or mydumper to import and export data to upgrade the version.

Features: It can cross operating systems and large versions; However, the upgrade speed is slow, and problems such as garbled codes are easy to occur.

Preparation before upgrade:

Make a backup in advance.

Learn about the new version changes (which are no longer compatible and which functions are no longer supported)

On the official website general information- > what is new in mysql 5.7

Considerations for upgrading:

Confirm whether there are major changes in the new version

Pay attention to the changes of SQL and mode

For example, SQL mode has changed in MySQL5.7. For SQL mode that is no longer supported, some SQL will not run. At this time, you can empty SQL mode, and set SQL mode after running.

After the upgrade is successful, confirm whether the service SQL can run through

Is the program layer normal

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

After the upgrade is completed, 1 must use the same program as the online version to test whether there are problems.

Change of storage engine

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

Pay attention to the garbled character set

Next, upgrade MySQL5.6 to MySQL5.7 using in-place upgrade.

In-place upgrade Upgrade MySQL

Environment:

5.6. 15- > 5.7.20

Preparation before upgrade:

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

1. Download and decompress the software package of 5.7

# 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. Turn off 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 file (5.7 replaces 5.6)

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

4. Start MySQL using an 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: mysql_upgrade Is to check that all tables of all libraries are compatible with the current new version and update the system libraries. 

6. Restart to ensure that the changes made to the system tables are effective

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

At this point, the upgrade is completed.

Question: Upgrade MySQL. What if the upgrade fails?

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