nnoDB: 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/ ... roubleshooting.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/ ... roubleshooting.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/ ... roubleshooting.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/ ... roubleshooting.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/ ... roubleshooting.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/ ... roubleshooting.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個表:

#!/bin/bash

TABLELIST="innodb_index_stats"

TABLELIST="${TABLELIST} innodb_table_stats"

TABLELIST="${TABLELIST} slave_master_info"

TABLELIST="${TABLELIST} slave_relay_log_info"

TABLELIST="${TABLELIST} slave_worker_info"

mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

將 mysql_innodb_tables.sql拷貝到出現壞表錯誤的那臺數據庫機器,并將其導入到系統庫mysql庫中.

# mysql -uroot -p mysql < mysql_innodb_tables.sql

運行FLUSH TABLES(可選)

總結

以上是生活随笔為你收集整理的mysql 5.6.11 error 1059_mysql5.6.15问题如何解决的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。