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 optionsfile.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";
doneIt 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.