How can I restore the InnoDB ibdata file manually deleted by mistake?

In daily work, because I am not familiar with the InnoDB engine, many people in the group have deleted InnoDB ibdata files by mistake) and ib_logfileredo log redo transaction log files. If you have master-slave replication and synchronization, it's okay. What if it's a single machine? How to restore?

See the recovery demo below:

1. You can use sysbench to simulate Data Writing, for example:

 
 

  
  
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=10000 
  
  
--num-threads=90 --mysql-host=192.168.110.140 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 
  
  
--mysql-db=test --oltp-table-name=uncompressed  --mysql-socket=/tmp/mysql.sock run 
 
 

2. rm-f ib *

3. At this time, I guess you are scared and have a trembling face. If you see this article, your mind will be stable and you will be able to recover.

4. At this time, you will find that the database can still work normally and data can still be written. Remember, do not kill the mysqld process at this time; otherwise, you will only have to skip the building and the gods will not be able to save you.

5. First find the mysqld process pid

 
 

  
  
# netstat -ntlp | grep mysqld 
  
  
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      30426/mysqld  
 
 

Here I am 30426

6. Key Steps

 
 

  
  
# Ll/proc/30426/fd | egrep 'ib _ | ibdata'
  
  
Lrwx ------ 1 root 64 September 24 16:51 10->/u2/mysql/data/ib_logfile1
  
  
Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile2
  
  
Lrwx ------ 1 root 64 September 24 16:51 4->/u2/mysql/data/ibdata1
  
  
Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile0
 
 

10, 11, 4, and 9 are the files to be restored.

7. You can disable the front-end service or execute flush tables with read lock. This step prevents the database from being written for subsequent recovery.

8. How can I verify that no write operation is performed? Take the following steps and remember to observe them together.

 
 

  
  
Set global innodb_max_dirty_pages_pct = 0;
  
  
# Refresh the dirty pages to the disk as soon as possible.
 
 

 
 

  
  
mysql> show master status; 
  
  
+------------------+----------+--------------+------------------+ 
  
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
  
  
+------------------+----------+--------------+------------------+ 
  
  
| mysql-bin.000002 |      107 |              |               | 
  
  
+------------------+----------+--------------+------------------+ 
  
  
1 row in set (0.00 sec) 
 
 

Make sure that the File and Position values do not change.

 
 

  
  
Show engine innodb status \ G;
  
  

  
  
------------
  
  
TRANSACTIONS
  
  
------------
  
  
Trx id counter A21837
  
  
Purge done for trx's n: o <A21837 undo n: o <0
  
  
# Ensure that the background Purge process clears all undo logs, and the transaction IDs must be consistent.
  
  

  
  
-------------------------------------
  
  
INSERT BUFFER AND ADAPTIVE HASH INDEX
  
  
-------------------------------------
  
  
Ibuf: size 1, free list len 65, seg size 67, 0 merges
  
  
# Insert buffer merge insert cache equal to 1
  
  
 
  
  
---
  
  
LOG
  
  
---
  
  
Log sequence number 18158813743
  
  
Logflushed up to 18158813743
  
  
Last checkpoint at 18158813743
  
  
# Make sure these three values do not change
  
  
 
  
  
----------------------
  
  
BUFFER POOL AND MEMORY
  
  
----------------------
  
  
Total memory allocated 643891200; in additional pool allocated 0
  
  
Dictionary memory allocated 39812
  
  
Buffer pool size 38400
  
  
Free buffers 37304
  
  
Database pages 1095
  
  
Old database pages 424
  
  
Modified db pages 0
  
  
# Make sure that the number of dirty pages is 0
  
  
 
  
  
--------------
  
  
ROW OPERATIONS
  
  
--------------
  
  
0 queries inside InnoDB, 0 queries in queue
  
  
1 read views open inside InnoDB
  
  
Main thread process no. 30426, id 140111500936976, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0
  
  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
  
  
# Ensure that the values of insert, update, and delete are 0
 
 

9. After the above series of validation work is completed, we can recover. Do you still remember the deleted files we recorded just now?

 
 

  
  
# Ll/proc/30426/fd | egrep 'ib _ | ibdata'
  
  
Lrwx ------ 1 root 64 September 24 16:51 10->/u2/mysql/data/ib_logfile1
  
  
Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile2
  
  
Lrwx ------ 1 root 64 September 24 16:51 4->/u2/mysql/data/ibdata1
  
  
Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile0
 
 

Copy these files to the original directory and modify user attributes.

 
 

  
  
#cd /proc/10755/fd 
  
  
#cp 10 /u2/mysql/data/ib_logfile1 
  
  
#cp 11 /u2/mysql/data/ib_logfile2 
  
  
#cp 4 /u2/mysql/data/ibdata1 
  
  
#cp 9 /u2/mysql/data/ib_logfile0 
 
 

And modify user attributes

 
 

  
  
#cd /u2/mysql/data/ 
  
  
#chown mysql:mysql ib* 
 
 

10. All you can do is restart MySQL.

 
 

  
  
/etc/init.d/mysql restart 
 
 

How? It's that simple. You can also try it.

 

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1004810