MySQL 版本升级本次展示5.6升级到5.7。 Column count of performance_schema.

本次展示5.6升级到5.7。

参考文献：dev.mysql.com/doc/refman/…

需要的条件：

5.6版本已安装

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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)[(none)]> quit;
Bye

5.7版本的包

lrwxrwxrwx.  1 root  root    36 7月  24 20:44 mysql -> mysql-5.6.43-linux-glibc2.12-x86_64/
drwxr-xr-x. 13 mysql mysql 4096 7月  24 20:53 mysql-5.6.43-linux-glibc2.12-x86_64
drwxr-xr-x. 10 mysql mysql 4096 7月  24 18:57 mysql-5.7.25-linux-glibc2.12-x86_64

步骤一、将运行的MySQL关闭

[root@localhost local]# service mysql stop
Shutting down MySQL.. SUCCESS!

步骤二、删除软链

[root@localhost local]# unlink mysql

步骤三、将软链指向新的版本，（此时升级已经完成）

[root@localhost local]# ln -s mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
lrwxrwxrwx.  1 root  root    36 7月  27 17:36 mysql -> mysql-5.7.25-linux-glibc2.12-x86_64/
drwxr-xr-x. 13 mysql mysql 4096 7月  24 20:53 mysql-5.6.43-linux-glibc2.12-x86_64
drwxr-xr-x. 10 mysql mysql 4096 7月  24 18:57 mysql-5.7.25-linux-glibc2.12-x86_64

步骤四、启动

[root@localhost local]# service mysql start
Starting MySQL... SUCCESS! 
[root@localhost local]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost local]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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)[(none)]>

此时查看一下错误日志，会发现日志中有很多 error

[root@localhost data]# vim error.log 
……
2020-07-27T09:51:48.042540Z 0 [ERROR] Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50643, now running 50725. Please use mysql_upgrade to fix this error.
2020-07-27T09:51:48.042601Z 0 [ERROR] Column count of performance_schema.events_statements_history is wrong. Expected 41, found 40. Created with MySQL 50643, now running 50725. Please use mysql_upgrade to fix this error.
2020-07-27T09:51:48.042705Z 0 [ERROR] Column count of performance_schema.events_statements_history_long is wrong. Expected 41, found 40. Created with MySQL 50643, now running 50725. Please use mysql_upgrade to fix this error.
2020-07-27T09:51:48.042810Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
2020-07-27T09:51:48.042874Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).

……
2020-07-27T09:45:51.576278Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
2020-07-27T09:45:51.576288Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
2020-07-27T09:45:51.576297Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
2020-07-27T09:45:51.576308Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
2020-07-27T09:45:51.576318Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
2020-07-27T09:45:51.576329Z 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
2020-07-27T09:45:51.576339Z 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
2020-07-27T09:45:51.576349Z 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
2020-07-27T09:45:51.576359Z 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
2020-07-27T09:45:51.576368Z 0 [ERROR] Native table 'performance_schema'.'status_by_host' has the wrong structure
2020-07-27T09:45:51.576378Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
2020-07-27T09:45:51.576388Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
2020-07-27T09:45:51.576398Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
2020-07-27T09:45:51.576407Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
2020-07-27T09:45:51.576417Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
2020-07-27T09:45:51.576432Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2020-07-27T09:45:51.576442Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
2020-07-27T09:45:51.576490Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
2020-07-27T09:45:51.576499Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
2020-07-27T09:45:51.576577Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2020-07-27T09:45:51.577692Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.25'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

解读第一行日志这个表原来有41个列，现在只找到40个，创建的时候是5.6.43,现在运行的是5.7.25,赶紧运行一下mysql_upgrade 修复这个错误

Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50643, now running 50725. Please use mysql_upgrade to fix this error.

步骤五、运行 mysql_upgrade 来修复错误

[root@localhost bin]# ./mysql_upgrade -s -uroot -p
Enter password: 
The --upgrade-system-tables option was used, 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.
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]#

注意：官方文档上说是没有问题，这里-s 是只更新原数据信息