MySQL upgrade from 5.6.18 to 5.7.11

Upgrade MySQL5.6.18 to 5.7.11 on rhel7.2

This time using the binary package to upgrade with the Out-place method.

1. Backup Data

The backup is heavier than everything!

Various backup methods Mysqldump, CP .....

2. Upload 5.7.11 Package and unzip

[Email protected] mysql]# lsmysql5.6.18 mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz[[email protected] mysql]# TAR-ZXVF Mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz ... [Email protected] mysql]# MV mysql-5.7.11-linux-glibc2.5-x86_64 mysql5.7.11

[Email protected] mysql]# chown mysql:mysql-r mysql5.7.11
[Email protected] mysql]# ls-l
Total 8
Drwxr-xr-x. MySQL mysql 4096 Dec 5 15:17 mysql5.6.18
Drwxr-xr-x. 9 MySQL mysql 4096 Feb 2 mysql5.7.11

3. Stop the old version of the database

#设置innodb_fast_shutdown =0[[email protected] bin]# pwd/opt/mysql/mysql5.6.18/bin[[email  Protected] bin]# ./mysql -u root -p --execute= "SET GLOBAL innodb_fast_ Shutdown=0 "Enter password: [[email protected] bin]# ./mysql -u root -p  --execute= "show global variables like  ' Innodb_fast_shutdown '" Enter password:  +----------------------+-------+| variable_name        |  value |+----------------------+-------+| innodb_fast_shutdown | 0      |+----------------------+-------+ #关闭MySQL [[email protected] bin]# ./mysqladmin  -uroot -p shutdown enter password: [[email protected] bin]# ps  -ef |grep mysqlroot      2771  1575  0  16:58 pts/0     00:00:00 grep --color=auto mysql 

Innodb_fast_shutdown parameter explanation reference: Http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fast_shutdown

4. Modify the configuration file

#修改/etc/my.cnf file [[email protected] bin]# Vi/etc/my.cnfbasedir =/opt/mysql/mysql5.7.11 #basedir指向新的软件目录

5. Start a new version of the software

[[email protected] mysql5.7.11]# pwd/opt/mysql/mysql5.7.11[[email protected]  mysql5.7.11]# ./bin/mysqld_safe --user=mysql &[1] 3000[[email protected]  mysql5.7.11]# 2016-12-05t09:03:09.133964z mysqld_safe logging to  '/mysqldata/ Rhel7.err ' .2016-12-05t09:03:09.161910z mysqld_safe starting mysqld daemon with  Databases from /mysqldata[[email protected]l7 mysql5.7.11]# ps -ef |grep  mysqlroot      3000  1575  0 17:03 pts/0     00:00:00 /bin/sh ./bin/mysqld_safe --user=mysqlmysql      3118  3000  3 17:03 pts/0    00:00:00 /opt/ Mysql/mysql5.7.11/bin/mysqld --basedir=/opt/mysql/mysql5.7.11 --datadir=/mysqldata --plugin-dir =/opt/mysql/mysql5.7.11/lib/plugin --user=mysql --log-error=/mysqldata/rhel7.err --pid-file=/mysqldata/rhel7.pidroot       3147  1575  0 17:03 pts/0     00:00:00 grep --color=auto mysql

6. Upgrade Database

[[Email protected] mysql5.7.11]# ./bin/mysql_upgrade -uroot -penter password:  checking if update is needed. Checking server version. Running queries to upgrade mysql server. checking system database.mysql.columns_priv                                   OKmysql.db                                             okmysql.engine_cost                                    okmysql.event                                          OKmysql.func                                           OKmysql.general_log                                    okmysql.gtid_ executed                                 okmysql.help_ category                                 OKmysql.help_keyword                                   OKmysql.help_relation                                  OKmysql.help_topic                                     OKmysql.innodb_index_stats                             okmysql.innodb_table_stats                            okmysql.ndb_binlog_ index                              OKmysql.plugin                                          okmysql.proc                                           OKmysql.procs_priv                                     OKmysql.proxies_priv                                   OKmysql.server_cost                                    OKmysql.servers                                        OKmysql.slave_master_info                              okmysql.slave_relay_log_info                          OKmysql.slave_worker_info                              OKmysql.slow_log                                       OKmysql.tables_priv                                    OKmysql.time_zone                                      OKmysql.time_zone_leap_second                          OKmysql.time_zone_name                                 OKmysql.time_zone_transition                          okmysql.time_ zone_transition_type                     OKmysql.user                                           okupgrading the sys schema. checking databases.sys.sys_config                                       OKtest.t_nu                                            OKtest.u                                                okupgrade process completed successfully. Checking if update is needed.

7. Restart MySQL to ensure all upgrade applications succeed

[[email protected] mysql5.7.11]# ./bin/mysqladmin -uroot -p123456 shutdown  mysqladmin: [warning] using a password on the command line  Interface can be insecure. [[email protected] mysql5.7.11]# 2016-12-05t09:09:39.211113z mysqld_safe mysqld  from pid file /mysqldata/rhel7.pid ended[1]+  done                     ./bin/mysqld_safe  --user=mysql[[email protected] mysql5.7.11]# ps -ef |grep mysqlroot       3162  1575  0 17:09 pts/0     00:00:00 grep --color=auto mysql[[email protected] mysql5.7.11]# ./bin/mysqld_ Safe --user=mysql &[1] 3163[[email protected] mysql5.7.11]# 2016-12-05t09:10:02.064621z mysqld_safe logging to  '/mysqldata/rhel7.err '. 2016-12-05T09 :10:02.097210z mysqld_safe starting mysqld daemon with databases from  /mysqldata[[email protected] mysql5.7.11]# ps -ef |grep mysqlroot       3163  1575  0 17:10 pts/0     00:00:00 /bin/sh ./bin/mysqld_safe --user=mysqlmysql     3287   3163  2 17:10 pts/0    00:00:00 /opt/mysql/mysql5.7.11/bin/ mysqld --basedir=/opt/mysql/mysql5.7.11 --datadir=/mysqldata --plugin-dir=/opt/mysql/ mysql5.7.11/lib/plugin --user=mysql --log-error=/mysqldata/rhel7.err --pid-file=/mysqldata/ rhel7.pidroot      3316  1575  0 17:10 pts/0     00:00:00 grep --color=auto mysql[[email protected] mysql5.7.11]# ./bin/mysql -uroot - 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 2server version: 5.7.11 mysql  community server  (GPL) copyright  (c)  2000, 2016, 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>  show status;+-----------------------------------------------+------------------------------------------- -------+| variable_name                                   | Value                                              |+------------------------------------ -----------+--------------------------------------------------+| aborted_clients                                 | 0                                                  | |  Aborted_connects                               | 0                                                   | |  Binlog_cache_disk_use                          | 0                                                  | |  Binlog_cache_use                               | 0                                                   | |  Binlog_stmt_cache_disk_use                     | 0                                                  | |  Binlog_stmt_cache_use                          | 0                                                   | |  Bytes_received                                 |  248                                                | ...

The following error may be reported if not restarted:

Mysql> Show status; ERROR 1682 (HY000): Native table ' Performance_schema '. Session_status ' has the wrong structure

Official Document: Http://dev.mysql.com/doc/refman/5.7/en/upgrading.html#upgrade-procedure-inplace

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1879904

MySQL upgrade from 5.6.18 to 5.7.11