How To Backup And Restore MySQL Databases Using The MYSQLdump Command

MySQL - October 15, 2023

by Saurabh Soradge, MySql DBA - Data Patrol Technologies

• Importance of Backup
• Generate Backup Using MySQLdump Utility: MySQL Prerequisite 
• Syntax of the MySQLdump Command
• Backup a Single/ Multiple/ Specific Database
• Create a Compressed MySQL Database Backup
• Taking Compress MySQLdump Backup: Common options for the MySQLdump Program
• Methods to Restore the Database

 

Importance of Backup

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replica servers.

 

Generate backup using MySQLdump utility

Mysqldump is a logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in XML, delimited text, or CSV format.

Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, PROCESS (as of MySQL 8.0.21) if the --no-tablespaces option is not used, and (as of MySQL 8.0.32) the RELOAD or FLUSH_TABLES privileged with --single-transaction if both gtid_mode=ON and --set-gtid=purged=ON|AUTO.

To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

 MySQL prerequisites:

1. Space requirements

2. Sql_mode

3. Unique_checks and foreign_key_checks

4. Privileges required for running mysqldump

• SELECT
• SHOW VIEW (If any database has Views)
• TRIGGER (If any table has one or more triggers)
• LOCK TABLES (If you use an explicit --lock-tables)

5. Max_allowed_packet

 

Syntax of the MySQLdump command

$ mysqldump   -u [database_username] –p [database_password] [options] [database_name] [tablename] > [database_backup_file.sql]

• -u [database_username]: The username to connect to the MySQL server.
• -p[database_password]: The valid password of the MySQL user.
• [options]: The configuration option to customise the backup
• [database_name]: Name the database you want to take backup.
• [tablename]: Name of that table name that you want to take backup. This is an optional parameter.
• “<” OR ”>”: This character indicates whether we are generating the backup of the database or restoring the database. You can use “>” to generate the backup and “<” to restore the backup
• [database_backup_file.sql]: Path and name of the backup file that you want to save the dump file.

 

Backup a single database

$ mysqldump  -u root  -p datapatrol > datapatrol.sql

 

A dump file is generated after the backup is done

After the backup is taken successfully, let us open the backup file to view the content of the backup file

As you can see in the above image, the backup file contains the various T-SQL statements that can be used to re-create the objects.

This script starts with some comments that give some general information about this backup. For example, the first line includes information about the mysqldump program.

Then, the third line identifies the host (localhost) and the database (datapatrol). Unfortunately, only the first database in the backup is listed, Which is probably a bug. Finally, the fifth line identifies the version of the MySQL server.

 

Backup of multiple databases or all the databases

$ mysqldump  -u root  -p --databases test Saurabh Shubham datapatrol > databases_26062023.sql

or

$ mysqldump  -u root  -p --all-databases >all_databases_26062023.sql

 

Backup of a specific table

If you want to generate the backup of a specific table, then you must specify the name of the tables after the name of the database. The following command generates the backup of the employee table of the datapatrol database.

$ mysqldump  -u root  -p datapatrol employee > datapatrol_employee.sql

 

Create a compressed MySQL database backup

If your database size is large then you can compress the output to save disk space. You will need to simply pipe the output to the gzip utility and redirect it to a file. Here is the syntax. 

$ mysqldump   -u [database_username] –p [database_password] [options] [database_name] [tablename] | gzip > file_name.sql.gz

 

Taking compressed MySQLdump backup

$ mysqldump  -u root  -p datapatrol | gzip > datapatrol.sql.gz

 

As we see above our datapatrol.sql.gz compressed output file was created 

Common options for the MySQLdump program

Restore a MySQL database using MySQLdump

Restoring a dump file made with mysqldump is just a matter of using the MySQL client to execute all of the SQL statements contained in the dump file.

You can restore all of the databases, a single database, individual tables, or even specific rows of data.

MYSQL database restoration syntax

$ mysql   -u [database_username] –p [database_password] [database_name] < [File_name]

 

There are various methods to restore the database

1. Using MySQL command

$ Mysql  -u root -p datapatrol < datapatrol.sql

 

2. Using the source command

Login to the database first

$ Mysql> Source file_name

 

Restore compressed MySQL database backup 

Before restoring the zip dump file it will be firstly unzipped and then restored with the help of the below command

$ Mysql  -u root -p datapatrol < datapatrol.sql

 

 

Scenario

Restore a single MySQL database from a full MySQL Dump file.

If you want to restore a single MySQL database from a Database backup file that contains multiple MySQL databases, you can use the --one-database option in the restoration command.

$ mysql -u user_name -p --one-database database_name1<all_databases_backup_file.sql

 

$ mysql -u root  -p  --one-database datapatrol <all_databases_backup_file.sql

 

Restore a specific table from a full database backup

If we want only one specific table restoration from a full backup of the database

Step 1:

Create a dummy database named datapatrol_dummy and restore the backup of the datapatrol database on it. Following is the command.  

Mysql> create database datapatrol_dummy;Mysql>use datapatrol_dummy;Mysql> source dataparol.sql

 

Step 2:

Backup the table that you want i.e., employee table to dataparol_dummy_employee_26062023.sql file. Following is the command

$ mysqldump  -u root  -p datapatrol_dummy employee > dataparol_dummy_employee_26062023.sql

 

Step 3:

Restore the employee table from the “dataparol_dummy_employee_26062023.sql ” file. Following is the command on the MySQL command-line tool.

Mysql> source dataparol_dummy_employee_26062023.sql

OR

$ mysql -u root  -p  datapatrol < dataparol_dummy_employee_26062023.sql

 

 

References:

• https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
• https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html
• https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html