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 procedures, 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 consistencet & 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 the below command to take multiple database backups 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 takes 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 adds 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 the below command to take a single table backup. This command adds 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 the 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 adds 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.

BACKUP, BACKUP AND RECOVERY, MYSQL

Mysql Logical Backup using mysqldump – Export

Related Articles