Difference between revisions of "Database Backup"

From Amahi Wiki
Jump to: navigation, search
 
(66 intermediate revisions by 3 users not shown)
Line 1: Line 1:
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 [http://members.multimania.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup] script.
+
{{MessageBox|
 
+
backgroundcolor = #faa|
 +
image =Warning.png|
 +
heading =WARNING|
 +
message = 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 [http://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] script.
 +
__NOTOC__
 
== Features ==
 
== 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 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 seperate directory and file for each database.
+
* 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.
 
* Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
 
* Can backup remote MySQL servers to a central server.
 
* Can backup remote MySQL servers to a central server.
Line 9: Line 14:
 
* Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).
 
* 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 email the compressed database backup files to the specified email address.
* Can specify maximun size backup to email.
+
* Can specify maximum size backup to email.
 
* Can be set to run PRE and POST backup commands.
 
* Can be set to run PRE and POST backup commands.
 
* Choose which day of the week to run weekly backups.
 
* Choose which day of the week to run weekly backups.
  
 
== What it does ==
 
== 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 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/db/weekly'', these will be rotated every 5 weeks so there should never be more than 5 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/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.
+
* 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 ==
 
== How to Install ==
* Create a share called '''Database''' with the path of ''/var/hda/files/db''.
+
<br />
 +
<b><u><big>Ubuntu</big></u></b>
 +
* install via apt-get
 +
sudo apt-get install automysqlbackup
 +
* edit the config file located at <code>/etc/default/automysqlbackup</code>
 +
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
 +
<br />
 +
<b><u><big>Fedora</big></u></b>
 +
* Create a share called '''dbbackup'''.
 
* Download the script to ''/tmp'' directory.
 
* Download the script to ''/tmp'' directory.
cd /tmp
+
<pre style="white-space: pre-wrap; word-break: keep-all;">cd /tmp
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</pre>
* Some changes will need to be made for this to work on your HDA.  Use your favorite text editor and update 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:
''USERNAME=debian'' on line 39 to:
+
<pre>Line 39: USERNAME=root
  USERNAME=root
+
Line 42:  PASSWORD=hda
''PASSWORD='' on line 42 to:
+
Line 51:  BACKUPDIR="/var/hda/files/dbbackup"
  PASSWORD=hda
+
Line 65:  MAILADDR="root"
''BACKUPDIR="/srv/backup/db" on line 51 to:
+
Line 76:  DBEXCLUDE="information_schema performance_schema mysql"
  BACKUPDIR="/var/hda/files/db"
+
Line 79:  CREATE_DATABASE=yes
''MAILADDR="maintenance@example.com"'' on line 65 to:
+
Line 94:  LATEST=yes</pre><br />
  MAILADDR="root"
+
 
''DBEXCLUDE=""'' on line 76 to:
+
* Rename and copy the ''automysqlbackup-2.5.1-01.sh'' from ''/tmp'' to ''/usr/bin'' and set the permissions:
  DBEXCLUDE="information_schema"
+
<pre>cd /usr/bin
''CREATE_DATABASE=no'' on line 79 to:
+
cp /tmp/automysqlbackup-2.5.1-01.sh automysqlbackup
  CREATE_DATABASE=yes
+
chmod 755 automysqlbackup</pre>
''LATEST=no'' on line 94 to:
+
* Set ''automysqlbackup'' as a daily cron job (Optional):
  LATEST=yes
+
<pre>cd /etc/cron.daily
* Copy the file from ''/tmp'' to ''/usr/local/bin'' and set the permissions:
+
ln -s /usr/bin/automysqlbackup</pre>
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 ==
+
== 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 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:
* To run anytime, do the following:
+
Line 85:  DOWEEKLY=6
/usr/local/bin/automysqlbackup-2.5.1-01.sh
+
* 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.
 
'''NOTE:'''  When it's run the first time, it will create the directory structure automatically if it does not already exist.

Latest revision as of 15:54, 18 June 2020

Warning.png 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.