Using Cron Jobs to Backup Files & Databases

Using Cron Jobs to Backup Files & Databases
Solution 1 - Cron job to backup your home directory

This is useful when you do not keep track of all the changes you make on your website : when you edit files online or allow image uploads etc. You need to set this backup as regularly as your disk space allows it : once a week is a good frequency if you do not modify your files too often.

date=`date -I` ; tar -zcf backup_$date.tgz ./public_html

Explanations : we first define the date of the day in the variable $date, that will later be used for the filename creation. The date element is very useful to sort your backups if you don’t download them every week. Then we compress our public_html folder into a tgz file. In order to put this cron in place, just copy this line in the Command field, select your backup frequency and save.

2 - Cron job to backup your MySQL databases

The MySQL databases are where all the information is stored and this is the most important thing when your site or forum is database-driven, as more and more sites are nowadays. Losing a few days of data can be *very* frustrating, both for you as administrator and for your users. This kind of backup needs to be run every day or so (still depends of the size of your databases !).

date=`date -I` ; mysqldump -uDBUSER -pDBPASS –all-databases | gzip > /home/CPANEL/xbackup_$date.sql.gz

Explanations : this kind of backup requires some extra work on your part because you need to create a MySQL user that will be able to access all your databases to create a dump file with all the structures and data. Here’s how to do it, step by step :

1. Create a new MySQL user “backup”. Go to Cpanel > MySQL Databases and create the user, granting him all privileges on your databases.
2. Fill in the cron job above with :
* DBUSER as the MySQL user you have just created (”backup” in our example)
* DBPASS as the password associated with the MySQL user.
* CPANEL as your Cpanel username

The date element is still used as part of the backup filename for easy maintenance. The script makes a dump of all your databases using the login information of your backup MySQL user and gzipping the dump file on the fly at the root of your account. Make sure you edit the variable in capital letters so as to reflect your settings. Set your backup frequency, save and relax. Your SQL data should now be saved everyday (recommended).