MySQL5.6升级到5.7

MySQL5.6升级

首先将MySQL安全的关闭。

/etc/init.d/mysqld stop
[root@elk-node03 bin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-log 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 its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost:mysql.sock)[(none)]>

然后将mysql的软链接切换到5.7的版本上面

# 切换之前
[root@elk-node03 application]# ls -ld mysql*
lrwxrwxrwx.  1 root  root    48 Feb 14 20:10 mysql -> /application/mysql-5.6.36-linux-glibc2.5-x86_64/
drwxr-xr-x. 13 root  root  4096 Feb 17 21:10 mysql-5.6.36-linux-glibc2.5-x86_64
drwxr-xr-x. 10 mysql mysql 4096 Feb 17 20:15 mysql-5.7.20-linux-glibc2.12-x86_64
# 切换之后
[root@elk-node03 application]# unlink mysql
[root@elk-node03 application]# ln -s /application/mysql-5.7.20-linux-glibc2.12-x86_64/ /application/mysql
[root@elk-node03 application]# ls -ld mysql*
lrwxrwxrwx.  1 root  root    49 Feb 18 07:36 mysql -> /application/mysql-5.7.20-linux-glibc2.12-x86_64/
drwxr-xr-x. 13 root  root  4096 Feb 17 21:10 mysql-5.6.36-linux-glibc2.5-x86_64
drwxr-xr-x. 10 mysql mysql 4096 Feb 17 20:15 mysql-5.7.20-linux-glibc2.12-x86_64

这个时候如果启动mysql的话是可以看到：

[root@elk-node03 application]# /etc/init.d/mysqld start
Starting MySQL.................... SUCCESS! 
[root@elk-node03 application]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log 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 its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(root@localhost:mysql.sock)[(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

可以看到已经升级到了5.7版本，只是看起来升级成功了。如果查看error.log日志，那么就会发现有许多的报错。这个时候还需要使用另一个命令来修复。

[root@elk-node03 application]# mysql_upgrade -uroot -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

执行之后才算是升级成功,登录进去发现sys库又重新出现了。

(root@localhost:mysql.sock)[(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

如果在线上升级的话，mysql会将所有的表进行按照5.7的格式进行重建，但是mysql的innodb都是兼容的。所以可以使用mysql_upgrade的一个参数。

[root@elk-node03 application]# mysql_upgrade --help
-s, --upgrade-system-tables 
                      Only upgrade the system tables, do not try to upgrade the
                      data.

使用这个参数的意义就是这更新系统表，而不涉及到数据表的更新。

[root@elk-node03 application]# mysql_upgrade -s --force
The --upgrade-system-tables option was used, databases won't be touched.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.1).
Upgrade process completed successfully.
Checking if update is needed.