现象：
show tables可以看到该表，desc查看有关表的表结构又提示表不存在；
以engine_cost表为例：
Error: Couldn't read status information for table engine_cost ()
mysqldump: Couldn't execute 'show create table `engine_cost`': Table 'mysql.engine_cost' doesn't exist (1146)
1、服务器上登录进MySql
[root@node1 ~]# mysql -p -S /var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 58731
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
mysql>use mysql;
mysql>show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql>desc engine_cost;
ERROR 1146 (42S02): Table 'mysql.engine_cost' doesn't exist
2、在服务器上直接查看mysql数据目录的mysql库
[root@node1 mysql]#pwd
/data/mysql/data/mysql
[root@node1 mysql]#ll engine_cost*
-rw-r-----. 1 mysql mysql 8780 Jul 17 2020 engine_cost.frm
-rw-r-----. 1 mysql mysql 98304 Jul 17 2020 engine_cost.ibd
[root@node1 mysql]#
解决办法：
1、删除engine_cost系统表
drop table mysql.engine_cost;
2、删除engine_cost.frm，engine_cost.ibd
rm -rf engine_cost.ibd (engine_cost.frm在drop table mysql. engine_cost;时会自动删除)
3、重新创建engine_cost系统表
[root@node1 mysql]#find / -name "mysql_system_tables.sql"
/data/mysql/share/mysql_system_tables.sql
根据mysql_system_tables.sql中的创建engine_cost表的sql语句,重新创建engine_cost.
CREATE TABLE IF NOT EXISTS engine_cost (
engine_name VARCHAR(64) NOT NULL,
device_type INTEGER NOT NULL,
cost_name VARCHAR(64) NOT NULL,
cost_value FLOAT DEFAULT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
comment VARCHAR(1024) DEFAULT NULL,
PRIMARY KEY (cost_name, engine_name, device_type)
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci STATS_PERSISTENT=0;
INSERT IGNORE INTO engine_cost(engine_name, device_type, cost_name) VALUES
("default", 0, "memory_block_read_cost"),
("default", 0, "io_block_read_cost");