This tutorial is to guide you on setting up a simple, automatic MySQL database backups using mysqldump and a cron job.
- Installed and running MySQL server
- SSH access
- root or sudo user
Step 1: Backing up a MySQL database
You can use the ‘mysqldump’ that comes in any standard MySQL installation to do a database dump from the shell. To backup the ‘exampledb’ database to a file do:
mysqldump -uroot -p exampledb > exampledb.sql
Note that there is no space between the ‘-u’ switch and the user (root). With the ‘-p’ switch and paramater after it the ‘mysqldump’ will prompt you to enter a password.
This command will output plain SQL queries in the exampledb.sql, but as they are text they can be compressed really good to save space. So to make a compressed backup, let’s do:
mysqldump -uroot -p exampledb | gzip > exampledb.sql.gz
You can use these commands to make manual backups of your databases. If you want to backup all databases on a server, use the ‘–all-databases’ switch instead of database name, like this:
mysqldump -uroot -p –all-databases| gzip > mysql_server.sql.gz
Step 2: Setting an automated scheduled backup of a MySQL database
Now that you know how to make manual database backups, let’s automate them with a cron job. You must set up a cron job that calls the ‘mysqldump’ tool on a schedule.
First let’s make a directory for storing the backups:
As backups are more usefull when they provide history, let’s give the backup file with more meaningful name by adding the date and time when the backup was taken:
mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”%Y_%m_%d_%I_%M”).sql.gz
To automate the backup process ‘mysqldump’ must have the user password inline, so it’s highly recommended to set up an additional database user specifically for backups.
Open a crontab editor:
and add the following line at the bottom:
0 1 * * * mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”\%Y_\%m_\%d_\%I_\%M”).sql.gz
The “0 1 * * *” stands for a schedule on every 01:00 or everyday at one after midnight. The cron line format is “m h dom mon dow command”, where:
- m – is for minutes
- h – is for hour
- dom – is the day of the month
- mon – is the month
- dow – day of the week
The ‘*’ symbol in any of these means “on every” minute, hour, day and so.
Saving the crontab will set your backup on schedule and you are good to go.