Setting up daily, weekly and monthly MySQL Database Backups using AutoMySQLBackup

AutoMySQLBackup is a shell script that allows daily, weekly and monthly backups of your local and remote MySQL Databases. It’s meant to run on Linux/Unix through a cron job. Its highly configurable, and easy to setup as I will show you today! First you will need to acquire the shell script and drop it on your server. You can download AutoMySQLBackup on their SourceForge Project Page

AutoMySQLBackup is a shell script that allows daily, weekly and monthly backups of your local and remote MySQL Databases. It’s meant to run on Linux/Unix through a cron job. Its highly configurable, and easy to setup as I will show you today!

First you will need to acquire the shell script and drop it on your server. You can download AutoMySQLBackup on their SourceForge Project Page

Typically I will just do a “wget” into a downloads folder within my root directory just to keep it organized:

[root@jager:/root]# mkdir download
[root@jager:/root]# cd download/
[root@jager:/root/download]# wget “http://internap.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5”
–12:01:24– http://internap.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5
=> `automysqlbackup.sh.2.5′
Resolving internap.dl.sourceforge.net… 66.79.151.7, 66.79.148.7
Connecting to internap.dl.sourceforge.net|66.79.151.7|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 25,316 (25K) [application/x-sh]

100%[=======================================================================================================================================>] 25,316 –.–K/s

12:01:25 (180.28 KB/s) – `automysqlbackup.sh.2.5′ saved [25316/25316] [root@jager:/root/download]#

Now that we have the script, we need to give it a home. Since this is a custom script thats not apart of the distribution, we can place it in “/usr/local/sbin”.

[root@jager:/root/download]# cp automysqlbackup.sh.2.5 /usr/local/sbin/automysqlbackup.sh
`automysqlbackup.sh.2.5′ -> `/usr/local/sbin/automysqlbackup.sh’
[root@jager:/root/download]# cd /usr/local/sbin
[root@jager:/usr/local/sbin]# ls -la
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rw-r–r– 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh

So now the script has a home we must allow it to run. A simple “chmod u+x” will do, you will also want to confirm that only the user has execute permission and not the group or other since we don’t want users on the server running the script or even seeing whats inside the file as it will have a database login.

[root@jager:/usr/local/sbin]# chmod u+x automysqlbackup.sh
[root@jager:/usr/local/sbin]# ls -al
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rwxr–r– 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh*

As you can see above, User/Group/Other have read access, which we will have to change.

[root@jager:/usr/local/sbin]# chmod 700 automysqlbackup.sh
[root@jager:/usr/local/sbin]# ls -al
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rwxr-xr-x 1 root staff 4184 2008-01-01 05:27 addwww*
-rwx—— 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh*

There, now only the user will be able to execute and read the files contents. Lets move on to configuration.

The configuration is rather simple, you’ll be asked for a username, password and hostname as well as a directory that you would like to have the backups stored. Now this will pose a security concern as you will be providing your login information for your databases, you can choose to use the root login or create a login that will only allow SELECT for all databases. This is mostly up to you, just remember that its possible if you system is compromised or if permissions change on this file that 3rd parties might have access to the login information contained within this file.

Lets go over the basics.

# Username to access the MySQL server e.g. dbuser
USERNAME=root

# Username to access the MySQL server e.g. password
PASSWORD=password

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”DB1 DB2 DB3″

# Backup directory location e.g /backups
BACKUPDIR=”/backups”

# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”stdout”

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″

# Email Address to send mail to? ([email protected])
MAILADDR=”[email protected]

Most of the above is pretty straight forward, you mostly have to just change the username and password and chagne “DBNAMES” to “ALL”. You will also want to keep track of the backups via email incase they break. Here’s and example configuration:

# Username to access the MySQL server e.g. dbuser
USERNAME=root

# Username to access the MySQL server e.g. password
PASSWORD=mymysqlpassword

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”all”

# Backup directory location e.g /backups
BACKUPDIR=”/home/backup”

# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”stdout”

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″

# Email Address to send mail to? ([email protected])
MAILADDR=”root”

