Difference between revisions of "Database Backup"

From Amahi Wiki
Jump to: navigation, search
 
(82 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 automatate it.  Follow the steps below to help keep your data safe.
+
{{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 ==
 +
* 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.
  
== Installation ==
+
== What it does ==
* Create a share called '''Database''' with the path of ''/var/hda/files/db''.
+
* 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.
* Download the script [http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%202.5/automysqlbackup-2.5.1-01.sh AutoMySQLBcckup] file to ''/tmp'' directory.
+
* 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.
cd /tmp
+
* 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.
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:
+
== How to Install ==
''USERNAME=debian'' on line 39 to:
+
<br />
  USERNAME=root
+
<b><u><big>Ubuntu</big></u></b>
''PASSWORD='' on line 42 to:
+
* install via apt-get
  PASSWORD=hda
+
sudo apt-get install automysqlbackup
''BACKUPDIR="/srv/backup/db" on line 51 to:
+
* edit the config file located at <code>/etc/default/automysqlbackup</code>
  BACKUPDIR="/var/hda/files/db"
+
sudo nano /etc/default/automysqlbackup
<nowiki>''MAILADDR="maintenance@example.com"''</nowiki> on line 65 to:
+
* At the bare minimun, in the config file, edit the BACKUPDIR variable
  MAILADDR="root"
+
* run automysqlbackup to see if it works (you should get backups added to your backupdir directory:
''DBEXCLUDE=""'' on line 76 to:
+
sudo automysqlbackup
  DBEXCLUDE="information_schema"
+
* Edit cron:
''CREATE_DATABASE=no'' on line 79 to:
+
sudo crontab -e
  CREATE_DATABASE=yes
+
* add the following for running cron everyday at midnight
''LATEST=no'' on line 94 to:
+
0 0 * * * /usr/sbin/automysqlbackup
  LATEST=yes
+
<br />
* Move the file from your home directory to /usr/local/bin and set the permissions:
+
<b><u><big>Fedora</big></u></b>
mv /
+
* Create a share called '''dbbackup'''.
 +
* Download the script to ''/tmp'' directory.
 +
<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</pre>
 +
* 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:
 +
<pre>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</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 ==
 
== 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.

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.