If you are running MySQL on Linux and need to back up your databases, you can use Bash commands from the terminal to accomplish the task. A simple script could even make daily backups. In this guide, we’ll show you how to backup your MySQL databases and restore data from those backups.
Make a Backup With mysqldump Command
The mysqldump command will export your databases to a .sql file that can be easily imported back into MySQL later if you need to restore a backup.
You can back up a single database, multiple databases, or every database inside MySQL. No matter what you choose, you only need to execute a single command.
Back Up a Single Database
To back up a particular database inside MySQL, you can specify its name in your mysqldump command:
$ mysqldump -u root -p my_database > my_database_backup.sql
In the example above, the -u option allows us to specify a MySQL username (root) in our command. -p tells MySQL to prompt us for a password. The name of our database is my_database and we are exporting it into a file named my_database_backup.sql.
The mysqldump command doesn’t give us any exciting output. If your terminal remains empty, that means it didn’t encounter any errors and you should end up with the .sql backup file.
If your MySQL user is the same as your Linux username, you won’t need to specify the -u or -p options.
$ mysqldump my_database > my_database_backup.sql
You can also avoid the password prompt by specifying the password with the --password parameter within the mysqldump command. This is inherently less secure for obvious reasons, but it could be fine for certain situations:
$ mysqldump -u root --password="pass" my_database > my_database_backup.sql
Back Up to a Specific Location
The .sql file can be saved wherever you’d like; just specify the full path in your mysqldump command:
$ mysqldump -u root -p my_database > /home/user/backups/my_database_backup.sql
Create a Timestamped Backup
Timestamp your MySQL backups by utilizing the date command within your mysqldump syntax.
$ mysqldump -u root -p my_database > backup_$(date +"%Y_%m_%d").sql
Automatically Compress Backups
Text is easily compressed, so piping the mysqldump command to a compression utility could allow you to store your MySQL backup at a fraction of its usual size.
On Linux, gzip is probably the most popular option for data compression, so let’s pipe our command to gzip.
$ mysqldump -u root -p my_database | gzip > my_database_backup.sql.gz
Back Up Multiple Databases
To back up more than one MySQL database, use the --database option and specify each of the databases you’d like to back up. This will still back up the databases into a single file.
$ mysqldump -u root -p --databases first_db second_db > db_backup.sql
Back Up All Databases
If you want to do a complete backup of MySQL by exporting every database, use the --all-databases option. This will still only generate a single .sql file.
$ mysqldump -u root -p --all-databases > my_databases.sql
How to Restore a MySQL Backup
If you need to restore one of your MySQL backups, you can do so with a single mysql command and the MySQL dump file you created earlier. The only prerequisite is that the database needs to already exist.
In this example, we will restore the my_database database.
1. First, create the database if it doesn’t already exist.
$ mysql -u root -p -e "create database my_database";
2. After the database is created, import your backup with this command:
$ mysql -u root -p my_database < my_database_backup.sql
Restore a Single Database
If your .sql file contains multiple databases and you only need to restore a particular database, you can use the --one-database option and specify the name of the database that needs restored.
$ mysql --one-database my_database < multiple_databases.sql
Automate MySQL Backups With Cron
What you really want to do to keep your MySQL data safe is have automatic backups. These can be configured directly through cron, Linux’s built-in job scheduler.
1. Open up cron for editing with the following command. You’ll want to use your root user so you can avoid being prompted for a password when cron runs mysqldump.
sudo crontab -e
2. Pasting the following line into cron will create daily backups at midnight and put a timestamp in the file name.
0 0 * * * mysqldump my_database > /home/user/backup_$(date +"%Y_%m_%d").sql
3. If you’d prefer to compress your daily backups, pipe the command to gzip.
$ 0 0 * * * mysqldump my_database | gzip > /home/user/backup_$(date +"%Y_%m_%d").sql.gz