MySQL is one of the worlds most popular opensource databases in use today.  Much of MySQL’s success can be attributed to its general ease of installation and usability.  A functional MySQL installation is a single package install on most Linux distributions.

It’s so easy to hit the ground running with MySQL that we frequently encounter scenarios where backups haven’t been considered.  Backups are just as easy to get going as MySQL itself. We’ll walk you through the process in this article by creating a script that utilizes mysqldump, MySQL’s backup utility.

MYSQL BACKUP SCRIPT

This script is designed to be generic and run on the database server directly.  You can copy and paste it into your environment, set the executable bit, and run it immediately.  We deploy this script to /usr/local/bin/mysql_backup.sh.

The script will create an individual file for each database on your server in the /dumps path.  If you want to change where the dumps are written to you can change the STORE_PATH variable.

The arguments we use are defined in MYSQL_ARGS and provide the following.

–single-transaction Provides a consistent state of the database for reliable restore.
–routines Includes stored procedures and functions in the database dump.
–events Includes any scheduled events in the database dump.
–databases We specify the databases option to have the CREATE DATABASE statements added to our dump.

Copy to Clipboard

SCHEDULING MYSQL BACKUPS VIA CRON

It’s great to be able to take database backups manually, but the real power is in scheduling them to occur automatically.  For this task, we make use of crontab.

You must set the executable bit on the script by running

Copy to Clipboard

Run crontab -e as root which will open your $EDITOR.

Copy to Clipboard

Add the following line to the crontab file.  This will run daily at 01:00.

Copy to Clipboard
The leading fields before our command are defined as
 field #   meaning        allowed values
 -------   ------------   --------------
    1      minute         0-59
    2      hour           0-23
    3      day of month   1-31
    4      month          1-12 (or names, see below)
    5      day of week    0-7 (0 or 7 is Sun, or use names)
RESTORING FROM MYSQL BACKUPS
Now that we have backups of our databases running daily, we can revert to any previous backup by running the following command.
Copy to Clipboard
This command parses the compressed backup file we have in /dumps and will restore the database revenni_app to the state when the database backup was taken.
Revenni is a Toronto based IT consulting firm specializing in Linux System Administration, Linux Consulting, Managed Linux Services, and 24×7 Emergency Linux Support.