How to Back Up MySQL Databases with Linux Command Line

How to Back Up MySQL Databases with Linux Command Line and Automate with Cron

Last updated on

It’s important to make frequent automated backups of your MySQL databases should you ever accidentally alter data or suffer a hack. In this guide we will learn how to use mysqldump to export databases and use crontab to automate the entire process.

1. Prepare MySQL Backup Folder

There is no particular recommended folder to back up to in Linux so you can choose this yourself. In this guide, we are saving backups to /var/mysql_backups/. Ideally you would store these on an external drive or an offsite server, but in this guide we will focus on creating backups locally.

Begin by creating your backup folder.

sudo mkdir /var/mysql_backups/

If you’re not currently logged in to Linux as root, you should change the owner of the backup folder otherwise mysqldump will fail. $(whoami) will fetch the currently logged in user. You can always change the owner back to root once you’ve finish testing commands.

sudo chown $(whoami):$(whoami) /var/mysql_backups

2. mysqldump Client Utility

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.

Here’s the syntax of a typical mysqldump command.

mysqldump -u [username] –p[password] [database_name] > /path/to/[database_name].sql
  • -u [username] = the MySQL user (usually root) that has the necessary privileges to perform database dumps.
  • –p[password] = the user password. Note there is no space between -p and the password.
  • [database_name] = the database name
  • > = output dump to
  • /path/to/[database_name].sql – path and name of dump file. [database_name] can be whatever you want.

3. MySQL Back Up Examples

Back up All Databases

We can use the --all-databases option to dump all MySQL databases. In the following example, we are dumping all databases to /var/mysql_backups/ using the root user. The dump file is called all-databases.sql but you can change this to whatever you want.

mysqldump -u root -p[password] --all-databases > /var/mysql_backups/all-databases.sql

Back up a Single Database

To back up a single MySQL database, simply replace [database_name] with your own. The dump file in this example is called [database_name].sql but you can change this to whatever you want.

mysqldump -u root -p[password] [database_name] > /var/mysql_backups/[database_name].sql

Back up Multiple Databases

To back up more than one MySQL database, use the --databases option followed by the database names, separated by a space. The dump file in this example is called [database_names].sql but you can change it to whatever you want.

mysqldump -u root -p[password] [database_1_name] [database_2_name] > /var/mysql_backups/[database_names].sql

Back up a Single Table

To back up a single table from a MySQL database, simply enter the table name after the database name. The dump file in this example is called [table_name].sql but you can change it to whatever you want.

mysqldump -u root -p[password] [database_name] [table_name] > /var/mysql_backups/[table_name].sql

Back up Multiple Tables

To back up multiple tables from a MySQL database, simply enter the table names after the database name, separated by a space. The dump file in this example is called [table_names].sql but you can change this to whatever you want.

mysqldump -u root -p[password] [database_name] [table_1_name] [table_2_name] > /var/mysql_backups/[table_names].sql

Back up a Remote Database

To back up a remote MySQL database, simply use the -h option followed by the remote IP address or host name.

mysqldump -h [ip_or_hostname] -u root -p[password] [database_name] > /var/mysql_backups/[database_name].sql

Back up a Database with Compression

To back up a MySQL database with compression, we can ‘pipe’ the output to gzip using | gzip -c >.

mysqldump -u root -p[password] [database_name] | gzip -c > /var/mysql_backups/[database_name].sql.gz

You can also add the verbose option to see compression progress, useful for very large tasks. Example gzip -c --verbose >

4. Restore MySQL

Use mysql to restore .sql files to the database. Here’s the syntax of a typical MySQL restore.

mysql -u [username] –p[password] [database_name] < /path/to/[database_name].sql
  • -u [username] = the MySQL user, usually root, that has the necessary privileges to perform database imports.
  • –p[password] = the user password. Note there is no space between -p and the password.
  • [YOUR_DB_NAME] = the database name
  • < = import dump to
  • /path/to/[database_name].sql – path and name of dump

5. Automated MySQL Backups

cron is a service in Linux used to schedule automated commands. These are stored in a cron table called crontab.

To open crontab, run:

sudo crontab -e

Scroll to the bottom of the file and add your cron schedule and mysqldump command. In the example below, we are backing up a database daily with gzip compression. Gzip will reduce the size of large .sql files suitable for storing backups.

/tmp/crontab.QMOot4/crontab
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/mysql_backups/[database_name].sql.gz

00 01 * * * will run the command at 1AM every 24 hours and overwrite any current .sql.gz files. As a test, you can change this to run in the next 3 minutes. If your time now is 16:30, enter 33 16 * * * for it to run at 16:33. (For more information on how to configure cron schedules, see Step 6 below).

Save and close crontab to initiate cron. (if using nano, press CTRL + X, press Y and then press ENTER)

Wait for your test cron to run and list the backup folder until you see your .sql.gz file.

ls -l /var/mysql_backups/
-rw-r--r-- 1 root root 66M Dec 3 16:33 [database_name].sql.gz

If your .sql.gz file doesn’t appear after a while, make sure your command and crontab time are correct. You can also check the cron log with:

sudo grep CRON /var/log/syslog

Once you’ve verified the cron is running, you can change the schedule back to your preferred time.

6. MySQL Backup Frequency and Retention

In the previous step, we learned how to configure cron to run every 24 hours and overwrite our sql.gz file. However, you may want to retain multiple backups spread out over a week or longer.

We will now learn how to configure schedules in crontab and include a timestamp in the filename to allow for more organized archiving of multiple files.

