mysql 5.6 ibdata1,5.6中删除ibdata1之后的报错

MICDEL

于 2021-03-17 01:13:11 发布

阅读量157

点赞数

文章标签： mysql 5.6 ibdata1

今天做测试时，查看错误日志发现有报错

2014-02-08 09:55:33 16545 [Warning] InnoDB: Cannot open table bit/inside_log 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.

2014-02-08 09:55:33 16545 [ERROR] Failed to open table bit/inside_parameters#P#p201311.

2014-02-08 09:55:33 16545 [Warning] InnoDB: Cannot open table bit/inside_parameters#P#p201311 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.

2014-02-08 09:55:33 b6cc0b70 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

原因是：innodb在.frm中存放了表的信息，但是同时也在数据字典中存放有表信息

此时可能是直接操作了数据文件而数据字典没来得及更新服务器便挂掉了导致不一致

drop table inside_parameters;

更新数据字典，同时会自动删除数据文件

2014-02-08 10:05:56 16545 [Warning] InnoDB: Cannot open table bit/inside_parameters#P#p201311 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.

2014-02-08 10:06:05 b6cc0b70 InnoDB: Error: table `bit`.`inside_parameters` /* Partition `p201311` */ does not exist in the InnoDB internal

InnoDB: data dictionary though MySQL is trying to drop it.

InnoDB: Have you copied the .frm file of the table to the

InnoDB: MySQL database directory from another database?

InnoDB: You can look for further help from

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2014-02-08 10:06:05 b6cc0b70 InnoDB: Error: table `bit`.`inside_parameters` /* Partition `p201312` */ does not exist in the InnoDB internal

InnoDB: data dictionary though MySQL is trying to drop it.

InnoDB: Have you copied the .frm file of the table to the

InnoDB: MySQL database directory from another database?

InnoDB: You can look for further help from

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2014-02-08 10:06:05 b6cc0b70 InnoDB: Error: table `bit`.`inside_parameters` /* Partition `p201401` */ does not exist in the InnoDB internal

InnoDB: data dictionary though MySQL is trying to drop it.

InnoDB: Have you copied the .frm file of the table to the

InnoDB: MySQL database directory from another database?

InnoDB: You can look for further help from

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2014-02-08 10:06:05 b6cc0b70 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

此时一直提示innodb_table_stats表不存在

查阅官方文档之后发现是5.6的新特性

在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的时候会出现我之前的警告信息

解决办法，从新库中导出这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 -uroot -p mysql

OK！