Remember the recovery process after a false deletion of ‘ibdata1’

Time:2021-8-9

After a long time of searching for information online, it is finally solved and recorded
Explain the situation
a. The database is not backed up
b. The server does not have a snapshot
c. After deletion, restart MySQL multiple times and reboot with the server
d. Passrm -rfDeletedibdata1,ib_logfile0,ib_logfile1.frm,.ibdThese files exist

Let’s talk about the process of exploring solutions

1. UtilizationextundeleteFile repair

DF – H viewing partitions
LS – LIA / view node
extundelete –inode node /dev/vda1
extundelete /dev/vda1 –restore-all
extundelete /dev/vda1 –restore-directory /tmp
extundelete /dev/vda1 –restore-files /usr/local/mysql/var/ibdata1
extundelete –restore-inode node /dev/vda1

  • Possible problems:

Remember the recovery process after a false deletion of 'ibdata1'

  • The final conclusion

Remember the recovery process after a false deletion of 'ibdata1'

This method generates aRECOVERED_FILESFolder, the ideal situation is to restore all the data, but unfortunately, the data can not be restored in this way

Remember the recovery process after a false deletion of 'ibdata1'

  • Reference articles
    https://blog.csdn.net/liupin67/article/details/84401211

2,/Proc / process number / FD

The premise of this is to restart the server without restarting the service

CD / proc / process number / FD

Remember the recovery process after a false deletion of 'ibdata1'

3. Passed.frm,.ibdRecover data

First, download the MySQL tool through.frmTable creation statement found

yum install mysql-utilities.noarch

There are many ways to download this tool. Search online by yourselfmysql-utilities

mysqlfrm –server=root:[email protected] databasename:tablename.frm –port=3307 –user=mysql

Create a new database with the same name locally, and then run the above again.frmGetsql

  • Possible problems

Remember the recovery process after a false deletion of 'ibdata1'

Remember the recovery process after a false deletion of 'ibdata1'
The solution to this problem is to addROW_FORMAT=COMPACT

Remember the recovery process after a false deletion of 'ibdata1'
Then create a table, delete the previous table space, and import a new table space

CREATE TABLE `test`.`coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_ ID ` int (11) not null comment 'store ID',
  `Type ` tinyint (4) default null comment 'coupon type: 1, discount coupon; 2, full minus; ',
  `minimum_ Payment ` float default null comment 'minimum usage amount (i.e. the coupon can be used when the order is full)',
  `Amount ` float default null comment 'value of offset amount or discount (i.e. discount value or full impairment)',
  `time_ Show ` int (11) default null comment 'coupon issuing start time',
  `time_ Hide ` int (11) default null comment 'coupon issuing end time',
  `time_ Start ` int (11) default null comment 'coupon usage start time',
  `time_ End ` int (11) default null comment 'coupon usage deadline',
  `Status ` int (11) default null comment 'status: 0, unpublished 1, published 2, deleted',
  `time_ Create ` int (11) default null comment 'data creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_ Bin comment = 'coupon' row_ FORMAT=COMPACT

ALTER TABLE `test`.`coupon` DISCARD TABLESPACE;
ALTER TABLE `test`.`coupon` IMPORT TABLESPACE;

Finally, post the effect after the solution

Not all of them were restored, so we took a few tables to do experiments. If all of them were restored, we need to write scripts.frmAll the table creation statements are obtained, then imported and operated in batches. I won’t talk about it here

Remember the recovery process after a false deletion of 'ibdata1'

Remember the recovery process after a false deletion of 'ibdata1'

Because I have done too many operations on the service and server, I don’t understand the partition and mount deeply. The first two methods are not applicable to my own situation. The third method solves the problem


Record a MySQL data recovery exploration process. If there is any supplement, welcome to communicate

This work adoptsCC agreement, reprint must indicate the author and the link to this article

I hope you really like it

Recommended Today

SQL exercise 20 – Modeling & Reporting

This blog is used to review and sort out the common topic modeling architecture, analysis oriented architecture and integration topic reports in data warehouse. I have uploaded these reports to GitHub. If you are interested, you can have a lookAddress:https://github.com/nino-laiqiu/TiTanI recorded a relatively complete development process in my hexo blog deployed on GitHub. You can […]