Backing up Moodle Database

This particular utility is only applicable to Mysql Databases using the MYISAM storage engine. I could have used the mysqldump utility, but I prefer the mysqlhotcopy tool as it executes much faster on larger databases with less overheads.

So the Moodle install is up and running. Time for a simple database backup using the Mysqlhotcopy tool. It allows for literally "hot" copying of the database without stopping the service!

This can be shell scripted to run nightly.

0. Login as root.

1. Make a script directory and create a bare shell script
mkdir /temp/scripts && cd /temp/scripts
touch database_backup.run
chmod +x database_backup.run


2. Edit the script using VI or nano (I <3 nano)
cd /temp/scripts
nano database_backup.run
Add lines (edit to include your database name, username, password etc). Since the database password will be stored in the file as plaintext, make sure the folder is owned by root. If preferred, the date can be appeded to the end of the foldername for simple identification.
mysqlhotcopy -u yourdbusername -p yourdbpassword --addtodest yourdbname /path/to/backup/folder
mv /path/to/backup/folder /path/to/backup/folder_$(date +%Y%m%d)_(date +%H%M)

Save and exit


3. Test the script to make sure it works as advertised!
cd /temp/scripts
./database_backup.run


Once it runs without errors, it can be set to run automatically via cron.

4. Add the script to the crontab to execute nightly
I prefer to run this script at periods of low activity - 1:00am should be fine for most installs.

nano /etc/crontab
Append the following:

#database backup script
00 1 * * *  root /bin.sh /temp/scripts/database_backup.run


This tutorial demonstrates how to make a copy of  raw database files on the same server that the database resides on.

Later on (when I get some time), I  will show how to extend the strategy to compress the backup and transfer the files to another server - as well as how to do a Database restore from the raw files.

-n