MySQL 5.6 upgrade to MySQL 5.7

1. Background

MySQL 5.7 is the latest version of MySQL, with the following features compared to the MySQL 5.6 version

* Performance and Scalability: Improve InnoDB scalability and temporal table performance for faster network and big data loading operations.

*json Support: Using the JSON feature of MySQL , you can combine the power of NoSQL with the flexibility and relational database.

* Improved replication to improve usability performance. Includes multi-source replication, multi-line potentiation, online gtids, and enhanced semi-synchronous replication.

* Performance Mode provides a better perspective. We have added many new monitoring features to reduce space and overload, and use the new SYS mode to significantly improve usability.

* Security : We implement "safety first" requirements, many MySQL 5.7 new features to help users to ensure their database security.

* optimization : rewrite most parsers, optimizer and cost models. This improves maintainability, scalability, and performance.

*gis:mysql 5.7 new features, including InnoDB spatial indexes, using boost.geometry, while improving integrity and standard compliance.

2. the currently running MySQL 5.6 Environment

*mysql Current Version

[Email protected] ~]#/usr/local/mysql/bin/mysql-p123456

Warning:using a password on the CommandLine interface can be insecure.

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 2

Server version:5.6.36 MySQL communityserver (GPL)

Copyright (c), Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement.

Mysql> select version ();

+-----------+

| Version () |

+-----------+

| 5.6.36 |

+-----------+

1 row in Set (0.05 sec)

Mysql>

directory where *mysql

[Email protected] ~]# ll/usr/local/mysql-5.6.36-linux-glibc2.5-x86_64

Total 72

Drwxr-xr-x 2 mysql mysql 4096 June 04:05bin

-rw-r--r--1 mysql mysql 17987 Mar 14:43 COPYING

Drwxr-xr-x 3 mysql mysql 4096 June 04:05data

Drwxr-xr-x 2 mysql mysql 4096 June 04:05docs

Drwxr-xr-x 3 mysql mysql 4096 June 04:05include

Drwxr-xr-x 3 mysql mysql 4096 June 04:06lib

Drwxr-xr-x 4 mysql mysql 4096 June 04:05man

-rw-r--r--1 root root 943 June 04:08 my.cnf

drwxr-xr-x MySQL mysql 4096 June 04:05 mysql-test

-rw-r--r--1 mysql mysql 2496 Mar 14:43readme

Drwxr-xr-x 2 mysql mysql 4096 June 04:05scripts

drwxr-xr-x MySQL mysql 4096 June 04:05 share

Drwxr-xr-x 4 mysql mysql 4096 June 04:06sql-bench

Drwxr-xr-x 2 mysql mysql 4096 June 04:05support-files

The directory where the *mysql data resides

[Email protected] ~]# Ll/data/mysql_data

Total 110616

-RW-RW----1 MySQL mysql 04:10 auto.cnf

-RW-RW----1 MySQL mysql 12582912 June 2404:10 ibdata1

-RW-RW----1 MySQL mysql 50331648 June 2404:10 Ib_logfile0

-RW-RW----1 MySQL mysql 50331648 June 2404:08 Ib_logfile1

drwx------2 MySQL mysql 4096 June 04:08 MySQL

-RW-RW----1 MySQL mysql 1771 June 04:10 Mysql.err

-RW-RW----1 MySQL mysql 6 June 04:10 Mysql.pid

drwx------2 MySQL mysql 4096 June 04:08 Performance_schema

drwx------2 MySQL mysql 4096 June 04:08 test

*mysql Startup Scripts Basedir and datadir settings

[Email protected] ~]# grep-e ' ^basedir=|^datadir= '/etc/init.d/mysqld

Basedir=/usr/local/mysql

Datadir=/data/mysql_data

3. Upgrade

* Normal Stop database

[[email protected] mysql]#/etc/init.d/mysqld stop

Shutting down MySQL. success!

* download mysql 5.7 latest version [ recommended from mysql official download ]

[Email protected] ~]# wgethttps://dev.mysql.com/get/downloads/mysql-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

* unzip to the specified directory

[Email protected] ~]# tar zxvfmysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local/

* Delete the original soft link

[Email protected] ~]# Unlink/usr/local/mysql

* new Soft link point to MySQL5.7 directory

[Email protected] ~]# ln-s/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/usr/local/mysql

* start MySQL with a script

[[email protected] ~]#/etc/init.d/mysqld start

Starting MySQL ..... success!

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

[Email protected] ~]#/usr/local/mysql/bin/mysql_upgrade-s-p123456

Mysql_upgrade: [Warning] Using a Passwordon the command line interface can be insecure.

The--upgrade-system-tables option wasused, 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 to MySQL Service view version

[Email protected] ~]#/usr/local/mysql/bin/mysql-p123456

MySQL: [Warning] Using a password on Thecommand line interface can be insecure.

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 5

Server version:5.7.18 MySQL communityserver (GPL)

Copyright (c), Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement.

Mysql> select version ();

+-----------+

| Version () |

+-----------+

| 5.7.18 |

+-----------+

1 row in Set (0.00 sec)

MySQL 5.6 upgrade to MySQL 5.7