MySql系统库，表不存在修复记录

现象：

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");