There is detailed information within the configuration file that outlines each option and its purposes and values. The following are a couple that you should keep in mind or that might be relevant.

# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).

The following has a good point about cron.d on Debian:

# Finally copy automysqlbackup.sh to anywhere on your server and make sure
# to set executable permission. You can also copy the script to
# /etc/cron.daily to have it execute automatically every night or simply
# place a symlink in /etc/cron.daily to the file if you wish to keep it
# somwhere else.
# NOTE:On Debian copy the file with no extention for it to be run
# by cron e.g just name the file “automysqlbackup”

# The list of MDBNAMES is the DB’s to be backed up only monthly. You should
# always include “mysql” in this list to backup your user/password
# information along with any other DBs that you only feel need to
# be backed up monthly. (if using a hosted server then you should
# probably remove “mysql” as your provider will be backing this up)
# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed
# up anyway.

The following is the exact rotation schedule.

#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly..
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run…Can be changed with DOWEEKLY setting..
# Weekly Backups are rotated on a 5 week cycle..
# Monthly Backups are run on the 1st of the month..
# Monthly Backups are NOT rotated automatically…
# It may be a good idea to copy Monthly backups offline or to another
# server..

So now you just need to install a cron for this to run daily! Just enter the following into your /etc/crontab.

59 11 * * * root /usr/local/sbin/automysqlbackup.sh

The about line will run the backup at midnight based on the servers timezone. You can also copy the script to /etc/cron.daily if you’re on Debian or if your distribution supports this method.

[root@jager:/usr/local/sbin]# mv automysqlbackup.sh /etc/cron.daily/automysqlbackup

You must make sure that you leave the .sh off the end of the filename in order for it to be processed by the cron daemon. Last but not least run the script and see if it works!


Did you like this article?


0 Shares:
You May Also Like

Exim4 and PHP and PHP-CGI mail() function using incorrect From: and applying Sender: headers.

If you're using Exim4 and PHP as a module or as a CGI with suexec. You may have noticed some issues with your mail. Specifically you would have noticed that either the "From:" header was using "nobody@machinename" or "user@machine name, its also possibly that you had an additional header called "Sender:". There are two things you need to do to fix this. You first need to make sure that your "php.ini" has the following value "sendmail_path = /usr/sbin/sendmail -t -i". Which is the default, double check this variable as it might be set to something else.
Read More

Boeing’s unmanned A160T Hummingbird helicopter takes flight

Not the first Unmanned aerial vehicle (UAV) made. But the Boeing A160HT Humming Bird should be one of the best once further tests and simulation in combat missions is completed.
Boeing's unmanned A160T Hummingbird helicopter takes flight -

Filed under:


It's been a few ticks since we've seen Boeing send an atypical aircraft into the friendly skies, but the firm's latest helicopter has successfully completed a 12-minute test flight without so much as a pilot on board. The A160T Hummingbird unmanned rotorcraft is a turbine-powered "warfighter" that aims to provide "intelligence, surveillance, and reconnaissance coverage" in locales that could make even the most calloused veteran queasy. During its time in the air, it reportedly met every objective set for it, and while we've no idea how soon this thing will be lifting itself up, it'll eventually reach speeds of up to 140 knots and stay airborne for up to 20 hours before returning to base for a pat on the wing.

[Via The Raw Feed, image courtesy of SkyControl]

 

Read | Permalink | Email this | Comments


Office Depot Featured Gadget: Xbox 360 Platinum System Packs the power to bring games to life!

[EnGadget]
Read More

Official Jailbreak for iPhone 2.1 Firmware Released OSX Only

There is now an official iphone-dev.org version of QuickPwn that supports the 2.1 firmware released by Apple on friday.
Some of the popular press and blogs have been backing the opposition. :-) While criticism and competition is fine it should be reported correctly, with all the facts. and certainly minus the FUD. Do you guys think we are “less and less relevant with each passing day” ? We don’t think so, and we certainly prefer our hacks to theirs. ;-) Though even if the world deems us irrelevant, the iPhone family of devices is still fun to hack!
Read More