How to recover data after accidentally deleting ibdata files in mysql5.7.33

How to recover data after accidentally deleting ibdata files in mysql5.7.33

1. Scenario description:

Many times, data cannot be accessed because the MySQL database cannot be started, but the application data is usually not lost. It is just that other files such as the system tablespace are damaged, or a MySQL bug is encountered.

If there is no backup at this time, many people will think that the data is lost, but in fact, most of the time the data can be saved.
For the tablespace of the MyISAM engine, you can simply copy the corresponding data files to a new database and the data can be restored.
For the database tablespace of the InnoDB engine, the data can be recovered by transferring the tablespace.
The premise is that MySQL has enabled the parameter innodb_file_per_table = 1 independent tablespace file

When the MySQL data tablespace file ibdata file is damaged or not modified or deleted, the MySQL service fails to restart. At the same time, MySQL data is not backed up in time. How can we recover as much MySQL data as possible? ?

2. Case demonstration:

2.1. Confirm how many records each table in the database has before the MySQL failure

for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt

How many tables are there in the database before confirming the MySQL failure?

There are 39 tables in total:

[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

2.2. Simulate the failure of deleting ibdata:

Delete the ibdata file (this is not allowed in production environments)

innodb_force_recovery =6 Use MySQL's forced startup parameters to start the MySQL service at this time, but it is of no avail. Since the data table space file ibdata file has been deleted, the MySQL service cannot be started at this time.
Because of this MySQL uses the innodb engine. And the independent tablespace parameter innodb_file_per_table = 1 is enabled. So at this time, you can use the method of transferring table space to rescue the data.

2.3. Ways to retrieve data:

First, create the missing table structure:

First, install mysql-utilities on the failed MySQL server.

yum -y install mysql-utilities

Use mysqlfrm to retrieve the table creation statement from the .frm file:

Analyze a .frm file to generate table creation statements

mysqlfrm --diagnostic 
[root@test02 db_bbs]# mysqlfrm --diagnostic /data/mysql/data/db_bbs/t_admin.frm |grep -v "^#"
CREATE TABLE `db_bbs`.`t_admin` (
 `f_id` int(4) NOT NULL AUTO_INCREMENT, 
 `f_type` tinyint(1) NOT NULL, 
 `f_username` varchar(80) NOT NULL, 
 `f_password` varchar(80) NOT NULL, 
 `f_nick_name` varchar(80) NOT NULL, 
 `f_real_name` varchar(80) NOT NULL, 
 `f_create_time` bigint(4) NOT NULL, 
 `f_update_time` bigint(4) NOT NULL, 
 `f_last_login_time` bigint(4) DEFAULT NULL, 
 `f_last_login_ip` varchar(80) DEFAULT NULL, 
 `f_status` tinyint(1) NOT NULL, 
PRIMARY KEY `PRIMARY` (`f_id`) USING BTREE
)ENGINE=InnoDB ROW_FORMAT = 2;

Import all table creation statements into the /tmp/create.sql file:

[root@test02 ~]# cd /data/mysql/data/db_bbs/
[root@test02 db_bbs]# for n in `ls -l /data/mysql/data/db_bbs/*.frm|awk -F '/' '{print $NF}'|xargs -n 40`;do mysqlfrm --diagnostic $n|grep -v "^#" >>/tmp/create.sql;done

Import the generated table creation statements into the new MySQL instance library:

[root@10-10-127-11 ~]# mysql db_bbs < create.sql 
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5' at line 10

The reason is that the table creation sql obtained contains the parameter ROW_FORMAT = 2.
###Remove the characters including ROW_FORMAT = 2, ROW_FORMAT = 5 in the table creation statement. Re-import the table creation statement###

The batch replacement commands are as follows:

cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g' /tmp/create.sql
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g' /tmp/create.sql
cat /tmp/create.sql|grep ROW_FORMAT |uniq -c

Exporting table creation statements to a new MySQL instance db_bbs database reports an error field is too long:

[root@10-10-127-11 ~]# mysql db_bbs -f < create.sql 
ERROR 1074 (42000) at line 232: Column length too big for column 'f_content' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 299: Column length too big for column 'f_desc' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 365: Column length too big for column 'f_body_image' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 406: Column length too big for column 'f_content' (max = 16383); use BLOB or TEXT instead
ERROR 1074 (42000) at line 433: Column length too big for column 'f_summary' (max = 16383); use BLOB or TEXT instead

After modifying the field length type, re-import the table creation sql into the new MySQL database

[root@10-10-127-11 ~]# mysql db_bbs -f < create.sql 
[root@10-10-127-11 ~]# 
[root@10-10-127-11 ~]# 
[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

###Discard the .ibd file that does not contain data in the newly created MySQL instance, and then import the .idb file of the faulty database###

Discard the data .ibd file of the newly created library:

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done
[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
ls: cannot access *.ibd: No such file or directory

*You can see that all .idb files have been discarded. Then copy the old .ibd files with data to the ./data/db_bbs/ directory of the new MySQL instance. Don't forget to change the owner: chown mysql. Then import these data files into the database**.

[root@test02 db_bbs]# scp *.ibd [email protected]:/data/mysql/data/db_bbs/
[email protected]'s password: 
browse_record.ibd 100% 100MB 50.0MB/s 00:02  
t_admin.ibd         
........
........
[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# ll *.ibd
-rw-r----- 1 root root 104857600 Mar 14 21:56 browse_record.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_admin.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_anonymous_code.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_apply.ibd
-rw-r----- 1 root root 9437184 Mar 14 21:56 t_attach.ibd
-rw-r----- 1 root root 147456 Mar 14 21:56 t_banner.ibd
-rw-r----- 1 root root 163840 Mar 14 21:56 t_banner_log.ibd
-rw-r----- 1 root root 114688 Mar 14 21:56 t_black_ip.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_black_user.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_block_userbaseinfo.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_collect.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_country_code.ibd
-rw-r----- 1 root root 163840 Mar 14 21:56 t_ct_goods.ibd
-rw-r----- 1 root root 131072 Mar 14 21:56 t_ct_goods_record.ibd
-rw-r----- 1 root root 9437184 Mar 14 21:56 t_ct_integral.ibd
-rw-r----- 1 root root 46137344 Mar 14 21:56 t_ct_integral_record.ibd
-rw-r----- 1 root root 27262976 Mar 14 21:56 t_ct_news.ibd
-rw-r----- 1 root root 9437184 Mar 14 21:56 t_ct_order.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_feedback.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_lexicon.ibd
-rw-r----- 1 root root 327680 Mar 14 21:56 t_logs.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_manage.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_module.ibd
-rw-r----- 1 root root 9437184 Mar 14 21:56 t_post_extend.ibd
-rw-r----- 1 root root 12582912 Mar 14 21:56 t_post.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_post_video.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_realtime_message.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_recommend.ibd
-rw-r----- 1 root root 46137344 Mar 14 21:56 t_reply.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_reward.ibd
-rw-r----- 1 root root 196608 Mar 14 21:56 t_sensitive_word.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_system_message.ibd
-rw-r----- 1 root root 9437184 Mar 14 21:56 t_userbaseinfo.ibd
-rw-r----- 1 root root 344064 Mar 14 21:56 t_userextendinfo.ibd
-rw-r----- 1 root root 12582912 Mar 14 21:56 t_user_health.ibd
-rw-r----- 1 root root 98304 Mar 14 21:56 t_user_message.ibd
-rw-r----- 1 root root 442368 Mar 14 21:56 t_user_read_module_log.ibd
-rw-r----- 1 root root 17825792 Mar 14 21:56 t_viewpoint.ibd
-rw-r----- 1 root root 114688 Mar 14 21:56 t_white_ip.ibd

[root@10-10-127-11 db_bbs]# chown mysql.mysql *.ibd

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done

When importing the tablespace of each table, errors occur for individual tables:

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done 
ERROR 1808 (HY000) at line 1: Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

Check the table file and find that only the browse_record table reports an error when imported into the independent table space, causing the data recovery of this table to fail.

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record
Warning: InnoDB: Tablespace has been discarded for table 'browse_record'
Error: Tablespace has been discarded for table 'browse_record'
error : Corrupt
db_bbs.t_admin OK
db_bbs.t_anonymous_code OK
db_bbs.t_apply OK
db_bbs.t_attach OK
db_bbs.t_banner OK
db_bbs.t_banner_log OK
db_bbs.t_black_ip OK
db_bbs.t_black_user OK
db_bbs.t_block_userbaseinfo OK
db_bbs.t_collect OK
db_bbs.t_country_code OK
db_bbs.t_ct_goods OK
db_bbs.t_ct_goods_record OK
db_bbs.t_ct_integral OK
db_bbs.t_ct_integral_record OK
db_bbs.t_ct_news OK
db_bbs.t_ct_order OK
db_bbs.t_feedback OK
db_bbs.t_lexicon OK
db_bbs.t_logs OK
db_bbs.t_manage OK
db_bbs.t_module OK
db_bbs.t_post OK
db_bbs.t_post_extend OK
db_bbs.t_post_video OK
db_bbs.t_realtime_message OK
db_bbs.t_recommend OK
db_bbs.t_reply OK
db_bbs.t_reward OK
db_bbs.t_sensitive_word OK
db_bbs.t_system_message OK
db_bbs.t_user_health OK
db_bbs.t_user_message OK
db_bbs.t_user_read_module_log OK
db_bbs.t_userbaseinfo OK
db_bbs.t_userextendinfo OK
db_bbs.t_viewpoint OK
db_bbs.t_white_ip OK

The solution to the above browse_record table restore failure error is as follows:

Reference: https://blog.csdn.net/weixin_30607659/article/details/94987901

Delete the browse_record table imported into the new MySQL instance, and then execute the following table creation statement to create a new browse_record table:

CREATE TABLE `browse_record` (
 `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` int(4) unsigned NOT NULL,
 `user_id` int(4) unsigned NOT NULL,
 `status` tinyint(1) unsigned NOT NULL,
 `update_time` bigint(4) unsigned NOT NULL,
 `create_time` bigint(4) unsigned NOT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `browse` (`post_id`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=compact;

Tip: If you delete the browse_record table in the new MySQL instance and the deletion fails, just drop the db_bbs database on the new MySQL instance, re-import the table creation statements for all db_bbs tables, and then execute the following command:

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

Re-import the tablespace into the new MySQL instance:

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done
[root@10-10-127-11 db_bbs]#

Data repair is complete here

Verify the tables in the MySQL db_bbs library:

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record OK
db_bbs.t_admin OK
db_bbs.t_anonymous_code OK
db_bbs.t_apply OK
db_bbs.t_attach OK
db_bbs.t_banner OK
db_bbs.t_banner_log OK
db_bbs.t_black_ip OK
db_bbs.t_black_user OK
db_bbs.t_block_userbaseinfo OK
db_bbs.t_collect OK
db_bbs.t_country_code OK
db_bbs.t_ct_goods OK
db_bbs.t_ct_goods_record OK
db_bbs.t_ct_integral OK
db_bbs.t_ct_integral_record OK
db_bbs.t_ct_news OK
db_bbs.t_ct_order OK
db_bbs.t_feedback OK
db_bbs.t_lexicon OK
db_bbs.t_logs OK
db_bbs.t_manage OK
db_bbs.t_module OK
db_bbs.t_post OK
db_bbs.t_post_extend OK
db_bbs.t_post_video OK
db_bbs.t_realtime_message OK
db_bbs.t_recommend OK
db_bbs.t_reply OK
db_bbs.t_reward OK
db_bbs.t_sensitive_word OK
db_bbs.t_system_message OK
db_bbs.t_user_health OK
db_bbs.t_user_message OK
db_bbs.t_user_read_module_log OK
db_bbs.t_userbaseinfo OK
db_bbs.t_userextendinfo OK
db_bbs.t_viewpoint OK
db_bbs.t_white_ip OK

2.4. Obtain the table records imported into the new MySQL instance db_bbs library and compare them with the original library test.txt table record file

[root@10-10-127-11 ~]# for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt11

Compare with the original library test.txt table record file.

[root@test02 ~]# vimdiff test.txt11 test.txt

The table records are completely consistent here. MySQL data repair is complete.

References:
https://mp.weixin.qq.com/s/r3KTPsFay292JnO0lgTLUg
https://www.cnblogs.com/jiangxu67/p/4744283.html
https://blog.csdn.net/Sonny_alice/article/details/80198200
https://www.cnblogs.com/jiangxu67/p/4744283.html

This is the end of this article about how to recover data from accidentally deleted ibdata files in MySQL 5.7.33. For more information about accidentally deleted ibdata in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MyBatis batch insert/modify/delete MySql data
  • Implementation of mysql using mysqlbinlog command to restore accidentally deleted data
  • MySQL database deletes duplicate data and only retains one method instance
  • How to implement batch deletion of large amounts of data in MySQL large tables
  • Why MySQL does not recommend deleting data
  • Python script to batch delete tens of millions of data in MySQL
  • Mysql delete data and data table method example
  • Why the table file size remains unchanged after deleting data in MySQL
  • Detailed explanation of several practical solutions for quickly deleting large amounts of data (tens of millions) in MySQL
  • Why the disk space is not released after deleting data in MySQL

<<:  4 flexible Scss compilation output styles

>>:  Docker removes abnormal container operations

Recommend

Detailed explanation of MySQL using profile to analyze slow SQL (group left join is more efficient than subquery)

Use profile to analyze slow SQL The main purpose ...

Vue makes a simple random roll call

Table of contents Layout part: <div id="a...

Detailed explanation of the process of using docker to build minio and java sdk

Table of contents 1minio is simple 2 Docker build...

Try Docker+Nginx to deploy single page application method

From development to deployment, do it yourself Wh...

How to install and deploy zabbix 5.0 for nginx

Table of contents Experimental environment Instal...

Detailed usage of Vue timer

This article example shares the specific code of ...

JavaScript canvas to achieve raindrop effect

This article example shares the specific code for...

MySQL 8.X installation tutorial under Windows

I had been using MySQL 5.7 before, but because My...

Solve the problem of mysql data loss when docker restarts redis

Official documentation: So mysql should be starte...

Tencent Interview: What are the reasons why a SQL statement executes very slowly? ---Don't watch the regret series (recommended)

To be honest, this question involves a lot of cor...

Detailed explanation of Object.create instance usage in js

1. Create a new object using the Object.create() ...

Why the explain command may modify MySQL data

If someone asked you whether running EXPLAIN on a...

Detailed explanation of pid and socket in MySQL

Table of contents 1. Introduction to pid-file 2.S...

How to implement nginx smooth restart

1. Background During the server development proce...

Nginx anti-crawler strategy to prevent UA from crawling websites

Added anti-crawler policy file: vim /usr/www/serv...