Database Backup

	WARNING
	This is recommended only for advanced users, proceed with caution.

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 automate it. This can be done using AutoMySQLBackup script.

Features

• Backup multiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database separately)
• Backup all databases to a single backup file or to a separate 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 maximum 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 it will run mysqldump and gzip will dump your specified databases to the /var/hda/files/dbbackup/daily directory, it will rotate daily backups weekly so you should never have more than 7 backups in there.
• Every Saturday it will again backup the databases you have chosen but they will be placed into /var/hda/files/dbbackup/weekly, these will be rotated every 5 weeks so there should never be more than 5 backups in there.
• Every 1st of the month it will create a backup of all databases and place them into /var/hda/files/dbbackup/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.

How to Install

Ubuntu

• install via apt-get

sudo apt-get install automysqlbackup

• edit the config file located at /etc/default/automysqlbackup

sudo nano /etc/default/automysqlbackup

• At the bare minimun, in the config file, edit the BACKUPDIR variable
• run automysqlbackup to see if it works (you should get backups added to your backupdir directory:

sudo automysqlbackup

• Edit cron:

sudo crontab -e

• add the following for running cron everyday at midnight

0 0 * * * /usr/sbin/automysqlbackup

Fedora

• Create a share called dbbackup.
• Download the script 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. As root user, update lines indicated in automysqlbackup-2.5.1-01.sh to read as follows:

Line 39:  USERNAME=root
Line 42:  PASSWORD=hda
Line 51:  BACKUPDIR="/var/hda/files/dbbackup"
Line 65:  MAILADDR="root"
Line 76:  DBEXCLUDE="information_schema performance_schema mysql"
Line 79:  CREATE_DATABASE=yes
Line 94:  LATEST=yes

• Rename and copy the automysqlbackup-2.5.1-01.sh from /tmp to /usr/bin and set the permissions:

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

• Set automysqlbackup as a daily cron job (Optional):

cd /etc/cron.daily
ln -s /usr/bin/automysqlbackup

Tips

• To change day of week (1=Monday thru 7=Sunday; defaults to 6=Saturday) for backup, update the number on the following line in automysqlbackup as root user:

Line 85:  DOWEEKLY=6

• To run anytime, as root do the following:

automysqlbackup

• This script is well documented. Options can be easily customized to meet your specific HDA requirements. The guidance can be found in the automysqlbackup, starting at line 110.

NOTE: When it's run the first time, it will create the directory structure automatically if it does not already exist.