Overview

This tutorial is to guide you on setting up a simple, automatic MySQL database backups using mysqldump and a cron job.

Prerequisites

• 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:

mkdir /var/backups/mysql

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 user specifically for backups.

Open a crontab editor:

crontab -e

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.