Best practices for MySQL upgrading and practices for MySQL upgrading

Best practices for MySQL upgrading and practices for MySQL upgrading

MySQL5.7 has many new features, such as Online DDL, multi-source Replication, enhanced semi-synchronization, tablespace transmission, sys database, and Group Replication. I finally waited for a chance to upgrade MySQL to MySQL 5.7.

MySQL upgrade Overview

The essence of MySQL upgrade:

Update Data Dictionary

Data dictionaries include mysql, information_schema, cece_schema, and sys schema.

Two Methods for 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 in the existing data directory, and run mysql_upgrade.

Features: data files are not changed, and the upgrade speed is fast. However, they cannot be cross-OS or cross-major version (5.5-> 5.7 ).

Logical upgrade:

It is suitable for upgrades between MySQL and major versions of different operating systems.

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

Features: Cross-OS and cross-major versions are supported. However, the upgrade speed is slow and garbled.

Preparations before Upgrade:

Back up data in advance.

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

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

Upgrade notes:

Check whether there are major changes to the new version.

Note the changes in SQL mode

For example, if the SQL mode changes in MySQL5.7, some SQL statements cannot run in the unsupported SQL mode. In this case, you can clear the SQL mode and set the SQL mode after running.

After the upgrade is successful, check whether the business SQL can run successfully.

Is the program layer normal?

Sometimes some of the original program language content is not supported by the new version of the database. For example, PHP4.0 was used once at 5.1, but some PHP functions were not supported when it was upgraded to 5.6.

After the upgrade, you must use the same program as the online version during the test to test whether there is a problem.

Storage engine changes

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

Pay attention to Character Set garbled characters

Next, use the in-place upgrade Method to upgrade MySQL5.6 to MySQL5.7.

In-place upgrade MySQL

Environment:

5.6.15-> 5.7.20

Preparations before Upgrade:

Backup + pay attention to new version changes
Upgrade operation:

1. Download and decompress 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. Disable 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 with 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: mysql_upgrade is used to check whether all tables in all databases are compatible with the current new version and update the system library.

6. Restart to make sure the changes made to the system table take effect.

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

So far, the upgrade is complete.

Q: What should I do if I fail to upgrade MySQL?

During upgrade, a slave database is generally created for upgrade. If the upgrade fails, the master database is not affected. If the upgrade succeeds, the test is also successful, the slave database will be upgraded to the new version, and the master database will be removed. The slave database will be upgraded to the new master database to upgrade the old master database.