Tag Archives: Drop Table in mysql

How To Restore Single Table From Full Database Backup in MySQL

How To Restore Single Table From Full Database Backup in MySQL?

Table of Contents
___________________________________________________________________________________________________

1. Issue Description
2. Data collection

Issue Simulation

3. Take MySQL Database Full Backup?
4. Drop Table
5. How To Restore Single Table From Full Database Backup in MySQL?
6. Verify
_______________________________________________________________________________________


1. Issue Description

One of your member by mistake DELETED main application Table, but we have a recent backup of the Database that contains this Table, which backup was made a few days ago.

So question is How To Restore Single Table From Full Database Backup in MySQL? Since we do not want to overwrite the other Tables that are up to date.


2. Data collection

Database backup taken using mysqldump, Database backup file contains SQL statements only.

ALL CREATE TABLE statements start with DROP TABLE IF EXISTS and then CREATE TABALE and then INSERT STATEMENTS and it will end with keyword UNLOCK TABLES

I want to Print all the lines between DROP TABLE IF EXISTS and the next UNLOCK TABLES occurrence from the databases_backup_orcl.sql file into a new file orcl_test_data.sql, but How?

sed - stream editor for filtering and transforming text

The option -n disables automatic printing
The option -e --expression=script -- we can use Sed addresses form /PATTERN/,/PATTERN/, which means that we are targeting a range of lines.

sed -n -e '/DROP TABLE.*`test_data`/,/UNLOCK TABLES/p' databases_backup_orcl.sql > orcl_test_data.sql

-- then restore table using below command
mysql orcl < orcl_test_data1.sql

Issue Simulation


3. Take MySQL Database Full Backup?

[root@rac1 ~]# mysqldump orcl > /u01/mysql/data/backup/databases_backup_orcl.sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/databases_backup_orcl.sql
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 /u01/mysql/data/backup/databases_backup_orcl.sql
[root@rac1 ~]#


4. Drop Table

[root@rac1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT table_name AS "Table",
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "orcl"
    -> ORDER BY (data_length + index_length) DESC;
+------------+-----------+
| Table      | Size (MB) |
+------------+-----------+
| test_data  |     41.58 | <----- This table we will drop now.
| test_data2 |     38.58 |
| data       |      8.52 |
+------------+-----------+
3 rows in set (0.01 sec)

mysql>

mysql> select count(*) from test_data;
+----------+
| count(*) |
+----------+
|  1000000 | <----- Note down the count
+----------+
1 row in set (0.14 sec)

mysql>

mysql> DROP TABLE test_data;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data2     |
+----------------+
2 rows in set (0.00 sec)

mysql>


5. How To Restore Single Table From Full Database Backup in MySQL?

[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 83000
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
[root@rac1 backup]#
[root@rac1 backup]# sed -n -e '/DROP TABLE.*`test_data`/,/UNLOCK TABLES/p' databases_backup_orcl.sql > orcl_test_data.sql
[root@rac1 backup]#
[root@rac1 backup]# ls -ltr
total 120768
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
-rw-r--r--. 1 root root 38671152 Mar  9 00:58 orcl_test_data.sql
[root@rac1 backup]#

-- Verify the orcl_test_data.sql file before going to start restore.

[root@rac1 backup]# cat orcl_test_data.sql | head -10
DROP TABLE IF EXISTS `test_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `datetime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `channel` int DEFAULT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
[root@rac1 backup]#

[root@rac1 backup]# cat orcl_test_data.sql | tail -1
UNLOCK TABLES;
[root@rac1 backup]#

[root@rac1 backup]# cat orcl_test_data.sql | egrep 'CREATE TABLE|UNLOCK'
CREATE TABLE `test_data` (
UNLOCK TABLES;
[root@rac1 backup]#

[root@rac1 backup]# ls -ltr
total 120768
-rw-r--r--. 1 root root 84988986 Mar  9 00:42 databases_backup_orcl.sql
-rw-r--r--. 1 root root 38671152 Mar  9 00:58 orcl_test_data.sql
[root@rac1 backup]#
-- Now restore the Table test_data to database orcl.
[root@rac1 backup]# mysql orcl < orcl_test_data.sql
[root@rac1 backup]#


6. Verify

[root@rac1 backup]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use orcl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_orcl |
+----------------+
| data           |
| test_data      |  <--- Table restored. 
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from test_data;
+----------+
| count(*) |
+----------+
|  1000000 | <---- Table count matching.
+----------+
1 row in set (0.14 sec)

mysql>

mysql> SELECT table_name AS "Table",
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "orcl"
    -> ORDER BY (data_length + index_length) DESC;
+------------+-----------+
| Table      | Size (MB) |
+------------+-----------+
| test_data  |     41.58 |  <---- 
| test_data2 |     38.58 |
| data       |      8.52 |
+------------+-----------+
3 rows in set (0.00 sec)

mysql>

We have successfully restored missing table from full backup without overwriting the other tables data. 🙂

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/