解决方法Table 'performance_schema.session_variables' doesn't exist

本文记录了一次MySQL数据库启动过程中遇到的性能模式错误,并详细展示了通过mysql_upgrade命令进行修复的过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 
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.

mysql> show variables like 'performance_schema';

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist


运行mysql时,提示Table ‘performance_schema.session_variables’ doesn’t exist

日志文件报错如下:

2017-04-27T08:58:04.195252Z 0 [Warning] System table 'time_zone_leap_second' is expected to be transactional.
2017-04-27T08:58:04.195281Z 0 [Warning] System table 'time_zone_name' is expected to be transactional.
2017-04-27T08:58:04.195289Z 0 [Warning] System table 'time_zone' is expected to be transactional.
2017-04-27T08:58:04.195296Z 0 [Warning] System table 'time_zone_transition_type' is expected to be transactional.
2017-04-27T08:58:04.195303Z 0 [Warning] System table 'time_zone_transition' is expected to be transactional.
2017-04-27T08:58:04.196269Z 0 [Warning] System table 'servers' is expected to be transactional.
2017-04-27T08:58:04.196770Z 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').
2017-04-27T08:58:04.196873Z 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').
2017-04-27T08:58:04.196968Z 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').
2017-04-27T08:58:04.197278Z 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).
2017-04-27T08:58:04.197369Z 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).
2017-04-27T08:58:04.198123Z 0 [ERROR] Column count of performance_schema.setup_actors is wrong. Expected 5, found 3. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.198356Z 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').
2017-04-27T08:58:04.198868Z 0 [ERROR] Column count of performance_schema.table_lock_waits_summary_by_table is wrong. Expected 68, found 73. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.198960Z 0 [ERROR] Column count of performance_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.199045Z 0 [ERROR] Column count of performance_schema.events_stages_current is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.199128Z 0 [ERROR] Column count of performance_schema.events_stages_history is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.199211Z 0 [ERROR] Column count of performance_schema.events_stages_history_long is wrong. Expected 12, found 10. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.199372Z 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).
2017-04-27T08:58:04.199461Z 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).
2017-04-27T08:58:04.199750Z 0 [ERROR] Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.199885Z 0 [ERROR] Column count of performance_schema.events_statements_history is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.200013Z 0 [ERROR] Column count of performance_schema.events_statements_history_long is wrong. Expected 41, found 40. Created with MySQL 50616, now running 50717. Please use mysql_upgrade to fix this error.
2017-04-27T08:58:04.200196Z 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).
2017-04-27T08:58:04.200296Z 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).
2017-04-27T08:58:04.200609Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure
2017-04-27T08:58:04.200651Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_current' has the wrong structure
2017-04-27T08:58:04.200687Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure
2017-04-27T08:58:04.200723Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure
2017-04-27T08:58:04.200762Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure
2017-04-27T08:58:04.200800Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_account_by_event_name' has the wrong structure
2017-04-27T08:58:04.200838Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_user_by_event_name' has the wrong structure
2017-04-27T08:58:04.200883Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_host_by_event_name' has the wrong structure
2017-04-27T08:58:04.200921Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_global_by_event_name' has the wrong structure
2017-04-27T08:58:04.201002Z 0 [ERROR] Incorrect definition of table performance_schema.users: expected column 'USER' at position 0 to have type char(32), found type char(16).
2017-04-27T08:58:04.201083Z 0 [ERROR] Incorrect definition of table performance_schema.accounts: expected column 'USER' at position 0 to have type char(32), found type char(16).
2017-04-27T08:58:04.201576Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_global_by_event_name' has the wrong structure
2017-04-27T08:58:04.201621Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_account_by_event_name' has the wrong structure
2017-04-27T08:58:04.201676Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_host_by_event_name' has the wrong structure
2017-04-27T08:58:04.201715Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_thread_by_event_name' has the wrong structure
2017-04-27T08:58:04.201755Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_user_by_event_name' has the wrong structure
2017-04-27T08:58:04.201791Z 0 [ERROR] Native table 'performance_schema'.'table_handles' has the wrong structure
2017-04-27T08:58:04.201827Z 0 [ERROR] Native table 'performance_schema'.'metadata_locks' has the wrong structure
2017-04-27T08:58:04.201864Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_configuration' has the wrong structure
2017-04-27T08:58:04.201898Z 0 [ERROR] Native table 'performance_schema'.'replication_group_members' has the wrong structure
2017-04-27T08:58:04.201934Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
2017-04-27T08:58:04.201969Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
2017-04-27T08:58:04.202003Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
2017-04-27T08:58:04.202041Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
2017-04-27T08:58:04.202077Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
2017-04-27T08:58:04.202118Z 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
2017-04-27T08:58:04.202153Z 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
2017-04-27T08:58:04.202192Z 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
2017-04-27T08:58:04.202227Z 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
2017-04-27T08:58:04.202260Z 0 [ERROR] Native table 'performance_schema'.'status_by_host' has the wrong structure
2017-04-27T08:58:04.202294Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
2017-04-27T08:58:04.202332Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
2017-04-27T08:58:04.202366Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
2017-04-27T08:58:04.202400Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
2017-04-27T08:58:04.202434Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
2017-04-27T08:58:04.202472Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2017-04-27T08:58:04.202520Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
2017-04-27T08:58:04.202654Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
2017-04-27T08:58:04.202686Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
2017-04-27T08:58:04.202896Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2017-04-27T08:58:04.202987Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-04-27T08:58:04.202999Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-04-27T08:58:04.219168Z 0 [Note] End of list of non-natively partitioned tables
2017-04-27T08:58:04.219316Z 0 [Note] mysqld: ready for connections.




