Best method for MySQL upgrade practice

MySQL5.7A lot of new features have been added, such as: Online DDL, multi-source replication, enhanced semi-synchronous, tablespace transfer, sys library, Group Replication, and so on. Finally, I have waited for an opportunity to upgrade MySQL to5.7was very excited.

Overview of MySQL upgrade

The essence of MySQL upgrade:

Upgrade of the data dictionary

The data dictionary includes: mysql, information_schema, performance_schema, sys schema.

Two ways of MySQL upgrade:

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, 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, between major versions.

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

Features: Can cross operating systems, cross major versions; however, the upgrade speed is slow, and it is easy to have garbled code and other problems.

Preparation before upgrade:

Make a backup in advance.

Understand the information of the changes in the new version (which are no longer compatible, and which functions are no longer supported)

On the official website's general information—>what is new in mysql 5.7

Points to note for the upgrade:

Confirm whether there are major changes in the new version

Pay attention to the changes in SQL mode

For example: in MySQL5.7There was a change in SQL mode, for the SQL mode that is no longer supported, some SQL statements may not run, at this time you can clear the SQL mode, run after it is set up.

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

whether all levels of the program are normal

Sometimes the part of the original program language used is not supported by the new version of the database. For example, once in5.1uses PHP4.0, but the upgrade to5.6, some PHP functions are not supported.

After the upgrade is complete, be sure to use the same program as the online version when testing to check for any issues.

Changes in storage engine

For example: in the future5.8Version, no longer supports myisam engine.

Pay attention to the problem of character set garbled

Next is, using in-Using place upgrade method, upgrade MySQL5.6Upgrade to MySQL5.7.

In-Perform MySQL upgrade using place upgrade

Environment:

5.6.15 -->5.7.20

Preparation before upgrade:

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

1and5.7of the software package, download, unzip

# 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

2Close 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

3Replace binary files (5.7 Replace 5.6)

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

4Start MySQL using the existing data directory

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

5Check if all tables are compatible with the current version and update the system database

# mysql_upgrade -uroot -p -S /data/mysql3308/mysql3308.sock
Note: The mysql_upgrade command checks whether all tables in all databases are compatible with the current new version and updates the system database.

6Restart to ensure that the changes made to the system tables take effect

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

Up to now, the upgrade is complete.

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

When performing an upgrade, it is generally recommended to create a secondary repository for the upgrade. If the upgrade fails, it will not affect the main repository; if the upgrade is successful and the test is also successful, the other secondary repositories will also be gradually upgraded to the new version, and finally the main repository will be taken offline, and a secondary repository will be promoted to the new main repository, and the old main repository will be upgraded to the new version.

Declaration: The content of this article is from the Internet, the copyright belongs to the original author, the content is contributed and uploaded by Internet users spontaneously, this website does not own the copyright, does not edit manually, nor bear relevant legal responsibility. If you find any content suspected of copyright, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email for reporting, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)