背景 对MySQL错误日志进行排查,发现几乎所有的服务器都会报类似错误
查看mysql错误日志
发现如下报错
[ERROR] InnoDB: Table`mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.658189Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.658189Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.668231Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.668231Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.668231Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:09:23.678192Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:10:11.298340Z 0 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
2018-12-18T07:10:11.298340Z 0 [Warning]InnoDB: Recalculation of persistent statistics requested for table `dzyd`.`#sql-528_1e0b85#p#gps_p_20181218`/* Partition `gps_p_20181218` */ but the required persistent statistics storageis not present or is corrupted. Using transient stats instead.
2018-12-18T07:10:26.158389Z 1969029 [ERROR]InnoDB: Table `mysql`.`innodb_table_stats` not found.
日志解析 :1,ql库中innodb_table_stats,innodb_index_stats 不存在 (可不仅仅是这两个表,可能还有其他表)
2,创建gps_p_20181218表时因为innodb_table_stats,innodb_index_stats这两个表的状态 出现意外而无法进行
出现上述情况原因
1数据库打开这几张表的默认引擎为MyISAM,但是这几张表在建表时的引擎为INNODB
2 数据库迁徙 或初始化时 删除过ibdata1(未知原因造成数据表丢失或损坏)
解决方法 删表重建(还可以全部重建,谨慎起见还是缺啥建啥吧)
1手动删建
mysql> use mysql;
CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATEutf8_bin NOT NULL,
`table_name` varchar(64) COLLATEutf8_bin NOT NULL,
`index_name` varchar(64) COLLATEutf8_bin NOT NULL,
`last_update` timestamp NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATEutf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOTNULL,
`sample_size` bigint(20) unsignedDEFAULT NULL,
`stat_description` varchar(1024)COLLATE utf8_bin NOT NULL,
PRIMARY KEY(`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATEutf8_bin NOT NULL,
`table_name` varchar(64) COLLATEutf8_bin NOT NULL,
`last_update` timestamp NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOTNULL,
`clustered_index_size` bigint(20)unsigned NOT NULL,
`sum_of_other_index_sizes`bigint(20) unsigned NOT NULL,
PRIMARY KEY(`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8COLLATE=utf8_bin STATS_PERSISTENT=0;
2登录数据库,进入mysql库,执行如下SQL删除表记住,一定要是drop table if exists
执行完后,可以用show tables查看一下,看表的数据是否已经比删除之前减少了
2、上一步操作完成后,停止数据库(5.7不用停止数据库),并进入到数据库数据文件所在目录,删除上面几个表所对应的idb文件
3、重新启动数据库,进入到mysql库,重建上面被删除的表结构:(经测试5.7不用重启立即生效)数据库的建表脚本在mysql软件的安装目录的share目录下或者mysql的安装包的script目录下(找一下看)