Difference between revisions of "Database Backup"

From Amahi Wiki
Jump to: navigation, search
 
(71 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 written by WipeOut.
+
{{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 />
* Download the script [http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh AutoMySQLBackup] file to ''/tmp'' directory.
+
<b><u><big>Ubuntu</big></u></b>
cd /tmp
+
* install via apt-get
wget http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh
+
sudo apt-get install automysqlbackup
* Some changes will need to be made for this to work on your HDA.  Use your favorite text editor and update as follows:
+
* edit the config file located at <code>/etc/default/automysqlbackup</code>
''USERNAME=debian'' on line 39 to:
+
sudo nano /etc/default/automysqlbackup
  USERNAME=root
+
* At the bare minimun, in the config file, edit the BACKUPDIR variable
''PASSWORD='' on line 42 to:
+
* run automysqlbackup to see if it works (you should get backups added to your backupdir directory:
  PASSWORD=hda
+
sudo automysqlbackup
''BACKUPDIR="/srv/backup/db" on line 51 to:
+
* Edit cron:
  BACKUPDIR="/var/hda/files/db"
+
sudo crontab -e
''MAILADDR="maintenance@example.com"'' on line 65 to:
+
* add the following for running cron everyday at midnight
  MAILADDR="root"
+
0 0 * * * /usr/sbin/automysqlbackup
''DBEXCLUDE=""'' on line 76 to:
+
<br />
  DBEXCLUDE="information_schema"
+
<b><u><big>Fedora</big></u></b>
''CREATE_DATABASE=no'' on line 79 to:
+
* Create a share called '''dbbackup'''.
  CREATE_DATABASE=yes
+
* Download the script to ''/tmp'' directory.
''LATEST=no'' on line 94 to:
+
<pre style="white-space: pre-wrap; word-break: keep-all;">cd /tmp
  LATEST=yes
+
wget http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh</pre>
* Copy the file from ''/tmp'' to ''/usr/local/bin'' and set the permissions:
+
* 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:
cd /usr/local/bin
+
<pre>Line 39: USERNAME=root
cp /tmp/automysqlbackup-2.5.1-01.sh .
+
Line 42:  PASSWORD=hda
chmod 755 automysqlbackup-2.5.1-01.sh
+
Line 51:  BACKUPDIR="/var/hda/files/dbbackup"
* Set as a daily cron job:
+
Line 65:  MAILADDR="root"
cd /etc/cron.daily
+
Line 76:  DBEXCLUDE="information_schema performance_schema mysql"
ln -s /usr/local/bin/automysqlbackup-2.5.1-01.sh
+
Line 79:  CREATE_DATABASE=yes
* 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.
+
Line 94:  LATEST=yes</pre><br />
 +
 
 +
* Rename and copy the ''automysqlbackup-2.5.1-01.sh'' from ''/tmp'' to ''/usr/bin'' and set the permissions:
 +
<pre>cd /usr/bin
 +
cp /tmp/automysqlbackup-2.5.1-01.sh automysqlbackup
 +
chmod 755 automysqlbackup</pre>
 +
* Set ''automysqlbackup'' as a daily cron job (Optional):
 +
<pre>cd /etc/cron.daily
 +
ln -s /usr/bin/automysqlbackup</pre>
 +
 
 +
== 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.
 
'''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.