Introduction
mysqldump is a utility that performs backups for MySQL database. These backup files comprise of SQL statements that can be run in a certain operating system to recreate the original database. The mysqldump utility can also backup a local database and at the same time restore it on a remote database using a single command. It also features an easy to use interface.
If you fail to back up the databases, you could experience a hard-drive/system failure or software bug that can be terrible. It is important to perform regular back up of your MySQL database.
In this tutorial, we will show you a step-by-step process on how to backup and restore MySQL databases using the mysqldump utility.
Command Syntax for Mysquldump
Before we start, we are going to show you how to run various commands in mysqldump. All mysqldump utility functions take the form:
mysqldump [options] > file.sql
- options: Represents mysquldump options
- file.sql: The dump (backup) file
How To Backup MySQL Database
1. Backup A Single Database
We shall start by creating a backup for a database called database_name with the user root then save it in the directory database_name.sql. To accomplish this we’ll execute the command below:
$ mysqldump -u root -p database_name > database_name.sql
You will be asked to provide the root password. Type it then click Enter to initiate the dump. The time it will take to complete this process depends on the size of your database.
You can omit the value -u and -p if you are logged in the system as the same user you have used to execute the export in which case you may not be prompted to enter a password.
$ mysqldump database_name > database_name.sql
2. Backup Multiple MySQL Databases
If you want to create a backup for multiple databases, you can use the–database option including the databases list you wish to backup. Make sure each database name is separated by a space.
$ mysqldump -u root -p --database database_name_a database_name_b > databases_a_b.sql
This command will create a backup or one dump file with databases listed.
3. Backup All MySQL Databases
To create a backup for all databases, you need to use the option –all databases.
$ mysqldump -u root -p --all-databases > all_databases.sql
This command will also create a backup with all databases.
4. Backup All Databases To Different Files
By default, mysqldump doesn’t have a backup option for all databases to different files. However, this can be achieved with a bash FOR loop as shown below:
for DB in $(mysql -e 'show databases' -s --skip-column-names); do
mysqldump $DB > "$DB.sql";
DoneThis command will create a separate backup file for all MySQL databases that utilize the name of the database as the filename.
Creating A Compressed Database Dump File/Backup
If your database is very big, it’s wise to create compressed output. To do so, feed the output to gzip utility, then redirect the output to a specific file using the command below:
$ mysqldump database_name > | gzip > database_name.sql.gz
Creating A Dump File using Timestamp
If you wish to create multiple backup files in the same location, add the date to the filename of your backup as shown below:
$ mysqldump database_name > database_name-$(date +%Y%m%d).sql
This command will create a dump file with the format database_name-20180817.sql
On the other hand, if you are utilizing cronjob systematize your databases, you can execute the command below to erase any backups that have existed for more than 30 days.
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
Make sure you edit the command based on your backup file names and location.
How To Restore MySQL Dump
To restore a MySQL backup file, use the MySQL tool. First, create a new database where you will import the files into. If there is an existing database, start by deleting it.
In our example, we will create a new database called database_name then import the backup file database_name.sql into it. To achieve this, run the commands below:
$ mysql -u root -p -e "create database database_name"; $ mysql -u root -p database_name < database_name.sql
Restoring A Single Database For MySQL
In case you have your MySQL databases backed up with -all databases option and you want to restore a single MySQL database file from the dump file, you will need to use the option–one database as shown in the command below:
$ mysql --one-database database_name < all_databases.sql
Exporting And Importing A Database An A Single Command
To import and export a dump file into one MySQL database, run the command below:
$ mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name
This command will channel the details to MySQL client hosted on a remote machine and import it into another database called remote_database_name. But before you run this command, ensure the database is present on the remote server.
Conclusion
That’s it. We have covered the basics to get you started. We hope this guide has helped you understand how to backup and restore MySQL databases using the mysqldump utility.
Check out these top 3 Best web hosting services
- Your query to the best web hosting can end by clicking on this link.