Crontab schedule

The default crontab entry begins with 5 stars followed by a command, which will run once a minute. You can change these to suit your exact schedule by minute, hour, day of month, month, and day of week.

.------------ minute (0-59) (* = every minute)
| .---------- hour (0-23) (* = every hour)
| | .-------- day of month (1-31) (* = every day)
| | | .------ month (1-12 or jan-dec) (* = every month)
| | | | .---- day of week (0-6 or mon-sun) (Sunday=0) (* = every day)
| | | | |
* * * * * command_to_run

Examples:

30 23 * * *       Every day at 11.30pm
0 0 * * *         Every day at midnight (00:00)
*/10 * * * *      Every 10 mins
0 */12 * * *      Every 12 hours 
0 17 * * sun      Every Sunday at 5pm
0 17 * * sun,mon  Every Sunday and Monday at 5pm
0 5,17 * * *      At 5am and 5pm daily
0 12 1 jan,feb *  At 12pm on the 1st of every Jan and Feb
0 0 1 * *         The 1st day of every month at midnight

Timestamps in Filename

To better manage multiple sql.gz files, it’s recommend that you append a timestamp to the filename. We can do this using the date variable in Linux.

Let’s test the date variable using echo. To echo the current date in the format YYYY-MM-DD:

echo `date +%Y-%m-%d`
2018-12-07

To get the number of the day of the week (0 to 6, where 0 is Sunday and 6 is Saturday)

echo `date +%w`
2

Above we can see the day is 2 for Tuesday.

You can also echo the name of day of the week:

echo `date +%a`
Tue

For a full list of control characters supported by the date command, see: Linux Shell Script Date Format

We can use these date control characters in crontab to give our sql.gz files unique filenames.

In the next step we will show some examples and backup scenarios using crontab schedules and timestamps in filenames.

7. MySQL Automated Backup Examples

Just a Daily MySQL Backup

If you just want a daily MySQL backup, use the crontab below to create a backup at 1am every morning. The sql.gz file will be overwritten daily.

/tmp/crontab.QMOot4/crontab
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/mysql_backups/[database_name].sql.gz

7-day rolling MySQL backup

In this scenario, we will run a MySQL database backup at 1am and keep a copy for each day of the week. This is the same backup we have for this website.

/tmp/crontab.QMOot4/crontab
00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/mysql_backups/[database_name].`date +\%a`.sql.gz

This will initiate a backup every 1am. This part `date +\%a` will add the day of the week to the filename (Mon, Tue, Wed, etc).  This is so you have a sql.gz file for each of the last 7 days and don’t have to worry about purging old copies. Note that in order to use the % symbol in crontab, it must be escaped with \, otherwise the cron will fail.

Here is a list of our folder showing a MySQL database backup for each day of the week. Older backups are overwritten automatically.

ls -l /var/mysql_backups/
-rw-r--r-- 1 root root 69M Dec 3 01:00 [database_name].Mon.sql.gz
-rw-r--r-- 1 root root 70M Dec 4 01:00 [database_name].Tue.sql.gz
-rw-r--r-- 1 root root 70M Dec 5 01:00 [database_name].Wed.sql.gz
-rw-r--r-- 1 root root 70M Dec 6 01:00 [database_name].Thu.sql.gz
-rw-r--r-- 1 root root 72M Dec 7 01:00 [database_name].Fri.sql.gz
-rw-r--r-- 1 root root 73M Dec 8 01:00 [database_name].Sat.sql.gz
-rw-r--r-- 1 root root 73M Dec 9 01:00 [database_name].Sun.sql.gz

12-month rolling MySQL backup

Another cron we have configured for this website is a monthly MySQL database backup going back one year.

/tmp/crontab.QMOot4/crontab
0 0 1 * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/mysql_backups/[database_name].`date +\%w`.sql.gz

This part `date +\%b` will add the month name (e.g ‘Jan’) in the sql.gz filename so you have a backup every month of the year.

Here is a list of our backup folder showing the monthly backup. As the years roll on, the older sql.gz files will simply be overwritten.

ls -l /var/mysql_backups/
-rw-r--r-- 1 root root 69M Jan 1 00:00 [database_name].Jan.sql.gz
-rw-r--r-- 1 root root 70M Feb 1 00:00 [database_name].Feb.sql.gz
-rw-r--r-- 1 root root 70M Mar 1 00:00 [database_name].Mar.sql.gz
-rw-r--r-- 1 root root 70M Apr 1 00:00 [database_name].Apr.sql.gz
-rw-r--r-- 1 root root 72M May 1 00:00 [database_name].May.sql.gz
-rw-r--r-- 1 root root 73M Jun 1 00:00 [database_name].Jun.sql.gz
-rw-r--r-- 1 root root 73M Jul 1 00:00 [database_name].Jul.sql.gz

What Next?

Now that you have your MySQL database backed up safely, you might also want to back up your web document root.

Let me know in the comments if this helped. Follow me on Twitter, Facebook and YouTube.

p.s. I increased my AdSense revenue by 68% using AI 🤖. Read my Ezoic review to find out how.

1 Star2 Stars3 Stars4 Stars5 Stars 5.00 (1 votes)

Leave a Reply

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

We use Markdown to style comments, like on Github and Reddit.
To do a line break, type two spaces after the sentence.
You can add inline code by wrapping it in backticks: `code here`

    To do an entire block of code  
    type four spaces before the line
    and it will appear in a block like this.
    <-- four empty spaces