MySQL 5.6 upgrade to MySQL 5.7--------version upgrade best Practice

1. Background

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

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

* JSON support: Using the JSON functionality 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 index, 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  -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 (); +-----------+|&nbsP;version ()  |+-----------+| 5.6.36    |+-----------+1 row in set   (0.05 SEC) mysql>

* The directory where MySQL resides

[[email protected] ~]# 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

* directory where MySQL data resides

[[EMAIL PROTECTED] ~]# 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

[Email protected] ~]# grep-e ' ^basedir=|^datadir= '/etc/init.d/mysqld basedir=/usr/local/mysqldatadir=/data/mysql_ Data

　

3. Upgrade

* Normal Stop database

[Email protected] mysql]#/etc/init.d/mysqld stopshutting down MySQL. success!

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

[Email protected] ~]# wget https://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 zxvf mysql-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 MySQL 5.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 startstarting MySQL ..... success!

* Use the Mysql_upgrade in MySQL 5.7 package to upgrade the system table in MySQL data-p to specify the password

[Email protected] ~]#/usr/local/mysql/bin/mysql_upgrade-s-p123456mysql_upgrade: [Warning] Using a password on the Comm and line interface can insecure. The--upgrade-system-tables option was used, databases won ' t is 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 -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

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1941616

MySQL 5.6 upgrade to MySQL 5.7--------version upgrade best Practice