Database Backup

From Amahi Wiki
Jump to: navigation, search

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 script.

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 it will run mysqldump and gzip will dump your specified databases to the /var/hda/files/db/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/db/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/db/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

  • Create a share called Database with the path of /var/hda/files/db.
  • 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. 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

TIps

  • This script is well documented. Options can be easily customized to meet your specific HDA requirements. The guidance can be found in the script file, starting at line 110.
  • To run anytime, do the following:
/usr/local/bin/automysqlbackup-2.5.1-01.sh


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