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

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/www_backups/. Ideally you would store these on an offsite server, but in this guide we will focus on creating backups locally.

Begin by creating your backup folder.

sudo mkdir /var/www_backups/

If you’re not currently logged in to Linux as root, you should change the owner of the backup folder otherwise your mysqldump tests will fail with a permissions error. $(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/www_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 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.

Important Note On –p[password] 

For the purposes of this guide, we will be showing some mysqldump examples using the –p[password] option. However, you are discouraged from using this as the entire mysqldump command (including password) can be viewed by any other user on the system with a simple ps ax command while the dump is running. 

You should instead store your password in ~/.my.cnf and then you can omit the –p[password] option from your commands. If you are running the command in cron, use the option --defaults-extra-file=/path/to/.my.cnf so mysqldump knows where to fetch the password. See more in Step 8 at the end of this guide.

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/www_backups/ using the root user. The dump file is called all-databases.sql but you can change this to whatever you want. Usually you will need to use the root MySQL account to gain access to all databases.

mysqldump -u root -p[password] --all-databases > /var/www_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. We are using the root user in this example, though you could use any user that has access to that particular database.

mysqldump -u root -p[password] [database_name] > /var/www_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/www_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/www_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/www_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/www_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/www_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.
• [database_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

If you are prompted to choose an editor, choose nano.

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.

00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/www_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/www_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 of a database, use the crontab below to create a backup at 1am every morning. The sql.gz file will be overwritten daily.

00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/www_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.

00 01 * * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/www_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 the DevAnswers’ WordPress database backup for each day of the week. Older backups are overwritten automatically.

ls -l /var/www_backups/
-rw-r--r-- 1 root root 69M Dec 3 01:00 wp_devanswers.Mon.sql.gz
-rw-r--r-- 1 root root 70M Dec 4 01:00 wp_devanswers.Tue.sql.gz
-rw-r--r-- 1 root root 70M Dec 5 01:00 wp_devanswers.Wed.sql.gz
-rw-r--r-- 1 root root 70M Dec 6 01:00 wp_devanswers.Thu.sql.gz
-rw-r--r-- 1 root root 72M Dec 7 01:00 wp_devanswers.Fri.sql.gz
-rw-r--r-- 1 root root 73M Dec 8 01:00 wp_devanswers.Sat.sql.gz
-rw-r--r-- 1 root root 73M Dec 9 01:00 wp_devanswers.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.

0 0 1 * * mysqldump -u root -p[password] [database_name] | gzip -c > /var/www_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/www_backups/
-rw-r--r-- 1 root root 69M Jan 1 00:00 wp_devanswers.Jan.sql.gz
-rw-r--r-- 1 root root 70M Feb 1 00:00 wp_devanswers.Feb.sql.gz
-rw-r--r-- 1 root root 70M Mar 1 00:00 wp_devanswers.Mar.sql.gz
-rw-r--r-- 1 root root 70M Apr 1 00:00 wp_devanswers.Apr.sql.gz
-rw-r--r-- 1 root root 72M May 1 00:00 wp_devanswers.May.sql.gz
-rw-r--r-- 1 root root 73M Jun 1 00:00 wp_devanswers.Jun.sql.gz
-rw-r--r-- 1 root root 73M Jul 1 00:00 wp_devanswers.Jul.sql.gz

8. Storing the MySQL Password in my.cnf

You are discouraged from using the –p[password] option as the entire mysqldump command (including password) can be viewed by any other user on the system with a simple ps ax command while the dump is running. 

You should instead store your password in ~/.my.cnf and if using cron use the option --defaults-extra-file=/path/to/.my.cnf.

8.1. Edit my.cnf

The my.cnf file is hidden in your home directory, usually /home/username/.my.cnf

Open my.cnf in nano. (Note that ~ denotes the home directory for the currently logged in user).

sudo nano ~/.my.cnf

Enter in the following two lines replacing YOUR_PASSWORD_HERE with your own.

[mysqldump]
password=YOUR_PASSWORD_HERE

Save and exit (press CTRL + X, press Y and then press ENTER)

You should also set permissions for my.cnf to 600.

sudo chmod 600 ~/.my.cnf

You can now run mysqldump commands without the –p[password] option.

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

8.2. Crontab and –defaults-extra-file

Because cron runs as root, you must use the --defaults-extra-file option so mysqldump knows where to fetch your password. Just replace /home/username/.my.cnf with the path to your own my.cnf file. This crontab example runs at 1AM every morning and compresses the dump to a gzip.

0 01 * * * mysqldump --defaults-extra-file=/home/username/.my.cnf -u root [database_name] | gzip -c > /var/mysql_backups/[database_name].sql.gz

9. Offsite Backups

Now that you have your MySQL backups stored locally in /var/www_backups, you should consider an offsite backup. For example, you could configure SFTP access to the /var/www_backups directory and then run an SFTP cron job on another server to pull these backups nightly.

What Next?

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

• Automatically Back Up Your Web Server Doc Root with Tar and Cron

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.