Difference between revisions of "Database Backup"
From Amahi Wiki
(12 intermediate revisions by 2 users not shown) | |||
Line 4: | Line 4: | ||
heading =WARNING| | heading =WARNING| | ||
message = This is recommended only for advanced users, proceed with caution.}} | 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:// | + | 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 24: | 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. | ||
− | + | <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 42: PASSWORD=hda | Line 42: PASSWORD=hda | ||
Line 51: BACKUPDIR="/var/hda/files/dbbackup" | Line 51: BACKUPDIR="/var/hda/files/dbbackup" | ||
Line 35: | Line 50: | ||
Line 76: DBEXCLUDE="information_schema performance_schema mysql" | Line 76: DBEXCLUDE="information_schema performance_schema mysql" | ||
Line 79: CREATE_DATABASE=yes | Line 79: CREATE_DATABASE=yes | ||
− | Line 94: LATEST=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: | * 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 | 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): | ||
− | + | <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 | |
* To run anytime, as root do the following: | * 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. | * 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 | |
---|---|
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.