MySQL5.6更新到5.7

MySQL5.6更新

首先将MySQL安全的關閉。

/etc/init.d/mysqld stop
[[email protected] bin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 
Server version: -log MySQL Community Server (GPL)

Copyright (c) , , 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.

([email protected]:mysql.sock)[(none)]>
           

然後将mysql的軟連結切換到5.7的版本上面

# 切換之前
[root@elk-node03 application]# ls -ld mysql*
lrwxrwxrwx.   root  root     Feb  : mysql -> /application/mysql-5.6.36-linux-glibc2.5-x86_64/
drwxr-xr-x.  root  root   Feb  : mysql--linux-glibc2-x86_64
drwxr-xr-x.  mysql mysql  Feb  : mysql--linux-glibc2-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.   root  root     Feb  : mysql -> /application/mysql-5.7.20-linux-glibc2.12-x86_64/
drwxr-xr-x.  root  root   Feb  : mysql--linux-glibc2-x86_64
drwxr-xr-x.  mysql mysql  Feb  : mysql--linux-glibc2-x86_64
           

這個時候如果啟動mysql的話是可以看到：

[[email protected] application]# /etc/init.d/mysqld start
Starting MySQL.................... SUCCESS! 
[[email protected] application]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 
Server version: -log MySQL Community Server (GPL)

Copyright (c) , , 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.
([email protected]:mysql.sock)[(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
 rows in set ( sec)
           

可以看到已經更新到了5.7版本，隻是看起來更新成功了。如果檢視error.log日志，那麼就會發現有許多的報錯。這個時候還需要使用另一個指令來修複。

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

庫又重新出現了。

([email protected]: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

的一個參數。

[[email protected] application]# mysql_upgrade --help
-s, --upgrade-system-tables 
                      Only upgrade the system tables, do not try to upgrade the
                      data.
           

使用這個參數的意義就是這更新系統表，而不涉及到資料表的更新。

[[email protected] 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 ).
Upgrade process completed successfully.
Checking if update is needed.