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

This article is about how to upgrade MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Many new features have been added to MySQL5.7, 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. I was very excited.

Overview of MySQL upgrad

The essence of MySQL upgrade:

Upgrade of data dictionary

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

There are two ways to upgrade MySQL:

In-place upgrade:

Suitable for small version upgrade.

That is, close the current MySQL, replace the current binaries or packages, restart MySQL on the existing data directory, and run mysql_upgrade.

Features: do not change the data file, upgrade speed; however, can not cross the operating system, can not cross the large version (5.5-> 5.7).

Logical upgrade:

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

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

Features: cross-operating system, cross-version; however, the upgrade speed is slow, prone to garbled and other problems.

Preparation before upgrade:

Make a backup ahead of time.

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

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

Considerations for upgrade:

Confirm if there are any major changes in the new version

Note the changes in SQL mode

For example, SQL mode has changed in MySQL5.7. For SQL mode that is no longer supported, some SQL will fail. You can clear SQL mode at this time, and set SQL mode after running.

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

Are all the program layers normal?

Sometimes the content of the original program language is not supported by the new version of the database. For example, on one occasion, PHP4.0 was used at 5.1, but some functions upgraded to 5.6 PHP are not supported.

After the upgrade is complete, be sure to test with the same program as the online version to see if there is a problem.

Changes to the storage engine

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

Pay attention to the garbled character set

The next step is to 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 operation:

1. Download and decompress the 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. 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 binaries (5.7 replace 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 role of mysql_upgrade is to check that all tables in all libraries are compatible with the current new version, and to 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: to upgrade MySQL, what if the upgrade fails?

When upgrading, a slave library is generally created to upgrade. If the upgrade fails, the master database will not be affected. If the upgrade is successful and the test is also successful, the other slave libraries will be gradually upgraded to the new version, and finally the master library will be offline. Upgrade a slave library as the new master library, and upgrade the version of the old master library.

Thank you for reading! This is the end of the article on "how to upgrade MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.