Mysql Logical Backup using mysqldump – Import
RESTORE
This blog post is a continuation of the previous blog post Mysql Logical Backup using mysqldump – Export Click here to read more.
In the previous blog, we saw How to perform the export of the Database and Table. Now in this post, we will perform How to do an import of the same dump files.
Below are backup dumpfiles we created using mysqldump backup utility.
[root@test-machine01 backup]# ls -ltr
total 1211856
drwxr-xr-x. 4 root root 4096 Sep 7 07:24 test_db-master
-rw-r--r--. 1 root root 36688498 Jan 18 10:46 master.zip
-rw-r--r--. 1 root root 169504904 Jan 18 12:45 alldbdump.sql
-rw-r--r--. 1 root root 169505202 Jan 18 12:52 alldb_allobjects_consdump.sql
-rw-r--r--. 1 root root 169505202 Jan 18 13:04 alldb_allobjects_dump.sql
-rw-r--r--. 1 root root 168380599 Jan 18 13:08 employees_repldb_dump.sql
-rw-r--r--. 1 root root 168375919 Jan 18 13:10 employeesdb_dump.sql
-rw-r--r--. 1 root root 3699 Jan 18 13:51 employeesmultitable_dump.sql
-rw-r--r--. 1 root root 154544978 Jan 18 14:03 employee_ignoretable_dump.sql
-rw-r--r--. 1 root root 2775 Jan 18 14:03 employeesspecifictable_dump.sql
-rw-r--r--. 1 root root 2066 Jan 18 14:10 employee_specificrows_dump.sql
-rw-r--r--. 1 root root 1693 Jan 18 14:14 skip_dropcreate_table_dump.sql
-rw-r--r--. 1 root root 47777 Jan 18 14:16 onlyschema_nodata_alldbdump.sql
-rw-r--r--. 1 root root 168377347 Jan 18 14:20 onlydata_noschema_alldbdump.sql
-rw-r--r--. 1 root root 35965794 Jan 18 14:46 alldbdump.sql.gz
To perform import we have created a 2nd mysql instance in the same server. Click here to read more.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
Compressed Backup Import: Use the below command to import the compressed backup.
[root@test-machine01 backup]# zcat alldbdump.sql.gz | mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock
Enter password:
[root@test-machine01 backup]#
Full DB backup import: Please note by default mysqldump utility add CREATE DATABASE /IF NOT EXISTS/ statement in dumpfile. So if DB doesn’t exist it will be created.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < alldb_allobjects_consdump.sql
Enter password:
[root@test-machine01 backup]#
Import Specific Databases from full backup: Use option –one-database to extract a single DB from all or multiple DB backup. Please note –one-database works only for DB backup which already exists in dumpfile and db should be created before attempting import command otherwise you will get below error.. If you try to import in a different DB name it will not import any objects. Please refer below.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock --one-database employees < employees_repldb_dump.sql
Enter password:
ERROR 1049 (42000): Unknown database 'employees'
[root@test-machine01 backup1]#
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock --one-database employees < employees_repldb_dump.sql
Enter password:
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock --one-database employees < alldb_allobjects_consdump.sql
Enter password:
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock --one-database employees_test1 < employees_repldb_dump.sql
Enter password:
ERROR 1049 (42000): Unknown database 'employees_test1'
[root@test-machine01 backup]#
mysql> create database employees_test1;
Query OK, 1 row affected (0.00 sec)
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock --one-database employees_test1 < employees_repldb_dump.sql
Enter password:
[root@test-machine01 backup]#
mysql> use employees_test1;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>
Import DB in Same or Different database Name: This option work only for backup which was taken without –all-databases or –databases options.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> create database employees;
Query OK, 1 row affected (0.01 sec)
mysql> exit
Bye
[root@test-machine01 backup]#
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees < employeesdb_dump.sql
Enter password:
[root@test-machine01 backup]#
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test2 < employeesdb_dump.sql
Enter password:
ERROR 1049 (42000): Unknown database 'employees_test2'
[root@test-machine01 backup]#
mysql> create database employees_test2;
Query OK, 1 row affected (0.00 sec)
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test2 < employeesdb_dump.sql
Enter password:
[root@test-machine01 backup]#
mysql> use employees_test2;
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>
mysql> show tables;
+---------------------------+
| Tables_in_employees_test2 |
+---------------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------------+
8 rows in set (0.00 sec)
mysql>
Table Import in same or different DB: Please note by default mysqldump add drop and create table statement in dumpfile. Use the below commands to import the table dumpfile.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees < employeesmultitable_dump.sql
Enter password:
[root@test-machine01 backup]#
mysql> use employees_test1
Database changed
mysql> show tables;
Empty set (0.00 sec)
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test1 < employeesmultitable_dump.sql
Enter password:
[root@test-machine01 backup]#
mysql> select database();
+-----------------+
| database() |
+-----------------+
| employees_test1 |
+-----------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_employees_test1 |
+---------------------------+
| departments |
+---------------------------+
1 row in set (0.01 sec)
Import Table-Rows-Only dump: For Table-Row-Only dump, Table definition should exist in DB otherwise you will get the below error.
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test1 < skip_dropcreate_table_dump.sql
Enter password:
ERROR 1146 (42S02) at line 22: Table 'employees_test1.departments' doesn't exist
[root@test-machine01 backup]#
mysql> select database();
+-----------------+
| database() |
+-----------------+
| employees_test1 |
+-----------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_employees_test1 |
+---------------------------+
| departments |
+---------------------------+
1 row in set (0.00 sec)
mysql> truncate table departments;
Query OK, 0 rows affected (0.04 sec)
mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
mysql>
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test1 < skip_dropcreate_table_dump.sql
Enter password:
[root@test-machine01 backup]#
mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.03 sec)
mysql>
Importing schema only and then Data: Use the below commands to import schema definition first and then load data.
mysql> drop database employees;
Query OK, 8 rows affected (0.07 sec)
mysql> drop database repl_test;
Query OK, 4 rows affected (0.01 sec)
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test1 < onlyschema_nodata_alldbdump.sql
Enter password:
[root@test-machine01 backup]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| employees_test1 |
| employees_test2 |
| employees_test3 |
| information_schema |
| mysql |
| performance_schema |
| repl_test |
| sys |
+--------------------+
9 rows in set (0.00 sec)
mysql> use employees;
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_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
[root@test-machine01 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees_test1 < onlydata_noschema_alldbdump.sql
Enter password:
[root@test-machine01 backup]#
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.93 sec)
mysql>
Part I: Mysql Logical Backup using mysqldump – Export
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
