Difference between revisions of "Database Backup"

From Amahi Wiki
Jump to: navigation, search
 
(26 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__
 
__NOTOC__
 
== Features ==
 
== Features ==
Line 19: Line 24:
  
 
== How to Install ==
 
== How to Install ==
 +
<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'''.
 
* 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.  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:
 
+
<pre>Line 39:  USERNAME=root
      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 performance_schema mysql"
      Line 76:  '''DBEXCLUDE="information_schema"'''
+
Line 79:  CREATE_DATABASE=yes
      Line 79:  '''CREATE_DATABASE=yes'''
+
Line 94:  LATEST=yes</pre><br />
      Line 94:  '''LATEST=yes'''
 
  
 
* 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:
      cd /usr/bin
+
<pre>cd /usr/bin
      cp /tmp/automysqlbackup-2.5.1-01.sh automysqlbackup
+
cp /tmp/automysqlbackup-2.5.1-01.sh automysqlbackup
      chmod 755 automysqlbackup
+
chmod 755 automysqlbackup</pre>
 
* Set ''automysqlbackup'' as a daily cron job (Optional):
 
* Set ''automysqlbackup'' as a daily cron job (Optional):
      cd /etc/cron.daily
+
<pre>cd /etc/cron.daily
      ln -s /usr/bin/automysqlbackup
+
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:
 
* 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'''
+
Line 85:  DOWEEKLY=6
 
* To run anytime, as root do the following:
 
* To run anytime, as root do the following:
      automysqlbackup
+
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.
 
* 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.