Backup and Restore MySQL Databases via Linux Command Line

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.

mysqldump command creating a mysql database backup on Linux

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

mysqldump creating a sql export file with timestamp

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

Piping mysqldump command to gzip for compression of database backup

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

Restoring a MySQL database backup

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

Cron configuration showing daily backup of MySQL database

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

Leave a Comment

Your email address will not be published. Required fields are marked *