Difference between revisions of "Database Backup"

From Amahi Wiki
Jump to: navigation, search
Line 24: Line 24:
 
wget http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh}}
 
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:
 
* 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:
{{Text|Text=Line 39:  '''USERNAME=root'''
+
{{Text|Text=Line 39:  USERNAME=root
Line 42:  '''PASSWORD=hda'''
+
Line 42:  PASSWORD=hda
Line 51:  '''BACKUPDIR="/var/hda/files/dbbackup'''"
+
Line 51:  BACKUPDIR="/var/hda/files/dbbackup"
Line 65:  '''MAILADDR="root"'''
+
Line 65:  MAILADDR="root"
Line 76:  '''DBEXCLUDE="information_schema"'''
+
Line 76:  DBEXCLUDE="information_schema"
Line 79:  '''CREATE_DATABASE=yes'''
+
Line 79:  CREATE_DATABASE=yes
Line 94:  '''LATEST=yes'''}}<br />
+
Line 94:  LATEST=yes}}<br />
  
 
* Rename and copy the ''automysqlbackup-2.5.1-01.sh'' from ''/tmp'' to ''/usr/bin'' and set the permissions:
 
* Rename and copy the ''automysqlbackup-2.5.1-01.sh'' from ''/tmp'' to ''/usr/bin'' and set the permissions:

Revision as of 02:22, 9 February 2011

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

  • Create a share called dbbackup.
  • Download the script to /tmp directory.
bash code
​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:
Text
​Line 39: USERNAME=root Line 42: PASSWORD=hda Line 51: BACKUPDIR="/var/hda/files/dbbackup" Line 65: MAILADDR="root" Line 76: DBEXCLUDE="information_schema" 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:
bash code
​cd /usr/bin cp /tmp/automysqlbackup-2.5.1-01.sh automysqlbackup chmod 755 automysqlbackup​
  • Set automysqlbackup as a daily cron job (Optional):
bash code
​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:
Text
​Line 85: '''DOWEEKLY=6'''​
  • To run anytime, as root do the following:
bash code
​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.