在删除ibdata1和ib_logfile的情况下恢复MySQL数据库

昨天，有个朋友对公司内部使用的一个MySQL实例开启binlog，但是在启动的过程中失败了（他也没提，为何会失败），在启动失败后，他删除了ibdata1和ib_logfile，后来，能正常启动了，但所有的表通过show tables能看到，但是select的过程中却报“Table doesn"t exist”。于是，建议他试试可传输表空间。同时，自己也测试了下，确实可行。测试版本 MySQL 5.6.32 社区版恢复的基本步骤1. 将原来的数据文件COPY到其它目录下。2. 创建同名表，表结构必须保持一致。3. 导出表空间mysql> ALTER TABLE t DISCARD TABLESPACE;4. 将原来的数据文件COPY回来5. 导入表空间mysql> ALTER TABLE t IMPORT TABLESPACE下面的演示会略为复杂，主要是还原整个场景，并针对上述步骤中的2,4做了一个测试。首先，创建测试数据在这里创建两张表。之所以创建两张相同的表是为了方便后续的测试。mysql> create table t1（id int,hiredate datetime）;Query OK, 0 rows affected （0.14 sec）mysql> create table t2（id int,hiredate datetime）;Query OK, 0 rows affected （0.01 sec）mysql> insert into t1 values（1,now（））;Query OK, 1 row affected （0.06 sec）mysql> insert into t1 values（2,now（））;Query OK, 1 row affected （0.00 sec）mysql> insert into t2 values（1,now（））;Query OK, 1 row affected （0.00 sec）mysql> insert into t2 values（2,now（））;Query OK, 1 row affected （0.00 sec）关闭数据库# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqladmin shutdown -uroot -p123456 -h127.0.0.1 -P3310删除ibdata1，ib_logfile0和ib_logfile1[root@localhost data]# cd /data/[root@localhost data]# lsauto.cnfib_logfile0localhost.localdomain.errmysql_upgrade_infotestibdata1 ib_logfile1mysqlperformance_schema[root@localhost data]# rm -rf ibdata1 [root@localhost data]# rm -rf ib_logfile*[root@localhost data]# lsauto.cnflocalhost.localdomain.errmysqlmysql_upgrade_infoperformance_schematest 重新启动数据库# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/my.cnf &并没有报错启动过程中的日志信息如下：# 2016-08-18 11:13:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option （see documentation for more details）.2016-08-18 11:13:18 0 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld （mysqld 5.6.32） starting as process 3948 ...2016-08-18 11:13:18 3948 [Note] Plugin "FEDERATED" is disabled.2016-08-18 11:13:18 3948 [Note] InnoDB: Using atomics to ref count buffer pool pages2016-08-18 11:13:18 3948 [Note] InnoDB: The InnoDB memory heap is disabled2016-08-18 11:13:18 3948 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2016-08-18 11:13:18 3948 [Note] InnoDB: Memory barrier is not used2016-08-18 11:13:18 3948 [Note] InnoDB: Compressed tables use zlib 1.2.32016-08-18 11:13:18 3948 [Note] InnoDB: Using Linux native AIO2016-08-18 11:13:18 3948 [Note] InnoDB: Using CPU crc32 instructions2016-08-18 11:13:18 3948 [Note] InnoDB: Initializing buffer pool, size = 128.0M2016-08-18 11:13:19 3948 [Note] InnoDB: Completed initialization of buffer pool2016-08-18 11:13:19 3948 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created！2016-08-18 11:13:19 3948 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB2016-08-18 11:13:19 3948 [Note] InnoDB: Database physically writes the file full: wait...2016-08-18 11:13:19 3948 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB2016-08-18 11:13:21 3948 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB2016-08-18 11:13:22 3948 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02016-08-18 11:13:22 3948 [Warning] InnoDB: New log files created, LSN=457812016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer not found: creating new2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer created2016-08-18 11:13:22 3948 [Note] InnoDB: 128 rollback segment（s） are active.2016-08-18 11:13:22 3948 [Warning] InnoDB: Creating foreign key constraint system tables.2016-08-18 11:13:22 3948 [Note] InnoDB: Foreign key constraint system tables created2016-08-18 11:13:22 3948 [Note] InnoDB: Creating tablespace and datafile system tables.2016-08-18 11:13:22 3948 [Note] InnoDB: Tablespace and datafile system tables created.2016-08-18 11:13:22 3948 [Note] InnoDB: Waiting for purge to start2016-08-18 11:13:22 3948 [Note] InnoDB: 5.6.32 started; log sequence number 02016-08-18 11:13:22 3948 [Note] Server hostname （bind-address）: "*"; port: 33102016-08-18 11:13:23 3948 [Note] IPv6 is available.2016-08-18 11:13:23 3948 [Note] - "::" resolves to "::";2016-08-18 11:13:23 3948 [Note] Server socket created on IP: "::".2016-08-18 11:13:23 3948 [Note] Event Scheduler: Loaded 0 events2016-08-18 11:13:23 3948 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld: ready for connections.Version: "5.6.32"socket: "/data/mysql.sock"port: 3310MySQL Community Server （GPL）可见，在启动的过程中，MySQL会重建ibdata1和redo log。登录mysql客户端，看之前创建的t1，t2是否能访问# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysql -h127.0.0.1 -p123456 -uroot -P3310mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| t1 || t2 |+----------------+2 rows in set （0.00 sec）mysql> select * from t1;ERROR 1146 （42S02）: Table "test.t1" doesn"t exist 通过show tables能查看有t1表存在，但表中的具体内容则无法查看同时，错误日志中输出以下信息2016-08-18 11:15:13 3948 [Warning] InnoDB: Cannot open table test/t1 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.将数据目录下的test目录中的t1，t2表的数据文件和表定义文件COPY到其它地方[root@localhost test]# cd /data/test/[root@localhost test]# lltotal 216-rw-rw---- 1 mysql mysql8594 Aug 18 11:06 t1.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd-rw-rw---- 1 mysql mysql8594 Aug 18 11:06 t2.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd[root@localhost test]# mv * /backup/[root@localhost test]# ls[root@localhost test]# ll /backup/total 216-rw-rw---- 1 mysql mysql8594 Aug 18 11:06 t1.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd-rw-rw---- 1 mysql mysql8594 Aug 18 11:06 t2.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd 登录客户端，创建t1和t2表，注意表结构和之前的必须保持一致细心的童鞋会发现，下面的创表语句和刚开始的创表语句并不一样，列名不一致，这个其实是为了后续的测试mysql> show tables;Empty set （0.00 sec）mysql> create table t1（id_1 int,hiredate_1 datetime）;ERROR 1146 （42S02）: Table "test.t1" doesn"t exist明明已经手动移除了，为什么创建表的时候还报这个错误呢？接下来，可先执行个drop table操作mysql> drop table t1;ERROR 1051 （42S02）: Unknown table "test.t1"mysql> create table t1（id_1 int,hiredate_1 datetime）;Query OK, 0 rows affected （0.07 sec）对于t2表，我们定义一个不同的表结构，看是否可行？mysql> drop table t2;ERROR 1051 （42S02）: Unknown table "test.t2"mysql> create table t2（id_1 int）;Query OK, 0 rows affected （0.01 sec）导出表空间mysql> ALTER TABLE t1 DISCARD TABLESPACE;Query OK, 0 rows affected （0.00 sec）mysql> ALTER TABLE t2 DISCARD TABLESPACE;Query OK, 0 rows affected （0.00 sec）这个时候，数据目录下的test目录下，数据文件没有了，只剩下了表结构文件[root@localhost test]# lst1.frmt2.frm导入表空间首先对t1表进行测试在这里，测试如下两种情况1. 新的t1.frm+旧的t1.ibd2. 旧的t1.frm+旧的t1.ibd第一种情况只是将t1表的数据文件COPY回来[root@localhost test]# cp /backup/t1.ibd .[root@localhost test]# chown mysql.mysql t1.ibd 导入t1表的表空间mysql> ALTER TABLE t1 IMPORT TABLESPACE;Query OK, 0 rows affected, 1 warning （0.21 sec）mysql> show warnings;+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1810 | InnoDB: IO Read error: （2, No such file or directory） Error opening "./test/t2.cfg", will attempt to import without schema verification |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+1 row in set （0.00 sec）查看t1表是否能访问mysql> select * from t1;+------+---------------------+| id_1 | hiredate_1|+------+---------------------+|1 | 2016-08-18 17:45:02 ||2 | 2016-08-18 17:45:02 |+------+---------------------+2 rows in set （0.00 sec）mysql> flush table t1;Query OK, 0 rows affected （0.00 sec）mysql> select * from t1;+------+---------------------+| id_1 | hiredate_1|+------+---------------------+|1 | 2016-08-18 17:45:02 ||2 | 2016-08-18 17:45:02 |+------+---------------------+2 rows in set （0.00 sec）喔，确实能访问，注意观察，表的列名与新的创表语句保持一致。在这里之所以使用flush table操作，是为了刷新内存中的表定义。下面看看t1的第二种情况，旧的t1.frm+旧的t1.ibdmysql> ALTER TABLE t1 DISCARD TABLESPACE;Query OK, 0 rows affected （0.00 sec）[root@localhost test]# cp /backup/t1.frm .cp: overwrite `./t1.frm"？ y[root@localhost test]# cp /backup/t1.ibd .[root@localhost test]# chown mysql.mysql t1.frm [root@localhost test]# chown mysql.mysql t1.ibd mysql> ALTER TABLE t1 import TABLESPACE;Query OK, 0 rows affected, 1 warning （0.04 sec）mysql> select * from t1;+------+---------------------+| id_1 | hiredate_1|+------+---------------------+|1 | 2016-08-18 17:45:02 ||2 | 2016-08-18 17:45:02 |+------+---------------------+2 rows in set （0.00 sec）mysql> flush table t1;Query OK, 0 rows affected （0.00 sec）mysql> select * from t1;+------+---------------------+| id | hiredate|+------+---------------------+|1 | 2016-08-18 17:45:02 ||2 | 2016-08-18 17:45:02 |+------+---------------------+2 rows in set （0.00 sec）第一次查询的时候还是新的列名，对表进行flush后，就恢复到原来的列名了。下面来看看t2表的导入情况因为t2表的表结构发生了改变，在这里，也是测试如下两种情况1. 新的t2.frm+旧的t2.ibd2. 旧的t2.frm+旧的t2.ibd首先，只是导入t2表的数据文件[root@localhost test]# cp /backup/t2.ibd .[root@localhost test]# lltotal 216-rw-rw---- 1 mysql mysql8594 Aug 18 17:55 t1.frm-rw-r----- 1 mysql mysql 98304 Aug 18 18:00 t1.ibd-rw-rw---- 1 mysql mysql8556 Aug 18 17:52 t2.frm-rw-r----- 1 rootroot98304 Aug 18 18:10 t2.ibd[root@localhost test]# chown mysql.mysql t2.ibd 导入t2表的表空间进行测试mysql> ALTER TABLE t2 import TABLESPACE;Query OK, 0 rows affected, 1 warning （0.03 sec）mysql> select * from t2;+------+| id_1 |+------+|1 ||2 |+------+2 rows in set （0.00 sec）mysql> flush table t2;Query OK, 0 rows affected （0.00 sec）mysql> select * from t2;+------+| id_1 |+------+|1 ||2 |+------+2 rows in set （0.00 sec）从结果可以看出，只能读出第一列。下面测试第二种情况，旧的t2.frm和t2.ibdmysql> ALTER TABLE t2 DISCARD TABLESPACE;Query OK, 0 rows affected （0.06 sec）[root@localhost test]# rm -rf t2.frm [root@localhost test]# cp /backup/t2.frm .[root@localhost test]# cp /backup/t2.ibd .[root@localhost test]# chown mysql.mysql t2.frm [root@localhost test]# chown mysql.mysql t2.ibd mysql> ALTER TABLE t2 import TABLESPACE;Query OK, 0 rows affected, 1 warning （0.09 sec）mysql> select * from t2;+------+| id_1 |+------+|1 ||2 |+------+2 rows in set （0.00 sec）mysql> flush table t2;Query OK, 0 rows affected （0.00 sec）mysql> select * from t2;ERROR 1146 （42S02）: Table "test.t2" doesn"t exist在重新刷新后，就出现错误了，个人感觉，这个和系统表空间中的数据字典信息有关。实际上，后续还测试了一下，如果将hiredate的列定义为varchar，则无论是使用之前的frm文件还是之后的，在导入表空间，进行查询时，数据库直接挂掉。mysql> create table t1（id int,hiredate varchar（10））;Query OK, 0 rows affected （0.05 sec）mysql> ALTER TABLE t1 DISCARD TABLESPACE;Query OK, 0 rows affected （0.00 sec）mysql> ALTER TABLE t1 import TABLESPACE;Query OK, 0 rows affected, 1 warning （0.03 sec）mysql> select * from t1;ERROR 2013 （HY000）: Lost connection to MySQL server during query 结论经过上面的一系列测试，可以看到1. 使用可传输表空间，可以解决在删除ibdata1和ib_logfile的情况下恢复MySQL数据库，当然，本文测试的前提是数据库正常关闭下删除的ibdata1和ib_logfile。2. 使用可传输表空间，建议新建表的表结构和原来的表结构完全一致，同时，在导入表空间前，只需COPY回原来的数据文件，即ibd。事实上，在数据库正常关闭下删除ibdata1，会导致mysql库中的以下几张表无法访问mysql> select table_name from information_schema.tableswhere 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> select * from mysql.innodb_index_stats;ERROR 1146 （42S02）: Table "mysql.innodb_index_stats" doesn"t existmysql> select * from mysql.innodb_table_stats;ERROR 1146 （42S02）: Table "mysql.innodb_table_stats" doesn"t existmysql> select * from mysql.slave_master_info;ERROR 1146 （42S02）: Table "mysql.slave_master_info" doesn"t existmysql> select * from mysql.slave_relay_log_info;ERROR 1146 （42S02）: Table "mysql.slave_relay_log_info" doesn"t existmysql> select * from mysql.slave_worker_info;ERROR 1146 （42S02）: Table "mysql.slave_worker_info" doesn"t exist 同时，错误日志中报如下信息 2016-08-19 12:10:18 3041 [Warning] InnoDB: Cannot open table mysql/innodb_index_stats 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-08-19 12:10:26 3041 [Warning] InnoDB: Cannot open table mysql/innodb_table_stats 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-08-19 12:10:34 3041 [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-08-19 12:10:40 3041 [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-08-19 12:10:46 3041 [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. 要解决这个问题，只能重建这些表。参考1. http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html本文永久更新链接地址