The recovery method after MySQL mistakenly deletes ibdata1 _mysql

How to recover after MySQL mistakenly deletes ibdata1

If you mistakenly delete the MySQL innodb related data file ibdata1 and log file ib_logfile* in the online server,
How should it be recovered?

It's supposed to be a cold sweat, right?
==================================
Smoke a cigarette first and calm down.
==================================
Look at the site again, found that everything is normal, the data read and write operations are completely normal.
What's the situation?

In fact, mysqld in the running state, the files are kept open,
Even if they are deleted, they still exist in the file system, and mysqld can still read and write to them.

Copy Code code as follows:

root@localhost:/var/lib/mysql# ls-la/proc/14101/fd/| GREP-E ibdata-e Ib_
LRWX------1 Root Aug 7 23:29 3->/var/lib/mysql/ibdata1 (deleted)
LRWX------1 Root Aug 7 23:29 8->/var/lib/mysql/ib_logfile0 (deleted)
LRWX------1 Root Aug 7 23:29 9->/var/lib/mysql/ib_logfile1 (Deleted)

14101 is the mysqld PID (process ID)

As long as the mysqld does not end, it is possible to find these deleted files through the proc file system (already marked as deleted state).

It's time to breathe a sigh of relief. Just copy these files back to/var/lib/mysql, all right?

It's definitely not that simple.

Because, in the InnoDB buffer pool, there are 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, the data is lost and the Ibdata1 file is corrupted.

When you back up MySQL data, you can't back up these files directly, and that's the same truth.

We must ensure that all data modifications in the buffer pool are saved to the hard drive file.
To do this, first stop more write/update/delete operations, and then wait for INNODB flush pages to disk.
If you stop writing, you can turn the Web application off, or lock tables:

Copy Code code as follows:

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

At this time will wait for it flush end, how to know whether there is no end? Observe the checkpoint age.

Copy Code code as follows:

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 the value of the last checkpoint at.
If 0, all of the page is flush to the hard drive file.

At this time will wait for it flush end, how to know whether there is no end? Observe the checkpoint age.

Copy Code code as follows:

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 the value of the last checkpoint at.
If 0, all of the page is flush to the hard drive file.

To speed up this flush process, you can set this up:

Copy Code code 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 some of the background threads complete their work,
such as insert buffer thread. The size of the ibuf should be =1

Copy Code code as follows:

-------------------------------------
INSERT BUFFER and adaptive HASH INDEX
-------------------------------------
Ibuf:size 1, free list len 398, SEG SIZE 400,

and purge thread, which should purge all the transactions:

Copy Code code as follows:

------------
Transactions
------------
Trx ID Counter 0 16644
Purge for Trx ' s N:o < 0 16644 undo N:o < 0 0

Also make sure that the InnoDB no longer writes:

Copy Code code as follows:

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, OS file writes, OS Fsyncs
0.00 READS/S, 0 avg bytes/read, 0.00 WRITES/S, 0.00 FSYNCS/S

Then copy the file back:

Copy Code code as follows:

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 Permissions
root@localhost:/var/lib/mysql# chown-r mysql ib* reboot mysqld
root@localhost:/var/lib/mysql#/etc/init.d/mysql Restart
End ~ ~ ~

Conclusion:
1 when there is an accident, do not panic, smoke a cigarette first calm down.
2 When the solution is not clear, do not operate, such as restart Mysqld, restart the server.
3 It is necessary to monitor the existence of MySQL Ibdata and other files.