MySQL restores single table data with full database backup data

Keywords: MySQL SQL Database mysqldump

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
/*!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!

Posted by paulchen on Tue, 03 Dec 2019 11:20:06 -0800