MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
在安装MySQL 5.6.30时,安装完成后,后台日志报如下警告信息:
2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Fetch of persistent statistics requested for table "hj_web"."wechat_res" 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. 2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2016-05-27 14:03:52 28585 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
问题产生原因:具体原因目前不详,网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM,但是这几张表在建表时的引擎为INNODB
但是能确定的,这几张表确实是在mysql5.6中新入的
innodb_index_stats, innodb_tables_stats, slave_master_info, slave_relay_log_info, slave_worker_info
解决方法:
1、登录数据库,进入mysql库,执行如下SQL删除5张表
记住,一定要是drop table if exists
mysql> use mysql; mysql> drop table if exists innodb_index_stats; mysql> drop table if exists innodb_table_stats; mysql> drop table if exists slave_master_info; mysql> drop table if exists slave_relay_log_info; mysql> drop table if exists slave_worker_info;
执行完后,可以用show tables查看一下,看表的数据是否已经比删除之前减少了,如果减少了,说明你成功了!
2、上一步操作完成后,停止数据库,并进入到数据库数据文件所在目录,删除上面5个表所对应的idb文件,如下所示:
# /etc/init.d/mysqld stop # cd /data/mysql/data/mysql/ # ls -l *.ibd -rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_index_stats.ibd -rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_table_stats.ibd -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_master_info.ibd -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_relay_log_info.ibd -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_worker_info.ibd # /bin/rm -rf *.ibd
3、重新启动数据库,进入到mysql库,重建上面被删除的表结构:
数据库的建表脚本在mysql软件的安装目录的share目录下或者mysql的安装包的script目录下,我的mysql软件的安装路径为/data/mysql/
# /etc/init.d/mysqld start mysql> use mysql; mysql> source /data/mysql/share/mysql_system_tables.sql mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | 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 | +---------------------------+ 28 rows in set (0.00 sec)
mysql> desc innodb_table_stats; +--------------------------+---------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+---------------------+------+-----+-------------------+-----------------------------+ | database_name | varchar(64) | NO | PRI | NULL | | | table_name | varchar(64) | NO | PRI | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | n_rows | bigint(20) unsigned | NO | | NULL | | | clustered_index_size | bigint(20) unsigned | NO | | NULL | | | sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | | +--------------------------+---------------------+------+-----+-------------------+-----------------------------+ 6 rows in set (0.00 sec)
mysql> desc slave_master_info; +------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------------+------+-----+---------+-------+ | Number_of_lines | int(10) unsigned | NO | | NULL | | | Master_log_name | text | NO | | NULL | | | Master_log_pos | bigint(20) unsigned | NO | | NULL | | | Host | char(64) | NO | PRI | | | | User_name | text | YES | | NULL | | | User_password | text | YES | | NULL | | | Port | int(10) unsigned | NO | PRI | NULL | | | Connect_retry | int(10) unsigned | NO | | NULL | | | Enabled_ssl | tinyint(1) | NO | | NULL | | | Ssl_ca | text | YES | | NULL | | | Ssl_capath | text | YES | | NULL | | | Ssl_cert | text | YES | | NULL | | | Ssl_cipher | text | YES | | NULL | | | Ssl_key | text | YES | | NULL | | | Ssl_verify_server_cert | tinyint(1) | NO | | NULL | | | Heartbeat | float | NO | | NULL | | | Bind | text | YES | | NULL | | | Ignored_server_ids | text | YES | | NULL | | | Uuid | text | YES | | NULL | | | Retry_count | bigint(20) unsigned | NO | | NULL | | | Ssl_crl | text | YES | | NULL | | | Ssl_crlpath | text | YES | | NULL | | | Enabled_auto_position | tinyint(1) | NO | | NULL | | +------------------------+---------------------+------+-----+---------+-------+ 23 rows in set (0.00 sec)
说明表都正常了,再次查看mysql报错日志,就会发现没有了关于这5个表的报错日志。