mysql 误删除ibdata1，数据库并未重启之后的恢复方法（一）

场景

误删除了在线服务器中mysql innodb相关的数据文件ibdata1以及日志文件 ib_logfile*

模拟误操作

注意：在恢复数据库之前一定要做好数据文件的备份，以防止恢复失败，还可以再次尝试。
[root@mariadb3 data]# rm -rf ibdata1 ib_logfile0 ib_logfile1 ib_logfile2
在删除ibdata1及ib_logfile* 时做以下操作
MariaDB [test]> create table t3 (id int,name varchar(32));
Query OK, 0 rows affected (0.17 sec)

MariaDB [test]> insert into t3 values(1,'test');
Query OK, 1 row affected (0.72 sec)

MariaDB [test]> select * from t3;
+------+------+
| id   | name |
+------+------+
|    1 | test |
+------+------+
查看数据目录生成了t3表的表结构及数据文件
[root@mariadb3 test]# ll
-rw-rw---- 1 mysql mysql   552 Apr  3 14:43 t3.frm
-rw-rw---- 1 mysql mysql 98304 Apr  3 14:43 t3.ibd
其实，mysqld在运行状态中，会保持这些文件为打开状态，
即使把它们删除了，它们仍旧存在于文件系统中，mysqld仍然可以对其进行读写。

[root@mariadb3 data_bak]# ps -ef|grep mysql
root     14397     1  0 12:18 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
mysql    14749 14397  0 12:18 ?        00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --open-files-limit=65535 --pid-file=/data/mysql/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root     16561 16518  0 14:41 pts/3    00:00:00 mysql -uroot -px xx
root     16712 15882  0 14:58 pts/2    00:00:00 grep --color=auto mysql

[root@mariadb3 test]#  ls -la /proc/14749/fd/ | grep -e ibdata -e ib_
lrwx------ 1 root  root  64 Apr  3 14:58 11 -> /data/mysql/data/ib_logfile0 (deleted)
lrwx------ 1 root  root  64 Apr  3 14:58 12 -> /data/mysql/data/ib_logfile1 (deleted)
lrwx------ 1 root  root  64 Apr  3 14:58 13 -> /data/mysql/data/ib_logfile2 (deleted)
lrwx------ 1 root  root  64 Apr  3 14:58 7 -> /data/mysql/data/ibdata1 (deleted)

14749是mysqld的pid(进程ID)
只要mysqld不结束，就可以通过proc文件系统找到这几个被删除的文件(已经被Mark为deleted状态)。

因为，在innodb的buffer pool中，有许多dirty page(就是内存中的数据已经被修改，但是没有写回文件中),
如果直接把文件复制回去，轻则数据丢失，重则ibdata1文件损坏。

备份mysql数据的时候，也不能直接备份这几个文件，是同样的道理。

查看buffer pool中的数据修改都保存到了硬盘文件上面

我们必须保证所有buffer pool中的数据修改都保存到了硬盘文件上面，
为此，首先要停止更多的写入/更新/删除操作，然后等待innodb flush pages to disk.
停止写入的话，可以把网站应用关闭，或者lock tables:
通过锁表停止写入:
MariaDB [(none)]>  FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
查看所有的page都flush到硬盘文件中
MariaDB [(none)]> SHOW engine innodb STATUS\G;
---
LOG
---
Log sequence number 9479354
Log flushed up to   9479354
Pages flushed up to 9479354
Last checkpoint at  9479345
0 pending log flushes, 0 pending chkp writes
28 log i/o's done, 0.00 log i/o's/second

checkpoint age 就是 Log sequence number的值减去 Last checkpoint at的值，
如果为0，那么表示所有的page都flush到硬盘文件中了。
为了加速这个flush的过程，可以这样设置一下：
MariaDB [(none)]> SET global innodb_max_dirty_pages_pct=0;
Query OK, 0 rows affected (0.00 sec)
此外，还必须保证一些后台的线程完成了它们的工作，
比如insert buffer thread. ibuf的大小应该=1
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
还有purge thread，它应该purge了全部的transactions:
------------
TRANSACTIONS
------------
Trx id counter 3853
Purge done for trx's n:o < 3372 undo n:o < 0 state: running but idle
还要确保innodb不再进行写操作了：
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
540 OS file reads, 184 OS file writes, 41 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

恢复ibdata1，ib_logfile* 文件

然后把文件复制回去
[root@mariadb3 test]# cp /proc/14749/fd/7 /data/mysql/data/ibdata1
[root@mariadb3 test]# cp /proc/14749/fd/11 /data/mysql/data/ib_logfile0
[root@mariadb3 test]# cp /proc/14749/fd/12 /data/mysql/data/ib_logfile 1
[root@mariadb3 test]# cp /proc/14749/fd/13 /data/mysql/data/ib_logfile2
修改权限
[root@mariadb3 data]# cd /data/mysql/data
[root@mariadb3 data]# chown mysql.mysql ib*
[root@mariadb3 data]# ll
-rw-r-----  1 mysql mysql 1073741824 Apr  3 15:16 ibdata1
-rw-r-----  1 mysql mysql  134217728 Apr  3 15:16 ib_logfile0
-rw-r-----  1 mysql mysql  134217728 Apr  3 15:15 ib_logfile1
-rw-r-----  1 mysql mysql  134217728 Apr  3 15:15 ib_logfile2
重启数据库
[root@mariadb3 data]# service mysql restart
Restarting mysql (via systemctl):                          [  OK  ]