Mysql restoration method after ibdata1 is deleted by mistake _ MySQL

Mysql restoration method after deleting ibdata1 by mistake bitsCN.com

How can I recover an ibdata1 database deleted by mysql by mistake?

If ibdata1 and ib_logfile * related to mysql innodb are deleted by mistake on the online server *,
How should we restore it?

At this time it should be cold sweat, right?
========================================
Smoke a cigarette and calm down.
========================================
After observing the website, we found that everything was normal and data reading and writing operations were completely normal.
What is the situation?

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

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

14101 is the pid of mysqld (process ID)

As long as mysqld does not end, you can use the proc file system to find the deleted files (the files have been marked as deleted ).

At this time, I should have breathed a sigh of relief. Just copy these files back to/var/lib/mysql?

It is definitely not that simple.

Because there are many dirty pages in the buffer pool of innodb (that is, the data in the memory has been modified but not written back to the file ),
If the file is directly copied back, data is lost if the file is duplicated, and the ibdata1 file is damaged if the file is duplicated.

When backing up mysql data, you cannot directly back up these files.

We must ensure that all data changes in the buffer pool are saved to the hard disk file,
To do this, stop writing, updating, and deleting data, and wait for innodb flush pages to disk.
If you stop writing, you can disable the website application or lock tables:

Mysql> flush tables with read lock;
Query OK, 0 ROWS affected (0.37 sec)

At this time, we have to wait for its flush to end. how can we know if it has ended? Observe the checkpoint age.

Mysql> SHOW engine innodb STATUS
---
LOG
---
Log sequence number 363096003
Logflushed up TO 363096003
LAST checkpoint at 363096003

The checkpoint age is the value of Log sequence number minus the value of Last checkpoint,
If the value is 0, all pages are flushed to the hard disk file.

At this time, we have to wait for its flush to end. how can we know if it has ended? Observe the checkpoint age.

Mysql> SHOW engine innodb STATUS
---
LOG
---
Log sequence number 363096003
Logflushed up TO 363096003
LAST checkpoint at 363096003

The checkpoint age is the value of Log sequence number minus the value of Last checkpoint,
If the value is 0, all pages are flushed to the hard disk file.

To speed up the flush process, you can set it 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, the insert buffer thread. ibuf size should be 1

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

There is also the purge thread, which should purge all transactions:

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

Make sure that innodb does not perform write operations any more:

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

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 permissions
Root @ localhost:/var/lib/mysql # chown-R mysql ib * restart mysqld
Root @ localhost:/var/lib/mysql #/etc/init. d/mysql restart
End ~~~

Conclusion:
1) when an accident occurs, never panic. take a cigarette and calm down.
2) when the solution is unclear, do not perform operations, such as restarting mysqld and the server.
3) it is necessary to monitor the existence of mysql ibdata and other files.

BitsCN.com