Sharing examples of the best way to upgrade MySQL

MySQL5.7 adds many new features, such as: Online DDL, multi-source replication, enhanced semi-synchronization, table space transfer, sys library, Group Replication, etc. Recently, I finally got an opportunity to upgrade MySQL to 5.7, and I was very excited. This article mainly shares with you the best method of upgrading MySQL from theory to practice. Friends in need can follow and learn and operate.

Overview of MySQL upgrade

The essence of MySQL upgrade:

Upgrade of data dictionary

The data dictionary includes: 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: No data files are changed, and the upgrade speed is fast; however, it cannot cross operating systems and 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 upgrade the version.

Features: Can cross operating systems and major versions; however, the upgrade speed is slow and prone to problems such as garbled codes.

Preparation before upgrading:

Make backups in advance.

Get information about changes in the new version (which ones are no longer compatible, which features are no longer supported)

General information on the official website—>what is new in mysql 5.7

Notes on upgrading:

Confirm whether there are major changes in the new version

Pay attention to the changes in SQL mode

For example: SQL mode has changed in MySQL 5.7. For SQL modes that are no longer supported, some SQL will not run. At this time, you can clear the SQL mode and set the SQL mode after running.

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

Are all program layers normal?

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

After the upgrade is completed, be sure to use the same program as the online version during testing to see if 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

The next step is to use the in-place upgrade method to upgrade MySQL5.6 to MySQL5.7.

In-place upgrade Upgrade MySQL

environment:

5.6.15 —>5.7.20

Preparation before upgrading:

Back up + 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
注：mysql_upgrade的作用是检查所有库的所有表是否与当前的新版本兼容，并更新系统库。

6. Restart to ensure that changes made to the system tables 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 fails when upgrading MySQL?

When upgrading, a slave library is generally created for upgrade. If the upgrade fails, it will not affect the main library; if the upgrade is successful and the test is successful, other slave libraries will be gradually upgraded to the new version, and finally the main library will be upgraded. When the database goes offline, a slave database is promoted as the new master database, and the old master database is upgraded.

Related recommendations:

Solution to the problem that mysql cannot be started after upgrading

Detailed explanation of the PHP encapsulated Mysql operation class

How PHP solves the problem of Chinese garbled data stored in MySQL

The above is the detailed content of Sharing examples of the best way to upgrade MySQL. For more information, please follow other related articles on the PHP Chinese website!