| Automating Database Backups Using Crond |
| Description: A fairly detailed article describing how databases can be backed up automatically using mysqldump and crond. |
| Author: bleak |
| Date: Mon Aug 04, 2003 10:41 pm |
| Type: HowTo |
| Keywords: crond, mysql, mysqldump, database, backup, automatic, commandline, shell, script |
| Category: Administrating |
 |
Automating Database Backups Using Crond
Overview
This article is orginially in response to John Abela's question on how to setup an automatic, dynamic database backup. Such backups, which save each backup to a different file, are covered here; but so, too, are
more simplistic automatic backups. The process described in this article uses crond (the cron daemon) in conjunction with mysqldump to create the automated backups. Therefore, I recommend that you read my previous article Backing Up a mySQL Database Via the Commandline With mysqldump, so as to have a better understanding of the mysqldump commands used herein. As with my previous article,
this one is also fairly detailed; so be prepared for a bit of a read.
What You Will Need
1.) Access to the server commandline (covered in my previous article)
2.) Crond
There are both Windows and *nix versions of this program. As far as I know, there is only one version of this for Windows; however, there are many, many different distributions for *nix systems. Below are links to the Windows version and to the Linux version that I use.
Windows - http://www.kalab.com/freeware/cron/cron.htm
Linux - http://www.slackware.com/pb/?vers=slackware-current&set=a&package=dcron-2.3.3-i386-4
NOTE: Use of the Windows version is not covered in this article (although, many things may be the same on Windows as they are described here for Linux).
Also note that hosting providers may or may not allow users to setup cronjobs. It is adivised that you contact your host and find out whether or not they will allow it, before trying to set one up. If you run your own server, then there's no problem.
A Quick Crond Tutorial
The cron daemon runs as a process on *nix (and Windows) systems that allows for the automation of certain tasks. It "wakes up", so to speak, once every minute to check for jobs that are supposed to run at that time. Using crond, it is possible to automate the task of backing up your mySQL database: to have it backed up once a day, once every 2 weeks, or even every couple of minutes.
Crond is usually installed at the same time the rest of a *nix OS is, as one of the base packages. Crond, however, does not come with Windows by default (that I know of). So, if you are using Windows, or did not install Crond when you installed the rest of your *nix OS, then you will need to download it from one of the links I provided above.
A typical cronjob looks something like the following:
| Code: |
30 14 * * * /path/to/program_to_run
|
This cronjob would run whatever specified program at 2:30pm every day.
The 5 parts of a cronjob are:
- Minute: values are 0-59
- Hour: values are 0-23
- Day of the month: values are 0-31
- Month: values are 0-12 (or the first three letters of the month's name)
(e.g. "feb", "nov", etc)
- Day of the week: values are 0-7 (or the first three letters of the day's name)
(e.g. "tue", "fri", etc)
The numeric values may be used ranges, such as:
| Code: |
* 1-3 * * * /path/to/program
|
Specifying a job in this manner would tell crond to run the job at 1am, 2am, and 3am every day.
Ranges may also be stepped through in increments greater than one, such as:
| Code: |
0 0 * * 0-6/2 /path/to/program
|
This will tell crond to run the job every other day at 12 midnight.
Using Crond to Automatically Backup a Database
NOTE: The basic syntax for the mysqldump command (covered in my previous article) is as follows:
| Code: |
mysqldump [options] -u [username] -p[password] [database] [table_name(s)] > [backup_file]
|
Example:
| Code: |
mysqldump --add-drop-table -u bleak -pb133q phpbb > phpbb_backup.sql
|
For the sake of being consistent and causing as little confusion as possible, I will use bleak as the username, b133q as the password, and phpbb as the database in all of the following examples. This information will be different (except, possibly, the
database name) for everybody. Make sure you use the correct information, as given to you by your hosting provider. The name you specify for the backup is arbitrary and may be whatever you want.
If you just want a simple method of backing up your database every so often (say once a day) and don't mind have the backup file being overwritten every time a new backup is made, then you could use a simple cronjob, such as:
| Code: |
0 0 * * 0 mysqldump --add-drop-table -u bleak -pb133q phpbb > phpbb_backup.sql
|
This job would backup your database at 12 midnight every Monday morning.
If you don't want the backup file(s) to be overwritten every time a new one is made, the procedure is a bit more complicated. For me, the job would look something like:
| Code: |
0 0 * * 0 mysqldump --add-drop-table -u bleak -pb133q phpbb > phpbb_backup-$(date +%m%d%Y).sql
|
This cronjob will, again, backup your database at 12 midnight every Monday morning. However, this job will write every new backup to a different file, named based on the current date. The file output by this cronjob would, depending on the date, look something like:
phpbb_backup-07302003.sql
Another way to accomplish this, and one that adds a bit of scalability, is to write a shell script to handle the the backup process and then have crond run the script.
NOTE: You may use whatever editor you wish to write the script (jove and pico are my favorites); or you can write it in notepad (or some other Windows text editor) and upload it to the server. You may also save it as any filename you wish. The example below will be saved as "backup_phpbb".
The shell script
| Code: |
#! /bin/bash
BFILE=phpbb_backup-$(date +%m%d%Y_%k).sql
mysqldump --add-drop-table -u bleak -pb133q phpbb > $BFILE
|
To make this script file executable (so that it may be run by the server), use the chmod command like so:
chmod 711 backup_phpbb
This will give the owner (you) read, write, and execute access to the script. All others will have only execute permission.
If you are not familiar with the octal form of this command, the same may be accomplished using letters to denote permissions. The following will set the same access rights on the "backup_phpbb" script file as the command above:
chmod u+rwx,og-rw+x backup_phpbb
(For more information on chmod type either chmod --help or man chmod from the commandline.)
The Cronjob
| Code: |
* 0-23/3 * * * backup_phpbb > /dev/nul 2>&1
|
NOTE: The cronjob may be written to a file (e.g. "cronjobs") and made active by typing crontab cronjobs from the commandline. Or, if you prefer, you may edit the cronjob directly by typing crontab -e.
The above cronjob will run the backup script every three hours every day. The part that says "> /devl/dnull 2>&1" sends any output that may occur to /dev/null (i.e. it trashes any output so that it is not displayed to the screen).
If you want the backup files to be saved in a separate directory, instead of the directory in which the backup script resides, then you may specify the path to that directory in the shell script:
| Code: |
#! /bin/bash
BFILE=bckp/phpbb_backup-$(date +%m%d%Y_%k).sql
mysqldump --add-drop-table -u bleak -pb133q phpbb > $BFILE
|
This will place the backup files in a directory, under the directory in which the backup script resides, called "bckp".
The Date Function
The date function, which is used to dynamically name the backup files above, is fairly straightforward. I won't get into the nitty-gritty of it; instead, I'll provide a short list of of relevant format strings and how to use them.
Format strings:
%d - Day of the Month (01-31)
%k - Hour of the Day (0-23)
%l - Hour of the Day (1-12)
%m - Month (01-12)
%M - Minute (00-59)
%S - Second (00-60)
%y - Year (2-digit format (03))
%Y - Year (4-digit format (2003))
In order to use these strings with the date function, they must be preceded by a "+".
date +%m-%d-%y
Would output (depending on the actual date): 07-31-03
To use the function in order to display a dynamic filename, as in the examples above, the same rule applies. In this case, however, the function must be used in format of a shell script, which means including the "$" and parentheses "()":
| Code: |
$EXAMPLE=example_file-$(date +%k-%M-%S).txt
|
Which output something like: 15-15-00
It is important to be careful when formatting the date as you wish it to be displayed (especially when said format will be used in naming files, and in conjunction with a command such as mysqldump). One thing, in particular, that I've found causes trouble, is when forward slashes (/) are used when the output will be a filename. Because forward slashes are used to separate directory names (as back slashes are used in Windows), a file named based on the date, and formatted in this manner, will cause the shell to complain (usually errors such as "file name or directory does not exist"). I recommend that you use hyphens (-), periods (.), or underscores (_) to separate parts of a date format, as these seem not to cause a lot of problems.
For more complete information on the date function type either of these from the commandline:
date --help
man date
More Resources
As noted, the man pages are always a good source of information and most shell commands have one. To view a manpage, just type man <command> from the commandline.
mySQL - http://www.mysql.com/documentation/mysql/bychapter/
The Linux Documentation Project - http://www.tldp.org/ |
|
|
|
