Mysql Logical Backup using mysqldump – Export

Mysql Logical Backup using mysqldump – Export

In this article, we are going to demonstrate Mysql Logical Backup using mysqldump – Export. There are several ways to backup MySQL data.

They 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
              B) Mysqlpump  Click here to read more
              C) Mydumper    Click here to read more
              D) mysqlshell    Click here to read more


Mysqldump:

The mysqldump client utility performs logical backups, producing a collection of SQL statements that may be executed to breed the initial database object definitions and table data.
The mysqldump uses only 1 thread while taking backup and restore. That is the biggest drawback of it.
By default, mysqldump doesn’t guarantee consistency of backup and takes backup of tables data only. Not other objects like events routines triggers.

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

 

Full backup (Table): Use the below commands to take full mysql instance backup except for information_schema  and performance_schema.

[root@test-machine01 ~]# mysqldump -hlocalhost -uroot -p  --all-databases > /u01/backup/alldbdump.sql
Enter password:
[root@test-machine01 ~]# ls -ltr /u01/backup/alldbdump.sql
-rw-r--r--. 1 root root 169504904 Jan 18 12:45 /u01/backup/alldbdump.sql
[root@test-machine01 ~]#


Compressed Backup: Use the below command to create a compressed backup.

[root@test-machine01 backup]# mysqldump -hlocalhost -uroot -p  --all-databases | gzip - > /u01/backup/alldbdump.sql.gz
Enter password:
[root@test-machine01 backup]# ls -l /u01/backup/alldbdump.sql.gz
-rw-r--r--. 1 root root 35965794 Jan 18 14:46 /u01/backup/alldbdump.sql.gz
[root@test-machine01 backup]#


A full backup (All DB Objects): To include Stored procedure, Events, and Triggers in the backup. This command will add CREATE DATABASE/ IF NOT EXISTS/ & use db_name command in dumpfile.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --all-databases --routines --events --triggers > alldb_allobjects_dump.sql
Enter password:
[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -ltr alldb_allobjects_dump.sql
-rw-r--r--. 1 root root 169505202 Jan 18 12:50 alldb_allobjects_dump.sql
[root@test-machine01 ~]#


Consistent Backup: To get consistence & point in time recovery you should specify –single-transaction.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --all-databases --routines --events --triggers --single_transaction >/u01/backup/alldb_allobjects_consdump.sql
Enter password:
[root@test-machine01 ~]# ls -l /u01/backup/alldb_allobjects_consdump.sql
-rw-r--r--. 1 root root 169505202 Jan 18 12:52 /u01/backup/alldb_allobjects_consdump.sql
[root@test-machine01 ~]#


Multiple Databases: Use below command to take multiple database backup but not all. This command will add CREATE DATABASE/ IF NOT EXISTS/ & use db_name command in dumpfile.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --databases employees repl_test  >/u01/backup/employees_repldb_dump.sql
Enter password:
[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -l /u01/backup/employees_repldb_dump.sql
-rw-r--r--. 1 root root 168380599 Jan 18 13:08 /u01/backup/employees_repldb_dump.sql
[root@test-machine01 ~]#


Single database: Use the below command to take a single DB backup. This command take only objects backup, No CREATE DATABASE/ IF NOT EXISTS/ & use db_name command in dumpfile

[root@test-machine01 ~]# mysqldump -hlocalhost -uroot -p employees >/u01/backup/employeesdb_dump.sql
Enter password:
[root@test-machine01 ~]# ls -l /u01/backup/employeesdb_dump.sql
-rw-r--r--. 1 root root 168375919 Jan 18 13:10 /u01/backup/employeesdb_dump.sql
[root@test-machine01 ~]#


Multiple Table: Use the below command to take multiple tables backup. This command add DROP TABLE IF EXIST statement in dumpfile.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p --databases employees --tables current_dept_emp departments > /u01/backup/employeesmultitable_dump.sql
Enter password:
[root@test-machine01 ~]# ls -l /u01/backup/employeesmultitable_dump.sql
-rw-r--r--. 1 root root 3699 Jan 18 13:18 /u01/backup/employeesmultitable_dump.sql
[root@test-machine01 ~]#


Single Table: Use below command to take single table backup. This command add DROP TABLE IF EXIST statement in dumpfile.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p employees current_dept_emp > /u01/backup/employeesspecifictable_dump.sql
[root@test-machine01 backup]# ls -l /u01/backup/employeesspecifictable_dump.sql
-rw-r--r--. 1 root root 2775 Jan 18 13:50 /u01/backup/employeesspecifictable_dump.sql
[root@test-machine01 ~]#


Ignore Table: Use the below command to ignore table from DB backup.

[root@test-machine01 ~]#  mysqldump  -hlocalhost -uroot -p --databases employees  --ignore-table=employees.dept_emp  --ignore-table=employees.dept_manager >/u01/backup/employee_ignoretable_dump.sql
Enter password:
[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -l /u01/backup/employee_ignoretable_dump.sql
-rw-r--r--. 1 root root 154544978 Jan 18 14:03 /u01/backup/employee_ignoretable_dump.sql
[root@test-machine01 ~]#


Specific Table Rows: Use the below command to take specific row backup based on where condition. This command add DROP TABLE IF EXIST statement in dumpfile.

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --databases employees --tables employees --where="hire_date between '2020-01-01' and '2020-03-30'" > /u01/backup/employee_specificrows_dump.sql
Enter password:
[root@test-machine01 ~]# ls -l /u01/backup/employee_specificrows_dump.sql
-rw-r--r--. 1 root root 2066 Jan 18 14:10 /u01/backup/employee_specificrows_dump.sql
[root@test-machine01 ~]#


Only data no Create Table and Drop Table command.

[root@test-machine01 ~]# mysqldump -hlocalhost -uroot -p  --skip-add-drop-table --no-create-info --databases employees --tables departments > /u01/backup/skip_dropcreate_table_dump.sql
Enter password:
[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -l /u01/backup/skip_dropcreate_table_dump.sql
-rw-r--r--. 1 root root 1693 Jan 18 14:14 /u01/backup/skip_dropcreate_table_dump.sql
[root@test-machine01 ~]#


Only Schema no Data

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --all-databases --routines --events --triggers --no-data > /u01/backup/onlyschema_nodata_alldbdump.sql
Enter password:
[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -l /u01/backup/onlyschema_nodata_alldbdump.sql
-rw-r--r--. 1 root root 47777 Jan 18 14:16 /u01/backup/onlyschema_nodata_alldbdump.sql
[root@test-machine01 ~]#


Only Data no Schema

[root@test-machine01 ~]# mysqldump  -hlocalhost -uroot -p  --databases employees --no-create-db --no-create-info --complete-insert > /u01/backup/onlydata_noschema_alldbdump.sql
Enter password:
[root@test-machine01 ~]# ls -l /u01/backup/onlydata_noschema_alldbdump.sql
-rw-r--r--. 1 root root 168377347 Jan 18 14:20 /u01/backup/onlydata_noschema_alldbdump.sql
[root@test-machine01 ~]#


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

 914 Total Views,  1 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

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?

Spread the Knowledge!

Leave a Reply

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

thirteen − eight =

Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com