When backing up the database, a full database backup is used, but for some reasons, the data of a table needs to be rolled back to the backup database. If the entire database needs to be rolled back, it will take time, because this table may only have tens of meters, but other tables may have tens of hundreds of G, so the tables that need to be recovered need to be extracted

Now there is a backup database fdcsqlmysql-2018-11-03-00-01.sql. There are multiple tables in it. Now you need to restore the data of FDC document

Extract table building statement

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

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 'File ID',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'user ID',
  `name` char(40) NOT NULL DEFAULT '' COMMENT 'Identification',
   ...
   ...
   ...
  `entrust_rule` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT ' If the broker clicks to sign a commission agreement with the user: 1 for yes; 0 for no',
  `audit` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'Approved: 0 is not approved; 1 is picture approved; 2 is description approved; 3 is picture and description approved',
  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 (file name of backup file) > table_data.sql

Here, grep 'insert import doc'fdcsqlmysql-2018-11-30-03-00-01.sql > document.sql should be executed

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

Building database and building tables

First create the database, and then create the table FDC [document] according to the above SQL statement

Import table data

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

ok, finish!