How to restore single table data using MySQL full database backup data

Preface

When backing up the database, a full database backup is used. However, for some reason, the data of a table needs to be rolled back to the backup database. If the entire database is rolled back, it will take a long time, because this table may only be tens of MB, but other tables may be tens or hundreds of GB. At this time, the table that needs to be restored needs to be extracted.

We have all encountered this situation in actual work. There may be multiple databases in a MySQL instance. When we back up, we usually use a full backup to back up all databases into one file.

However, occasionally you may need to restore only one database or one table. How to solve it?

Now there is a backup library fdcsqlmysql-2018_11_30-03_00_01.sql, which contains multiple tables. Now we need to restore the data of the fdc_document table.

Extract table creation statements

sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `table name`/!d;q' mysqldump.sql (backup file name)

sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `fdc_document`/!d;q' fdcsqlmysql-2018_11_30-03_00_01.sql

DROP TABLE IF EXISTS `fdc_document`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fdc_document` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Document ID',
 `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'User ID',
 `name` char(40) NOT NULL DEFAULT '' COMMENT 'Identifier',
 ...
 ...
 ...
 `entrust_rule` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'The broker clicks whether to sign the entrustment agreement with the user: 1 for yes; 0 for no',
 `audit` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'Audit: 0 means unaudited; 1 means the image has been audited; 2 means the description has been audited; 3 means both the image and the description have been audited',
 PRIMARY KEY (`id`),
 KEY `idx_area_house` (`partition`,`category_id`,`status`,`is_off`) USING BTREE,
 KEY `idx_model_house` (`model_id`,`status`,`is_off`) USING BTREE,
 KEY `idx_community_house` (`community_id`,`estate`,`status`,`is_off`) USING BTREE,
 KEY `idx_uid_house` (`uid`,`model_id`,`is_off`) USING BTREE,
 KEY `idx_pid_house` (`id`,`pid`,`status`,`is_off`) USING BTREE,
 KEY `is_video` (`is_video`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=211138 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Extract table data

grep 'INSERT INTO table name' mysqldump.sql (backup file name) > table_data.sql

Here you should execute grep 'INSERT INTOfdc_document' fdcsqlmysql-2018_11_30-03_00_01.sql > document.sql

After execution, you will get the file document.sql, which is the required separate table file, and you can restore the table data normally.

Create database and table

First create the database, and then create the table fdc_document according to the above SQL statement

Import table data

MySQL [document]> source /data/backup/mysql/document.sql

OK, done!

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.