How to Back Up and Restore MySQL Databases with Mysqldump⚓
This will be a copy/paste from a well written article on Linuxize for backing up and restore a MySQL database. This article is a great resource for making and automating backups of local MySQL databases.
This should not necessarily be used for making backups of managed databases.
This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.
The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database. The mysqldump command can also generate files in CSV and XML format.
You can also use the mysqldump utility to transfer your MySQL database to another MySQL server.
If you don’t backup your databases, a software bug or a hard-drive failure could be disastrous. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of regularly backing up your MySQL databases.
Mysqldump Command Syntax⚓
Before going into how to use the mysqldump command, let’s start by reviewing the basic syntax.
The mysqldump utility expressions take the following form:
options- The mysqldump options
file.sql- The dump (backup) file
To use the mysqldump command the MySQL server must be accessible and running.
Backup a Single MySQL Database⚓
The most common use case of the mysqldump tool is to backup a single database.
For example, to create a backup of the database named
database_name using the user
root and save it to a file named
database_name.sql you would run the following command:
You will be prompted to enter the root password. After successful authentication, the dump process will start. Depending on the database size, the process can take some time.
If you are logged in as the same user that you are using to perform the export and that the user does not require a password, you can omit the
Backup Multiple MySQL Databases⚓
To backup multiple MySQL databases with one command you need to use the
--database option followed by the list of databases you want to backup. Each database name must be separated by space.
The command above will create a dump file containing both databases.
Backup All MySQL Databases⚓
--all-databases option to back up all the MySQL databases:
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⚓
mysqldump utility doesn’t provide an option to backup all databases to separate files but we easily achieve that with a simple bash
The command above will create a separate dump file for each database using the database name as the filename.
Create a Compressed MySQL Database Backup⚓
If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the
gzip utility, and redirect it to a file as shown below:
Create a Backup with Timestamp⚓
If you want to keep more than one backup in the same location, then you can add the current date to the backup filename:
The command above will create a file with the following format
Restoring a MySQL dump⚓
You can restore a MySQL dump using the
mysql tool. The command general syntax is as follows:
In most cases you’ll need to create a database to import into. If the database already exists, first you need to delete it.
In the following example the first command will create a database named
database_name and then it will import the dump
database_name.sql into it:
Restore a Single MySQL Database from a Full MySQL Dump⚓
If you backed up all your databases using the
-all-databases option and you want to restore a single database from a backup file which contains multiple databases use the
--one-database option as shown below:
Export and Import a MySQL Database in One Command⚓
Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner:
The command above will pipe the output to a mysql client on the remote host and it will import it into a database named
remote_database_name. Before running the command, make sure the database already exists on the remote server.
Automate Backups with Cron⚓
Automating the process of backing up the databases is as simple as creating a cron job what will run the mysqldump command at specified time.
To set up automated backups of a MySQL database using cronjob, follow the steps below:
- Create a file named
.my.cnfin your user home directory:
Copy and paste the following text into the .my.cnf file.
Do not forget to replace
dbpasswdwith the database user and user’s password.
- Restrict permissions of the credentials file so that only your user has access to it:
- Create a directory to store the backups:
- Open your user crontab file:
Add the following cron job that will create a backup of a database name
mydb every day at 3am:
Do not forget to replace
username with your actual user name. We’re also escaping the percent-signs (
%), because they have special meaning in crontab.
You can also create another cronjob to delete any backups older than 30 days:
Of course, you need to adjust the command according to your backup location and file names. To learn more about the find command check our How to Find Files in Linux Using the Command Line guide.
This tutorial covers only the basics, but it should be a good starting for anyone who wants to learn how to create and restore MySQL databases from the command line using the mysqldump utility.