The recovery method after mysql mistakenly deleted ibdata1

How to recover ibdata1 after mysql mistakenly deleted ibdata1

If the data file ibdata1 and log file ib_logfile* related to mysql innodb in the online server are deleted by mistake,
How do you recover?

At this time should 1 body cold sweat?
==================================
Have a cigarette and calm down.
==================================
Then observe 1 website, found that 1 cut is very normal, data read and write operations are completely normal.
How does this work?

In fact, mysqld keeps these files open while it is running,
Even if you delete them, they still exist in the file system and mysqld can still read and write to them.

root@localhost:/var/lib/mysql# ls -la /proc/14101/fd/ | grep -e ibdata -e ib_
lrwx------ 1 root  root  64 Aug  7 23:29 3 -> /var/lib/mysql/ibdata1 (deleted) 
lrwx------ 1 root  root  64 Aug  7 23:29 8 -> /var/lib/mysql/ib_logfile0 (deleted) 
lrwx------ 1 root  root  64 Aug  7 23:29 9 -> /var/lib/mysql/ib_logfile1 (deleted) 

14101 is pid for mysqld (process ID)

As long as mysqld does not end, you can find the deleted files (already in deleted status by Mark) via the proc file system.

That should be a relief. Just copy these files back to /var/lib/mysql?

It's definitely not that simple.

Because, in buffer pool of innodb, many dirty page(that is, the data in memory has been modified, but not written back to the file),
If you copy the file back directly, at least the data is lost, at least ibdata1 file is corrupted.

When backing up mysql data, you cannot directly back up these files, for the same reason.

We must make sure that all the data changes in buffer pool are saved to the hard disk file,
To do this, first stop more write/update/delete operations and then wait for innodb flush pages to disk.
To stop writing, close the application or lock tables:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 ROWS affected (0.37 sec) 

Then you have to wait for it to end. How do you know if it's over? Just look at checkpoint age.

mysql> SHOW engine innodb STATUS   
---
LOG 
--- 
Log SEQUENCE NUMBER 363096003 
Log flushed up TO 363096003 
LAST checkpoint at 363096003 

checkpoint age is the value of Log sequence number minus Last checkpoint at,
If 0, then all page flush are in the hard disk file.

And then, when it is over, how do you know if it is over? Just look at checkpoint age.

mysql> SHOW engine innodb STATUS   
--- 
LOG 
--- 
Log SEQUENCE NUMBER 363096003 
Log flushed up TO 363096003 
LAST checkpoint at 363096003 

checkpoint age is the value of Log sequence number minus Last checkpoint at,
If it is 0, then all page is flush in the hard disk file.

To speed up the flush process, set 1 as follows:

mysql> SET global innodb_max_dirty_pages_pct=0;
Query OK, 0 ROWS affected (0.01 sec) 

In addition, you must ensure that 1 of the background threads complete their work,
For example, insert buffer ES127en.ES128en should be 1 in size

------------------------------------- 
INSERT BUFFER AND ADAPTIVE HASH INDEX 
------------------------------------- 
Ibuf: SIZE 1, free list len 398, seg SIZE 400, 

And purge thread, it should be purge all transactions:

------------ 
TRANSACTIONS 
------------ 
Trx id counter 0 16644 
Purge done FOR trx's n:o < 0 16644 undo n:o < 0 0

Also make sure that innodb no longer writes:

FILE I/O 
-------- 
I/O thread 0 state: waiting FOR i/o request (INSERT buffer thread) 
I/O thread 1 state: waiting FOR i/o request (log thread) 
I/O thread 2 state: waiting FOR i/o request (READ thread)
 I/O thread 3 state: waiting FOR i/o request (WRITE thread) 
Pending normal aio reads: 0, aio writes: 0, 
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
332 OS file reads, 47 OS file writes, 32 OS fsyncs
0.00 reads/s, 0 avg bytes/READ, 0.00 writes/s, 0.00 fsyncs/s

Then copy the file back:

root@localhost:/var/lib/mysql# cp /proc/14101/fd/3 /var/lib/mysql/ibdata1
root@localhost:/var/lib/mysql# cp /proc/14101/fd/8 /var/lib/mysql/ib_logfile0
root@localhost:/var/lib/mysql# cp /proc/14101/fd/9 /var/lib/mysql/ib_logfile1
 Modify the permissions 
root@localhost:/var/lib/mysql# chown -R mysql ib*  restart mysqld
root@localhost:/var/lib/mysql# /etc/init.d/mysql restart
 The end of the ~~~

Conclusion:
1) When there is an accident, never panic, take a cigarette calm down 1.
2) Do not operate when the solution is not clear, such as restart mysqld and restart the server.
3) It is necessary to monitor the existence of ibdata and other files.