BackUp and Restore MySQL/MariaDB Database

The mysqldump is a utility used to backup and restore MySQL or MariaDB database using command line. It is creating a set of SQL statements while taking backup and those are used while restoring. In this tutorial we will show you how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.

You also can generate files in CSV and XML format using the mysqldump command. The mysqldump utility can be used to transfer your MySQL database to another MySQL server.

Mysqldump Command Syntax#

Following is the basic syntax of mysqldump utility:

mysqldump [options] > file.sql

• options – The mysqldump options
• file.sql – Name of the backup file

Ensure that to use the mysqldump command, the MySQL server must be accessible and running.

Backup a Single MySQL Database#

Generally, the mysqldump is used to backup a single database.

For example, if you would like to create a backup file of any database you would run the following command as a root user:

mysqldump -u root -p database_name > database_name.sql

Here, you should replace database_name with your database and database_name.sql is the backup file name.

It will ask you to enter the root password. On successful authentication, the process will start. It may take time depending on the size of database.

If you are already logged in as same user you can omit the -u and -p options:

mysqldump database_name > database_name.sql

Backup Multiple MySQL Databases#

You can also take the backup of multiple database in a single command using the --database option followed by the list of database you want to backup. Database name should separated by space.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

The above command will create a dump file which contains the both databases.

Backup All MySQL Databases#

To take the backup of all database use the --all-databases option:

mysqldump -u root -p --all-databases > all_databases.sql

Same as with the previous example the command above will create a single dump file containing all the databases.

Backup all MySQL databases to separate files#

There is not in-built facility to backup all databases to separate files. We can do it using the simple bash FOR loop:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    mysqldump $DB > "$DB.sql";
done

It will create a separate dump file for each database using the database name as the filename.

Create a Compressed MySQL Database Backup#

It is recommended to compress the output when the size of database is very large. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown below:

mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp#

It’s a good idea to keep back up with date. You can store more than one backup file in same location with the adding current date to the backup filename:

mysqldump database_name > database_name-$(date +%Y%m%d).sql

The command above will create a file with the following format database_name-20201003.sql

Restoring a MySQL dump#

You can restore a MySQL dump using the mysql tool. Following is the general syntax for MySQL dump:

mysql database_name < file.sql

Before restore the database you should create a database to import into it. If the database already exists, first you need to delete it.

Restore a Single MySQL Database from a Full MySQL Dump#

If you have backed up all databases and you want to restore any specific single database from a backup file, you would use the --one-database option as given below:

mysql --one-database database_name < all_databases.sql

Export and Import a MySQL Database in One Command#

To take a direct back and restore to another server in a single command you can use following one-liner command:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

It will pipe the output to a mysql client on the remote host and it will import it into a database named remote_database_name.

Conclusion#

In this tutorial, you learned the basics of mysqldump command, to create and restore MySQL databases using the command line mysqldump utility.

If you have any questions or feedback, please leave a comment below.