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

Source: Internet
Author: User
Tags egrep

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:

 
 
  1. sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=10000 
  2. --num-threads=90 --mysql-host=192.168.110.140 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 
  3. --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

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

Here I am 30426

6. Key Steps

 
 
  1. # Ll/proc/30426/fd | egrep 'ib _ | ibdata'
  2. Lrwx ------ 1 root 64 September 24 16:51 10->/u2/mysql/data/ib_logfile1
  3. Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile2
  4. Lrwx ------ 1 root 64 September 24 16:51 4->/u2/mysql/data/ibdata1
  5. 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.

 
 
  1. Set global innodb_max_dirty_pages_pct = 0;
  2. # Refresh the dirty pages to the disk as soon as possible.
 
 
  1. mysql> show master status; 
  2. +------------------+----------+--------------+------------------+ 
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
  4. +------------------+----------+--------------+------------------+ 
  5. | mysql-bin.000002 |      107 |              |               | 
  6. +------------------+----------+--------------+------------------+ 
  7. 1 row in set (0.00 sec) 
Make sure that the File and Position values do not change.
 
 
  1. Show engine innodb status \ G;
  2. ------------
  3. TRANSACTIONS
  4. ------------
  5. Trx id counter A21837
  6. Purge done for trx's n: o <A21837 undo n: o <0
  7. # Ensure that the background Purge process clears all undo logs, and the transaction IDs must be consistent.
  8. -------------------------------------
  9. INSERT BUFFER AND ADAPTIVE HASH INDEX
  10. -------------------------------------
  11. Ibuf: size 1, free list len 65, seg size 67, 0 merges
  12. # Insert buffer merge insert cache equal to 1
  13.  
  14. ---
  15. LOG
  16. ---
  17. Log sequence number 18158813743
  18. Logflushed up to 18158813743
  19. Last checkpoint at 18158813743
  20. # Make sure these three values do not change
  21.  
  22. ----------------------
  23. BUFFER POOL AND MEMORY
  24. ----------------------
  25. Total memory allocated 643891200; in additional pool allocated 0
  26. Dictionary memory allocated 39812
  27. Buffer pool size 38400
  28. Free buffers 37304
  29. Database pages 1095
  30. Old database pages 424
  31. Modified db pages 0
  32. # Make sure that the number of dirty pages is 0
  33.  
  34. --------------
  35. ROW OPERATIONS
  36. --------------
  37. 0 queries inside InnoDB, 0 queries in queue
  38. 1 read views open inside InnoDB
  39. Main thread process no. 30426, id 140111500936976, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0
  40. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
  41. # 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?

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

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

 
 
  1. #cd /proc/10755/fd 
  2. #cp 10 /u2/mysql/data/ib_logfile1 
  3. #cp 11 /u2/mysql/data/ib_logfile2 
  4. #cp 4 /u2/mysql/data/ibdata1 
  5. #cp 9 /u2/mysql/data/ib_logfile0 

And modify user attributes

 
 
  1. #cd /u2/mysql/data/ 
  2. #chown mysql:mysql ib* 

10. All you can do is restart MySQL.

 
 
  1. /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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.