Nisarg Upadhyay
Content of backup file

How to backup and restore MySQL databases using the mysqldump command

May 12, 2020 by

In this article, I am going to explain different ways to generate the backup in the MySQL database server. As we know, data is a valuable asset to the organization. As a database administrator, it is our primary and crucial job to keep the data available and safe. If the system or data center fails, database corruption, and data loss, we must be able to recover it within the defined SLA.

Different database platforms provide various methods to generate the backup and restore the database. Many vendors provide state of the art software and hardware solutions that can help to back up the database, and it can restore the database within the defined RTO and RPO.

Here, we are not going to discuss any third-party vendor’s backup solutions. I am going to explain about the native methods that are used to generate the backup of the database. We can generate the backup of the MySQL database using any of the following methods:

  1. Generate the backup using mysqldump utility
  2. Generate Incremental backups using Binary Log
  3. Generate backups using the Replication of Slaves

In this article, I am going to explain how we can use mysqldump to generate the backup of the MySQL database.

Generate backup using mysqldump utility

Mysqldump is a command-line utility that is used to generate the 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 the XML, delimited text, or CSV format.

This command is easy to use, but the only problem that occurs while restoring the database. As I mentioned, when we generate a backup of the MySQL database, it creates a backup file that contains SQL commands that necessary to rebuild or restore the database. Now, when we restore the database, the command executes all the SQL Statements to create tables and insert the data. If you have a large database, then the restoration process takes a long time to complete.

Note: By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database.

If you want to include the information_schema tables, you must explicitly specify the name of the database in the mysqldump command, also include the —skip-lock-tables option.

There are lots of options and features that can be used with mysqldump. You can view the complete list of options here. I am going to some of the basic features. Following is the syntax of the mysqldump utility.

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

The parameters are as following:

  1. -u [user_name]: It is a username to connect to the MySQL server. To generate the backup using mysqldump, ‘Select‘ to dump the tables, ‘Show View‘ for views, ‘Trigger‘ for the triggers. If you are not using —single-transaction option, then ‘Lock Tables‘ privileges must be granted to the user
  2. -p [password]: The valid password of the MySQL user
  3. [option]: The configuration option to customize the backup
  4. [database name]: Name of the database that you want to take backup
  5. [table name]: This is an optional parameter. If you want to take the backup specific tables, then you can specify the names in the command
  6. “<” 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
  7. [dumpfilename.sql]: Path and name of the backup file. As I mentioned, we can generate the backup in XML, delimited text, or SQL file so we can provide the extension of the file accordingly

Generate the backup of a single database

For example, you want to generate the backup of the single database, run the following command. The command will generate the backup of the “sakila” database with structure and data in the sakila_20200424.sql file.

mysqldump -u root -p sakila > C:\MySQLBackup\sakila_20200424.sql

When you run this command, it prompts for the password. Provide the appropriate password. See the following image:

Generate backup of database

Once backup generated successfully, let us open the backup file to view the content of the backup file. Open the backup location and double-click on the “sakila_20200424.sql” file.

Content of 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.

Generate the backup of multiple databases or all the databases

For example, you want to generate a backup of more than one database. You must add the databases option in the mysqldump command. The following command will generate the backup of “sakila” and “employees” database with structure and data.

mysqldump -u root -p –databases sakila employees > C:\MySQLBackup\sakila_employees_20200424.sql

See the following image:

Backup multiple database

Similarly, if you want to generate the backup of all the databases, you must use –all-databases option in the mysqldump command. The following command will generate the backup of all databases within MySQL Server.

mysqldump -u root -p –all-databases > C:\MySQLBackup\all_databases_20200424.sql

See the following image:

Generating Backup of all databases

Generate the backup of database structure

If you want to generate the backup of the database structure, then you must use the –no-data option in the mysqldump command. The following command generates the backup of the database structure of the sakila database.

mysqldump -u root -p –no-data sakila > C:\MySQLBackup\sakila_objects_definition_20200424.sql

See the following image:

Generate the backup of database structure

Generate the 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 actor table of the sakila database.

mysqldump -u root -p sakila actor payment > C:\MySQLBackup\actor_payment_table_20200424.sql

If you want to generate the backup of more than one tables, than you must separate the names of the tables with space, the following command generates the backup of actor and payment table of sakila database.

Generate the backup of table

Generate the backup of database data

If you want to generate the backup of the data without the database structure, then you must use the –no-create-info option in the mysqldump command. The following command generates the backup of data of the sakila database.

mysqldump -u root -p sakila –no-create-info > C:\MySQLBackup\sakila_data_only_20200424.sql

See the following image.

Generating backup of data only

Let us view the content of the backup file.

Content of backup file

As you can see in the above screenshot, the backup file contains the various T-SQL statements that can be used to insert data in the tables.

Restore the MySQL Database

Restoring a MySQL database using mysqldump is simple. To restore the database, you must create an empty database. First, let us drop and recreate the sakila database by executing the following command.

mysql> drop database sakila;
Query OK, 24 rows affected (0.35 sec)
mysql> create database sakila;
Query OK, 1 row affected (0.01 sec)
mysql>

When you restore the database, instead of using mysqldump, you must use mysql; otherwise, the mysqldump will not generate the schema and the data. Execute the following command to restore the sakila database:

mysql -u root -p sakila < C:\MySQLBackup\sakila_20200424.sql

Once command executes successfully, execute the following command to verify that all objects have been created on the sakila database.

mysql> use sakila;
Database changed
mysql> show tables;

See the following image:

Content of database which is restored

Restore a specific table in the database

For instance, someone dropped a table from the database. Instead of restoring the entire database, we can restore the dropped table from the available backup. To demonstrate, drop the actor table from the sakila database by executing the following command on the MySQL command-line tool.

mysql> use sakila;
Database changed
mysql> drop table actor;

To restore the actor table, perform the following step by step process.

Step 1 :

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

mysql> create database sakila_dummy;
mysql> use sakila_dummy;
mysql> source C:\MySQLBackup\sakila_20200424.sql

Step 2:

Backup the actor table to sakila_dummy_actor_20200424.sql file. Following is the command

C:\Users\Nisarg> mysqldump -u root -p sakila_dummy actor > C:\MySQLBackup\sakila_dummy_actor_20200424.sql

Step 3:

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

mysql> source C:\MySQLBackup\sakila_dummy_actor_20200424.sql

Execute the following command to verify the table has been restored successfully.

mysql> use sakila;
Database changed
mysql> show tables;

See the following image:

A table have been restored

Summary

In this article, I have explained how we can use the mysqldump command-line utility to generate the following:

  1. The backup of MySQL database, table, or the structure of the database
  2. Restore the MySQL database or table from the backup

Nisarg Upadhyay
Backup and restore, MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

693 Views