线上 MYSQL 升级报错故障处理总结

一、ERROR 1054 (42S22)

Unknown column 'password_last_changed' in 'mysql.user'

mysql5.6 升级为 5.7 遇到在授权用户时报错：

ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user'

字段 ’password_last_changed’ 在 MySQL <5.7 的版本中存在, 但是在 5.7，给删除了。  
所以升级了 mysql server 之后，你还有运行 ’mysql_upgrade’  脚本把 tables 从老版本中迁移到新版本。

解决方法：

# /data/mysql-5.7.16/bin/mysql_upgrade -uroot -pIp02016@mlS7639 -h127.0.0.1 -P3215

但是会报错：

mysql_upgrade失败是因为以下表不存在, Checking if update is needed. Checking server version. Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1146: Table 'mysql.innodb_table_stats' doesn't exist ......

解决方法：

mysql> use mysql;    # 一定要是 drop table if exists

mysql> drop table if exists mysql.innodb_index_stats; drop table if exists mysql.innodb_table_stats; drop table if exists mysql.slave_master_info; drop table if exists mysql.slave_relay_log_info; drop table if exists mysql.slave_worker_info;drop table if exists mysql.gtid_executed;drop table if exists mysql.engine_cost;drop table if exists mysql.help_topic;drop table if exists mysql.help_relation;drop table if exists mysql.help_keyword;drop table if exists mysql.help_category;drop table if exists mysql.plugin;drop table if exists mysql.servers;drop table if exists mysql.server_cost;drop table if exists mysql.time_zone;drop table if exists mysql.time_zone_leap_second;drop table if exists mysql.time_zone_name;drop table if exists mysql.time_zone_transition;drop table if exists mysql.time_zone_transition_type;drop table if exists mysql.ndb_binlog_index;drop table if exists sys.sys_config;

# 接下来，并进入到数据库数据文件所在目录，删除表所对应的idb文件

# cd /data/mysql5.7.16data/mysql3215/data/mysql
# ll innodb_* -rw-r----- 1 mysql mysql 9437184 Jul 7 02:46 innodb_index_stats.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 02:46 innodb_table_stats.ibd # rm -rf innodb_* # ll slave_* -rw-r----- 1 mysql mysql 98304 Jul 7 02:46 slave_master_info.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 slave_relay_log_info.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 slave_worker_info.ibd # rm -rf slave_* # ll gtid_executed* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 gtid_executed.ibd # rm -rf gtid_executed.ibd # ll server* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 server_cost.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 servers.ibd # rm -rf server* # ll engine_cost* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 engine_cost.ibd # rm -rf engine_cost.ibd # ll help_* -rw-r----- 1 mysql mysql 114688 Jul 7 04:12 help_category.ibd -rw-r----- 1 mysql mysql 245760 Jul 7 04:12 help_keyword.ibd -rw-r----- 1 mysql mysql 131072 Jul 7 04:12 help_relation.ibd -rw-r----- 1 mysql mysql 9437184 Jul 7 04:12 help_topic.ibd # rm -rf help_* ll plugin* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 plugin.ibd # rm -rf plugin.ibd # ll time_zone* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 time_zone.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 time_zone_leap_second.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 time_zone_name.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 time_zone_transition.ibd -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 time_zone_transition_type.ibd # rm -rf time_zone* # ll ndb_binlog_index.* -rw-r----- 1 mysql mysql 98304 Jul 5 18:19 ndb_binlog_index.ibd -rw-r----- 1 mysql mysql 0 Jul 7 03:13 ndb_binlog_index.MYD -rw-r----- 1 mysql mysql 1024 Jul 7 03:13 ndb_binlog_index.MYI # rm -rf ndb_binlog_index.*
# cd ../sys # ll sys_config* -rw-r----- 1 mysql mysql 98304 Jul 7 04:12 sys_config.ibd # rm -rf sys_config.ibd

mysql> source /data/mysql-5.7.16/share/mysql_system_tables.sql

最后：

# /data/mysql-5.7.16/bin/mysql_upgrade -uxxxx -pxxxx -h127.0.0.1 -Pxxxx ...... Upgrade process completed successfully. Checking if update is needed.

二、ERROR 1682 (HY000)

Native table 'performance_schema'.'session_variables' has the wrong structure

mysql 5.6 升级到 mysql5.7 查看参数报错：

mysql> show variables like '%read_only%';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure

报错原因：从 mysql5.7.6 开始 information_schema.global_status 已经开始被舍弃，为了兼容性，此时需要打开 show_compatibility_56

解决办法：set @@global.show_compatibility_56=ON;

mysql> set @@global.show_compatibility_56=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.00 sec)