mysql innodb_table_stats表不存在的解决方法
mysql 版本 5.6.14
公司有几台mysql服务器的错误日志显示,有几个系统表不存在.
innodb_table_stats
 innodb_index_stats
 slave_master_info
 slave_relay_log_info
 slave_worker_info
这是因为数据库初始化的时候,dba可能删除过ibdata1文件
虽然重启之后,数据库会自动创建一个ibdata1文件,但是上述系统表也是innodb引擎,所以不能访问了.
这虽然不会影响业务,但是使用innobackupex备份的时候,会写入错误日志.
最后错误日志里,都是这种信息.影响日常检查。
 解决的方法.
1.删除上述系统表
drop table mysql.innodb_index_stats;
 drop table mysql.innodb_table_stats;
 drop table mysql.slave_master_info;
 drop table mysql.slave_relay_log_info;
 drop table mysql.slave_worker_info;
2.删除相关的.frm .ibd文件
rm -rf innodb_index_stats*
 rm -rf innodb_table_stats*
 rm -rf slave_master_info*
 rm -rf slave_relay_log_info*
 rm -rf slave_worker_info*
3.重新创建上述系统表
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;
 create table `innodb_table_stats` (
  `database_name` varchar(64) collate utf8_bin not null,
  `table_name` varchar(64) collate utf8_bin not null,
  `last_update` timestamp not null default current_timestamp on update current_timestamp,
  `n_rows` bigint(20) unsigned not null,
  `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=utf8 collate=utf8_bin stats_persistent=0;
 create table `slave_master_info` (
  `number_of_lines` int(10) unsigned not null comment 'number of lines in the file.',
  `master_log_name` text character set utf8 collate utf8_bin not null comment 'the name of the master binary log currently being read from the master.',
  `master_log_pos` bigint(20) unsigned not null comment 'the master log position of the last read event.',
  `host` char(64) character set utf8 collate utf8_bin not null default '' comment 'the host name of the master.',
  `user_name` text character set utf8 collate utf8_bin comment 'the user name used to connect to the master.',
  `user_password` text character set utf8 collate utf8_bin comment 'the password used to connect to the master.',
  `port` int(10) unsigned not null comment 'the network port used to connect to the master.',
  `connect_retry` int(10) unsigned not null comment 'the period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `enabled_ssl` tinyint(1) not null comment 'indicates whether the server supports ssl connections.',
  `ssl_ca` text character set utf8 collate utf8_bin comment 'the file used for the certificate authority (ca) certificate.',
  `ssl_capath` text character set utf8 collate utf8_bin comment 'the path to the certificate authority (ca) certificates.',
  `ssl_cert` text character set utf8 collate utf8_bin comment 'the name of the ssl certificate file.',
  `ssl_cipher` text character set utf8 collate utf8_bin comment 'the name of the cipher in use for the ssl connection.',
  `ssl_key` text character set utf8 collate utf8_bin comment 'the name of the ssl key file.',
  `ssl_verify_server_cert` tinyint(1) not null comment 'whether to verify the server certificate.',
  `heartbeat` float not null,
  `bind` text character set utf8 collate utf8_bin comment 'displays which interface is employed when connecting to the mysql server',
  `ignored_server_ids` text character set utf8 collate utf8_bin comment 'the number of server ids to be ignored, followed by the actual server ids',
  `uuid` text character set utf8 collate utf8_bin comment 'the master server uuid.',
  `retry_count` bigint(20) unsigned not null comment 'number of reconnect attempts, to the master, before giving up.',
  `ssl_crl` text character set utf8 collate utf8_bin comment 'the file used for the certificate revocation list (crl)',
  `ssl_crlpath` text character set utf8 collate utf8_bin comment 'the path used for certificate revocation list (crl) files',
  `enabled_auto_position` tinyint(1) not null comment 'indicates whether gtids will be used to retrieve events from the master.',
  primary key (`host`,`port`)
 ) engine=innodb default charset=utf8 stats_persistent=0 comment='master information';