Chinaunix首页 | 论坛 | 博客
  • 博客访问: 195757
  • 博文数量: 87
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 840
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-18 21:12
文章分类

全部博文(87)

文章存档

2014年(86)

我的朋友

分类: Mysql/postgreSQL

2014-05-22 10:12:12


InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

MySQL 5.6的ibdata1表空间包含了5个InnoDB基础表,如下:
mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';

+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

在MySQL 5.6之前,如果关闭MySQL后删除ibdata1,再重新启动MySQL的时候ibdata1会被重新创建. 但从MySQL 5.6开始,这5个表不会被重建.即使删除了ibdata1,下面的10个文件仍然保留在/var/lib/mysql/mysql中(假如datadir = /var/lib/mysql/): innodb_index_stats.frm innodb_index_stats.ibd innodb_table_stats.frm innodb_table_stats.ibd slave_master_info.frm slave_master_info.ibd slave_relay_log_info.frm slave_relay_log_info.ibd slave_worker_info.frm slave_worker_info.ibd

在安装MySQL 5.6,使用mysql_install_db做初始化的时候需要指定–defaults-file选项,按照自定义的my.cnf里面的参数去初始化,而不能采用和5.5一样的方法删除之前的ibdata1文件后再重新生成新,因为MySQL 5.6在mysql系统库下引入上述的5个innodb表。否则启动mysqld的时候会出现如下类似的Warnings:




2013-09-15 23:28:04 14448 [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.
2013-09-15 23:28:04 14448 [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.
2013-09-15 23:28:04 14448 [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.
2013-09-15 23:30:02 15314 [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.
2013-09-15 23:30:02 15314 [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.
2013-09-15 23:30:02 15314 [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.

MySQL运行中有写操作的时候还会出现大量的Errors.

...
2013-09-29 14:52:25 7f9b0ab03700 InnoDB: Error: Fetch of persistent statistics requested for table "yzs"."tb_qcard" 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.
2013-09-29 14:54:58 7f9d6dcdf700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-09-29 14:54:58 7f9d6dcdf700 InnoDB: Error: Fetch of persistent statistics requested for table "yzs"."tb_topicrecord" 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.
2013-09-29 14:56:14 7f9d6848f700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-09-29 14:56:14 7f9d6848f700 InnoDB: Error: Fetch of persistent statistics requested for table "yzs"."groupproductstock" 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.
2013-09-29 14:56:14 7f9d6848f700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-09-29 14:56:14 7f9d6848f700 InnoDB: Error: Fetch of persistent statistics requested for table "yzs"."groupstockcount" 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服务器,使用相同my.cnf配置作为mysql_install_db做初始化时–defaults-file的选项.
  • 初始化后启动MySQL,然后使用mysqldump导出这5个表:
    mysqldump -uroot -p   mysql  innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info >tablelist.sql
    
  • 将 mysql_innodb_tables.sql拷贝到出现坏表错误的那台数据库机器,并将其导入到系统库mysql库中.
    # mysql -uroot -p mysql < mysql_innodb_tables.sql
    
  • 运行FLUSH TABLES(可选)
或者 将这五个表的表结构拉出来 在有问题的机器上重建即可。(删除之前请将残余文件删除(mysql 数据目录下))
(升级也会导致相同的问题 及处理的办法)
阅读(1445) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~