Mysql Logical Backup using mysqlpump – Import

February 13, 2021
()

Mysql Logical Backup using mysqlpump – Import


This blog post is a continuation of the previous blog post Mysql Logical Backup using mysqlpump – Export, Click here to read more.


mysql utility is used to import mysqlpump dump files so import commands are the same like we used in mysqldump.


In the previous blog, we saw How to perform the export of the Database and Table using mysqlpump. Now in this post, we will perform the import of the same dump files.


One of the differences with mysqldump is that mysqlpump adds CREATE DATABASE statements to the dump by default, unless specified with the –no-create-db option. mysqlpump also includes the database name while adding the CREATE TABLE statement.


Below are backup dumpfiles we created using mysqlpump backup utility.

[root@test-machine01 backup2]#  ls -ltr
total 1300780
-rw-r--r--. 1 root root 170126435 Jan 31 12:18 alldbdump.sql
-rw-r--r--. 1 root root  72578407 Jan 31 12:25 lz4compress_alldbdump.sql
-rw-r--r--. 1 root root  36918513 Jan 31 12:26 zlibcompress_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:47 parallel1_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:57 parallel2_alldbdump.sql
-rw-r--r--. 1 root root 169040795 Jan 31 14:07 employees_repldb_dump.sql
-rw-r--r--. 1 root root 169038676 Jan 31 14:08 employeesdb_dump.sql
-rw-r--r--. 1 root root      2775 Jan 31 14:09 employeesspecifictable_dump.sql
-rw-r--r--. 1 root root      3073 Jan 31 14:17 db_include.sql
-rw-r--r--. 1 root root  35227619 Jan 31 14:19 exclude_table.sql
-rw-r--r--. 1 root root 169037182 Jan 31 14:21 skip_dropcreate_table_dump.sql
-rw-r--r--. 1 root root     15654 Jan 31 14:23 onlyschema_nodata_alldbdump.sql
-rw-r--r--. 1 root root 169728790 Jan 31 14:24 onlydata_noschema_alldbdump.sql


To perform import we have created a 2nd mysql instance in the same server. Click here to read more.

[root@test-machine01 backup2]# mysql -u root -p -S /u01/mysql-2/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


Full DB backup import: Please note by default mysqlpump utility add CREATE DATABASE/IF NOT EXISTS statement in dumpfile. So if DB doesn’t exist it will be created.

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < alldbdump.sql
Enter password:
[root@test-machine01 backup2]#

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)


Single Database Import: Use the below command to import a single DB backup. Both commands will work, For the 2nd command, you need to create DB employees before executing the import command

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock  < employeesdb_dump.sql
Enter password:
[root@test-machine01 backup2]#

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees < employeesdb_dump.sql
Enter password:
[root@test-machine01 backup2]#


Compressed Backup Import: For compressed backup, you need to first decompress them and then run import commands.

Usage: lz4_decompress input_file output_file

[root@test-machine01 backup2]# lz4_decompress lz4compress_alldbdump.lz4 lz4compress_alldbdump.sql

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < lz4compress_alldbdump.sql
Enter password:
[root@test-machine01 backup2]#

Usage: zlib_decompress input_file output_file

[root@test-machine01 backup2]# zlib_decompress zlibcompress_alldbdump.zlib zlibcompress_alldbdump.sql

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < zlibcompress_alldbdump.sql
Enter password:
[root@test-machine01 backup2]#


Table Backup Import: You need to drop the table manually or you can add –add-drop-table option before taking table-level backup.

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < departmentstable1_dump.sql
Enter password:
ERROR 1050 (42S01) at line 23: Table 'departments' already exists

mysql> drop table departments;
Query OK, 0 rows affected (0.01 sec)

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < departmentstable1_dump.sql
Enter password:
[root@test-machine01 backup2]#


[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees < departmentstable1_dump.sql
Enter password:
[root@test-machine01 backup2]#


Import Only data no Create Table dump: I am doing truncate on the table first and then importing data using the below command.

mysql> truncate table departments;
Query OK, 0 rows affected (0.01 sec)

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock employees <  skip_dropcreate_table_dump.sql
Enter password:
[root@test-machine01 backup2]#

mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
|        9 |
+----------+


Only Schema no Data dumpfile import

[root@test-machine01 backup2]#  mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < onlyschema_nodata_alldbdump.sql
Enter password:
[root@test-machine01 backup2]#


Only Data no Schema dumpfile import

[root@test-machine01 backup2]# mysql -hlocalhost -uroot -p -S/u01/mysql-2/mysql.sock < onlydata_noschema_alldbdump.sql 
Enter password:
[root@test-machine01 backup2]#


Part I : Mysql Logical Backup using mysqlpump – 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.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *