How to restore a single database or table in MySQL and possible pitfalls

Preface:

The most commonly used MySQL logical backup tool is mysqldump. Generally, we back up the entire instance or part of the business database. I'm not sure if you have done any recovery. There may be many recovery scenarios, such as restoring a database or a table. So how do you restore a single database or table from full backup? What are the hidden pitfalls? Let’s take a look at this article together.

1. How to restore a single database or table

The previous article introduced MySQL backup and recovery. There may be more than one database in each of our database instances. Generally, the backup is to back up the entire instance, but the recovery requirements are diverse. For example, I want to restore only a certain database or a certain table. What should I do at this time?

If the amount of data in your instance is not large, you can restore the entire instance in another environment, and then back up the required database or table separately for recovery. However, this method is not flexible enough and is only applicable when the amount of data is relatively small.

In fact, it is quite convenient to restore a single database from a full backup. There is a --one-database parameter that can specify single database recovery. The following is a specific demonstration:

# View and backup all librariesmysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sbtest|
|sys|
|testdb|
| testdb2 |
+--------------------+

mysqldump -uroot -pxxxx -R -E --single-transaction --all-databases > all_db.sql

# Delete the testdb database and perform single database recovery mysql> drop database testdb;
Query OK, 36 rows affected (2.06 sec)

# If the testdb database does not exist before recovery, you need to manually create mysql -uroot -pxxxx --one-database testdb < all_db.sql

In addition to the above methods, you can also use manual screening to restore a single database or table. At this time, the famous sed and grep commands in Linux come in handy. We can use these two commands to filter out the statements of a single database or a single table from the full backup. The screening method is as follows:

# Restore a single database from a full backup sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_db.sql > testdb.sql

# Filter out single-table statements cat all_db.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > /tmp/test_tb_info.sql 
cat all_db.sql | grep --ignore-case 'insert into `test_tb`' > /tmp/test_tb_data.sql

2. Be careful of pitfalls

The above-mentioned method of manually filtering to restore a single database or a single table seems simple and convenient, but it actually hides a small pit. Let's demonstrate it in detail below:

# Back up the entire instance mysqldump -uroot -pxxxx -R -E --single-transaction --all-databases > all_db.sql

# Manually back up test_tb and then delete test_tb
mysql> create table test_tb_bak like test_tb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_tb_bak select * from test_tb;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> drop table test_tb;
Query OK, 0 rows affected (0.02 sec)

# Filter the test_db table creation and data insertion statements from the full backup cat all_db.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > test_tb_info.sql 
cat all_db.sql | grep --ignore-case 'insert into `test_tb`' > test_tb_data.sql

# Check the statement and it seems to be ok cat test_tb_info.sql

DROP TABLE IF EXISTS `test_tb`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_tb` (
 `inc_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
 `col1` int(11) NOT NULL,
 `col2` varchar(20) DEFAULT NULL,
 `col_dt` datetime DEFAULT NULL,
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
 PRIMARY KEY (`inc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='test table';
/*!40101 SET character_set_client = @saved_cs_client */;

cat test_tb_data.sql

INSERT INTO `test_tb` VALUES (1,1001,'dsfs','2020-08-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(2,1002,'vfsfs','2020-09-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(3,1003,'adsfsf',NULL,'2020-09-17 06:19:27','2020-09-17 06:19:27'),
(4,1004,'walfd','2020-09-17 14:19:27','2020-09-17 06:19:27','2020-09-18 07:52:13');

# Execute single table recovery operation mysql -uroot -pxxxx testdb < test_tb_info.sql
mysql -uroot -pxxxx testdb < test_tb_data.sql

# View the restored data and compare it with the backup tablemysql> select * from test_tb;
+--------+------+--------+---------------------+---------------------+---------------------+
| inc_id | col1 | col2 | col_dt | create_time | update_time |
+--------+------+--------+---------------------+---------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 06:19:27 | 2020-09-18 07:52:13 |
+--------+------+--------+---------------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from test_tb_bak;
+--------+------+--------+---------------------+---------------------+---------------------+
| inc_id | col1 | col2 | col_dt | create_time | update_time |
+--------+------+--------+---------------------+---------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+---------------------+---------------------+---------------------+
4 rows in set (0.00 sec)

If you observe carefully, you will find that there is something wrong with the recovered data. It seems that the time is not right. If you look carefully again, you will find that some of the time is off by 8 hours! After detailed investigation, we found that there was a problem with the time data recovery of the timestamp type field. To be precise, the backup file recorded the 0 time zone, but our system generally uses the East 8 zone, so the error of 8 hours occurred.

Then you may ask, why there will be no problem if all are restored? Good question. Let’s take a look at the backup file to find out.

# Backup file beginning -- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
--Server version 5.7.23-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */; 
Note the two lines above /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# End of backup file /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-09-18 15:56:40

If you look carefully at the backup file, you will find that in the file backed up by mysqldump, the session time zone will first be changed to 0, and then changed back to the original time zone at the end. This means that the timestamp data recorded in the backup file is based on the 0 time zone. If the filtered SQL is executed directly, the timestamp of the 0 time zone will be inserted into the system of the East 8th zone, which will obviously cause a time difference of 8 hours.

I wonder if you understand what I have read here. Those who have experience in backup and recovery may find it easier to understand. The solution to the above problem is also very simple, that is, before executing the SQL file, change the current session time zone to 0. Let's demonstrate again:

# Clear the test_db table datamysql> truncate table test_tb;
Query OK, 0 rows affected (0.02 sec)

# Add the time zone declaration at the beginning of the file vim test_tb_data.sql
set session TIME_ZONE='+00:00';
INSERT INTO `test_tb` VALUES (1,1001,'dsfs','2020-08-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(2,1002,'vfsfs','2020-09-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(3,1003,'adsfsf',NULL,'2020-09-17 06:19:27','2020-09-17 06:19:27'),
(4,1004,'walfd','2020-09-17 14:19:27','2020-09-17 06:19:27','2020-09-18 07:52:13');

# Execute recovery and compare to find that the data is correctmysql> select * from test_tb;
+--------+------+--------+---------------------+---------------------+---------------------+
| inc_id | col1 | col2 | col_dt | create_time | update_time |
+--------+------+--------+---------------------+---------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+---------------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from test_tb_bak;
+--------+------+--------+---------------------+---------------------+---------------------+
| inc_id | col1 | col2 | col_dt | create_time | update_time |
+--------+------+--------+---------------------+---------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+---------------------+---------------------+---------------------+
4 rows in set (0.00 sec)

Summarize:

It is easy to search for methods to restore a single database or table on the Internet. Most of them mention the above-mentioned method of manual screening using sed and grep commands. But most articles don't mention possible problems. If your table field has timestamp type, you should be extra careful when using this method. No matter what kind of recovery needs we face, we must be extra careful not to make the situation worse the more we recover. It is best to have an empty instance to practice before recovery.

The above is the details of how to restore a single database or a single table in MySQL, as well as the pitfalls that may be encountered. For more information about restoring a single database or a single table in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: