最近看了一本 mysql管理之道的书，有关于人工不小心删除 mysql的表空间文件和重做日志文件时该如何恢复，
这里笔记一下以防用的上。
查看目前MYSQL数据目录文件情况：
[root@myDBserver data]# ls -al
总用量 110704
drwxr-xr-x. 5 mysql mysql 4096 7月 3 14:29 .
drwxr-xr-x. 4 mysql mysql 4096 6月 4 22:56 ..
....
-rw-rw----. 1 mysql mysql 12582912 7月 3 14:29 ibdata1
-rw-rw----. 1 mysql mysql 50331648 7月 3 14:29 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 6月 4 23:08 ib_logfile1
....
然后使用 rm -f ib* 删除数据文件ibdata1和redo日志文件ib_logfile0、ib_logfile1
恢复过程：
(1) 确保mysqld进程还存在，切勿 pkill掉该进程
[root@myDBserver /]# netstat -ntlp | grep mysqld
tcp 0 0 :::3306 :::* LISTEN 3415/mysqld
注意最后信息 "3415/mysqld"
(2) 查看刚才被删除的文件
[root@myDBserver /]# ll /proc/3415/fd | egrep 'ib_|ibdata'
lrwx------ 1 root root 64 7月 3 14:33 10 -> /opt/mysql/data/ib_logfile1
lrwx------ 1 root root 64 7月 3 14:33 4 -> /opt/mysql/data/ibdata1
lrwx------ 1 root root 64 7月 3 14:33 9 -> /opt/mysql/data/ib_logfile0
其中 10,4,9就是需要恢复的文件
(3) 关闭前端业务或执行 FLUSH TABLES WITH READ LOCK;
(4) 验证是否此刻数据库已经没有写入操作了
mysql> set global innodb_max_dirty_pages_pct = 0;
【4.1】查看InoDB 目前的状态，确保脏数据已经刷入磁盘
mysql> show engine innodb status\G;
------------
TRANSACTIONS
------------
Trx id counter 6435
Purge done for trx's n:o < 6435 undo n:o < 0 state: running but idle
## 确保事务号一致
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
## 确保insert buffer 合并插入缓存等于1
---
LOG
---
Log sequence number 1721029
Log flushed up to 1721029
Last checkpoint at 1721029
## 确保这三个值不变
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 76753
Buffer pool size 16383
Free buffers 16181
Database pages 202
Old database pages 0
Modified db pages 0
## 确保脏页数量为0
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 3415, id 140701545637632, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 5
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
## 确保插入、更新、删除为0
【4.2】查看主库目前的状态，确保已经不再发生变化
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
(5) 恢复数据文件和日志文件
[root@myDBserver /]# ll /proc/3415/fd | egrep 'ib_|ibdata'
lrwx------ 1 root root 64 7月 3 14:33 10 -> /opt/mysql/data/ib_logfile1
lrwx------ 1 root root 64 7月 3 14:33 4 -> /opt/mysql/data/ibdata1
lrwx------ 1 root root 64 7月 3 14:33 9 -> /opt/mysql/data/ib_logfile0
[root@myDBserver /]# cd /proc/3415/fd
[root@myDBserver fd]# pwd
/proc/3415/fd
[root@myDBserver fd]# cp 10 /opt/mysql/data/ib_logfile1
[root@myDBserver fd]# cp 4 /opt/mysql/data/ibdata1
[root@myDBserver fd]# cp 9 /opt/mysql/data/ib_logfile0
[root@myDBserver fd]# cd /opt/mysql/data/
[root@myDBserver data]# chown mysql:mysql ib*
(6) 重启mysql 即可完成
[root@myDBserver /]# /etc/init.d/mysql restart;
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@myDBserver /]#