解决方法如下:

1、查看数据状态

 root@storm-master-01:/root#ps -ef|grep mysql
root      68391  67024  0 16:01 pts/4    00:00:00 mysql -h192.168.22.22 -uloan -px xxxx -P3308
mysql     72949  68459 16 17:55 pts/6    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/backup/backup-my.cnf
root      72980  68459  0 17:55 pts/6    00:00:00 grep --color=auto mysql

2、登录数据库如下

root@storm-master-01:/data/backup#mysql_upgrade -h192.168.22.22 -u root -p  --force -P3308 sock=/data/backup/mysql3308.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
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.ha_health_check                              OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation    
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

3、关闭数据库

4、启动数据库
root@storm-master-01:/data/backup#/usr/local/mysql/bin/mysqld --defaults-file=/data/backup/backup-my.cnf &


<think>嗯,用户遇到了MySQL升级到5.7.8-rc后出现的错误:“Table 'performance_schema.session_variables' doesn't exist”。首先,我需要确认这个问题的可能原因。根据引用[1]和[2],用户可能在升级过程中没有正确更新performance_schema数据库的结构。 接下来,我应该检查MySQL的升级步骤,特别是与performance_schema相关的部分。通常,升级后需要运行mysql_upgrade工具来更新系统表。如果用户没有执行这一步,可能会导致表结构不兼容,从而出现session_variables表不存在的错误。 另外,可能需要确认MySQL的版本是否确实升级成功,有时候升级过程中可能出现中断,导致部分文件未正确更新。用户需要验证版本号,并检查错误日志是否有其他线索。 解决方案方面,首先建议用户运行mysql_upgrade命令,这会自动修复系统表。如果问题仍然存在,可能需要手动重新初始化数据目录,或者检查权限问题,确保MySQL服务有权限访问performance_schema数据库。 此外,用户的问题可能与某些配置参数有关,比如是否禁用了performance_schema。需要检查my.cnf或my.ini文件中的配置,确保performance_schema=ON。如果被禁用,启用后重启服务应该会生成所需的表。 最后,考虑到用户可能对命令行操作不太熟悉,需要提供详细的步骤说明,并提醒备份数据的重要性,避免操作失误导致数据丢失。</think>### 解决MySQL performance_schema.session_variables表不存在的错误 #### 问题分析 该错误通常发生在MySQL版本升级后,尤其是从旧版本升级到5.7.x时。主要原因是`performance_schema`数据库的表结构未正确更新或初始化,导致`session_variables`表缺失[^1][^2]。 --- #### 逐步解决方案 1. **运行mysql_upgrade工具** - **作用**:该工具会自动检查并修复系统表的兼容性问题。 - **操作步骤**: ```bash # 停止MySQL服务 systemctl stop mysql # 运行升级命令(需使用root权限) mysql_upgrade -u root -p --force # 重新启动MySQL服务 systemctl start mysql ``` - **注意**:`--force`参数强制修复所有表,适用于升级后未正确初始化的场景。 2. **手动初始化数据目录** - **适用场景**:若`mysql_upgrade`无效,可能是数据目录损坏。 - **操作步骤**: ```bash # 备份原有数据(重要!) cp -r /var/lib/mysql /var/lib/mysql_backup # 删除旧数据目录(路径以实际配置为准) rm -rf /var/lib/mysql # 重新初始化MySQL mysqld --initialize --user=mysql # 启动服务 systemctl start mysql ``` 3. **验证performance_schema状态** - **检查是否启用**: ```sql SHOW VARIABLES LIKE 'performance_schema'; ``` - 若结果为`OFF`,需修改配置文件: ```ini # 编辑my.cnf或my.ini [mysqld] performance_schema=ON ``` - 重启服务生效: ```bash systemctl restart mysql ``` --- #### 关键注意事项 - **权限问题**:确保MySQL进程对`/var/lib/mysql`目录有读写权限。 - **版本兼容性**:确认升级后的版本是否支持`session_variables`表(5.7.x及以上支持)。 - **日志排查**:查看MySQL错误日志(默认路径`/var/log/mysql/error.log`)定位具体错误。 ---
评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值