Mysql Logical Backup using mysqlpump – Export

February 1, 2021
()

Mysql Logical Backup using mysqlpump – Export

In this article, we are going to demonstrate Mysql Logical Backup using mysqlpump – Export. 

There are several ways to backup MySQL data.

Those are: –
                   1) Physical Backup
                   2) Logical Backup


Physical Backup :
It copies the directories and its files that have database contents. Physical backup is also called Raw Backup.


Logical Backup :

It converts all the available rows into single or multiple inserts and also contains create statements to create databases, tables, triggers, and so on. Logical backup also called Text Backup.

List of utilities available for logical backup: – 

              A)  Mysqldump    Click here to read more 
              B)  Mysqlpump
              C)  Mydumper      Click here to read more
              D)  Mysqlshell      Click here to read more


Mysqlpump:

The Mysqlpump client utility performs logical backups, producing a group of SQL statements that will be executed to breed the primary database object definitions and table data.

For demonstration, we have already created a sample database click here to read more


Advantage of mysqlpump over mysqldump :

  • Native support to backup compression.
  • Native support to parallelism by default is 2.
  • By default, all objects are backed up including routine, triggers, events, no need to specify –routines –events –triggers like in mysqldump.
  • Show backup progress.
  • Support –include-database, –exclude-database, –include-table, –exclude-table with pattern matching(%).
  • By default doesn’t add the drop table command in the dump file. You need to specify the parameter explicitly by –add-drop-table.


Full DB Backup: Use the below commands to take full mysql instance backup except for information_schema and performance_schema.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p  --all-databases > /u01/backup2/alldbdump.sql
Enter password:
Dump progress: 0/3 tables, 250/331176 rows
Dump progress: 4/31 tables, 1399627/3916165 rows
Dump progress: 30/31 tables, 2755761/3916165 rows
Dump completed in 2928
[root@test-machine01 backup2]# ls -l /u01/backup2/alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:18 /u01/backup2/alldbdump.sql
[root@test-machine01 backup2]#


