How to recover a MySQL database with ibdata1 and ib_logfile removed

Yesterday, a friend of the company used a MySQL instance to open Binlog, but in the process of booting failed (he did not mention, why failed), after the boot failed, he deleted the Ibdata1 and Ib_logfile, later, can normally start, but all the table through show Tables can see, but the process of select is reported "Table doesn t exist".

So he suggested that he try to transfer the tablespace.

At the same time, I also tested the next, really feasible.

Beta version MySQL 5.6.32 Community Edition

Basic Steps for Recovery

1. Copy the original data file to a different directory. (frm structure file, IBD data file)

2. Create a table with the same name and the table structure must be consistent.

3. Export Table Space

mysql> SET foreign_key_checks = 0; # (setting does not check foreign keys)

mysql> ALTER TABLE t DISCARD tablespace;

mysql> SET foreign_key_checks = 1;

4. Copy the original data file back

5. Import Table Space

mysql> SET foreign_key_checks = 0; # (setting does not check foreign keys)

Mysql>alter TABLE t IMPORT tablespace;

mysql> SET foreign_key_checks = 1;

The following demonstration is slightly more complex, primarily to restore the entire scene, and to do a test for the 2,4 in the above steps.

First, create the test data

Create two tables here. Two identical tables are created to facilitate subsequent testing.

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)

Close the database

#/usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqladmin shutdown-uroot-p123456-h127.0.0.1-p3310

Delete Ibdata1,ib_logfile0 and Ib_logfile1

[Email protected] data]# cd/data/[[email protected] data]# lsauto.cnf  ib_logfile0  localhost.localdomain.err  mysql_upgrade_info  testibdata1   ib_logfile1  mysql                      performance_schema[[email protected] data] # RM-RF IBDATA1 [[email protected] data]# RM-RF ib_logfile*[[email protected] data]# lsauto.cnf  Localhost.localdomai N.err  mysql  mysql_upgrade_info  performance_schema  test

Restarting the database

#/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 &

and no error.

The log information during startup is as follows:

# 2016-08-18 11:13:18 0 [Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-08-18 11:13:18 0 [not E]/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 re F count Buffer pool pages2016-08-18 11:13:18 3948 [Note] innodb:the InnoDB memory heap is disabled2016-08-18 11:13:18 394  8 [note] innodb:mutexes and rw_locks use GCC atomic builtins2016-08-18 11:13:18 3948 [note] innodb:memory barrier are 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] Innod B: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 is created!2016-08-18 11:13:19 3948 [Note] innodb:setting file./ibdata1 size to MB2 016-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 ~ mb2016-08-18 11:13:21 3948 [note] Inno db:setting log file./ib_logfile1 size to 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) is 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- 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 EV ents2016-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)

As you can see, MySQL rebuilds ibdata1 and redo log during startup.

Log in to the MySQL client to see if the previously created T1,T2 can access

#/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 can see the presence of a T1 table, but the specific contents of the table cannot be viewed

Also, the following information is output in the error log

2016-08-18 11:15:13 3948 [Warning] innodb:cannot Open Table test/t1 from the internal data dictionary of InnoDB though th E. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how can resolve the problem.

Copy the data file and table definition file of the T1,t2 table in the test directory under the data directory to a different location

[[email protected] test]# cd/data/test/[[email protected] test]# lltotal 216-rw-rw----1 mysql mysql  8594 18 11:0 6 T1.FRM-RW-RW----1 mysql mysql 98304 11:07 t1.ibd-rw-rw----1 mysql mysql 8594 in  11:06 t2.frm-rw-rw---- 1 mysql mysql 98304 11:07 t2.ibd[[email protected] test]# MV */backup/[[email protected] test]# ls[[email protecte D] test]# ll/backup/total 216-rw-rw----1 mysql mysql  8594-11:06 t1.frm-rw-rw----1 mysql mysql 98304 18 1 1:07 t1.ibd-rw-rw----1 mysql mysql  8594 11:06 t2.frm-rw-rw----1 mysql mysql 98304-11:07 t2.ibd

Log in to the client, create the T1 and T2 tables, and note that the table structure and previous must be consistent

Careful children's shoes will find that the following statement and the beginning of the creation statement is not the same, the column name is inconsistent, this is actually for the subsequent testing

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

Obviously already manually removed, why also reported this error when creating a table?

Next, you can perform a drop table operation first

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)

For the T2 table, we define a different table structure to see if it works.

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)

Export Table Space

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)

At this time, the data directory under the test directory, the data file is not, only the table structure files left

[Email protected] test]# lst1.frm  t2.frm

Import Table Space

First Test the T1 table

Here, test the following two scenarios

1. The new t1.frm+ old T1.IBD

2. Old t1.frm+ old T1.ibd

First case

Just copy the data file of the T1 table back

Table space for importing T1 tables

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 ', would attempt to import without schema Verification |+---------+------+-------------------------------------------------------------------------------- ---------------------------------------------------------+1 Row in Set (0.00 sec)

See if the T1 table can be accessed

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)

Well, it's really accessible, watch out, the table's column names are consistent with the new statement.

The Flush table operation is used here to flush the table definition in memory.

Here's a look at T1 's second case, the old t1.frm+ old T1.IBD

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

When the first query is still a new column name, flush the table, it reverts to the original column name.

Let's look at the import of the T2 table.

Because the table structure of the T2 table has changed, here is also the test of the following two cases

1. The new t2.frm+ old T2.IBD

2. Old t2.frm+ old T2.ibd

First, just import the data file for the T2 table

[Email protected] test]# cp/backup/t2.ibd. [[email protected] test]# lltotal 216-rw-rw----1 mysql mysql  8594 17:55 t1.frm-rw-r-----1 mysql mysql 98304 A UG 18:00 t1.ibd-rw-rw----1 mysql mysql  8556 17:52 t2.frm-rw-r-----1 root  root  

Importing table spaces for T2 tables for testing

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)

As you can see from the results, you can only read the first column.

The second case is tested below, the old t2.frm and T2.IBD

mysql> ALTER TABLE T2 DISCARD tablespace; Query OK, 0 rows affected (0.06 sec)

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

After re-refreshing, there was an error, personal feeling, this is related to the data dictionary information in the system table space.

In fact, the follow-up test also, if the HireDate column is defined as varchar, whether it is used before the frm file or later, when the table space is imported, query, the database is directly hung off.

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 (HY000): Lost connection to MySQL server during query

Conclusion

After a series of tests above, you can see

1. Using the transportable tablespace, you can resolve the recovery of the MySQL database in the case of deleting Ibdata1 and ib_logfile, of course, the premise of this paper is to delete the ibdata1 and ib_logfile under the normal shutdown of the database.

2. Using a transportable tablespace, it is recommended that the table structure of the new table be exactly the same as the original table structure, and that you simply copy back the original data file, IBD, before importing the table space.

In fact, deleting ibdata1 under normal database shutdown can cause the following tables in the MySQL library to be inaccessible

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

Also, the following information is reported in the error log

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 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 tho Ugh the. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how 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 thou GH the. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how 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 T Hough the. frm file for theTable exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how 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 thou GH the. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how can resolve the problem.

To solve this problem, you can only rebuild these tables.

Reference

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

How to recover a MySQL database with ibdata1 and ib_logfile removed