Creating MySQL Backups With AutoMySQLBackup

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Mon, 2008-11-03 12:33. :: Backup | MySQL

Creating MySQL Backups With AutoMySQLBackup

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 10/17/2008

AutoMySQLBackup is a shell script that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

AutoMySQLBackup uses mysqldump to create SQL dumps of your databases. Please note that mysqldump will lock your databases while the backup is being created, and this can take from less than a second up to a few minutes, depending on the size of your database. If you're running a high-traffic web site with a large database, then AutoMySQLBackup is not for you!

This script will not help in the event of a hard drive crash. You should copy your backups offline regularly for best protection.

 

2 Using AutoMySQLBackup

You can download AutoMySQLBackup as follows:

cd /usr/local/bin
wget http://mesh.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5

Then open automysqlbackup.sh.2.5 and take a look at the configuration options. They are all well explained. You should at least configure the following settings:

vi automysqlbackup.sh.2.5

[...]
USERNAME=root
[...]
PASSWORD=yourrootsqlpassword
[...]
DBHOST=localhost
[...]
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="db_ispconfig web1 web2 web3"
[...]
# Backup directory location e.g /backups
BACKUPDIR="/backups"
[...]
# Email Address to send mail to? (user@domain.com)
MAILADDR="user@domain.com"
[...]

DBNAMES can contain one or multiple databases, separated by spaces.

If BACKUPDIR does not exist, automysqlbackup.sh.2.5 will create it automatically.

Make sure you fill in the right password and database host. If you want to back up local databases, use localhost; if you want to back up remote databases, use the remote hostname (please note that the remote database server must be configured to allow remote connections!).

Now we must make the script executable:

chmod 755 automysqlbackup.sh.2.5

Now you can run automysqlbackup.sh.2.5 like this if you are in the /usr/local/bin directory:

./automysqlbackup.sh.2.5

... or like this from any other directory:

automysqlbackup.sh.2.5

This is a sample output:

server1:~# automysqlbackup.sh.2.5
======================================================================
AutoMySQLBackup VER 2.5
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - server1.example.com
======================================================================
Backup Start Time Fri Oct 17 16:00:51 CEST 2008
======================================================================
Daily Backup of Database ( db_ispconfig )
Rotating last weeks Backup...


Backup Information for /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
         compressed        uncompressed  ratio uncompressed_name
              37231              382465  90.3% /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
----------------------------------------------------------------------
Backup End Fri Oct 17 16:00:52 CEST 2008
======================================================================
Total disk space used for backup storage..
Size - Location
68K /backups

======================================================================
If you find AutoMySQLBackup valuable please make a donation at
http://sourceforge.net/project/project_donations.php?group_id=101066
======================================================================
server1:~#

Take a look at the /backups directory...

ls -l /backups

... and you should find three subdirectories, daily, weekly, and monthly:

server1:~# ls -l /backups/
total 12
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 daily
drwxr-xr-x 2 root root 4096 2008-10-17 16:00 monthly
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 weekly
server1:~#

These directories will contain subdirectories named after the databases you chose to backup. For example, if you chose the database db_ispconfig, there will be a directory /backups/daily/db_ispconfig containing the database dump:

cd /backups/daily/db_ispconfig
ls -l

server1:/backups/daily/db_ispconfig# ls -l
total 40
-rw-r--r-- 1 root root 37231 2008-10-17 16:00 db_ispconfig_2008-10-17_16h00m.Friday.sql.gz
server1:/backups/daily/db_ispconfig#

The .gz extension means it's compressed. To restore a database, you'd first have to uncompress the dump:

gunzip db_ispconfig_2008-10-17_16h00m.Friday.sql.gz

... (this will give you the uncompressed dump named db_ispconfig_2008-10-17_16h00m.Friday.sql) and then restore it as described on http://www.howtoforge.com/faq/6_4_en.html.

Of course, you don't want to run automysqlbackup.sh.2.5 manually all the time. Therefore, we can create a daily cron job for it as follows:

cd /etc/cron.daily/
ln -s /usr/local/bin/automysqlbackup.sh.2.5 automysqlbackup

 

3 Links


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by joseP (not registered) on Fri, 2011-03-18 16:07.

It was really easy to implement and works pretty well.

I want to know if you can recomend an online service for backups to be able to upload the backed file.

also if there is a way to limit the amount of backed files to X number like 2 on the monthly 5 on the weekly and 7 on the daily so the hard drive does not get full of backups.

with a 2.4Gb database 250MB after compress it's important to be carefull and not run out of space so have it to clean the previous file lets say it runs today monday 7 so delete the previous Monday 1 and so

thanks for this great scrigt. :)

Submitted by Ross (not registered) on Tue, 2010-11-30 16:15.

I can run the automysqlbackup using sudo, but when run without it I get an 'find: '/var/lib/mysql' : Permission denied' error

I'm using Ubuntu, any idea what I'm doing wrong?

 

 

Submitted by emma (not registered) on Wed, 2010-06-02 10:47.

Nice article.

For backing up large databases with mysqldump there is a nice script that split up the files by table.

http://moinne.com/blog/ronald/mysql/backup-large-databases-with-mysqldump

For really large databases that cannot be taken offline, you need another method...

/Emma.

Submitted by Absolute (not registered) on Mon, 2009-11-09 14:15.

Thanks again.

Submitted by pearfire575 (not registered) on Mon, 2009-11-09 00:23.

Thanks for this simple tutorial on this great tool. Works wonders!

Submitted by David Ribera (not registered) on Sun, 2009-03-29 17:21.

Hi,
Thanks for the explanation. The script is working pretty fine for me.
I find very useful to change the option LATEST to yes.
This way creates a separated directory with the latest copy of each database. Very handy when it comes the time to search where is latest backup is stored.

Submitted by Corey Hopfner (not registered) on Thu, 2009-02-26 23:12.

Thanks for the info. Changing the file permissions  to 711 rather than 755 on the automysqlbackup.sh.2.5 file would be more secure as this file is holding secure info (your root mySQL user and pass). Set as 711 any user on the system could view the file and gain root access to your mySQL databases.

Cheers

 

 

 

Submitted by Anonymous (not registered) on Tue, 2009-07-14 21:19.

When I upload the file to my var/www/ folder and chmod 755 I still can not get this script to work I get a -bash automysqlbackup.sh.2.5 command not found ??

I have a basic unbuntu LAMP server setup

thanks for any help 

Submitted by Anonymous (not registered) on Thu, 2013-11-14 10:55.

Does anybody know something about a backup software for MySQL named Handy Backup?

I`m very interested in the tool.