升级MySQL 报错

题图来自网络

今天把测试系统的MySQL镜像版本5.6.34替换为5.7.16,也就是把MySQL 5.6.34 升级到5.7.16。
升级过程:

  1. 把MySQL 5.6.34的容器停了,docker stop mysql
  2. 启动新的MySQL:5.7.16,密码没改,数据库文件用-v 指向与5.6.34的同一路径。
  3. 启动完发现数据库可以连接,但是select 数据的时候有问题,查看启动日志发现后文中的错误。错误的原因是升级MySQL,默认数据库的表结构发生变化。
  4. 进入容器 docker exec -it mysql bash
  5. 在容器内执行 /usr/bin/mysql_upgrade -u root -p,修复完毕。

2016-11-25T06:35:16.576568Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_current: expected column ‘NESTING_EVENT_TYPE’ at position 15 to have type enum(‘TRANSACTION’,’STATEMENT’,’STAGE’,’WAIT’, found type enum(‘STATEMENT’,’STAGE’,’WAIT’).
2016-11-25T06:35:16.576651Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_history: expected column ‘NESTING_EVENT_TYPE’ at position 15 to have type enum(‘TRANSACTION’,’STATEMENT’,’STAGE’,’WAIT’, found type enum(‘STATEMENT’,’STAGE’,’WAIT’).
2016-11-25T06:35:16.576728Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_history_long: expected column ‘NESTING_EVENT_TYPE’ at position 15 to have type enum(‘TRANSACTION’,’STATEMENT’,’STAGE’,’WAIT’, found type enum(‘STATEMENT’,’STAGE’,’WAIT’).
2016-11-25T06:35:16.577004Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_user_by_event_name: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.577078Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_account_by_event_name: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.577614Z 0 [ERROR] Column count of performance_schema.setup_actors is wrong. Expected 5, found 3. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.577776Z 0 [ERROR] Incorrect definition of table performance_schema.setup_objects: expected column ‘OBJECT_TYPE’ at position 0 to have type enum(‘EVENT’,’FUNCTION’,’PROCEDURE’,’TABLE’,’TRIGGER’, found type enum(‘TABLE’).
2016-11-25T06:35:16.578178Z 0 [ERROR] Column count of performance_schema.table_lock_waits_summary_by_table is wrong. Expected 68, found 73. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578244Z 0 [ERROR] Column count of performance_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578309Z 0 [ERROR] Column count of performance_schema.events_stages_current is wrong. Expected 12, found 10. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578369Z 0 [ERROR] Column count of performance_schema.events_stages_history is wrong. Expected 12, found 10. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578433Z 0 [ERROR] Column count of performance_schema.events_stages_history_long is wrong. Expected 12, found 10. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578567Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_account_by_event_name: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.578638Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_user_by_event_name: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.578860Z 0 [ERROR] Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.578956Z 0 [ERROR] Column count of performance_schema.events_statements_history is wrong. Expected 41, found 40. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.579051Z 0 [ERROR] Column count of performance_schema.events_statements_history_long is wrong. Expected 41, found 40. Created with MySQL 50634, now running 50716. Please use mysql_upgrade to fix this error.
2016-11-25T06:35:16.579193Z 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).
2016-11-25T06:35:16.579268Z 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).
2016-11-25T06:35:16.579501Z 0 [ERROR] Native table ‘performance_schema’.’events_statements_summary_by_program’ has the wrong structure
2016-11-25T06:35:16.579559Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_current’ has the wrong structure
2016-11-25T06:35:16.579589Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_history’ has the wrong structure
2016-11-25T06:35:16.579614Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_history_long’ has the wrong structure
2016-11-25T06:35:16.579641Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_by_thread_by_event_name’ has the wrong structure
2016-11-25T06:35:16.579662Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_by_account_by_event_name’ has the wrong structure
2016-11-25T06:35:16.579688Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_by_user_by_event_name’ has the wrong structure
2016-11-25T06:35:16.579711Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_by_host_by_event_name’ has the wrong structure
2016-11-25T06:35:16.579736Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_global_by_event_name’ has the wrong structure
2016-11-25T06:35:16.579803Z 0 [ERROR] Incorrect definition of table performance_schema.users: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.579885Z 0 [ERROR] Incorrect definition of table performance_schema.accounts: expected column ‘USER’ at position 0 to have type char(32), found type char(16).
2016-11-25T06:35:16.580251Z 0 [ERROR] Native table ‘performance_schema’.’memory_summary_global_by_event_name’ has the wrong structure
2016-11-25T06:35:16.580286Z 0 [ERROR] Native table ‘performance_schema’.’memory_summary_by_account_by_event_name’ has the wrong structure
2016-11-25T06:35:16.580311Z 0 [ERROR] Native table ‘performance_schema’.’memory_summary_by_host_by_event_name’ has the wrong structure
2016-11-25T06:35:16.580338Z 0 [ERROR] Native table ‘performance_schema’.’memory_summary_by_thread_by_event_name’ has the wrong structure
2016-11-25T06:35:16.580364Z 0 [ERROR] Native table ‘performance_schema’.’memory_summary_by_user_by_event_name’ has the wrong structure
2016-11-25T06:35:16.580389Z 0 [ERROR] Native table ‘performance_schema’.’table_handles’ has the wrong structure
2016-11-25T06:35:16.580410Z 0 [ERROR] Native table ‘performance_schema’.’metadata_locks’ has the wrong structure
2016-11-25T06:35:16.580435Z 0 [ERROR] Native table ‘performance_schema’.’replication_connection_configuration’ has the wrong structure
2016-11-25T06:35:16.580459Z 0 [ERROR] Native table ‘performance_schema’.’replication_group_members’ has the wrong structure
2016-11-25T06:35:16.580483Z 0 [ERROR] Native table ‘performance_schema’.’replication_connection_status’ has the wrong structure
2016-11-25T06:35:16.580507Z 0 [ERROR] Native table ‘performance_schema’.’replication_applier_configuration’ has the wrong structure
2016-11-25T06:35:16.580548Z 0 [ERROR] Native table ‘performance_schema’.’replication_applier_status’ has the wrong structure
2016-11-25T06:35:16.580577Z 0 [ERROR] Native table ‘performance_schema’.’replication_applier_status_by_coordinator’ has the wrong structure
2016-11-25T06:35:16.580602Z 0 [ERROR] Native table ‘performance_schema’.’replication_applier_status_by_worker’ has the wrong structure
2016-11-25T06:35:16.580628Z 0 [ERROR] Native table ‘performance_schema’.’replication_group_member_stats’ has the wrong structure
2016-11-25T06:35:16.580649Z 0 [ERROR] Native table ‘performance_schema’.’prepared_statements_instances’ has the wrong structure
2016-11-25T06:35:16.580674Z 0 [ERROR] Native table ‘performance_schema’.’user_variables_by_thread’ has the wrong structure
2016-11-25T06:35:16.580698Z 0 [ERROR] Native table ‘performance_schema’.’status_by_account’ has the wrong structure
2016-11-25T06:35:16.580721Z 0 [ERROR] Native table ‘performance_schema’.’status_by_host’ has the wrong structure
2016-11-25T06:35:16.580745Z 0 [ERROR] Native table ‘performance_schema’.’status_by_thread’ has the wrong structure
2016-11-25T06:35:16.580768Z 0 [ERROR] Native table ‘performance_schema’.’status_by_user’ has the wrong structure
2016-11-25T06:35:16.580801Z 0 [ERROR] Native table ‘performance_schema’.’global_status’ has the wrong structure
2016-11-25T06:35:16.580825Z 0 [ERROR] Native table ‘performance_schema’.’session_status’ has the wrong structure
2016-11-25T06:35:16.580848Z 0 [ERROR] Native table ‘performance_schema’.’variables_by_thread’ has the wrong structure
2016-11-25T06:35:16.580867Z 0 [ERROR] Native table ‘performance_schema’.’global_variables’ has the wrong structure
2016-11-25T06:35:16.580907Z 0 [ERROR] Native table ‘performance_schema’.’session_variables’ has the wrong structure
2016-11-25T06:35:16.581008Z 0 [ERROR] Incorrect definition of table mysql.db: expected column ‘User’ at position 2 to have type char(32), found type char(16).
2016-11-25T06:35:16.581032Z 0 [ERROR] mysql.user has no Event_priv column at position 28
2016-11-25T06:35:16.581177Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.