你的位置:首页 > 数据库

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


昨天,有个朋友对公司内部使用的一个MySQL实例开启binlog,但是在启动的过程中失败了(他也没提,为何会失败),在启动失败后,他删除了ibdata1和ib_logfile,后来,能正常启动了,但所有的表通过show tables能看到,但是select的过程中却报“Table doesn't exist”。

于是,建议他试试可传输表空间。

同时,自己也测试了下,确实可行。

测试版本 MySQL 5.6.32 社区版

 

首先,创建测试数据

在这里创建两张表。之所以创建两张相同的表是为了方便后续的测试。

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.cnf ib_logfile0 localhost.localdomain.err mysql_upgrade_info testibdata1  ib_logfile1 mysql           performance_schema[root@localhost data]# rm -rf ibdata1 [root@localhost data]# rm -rf ib_logfile*[root@localhost data]# lsauto.cnf localhost.localdomain.err mysql mysql_upgrade_info performance_schema test

 

重新启动数据库

# /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: 3310 MySQL 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 -P3310

mysql> 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 mysql 8594 Aug 18 11:06 t1.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd-rw-rw---- 1 mysql mysql 8594 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 mysql 8594 Aug 18 11:06 t1.frm-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd-rw-rw---- 1 mysql mysql 8594 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.frm t2.frm

 

首先对t1表进行测试

在这里,测试如下两种情况

1. 新的t1.frm+旧的t1.ibd

2. 旧的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.ibd

mysql> 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.ibd

2. 旧的t2.frm+旧的t2.ibd

 

首先,只是导入t2表的数据文件

[root@localhost test]# cp /backup/t2.ibd .[root@localhost test]# lltotal 216-rw-rw---- 1 mysql mysql 8594 Aug 18 17:55 t1.frm-rw-r----- 1 mysql mysql 98304 Aug 18 18:00 t1.ibd-rw-rw---- 1 mysql mysql 8556 Aug 18 17:52 t2.frm-rw-r----- 1 root root 98304 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.ibd

mysql> 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. 使用可传输表空间,建议新建表的表结构和原来的表结构完全一致。

 

事实上,在数据库正常关闭下删除ibdata1,会导致mysql库中的以下几张表无法访问

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> 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

2. http://dba.stackexchange.com/questions/48166/cannot-open-table-mysql-innodb-index-stats