How to restore MySQL after mistakenly deleting ibdata1

How to restore MySQL after mistakenly deleting 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 should be a cold sweat, right?
==================================
Take a cigarette first, and calm yourself.
==================================
Look at the site again, found everything is normal, the data read and write operations are completely normal.
What's the situation?

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

Copy CodeThe code is as follows:
[Email protected]:/var/lib/mysql# ls-la/proc/14101/fd/| GREP-E ibdata-e Ib_
lrwx------1 root root 7 23:29 3-/var/lib/mysql/ibdata1 (Deleted)
lrwx------1 root root 7 23:29 8-/VAR/LIB/MYSQL/IB_LOGFILE0 (Deleted)
lrwx------1 root root 7 23:29 9-/var/lib/mysql/ib_logfile1 (Deleted)

14101 is the PID of the MYSQLD (process ID)

As long as the mysqld does not end, these deleted files can be found through the proc file system (already marked as deleted state).

It should be a sigh of relief. Just copy these files back to/var/lib/mysql, all right?

Things are definitely not so simple.

Because, in the buffer pool of InnoDB, there are many dirty page (that is, the in-memory data 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 damaged.

When backing up MySQL data, it is also not possible to back up these files directly.

We must ensure that all data modifications in the 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.
If you stop writing, you can close the Web app, or lock tables:

Copy CodeThe code is as follows:
Mysql> FLUSH TABLES with READ LOCK;
Query OK, 0 ROWS affected (0.37 sec)

Then it is time to wait for it to flush the end, how to know if there is no end? Observe the age of checkpoint.

Copy CodeThe code is 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 last checkpoint at.
If it is 0, then all the page is flush to the hard disk file.

Then it is time to wait for it to flush the end, how to know if there is no end? Observe the age of checkpoint.

Copy CodeThe code is 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 last checkpoint at.
If it is 0, then all the page is flush to the hard disk file.

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

Copy CodeThe code is 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 background threads have completed their work.
For example, insert buffer thread. The size of the IBUF should =1

Copy CodeThe code is as follows:
-------------------------------------
INSERT BUFFER and ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:size 1, free list len 398, SEG SIZE 400,

And purge thread, it should purge all the transactions:

Copy CodeThe code is as follows:
------------
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:

Copy CodeThe code is 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 CodeThe code is as follows:
[Email protected]:/var/lib/mysql# CP/PROC/14101/FD/3/var/lib/mysql/ibdata1
[Email protected]:/var/lib/mysql# cp/proc/14101/fd/8/VAR/LIB/MYSQL/IB_LOGFILE0
[Email protected]:/var/lib/mysql# cp/proc/14101/fd/9/var/lib/mysql/ib_logfile1
Modify Permissions
[Email protected]:/var/lib/mysql# chown-r MySQL ib* restart mysqld
[Email protected]:/var/lib/mysql#/etc/init.d/mysql Restart
End ~ ~

Conclusion:
1) In the event of an accident, do not panic, smoke a cigarette first calm down.
2) do not operate when the solution is ambiguous, such as restarting the MYSQLD and restarting the server.
3) It is necessary to monitor the existence of MySQL Ibdata and other files.

How to restore MySQL after mistakenly deleting ibdata1