Database Backup

It's good practice to periodically backup up your database. This functionality is coming to Amahi soon, but until then there is a simple way to automatize it.

This can be done using AutoMySQLBackup.

Features:

• Backup mutiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database seperately)
• Backup all databases to a single backup file or to a seperate directory and file for each database.
• Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
• Can backup remote MySQL servers to a central server.
• Runs automatically using cron or can be run manually.
• Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).
• Can email the compressed database backup files to the specified email address.
• Can specify maximun size backup to email.
• Can be set to run PRE and POST backup commands.
• Choose which day of the week to run weekly backups.

What it does: Every day AutoMySQLBackup will run (if setup on /etc/cron.daily) and using mysqldump and gzip will dump your specified databases to the /backups/daily directory, it will rotate daily backups weekly so you should never have more than 7 backups in there..

Every Saturday AutoMySQLBackup will again backup the databases you have chosen but they will be placed into /backups/weekly, these will be rotated every 5 weeks so there should never be more than 5 backups in there..

Every 1st of the month AutoMySQLBackup will create a backup of all databases and place them into /backups/monthly. These will never be rotated so it will be up to you to do your own house keeping. I would suggest taking a copy of this offline every month or two so that if you have a hard drive failure you will be able to restore your database..

Installation

• Create a share called Database with the path of /var/hda/files/db.
• Download the script AutoMySQLBackup file to /tmp directory.

cd /tmp
wget http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh

• Some changes will need to be made for this to work on your HDA. Use your favorite text editor and update as follows:

USERNAME=debian on line 39 to:

USERNAME=root

PASSWORD= on line 42 to:

PASSWORD=hda

BACKUPDIR="/srv/backup/db" on line 51 to:

BACKUPDIR="/var/hda/files/db"

MAILADDR="maintenance@example.com" on line 65 to:

MAILADDR="root"

DBEXCLUDE="" on line 76 to:

DBEXCLUDE="information_schema"

CREATE_DATABASE=no on line 79 to:

CREATE_DATABASE=yes

LATEST=no on line 94 to:

LATEST=yes

• Copy the file from /tmp to /usr/local/bin and set the permissions:

cd /usr/local/bin
cp /tmp/automysqlbackup-2.5.1-01.sh .
chmod 755 automysqlbackup-2.5.1-01.sh

• Set as a daily cron job:

cd /etc/cron.daily
ln -s /usr/local/bin/automysqlbackup-2.5.1-01.sh

• When it's run the first time, it will create the directory structure automatically. It will look like this:

daily
latest
monthly
weekly

• Each time it is executed, the script will backup all your databases and tables. Each database will be exported in to .sql files, zipped, and placed in its own folder for the respective period, i.e. latest, daily, etc. The sql will include the database/table create syntax as well.
• It will also rotate those each time so you will have backups preserved for quite some time.