mysql误删ibdata1，还未重启时的恢复办法

一、mysql运行中，删除ibdata1文件

[root@orderer data]# rm -rf ibdata1
[root@orderer data]# rm -rf ib_logfile0
[root@orderer data]# rm -rf ib_logfile1
[root@orderer data]# 

[root@orderer data]# ll
??? 12436
-rw-r----- 1 mysql mysql       56 1?  20 09:13 auto.cnf
-rw------- 1 mysql mysql     1680 1?  20 09:13 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 1?  20 09:13 ca.pem
-rw-r--r-- 1 mysql mysql     1112 1?  20 09:13 client-cert.pem
-rw------- 1 mysql mysql     1676 1?  20 09:13 client-key.pem
-rw-r----- 1 mysql mysql      220 1?  20 11:39 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 1?  20 11:40 ibtmp1

二、这种情况下，mysql还能正常运行，在这种状态下，我们可以从proc目录下对应的文件夹恢复这三个文件

查看当前mysqld服务的进程号

[root@orderer data]# ps -ef|grep mysqld
root     10696  3934  0 11:40 pts/3    00:00:00 /bin/sh /home/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf
mysql    10855 10696  0 11:40 pts/3    00:00:01 /home/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/home/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data --plugin-dir=/home/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/mysqld.log --pid-file=/home/mysql-5.7.26/run/mysqld.pid --socket=/home/mysql-5.7.26/mysql.sock
root     12705 10188  0 11:58 pts/5    00:00:00 grep --color=auto mysqld
[root@orderer data]# 

当前进程号位10855，再执行如下命令查看ibdata 和ib_log相关文件

[root@orderer fd]# cd /proc/10855/fd
[root@orderer fd]# ll |grep -e ibdata -e ib_
lrwx------ 1 root root 64 1?  20 11:42 10 -> /home/mysql-5.7.26/data/ibdata1 (deleted)
lrwx------ 1 root root 64 1?  20 11:42 4 -> /home/mysql-5.7.26/data/ib_logfile0 (deleted)
lrwx------ 1 root root 64 1?  20 11:42 9 -> /home/mysql-5.7.26/data/ib_logfile1 (deleted)

可以看到这3个文件被标记了deleted，只要mysql服务部停止，proc下面就存在这3个文件

但是不能直接把文件复制过去，这样会造成数据文件损坏，我们必须保证所有buffer pool中的数据修改都保存到了硬盘文件上面， 为此，首先要停止更多的写入/更新/删除操作，然后等待innodb flush pages to disk. 停止写入的话，可以把网站应用关闭，或者lock tables: 通过锁表停止写入:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> 

看所有的page都flush到硬盘文件中

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2020-01-20 14:12:43 0x7fd2dc0f5700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 9145 srv_idle
srv_master_thread log flush and writes: 9149
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
RW-shared spins 0, rounds 8, OS waits 4
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 8.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1283
Purge done for trx's n:o < 783 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422018894125792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
267 OS file reads, 70 OS file writes, 16 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1221256
Log flushed up to   1221256
Pages flushed up to 1221256
Last checkpoint at  1221247
0 pending log flushes, 0 pending chkp writes
16 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 51903
Buffer pool size   8191
Free buffers       8028
Database pages     163
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 128, created 35, written 46
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 163, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10855, Main thread ID=140543543736064, state: sleeping
Number of rows inserted 5, updated 0, deleted 0, read 42
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.02 sec)

ERROR: 
No query specified

mysql> 

checkpoint  就是 Log sequence number的值减去 Last checkpoint at的值， 如果为0，那么表示所有的page都flush到硬盘文件中了。

为了加速这个flush的过程，可以这样设置一下：

mysql> 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 1283
Purge done for trx's n:o < 783 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422018894125792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

还要确保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 (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
267 OS file reads, 70 OS file writes, 16 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

满足上面的几个条件后，我们就可以直接把proc下面的文件复制过去

[root@orderer fd]# cp 10 /home/mysql-5.7.26/data/ibdata1
[root@orderer fd]# cp 4 /home/mysql-5.7.26/data/ib_logfile0
[root@orderer fd]# cp 9 /home/mysql-5.7.26/data/ib_logfile1
[root@orderer fd]# 

最后，将3个文件的所有者权限变更为mysql

[root@orderer data]# chown -R mysql:mysql ibdata1
[root@orderer data]# chown -R mysql:mysql ib_logfile0
[root@orderer data]# chown -R mysql:mysql ib_logfile1
[root@orderer data]# 

 

三、重启数据库

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> 

[root@orderer mysql-5.7.26]# mysqld_safe --defaults-file=/etc/mysql/my.cnf 
2020-01-20T06:24:23.104597Z mysqld_safe Logging to '/home/mysql-5.7.26/log/mysqld.log'.
2020-01-20T06:24:23.150412Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data

连接数据库，并访问表

[root@orderer data]# mysql -uroot -p -S /home/mysql-5.7.26/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|    1 | aaaaa  |
|    2 | bbbb   |
|    3 | ccccc  |
|    4 | dddddd |
|    5 | eeeeee |
|    6 | fffff  |
|    7 | rrrrr  |
|    8 | uuuuuu |
|   10 | eerrrr |
|   11 | rrrrr  |
|   12 | hhhhhh |
|   13 | jjjjj  |
|   14 | hhhhh  |
+------+--------+
13 rows in set (0.00 sec)

mysql> 

可以正常访问。