Compressed Backup: Use the below command to create a compressed backup. You can use either LZ4 or ZLIB compression algorithm.
Please note LZ4 uses less time but provides less compression level on the contrary ZLIB takes more time but provides a high level of compression.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p  --all-databases --compress-output=lz4 > /u01/backup2/lz4compress_alldbdump.sql
Enter password:
Dump progress: 1/3 tables, 0/331176 rows
Dump progress: 4/31 tables, 1641377/3916165 rows
Dump progress: 30/31 tables, 3221511/3916165 rows
Dump completed in 2532
[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p  --all-databases --compress-output=zlib > /u01/backup2/zlibcompress_alldbdump.sql
Enter password:
Dump progress: 1/2 tables, 0/331152 rows
Dump progress: 2/31 tables, 307000/3916165 rows
Dump progress: 3/31 tables, 644774/3916165 rows
Dump progress: 4/31 tables, 1074627/3916165 rows
Dump progress: 4/31 tables, 1509877/3916165 rows
Dump progress: 30/31 tables, 1878511/3916165 rows
Dump progress: 30/31 tables, 2279261/3916165 rows
Dump progress: 30/31 tables, 2679761/3916165 rows
Dump progress: 30/31 tables, 3081511/3916165 rows
Dump progress: 30/31 tables, 3482261/3916165 rows
Dump progress: 30/31 tables, 3885761/3916165 rows
Dump completed in 10862
[root@test-machine01 backup2]#

[root@test-machine01 backup2]# ls -l *compress*
-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
[root@test-machine01 backup2]#


Parallel Backup: Use command to set the degree of parallelism –default-parallelism, You can even set parallelism to individual database/schema using command –parallel-schemas.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p --all-databases --default-parallelism=4 > /u01/backup2/parallel1_alldbdump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 30/31 tables, 1798761/3916165 rows
Dump progress: 30/31 tables, 3460261/3916165 rows
Dump completed in 2357
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/parallel1_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:47 /u01/backup2/parallel1_alldbdump.sql
[root@test-machine01 backup2]#


[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p -uroot -p --all-databases --default-parallelism=2 --parallel-schemas=4:employees  > /u01/backup2/parallel2_alldbdump.sql
Enter password:
Enter password:
Dump progress: 1/2 tables, 0/331152 rows
Dump progress: 30/31 tables, 1756261/3916165 rows
Dump progress: 30/31 tables, 3407511/3916165 rows
Dump completed in 2416
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/parallel2_alldbdump.sql
-rw-r--r--. 1 root root 170126435 Jan 31 12:57 /u01/backup2/parallel2_alldbdump.sql
[root@test-machine01 backup2]#


Multiple Databases: Use the below command to take multiple database backups but not all.

[root@test-machine01 backup2]# mysqlpump  -hlocalhost -uroot -p  --databases employees repl_test  >/u01/backup2/employees_repldb_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 2/11 tables, 496000/3912633 rows
Dump progress: 4/11 tables, 944127/3912633 rows
Dump progress: 4/11 tables, 1292127/3912633 rows
Dump progress: 10/11 tables, 1706185/3912633 rows
Dump progress: 10/11 tables, 2323935/3912633 rows
Dump progress: 10/11 tables, 2756935/3912633 rows
Dump progress: 10/11 tables, 3327185/3912633 rows
Dump progress: 10/11 tables, 3458685/3912633 rows
Dump progress: 10/11 tables, 3555685/3912633 rows
Dump progress: 10/11 tables, 3768685/3912633 rows
Dump progress: 10/11 tables, 3888685/3912633 rows
Dump completed in 13137
[root@test-machine01 backup2]# ls -l /u01/backup2/employees_repldb_dump.sql
-rw-r--r--. 1 root root 169040795 Jan 31 14:07 /u01/backup2/employees_repldb_dump.sql
[root@test-machine01 backup2]#


Single database: Use the below command to take a single DB backup.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p employees >/u01/backup2/employeesdb_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 2/6 tables, 85000/3912591 rows
Dump progress: 2/6 tables, 154250/3912591 rows
Dump progress: 2/6 tables, 309500/3912591 rows
Dump progress: 2/6 tables, 490750/3912591 rows
Dump progress: 4/6 tables, 688377/3912591 rows
Dump progress: 4/6 tables, 960627/3912591 rows
Dump progress: 4/6 tables, 1201877/3912591 rows
Dump progress: 5/6 tables, 1836935/3912591 rows
Dump progress: 5/6 tables, 2328685/3912591 rows
Dump progress: 5/6 tables, 2922185/3912591 rows
Dump progress: 5/6 tables, 3405935/3912591 rows
Dump completed in 12451
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/employeesdb_dump.sql
-rw-r--r--. 1 root root 169038676 Jan 31 14:08 /u01/backup2/employeesdb_dump.sql
[root@test-machine01 backup2]#


Multiple Tables: Use the below command to take multiple tables backup.

[root@test-machine01 backup2]# mysqlpump  -hlocalhost -uroot -p employees current_dept_emp departments > /u01/backup2/employeesspecifictable_dump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump completed in 355
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/employeesspecifictable_dump.sql
-rw-r--r--. 1 root root 2775 Jan 31 14:09 /u01/backup2/employeesspecifictable_dump.sql
[root@test-machine01 backup2]#


Single Table: Use the below command to take a single table backup.

[root@test-machine01 backup2]# mysqldump  -hlocalhost -uroot -p employees current_dept_emp > /u01/backup2/employeesspecifictable_dump.sql
Enter password:
[root@test-machine01 backup2]#


Exclude/Include database objects using regex: Mysqlpump support exclude and include commands you can also combine these commands with regex expression to include or exclude pattern of objects.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p --include-databases=repl% --result-file=/u01/backup2/db_include.sql
Enter password:
Dump progress: 1/4 tables, 0/42 rows
Dump completed in 76
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/db_include.sql
-rw-r--r--. 1 root root 3073 Jan 31 14:17 /u01/backup2/db_include.sql
[root@test-machine01 backup2]#

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p --include-databases=emp% --exclude-tables=departments,employees,salaries --result-file=/u01/backup2/exclude_table.sql
Enter password:
Dump progress: 0/2 tables, 250/331167 rows
Dump completed in 496
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/exclude_table.sql
-rw-r--r--. 1 root root 35227619 Jan 31 14:19 /u01/backup2/exclude_table.sql
[root@test-machine01 backup2]#


Only data no Create Table and Drop Table command.

[root@test-machine01 backup2]# mysqlpump -hlocalhost -uroot -p  --skip-add-drop-table --no-create-info --databases employees departments > /u01/backup2/skip_dropcreate_table_dump.sql        
Enter password:
Dump progress: 1/4 tables, 0/630599 rows
Dump progress: 5/6 tables, 1800935/3912591 rows
Dump progress: 5/6 tables, 3254935/3912591 rows
Dump progress: 5/6 tables, 3822185/3912591 rows
Dump completed in 3373
[root@test-machine01 backup2]# ls -l /u01/backup2/skip_dropcreate_table_dump.sql
-rw-r--r--. 1 root root 169037182 Jan 31 14:21 /u01/backup2/skip_dropcreate_table_dump.sql
[root@test-machine01 backup2]#


Only Schema no Data.

[root@test-machine01 backup2]# mysqlpump  -hlocalhost -uroot -p  --all-databases  --skip-dump-rows > /u01/backup2/onlyschema_nodata_alldbdump.sql
Enter password:
Dump completed in 169
[root@test-machine01 backup2]# ls -l /u01/backup2/onlyschema_nodata_alldbdump.sql
-rw-r--r--. 1 root root 15654 Jan 31 14:23 /u01/backup2/onlyschema_nodata_alldbdump.sql
[root@test-machine01 backup2]#


Only Data no Schema.

[root@test-machine01 backup2]# mysqlpump  -hlocalhost -uroot -p  --databases employees --no-create-db --no-create-info --complete-insert > /u01/backup2/onlydata_noschema_alldbdump.sql
Enter password:
Dump progress: 1/1 tables, 0/9 rows
Dump progress: 5/6 tables, 1793435/3912591 rows
Dump progress: 5/6 tables, 3459435/3912591 rows
Dump completed in 2479
[root@test-machine01 backup2]#
[root@test-machine01 backup2]# ls -l /u01/backup2/onlydata_noschema_alldbdump.sql
-rw-r--r--. 1 root root 169728790 Jan 31 14:24 /u01/backup2/onlydata_noschema_alldbdump.sql
[root@test-machine01 backup2]#


Part II : Mysql Logical Backup using mysqlpump – Import


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?

Jamsher Khan

🌐

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 *