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 database 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.