Upgrade from MySQL 5.6 to MySQL 5.7-version upgrade best practice

1. background

MySQL 5.7 is the latest version of MySQL. Compared with MySQL 5.6, MySQL 5.7 has the following characteristics

* Performance and scalability: Improve scalability of InnoDB and performance of temporary tables for faster network and big data loading operations.

* JSON support: Using MySQL's JSON capabilities, you can combine the flexibility of NoSQL with the power of relational databases.

* Improve replication to improve availability performance. Includes multi-source replication, multi-slave thread enhancement, online GTIDs, and enhanced semi-synchronous replication.

* Performance mode provides a better view. We added many new monitoring features to reduce space and overload, and significantly improved ease of use with the new SYS mode.

* Security: We put security first, and many new MySQL 5.7 features help users keep their databases secure.

* Optimizations: Overwrites most of the resolvers, optimizers and cost models. This improves maintainability, scalability and performance.

* GIS: MySQL 5.7 features new features, including InnoDB spatial indexing, use of Boost.Geometry, and improved integrity and standards compliance.

2. MySQL 5.6 environment currently running

* MySQL current version

[root@MySQL ~]# /usr/local/mysql/bin/mysql -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.36 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+-----------+| version() |+-----------+| 5.6.36 |+-----------+1 row in set (0.05 sec)mysql>

* MySQL Directory

[root@MySQL ~]# ll /usr/local/mysql-5.6.36-linux-glibc2.5-x86_64total 72drwxr-xr-x 2 mysql mysql 4096 Jun 24 04:05 bin-rw-r--r-- 1 mysql mysql 17987 Mar 18 14:43 COPYINGdrwxr-xr-x 3 mysql mysql 4096 Jun 24 04:05 datadrwxr-xr-x 2 mysql mysql 4096 Jun 24 04:05 docsdrwxr-xr-x 3 mysql mysql 4096 Jun 24 04:05 includedrwxr-xr-x 3 mysql mysql 4096 Jun 24 04:06 libdrwxr-xr-x 4 mysql mysql 4096 Jun 24 04:05 man-rw-r--r-- 1 root root 943 Jun 24 04:08 my.cnfdrwxr-xr-x 10 mysql mysql 4096 Jun 24 04:05 mysql-test-rw-r--r-- 1 mysql mysql 2496 Mar 18 14:43 READMEdrwxr-xr-x 2 mysql mysql 4096 Jun 24 04:05 scriptsdrwxr-xr-x 28 mysql mysql 4096 Jun 24 04:05 sharedrwxr-xr-x 4 mysql mysql 4096 Jun 24 04:06 sql-benchdrwxr-xr-x 2 mysql mysql 4096 Jun 24 04:05 support-files

* MySQL Data Directory

[root@MySQL ~]# ll /data/mysql_datatotal 110616-rw-rw---- 1 mysql mysql 56 Jun 24 04:10 auto.cnf-rw-rw---- 1 mysql mysql 12582912 Jun 24 04:10 ibdata1-rw-rw---- 1 mysql mysql 50331648 Jun 24 04:10 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 Jun 24 04:08 ib_logfile1drwx------ 2 mysql mysql 4096 Jun 24 04:08 mysql-rw-rw---- 1 mysql mysql 1771 Jun 24 04:10 MySQL.err-rw-rw---- 1 mysql mysql 6 Jun 24 04:10 MySQL.piddrwx------ 2 mysql mysql 4096 Jun 24 04:08 performance_schemadrwx------ 2 mysql mysql 4096 Jun 24 04:08 test

* MySQL startup script basedir and datadir settings

[root@MySQL ~]# grep -E '^basedir=|^datadir=' /etc/init.d/mysqld basedir=/usr/local/mysqldatadir=/data/mysql_data

3. upgrade

* Normal Stop Database

[root@MySQL mysql]# /etc/init.d/mysqld stopShutting down MySQL.. SUCCESS!

* Download MySQL 5.7 latest version [Recommended download from MySQL official]

[root@MySQL ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

* Extract to specified directory

[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

* Delete existing soft links

[root@MySQL ~]# unlink /usr/local/mysql

* New soft link to MySQL 5.7 directory

[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

* Start MySQL with a script

[root@MySQL ~]# /etc/init.d/mysqld startStarting MySQL..... SUCCESS!

* Use mysql_upgrade in MySQL 5.7 package to upgrade system table-p in MySQL data Specify password

[root@MySQL ~]# /usr/local/mysql/bin/mysql_upgrade -s -p123456mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.The --upgrade-system-tables option was used, databases won't be touched.Checking if update is needed.Checking server version.Running queries to upgrade MySQL server.Upgrading the sys schema.Upgrade process completed successfully.Checking if update is needed.

* Connect MySQL Services View Version

[root@MySQL ~]# /usr/local/mysql/bin/mysql -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.18 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+-----------+| version() |+-----------+| 5.7.18 |+-----------+1 row in set (0.00 sec)

4. summary

Demand-driven technology, technology itself has no advantages, only business points.