innodb_index_stats系统表不存在

环境:
OS:Centos 7
DB:5.6.40

数据库报如下的错误

2021-09-24 14:40:00 7fda204c5700 InnoDB: Error: Tablespace for table "mysql"."innodb_table_stats" is missing.
2021-09-24 14:40:00 7fda204c5700 InnoDB: Error: Fetch of persistent statistics requested for table "zjs"."config_info" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2021-09-24 14:40:00 7fda203bd700 InnoDB: Error: Tablespace for table "mysql"."innodb_table_stats" is missing.
2021-09-24 14:40:00 7fda203bd700 InnoDB: Error: Fetch of persistent statistics requested for table "db_live"."live_room_detail" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

 

尝试创建表,提示表已经存在
mysql> CREATE TABLE `innodb_index_stats` (
-> `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
-> `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
-> `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
-> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
-> `stat_value` bigint(20) unsigned NOT NULL,
-> `sample_size` bigint(20) unsigned DEFAULT NULL,
-> `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
-> PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists

但是查找表提示不存在
mysql> select * from mysql.innodb_index_stats;
ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist

查找相应的ibd frm也不存在

解决办法:
1.因为是系统表,从正常的库上拷贝这几个表的ibd frm 文件到故障库相应的目录
cp innodb_index_stats.frm /opt/mysql3307/data/mysql/
cp innodb_index_stats.ibd /opt/mysql3307/data/mysql/
cp innodb_table_stats.frm /opt/mysql3307/data/mysql/
cp innodb_table_stats.ibd /opt/mysql3307/data/mysql/

 

2.重启动mysql
/opt/mysql3307/bin/mysqladmin -h localhost -uroot -pyeemiao3040 -P3307 -S /opt/mysql3307/data/mysql.sock shutdown
/opt/mysql3307/bin/mysqld_safe --defaults-file=/opt/mysql3307/conf/my.cnf --user=mysql &

 

3.脱离表空间
use mysql;
alter table innodb_index_stats discard tablespace;
alter table innodb_table_stats discard tablespace;

 

4.加入表空间
use mysql;
alter table innodb_index_stats import tablespace;
alter table innodb_table_stats import tablespace;

 

--The End --