昨天，有個朋友對公司內部使用的一個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]# ls auto.cnf ib_logfile0 localhost.localdomain.err mysql_upgrade_info test ibdata1 ib_logfile1 mysql performance_schema [root@localhost data]# rm -rf ibdata1 [root@localhost data]# rm -rf ib_logfile*[root@localhost data]# ls auto.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 pages 2016-08-18 11:13:18 3948 [Note] InnoDB: The InnoDB memory heap is disabled 2016-08-18 11:13:18 3948 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-08-18 11:13:18 3948 [Note] InnoDB: Memory barrier is not used 2016-08-18 11:13:18 3948 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-08-18 11:13:18 3948 [Note] InnoDB: Using Linux native AIO 2016-08-18 11:13:18 3948 [Note] InnoDB: Using CPU crc32 instructions 2016-08-18 11:13:18 3948 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-08-18 11:13:19 3948 [Note] InnoDB: Completed initialization of buffer pool 2016-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 MB 2016-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 MB 2016-08-18 11:13:21 3948 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2016-08-18 11:13:22 3948 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2016-08-18 11:13:22 3948 [Warning] InnoDB: New log files created, LSN=45781 2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer not found: creating new 2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer created 2016-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 created 2016-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 start 2016-08-18 11:13:22 3948 [Note] InnoDB: 5.6.32 started; log sequence number 0 2016-08-18 11:13:22 3948 [Note] Server hostname (bind-address): '*'; port: 3310 2016-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 events 2016-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 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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]# ll 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 [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]# ls t1.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]# ll total 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. 使用可傳輸表空間，建議新建表的表結構和原來的表結構完全一致，同時，在導入表空間前，只需COPY回原來的數據文件，即ibd。
事實上，在數據庫正常關閉下刪除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 exist mysql> select * from mysql.innodb_table_stats; ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist mysql> select * from mysql.slave_master_info; ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist mysql> select * from mysql.slave_relay_log_info; ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist mysql> 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
