How to backup MySQL databases, web server files to a FTP server automatically
This is a simple backup solution for people who run their own web server and MySQL server on a dedicated box or VPS. Most dedicated hosting provider provides the backup service using NAS or FTP servers. These service providers will hook you to their redundant centralized storage array over private VLAN. Since I manage couple of boxes, here is my own automated solution. If you just want a shell script, go here (you just need to provided appropriate input and it will generate FTP backup script for you on fly).
Making incremental backups with tar
You can make tape backups. However, sometime tape is not an option. GNU tar allows you to make incremental backups with -g option. For example following command will make incremental backup of /var/www/html, /home, and /etc directories:
# tar -g /var/log/tar-incremental.log -zcvf /backup/today.tar.gz /var/www/html /home /etc
Where,
- -g: Create/list/extract new GNU-format incremental backup and store information to /var/log/tar-incremental.log file.
Making MySQL databases backup
mysqldump is a client program for dumping or backing up mysql databases, tables and data. For example following command displays the list of databases:
$ mysql -u root -h localhost -p -Bse 'show databases'
Output:
Enter password: brutelog cake faqs mysql phpads snews test tmp van wp
Now you can backup each database with mysqldump command:
$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz
A simple backup system plan
The main advantage of using FTP or NAS backup is a protection from data loss. You can use various protocols to backup data:
- FTP
- SSH
- RSYNC
- Other Commercial solutions
However, I am going to write about FTP backup solution here. The idea is as follows:
- Make full backup every Sunday night i.e. backup everything every Sunday
- Next backup only those files that has been modified since the full backup (incremental backup)
This is a seven-day backup cycle.
Our sample setup
Your-server ===> ftp/nas server IP:202.54.1.10 ===> 208.111.2.5
Let us assume that your ftp details are as follows:
- FTP server IP: 208.111.2.5
- FTP Username: nixcraft
- FTP Password: somepassword
- FTP Directory: /home/nixcraft (or /)
You will store data as follows:
=> /home/nixcraft/full/mm-dd-yy/files - Full backup
=> /home/nixcraft/incremental/mm-dd-yy/files - Incremental backup
Automating tasks of backup with tar
Now you know how to backup files and mysql databases using tar and mysqldump commands respectively. It is time to write a shell script that will automate entire procedure.
- First script will collect all data from both MySQL database server and from file system to temporary directory called /backup using tar command
- Next, script will login to ftp server and create a directory structure as discussed above
- Script will dump all files from /backup to ftp server
- Script will remove temporary backup from /backup
- Script will send you an email notification if ftp backups failed due to any reason.
You must have following command installed:
- ncftp ftp client
- mysqldump command
- GNU tar command
Here is the sample script:
#!/bin/sh
# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by http://bash.cyberciti.biz/backup/wizard-ftp-script.php
# ---------------------------------------------------------------------
### System Setup ###
DIRS="/home /etc /var/www"
BACKUP=/tmp/backup.$$
NOW=$(date +"%d-%m-%Y")
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +"%a")
FULLBACKUP="Sun"
### MySQL Setup ###
MUSER="admin"
MPASS="mysqladminpassword"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP server Setup ###
FTPD="/home/vivek/incremental"
FTPU="vivek"
FTPP="ftppassword"
FTPS="208.111.11.2"
NCFTP="$(which ncftpput)"
### Other stuff ###
EMAILID="admin@theos.in"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" == "$FULLBACKUP" ]; then
FTPD="/home/vivek/full"
FILE="fs-full-$NOW.tar.gz"
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +"%Hh%Mm%Ss")
FILE="fs-i-$NOW-$i.tar.gz"
tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW-$(date +"%T").gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u"$FTPU" -p"$FTPP" $FTPS<
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF
### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $BACKUP/*
else
T=/tmp/backup.fail
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T
rm -f $T
fi
Setup a cron job
Just add a cron job as per your requirements:
13 0 * * * /home/admin/bin/ftpbackup.sh >/dev/null 2>&1
Generate FTP backup script
Since I setup many Linux boxes, here is my own FTP backup script generator. You just need to provided appropriate input and it will generate FTP backup script for you on fly.
Next time I will write more about this method.
You may also be interested in other helpful articles:
- How to MySQL backup and data recovery with mysql-zrm
- Howto: Make consistent MySQL database backups using Solaris ZFS snapshots
- MySql backup script
- How To Setup Disk Quotas For MySQL Database
- Recovered from database Disaster / Corruption
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
Tags: backup_mysql, backup_script, ftp_backup, ftp_servers, gnu_tar, incremental_backups, MySQL, mysql_server, shell_script, tape_backups
On the mysqldump, there’s a -A option to do all databases at once (and –opt to make things more efficient). As you get a bigger database, look at mysqlhotcopy, only good for MyIASM tables but it’s a lot faster than mysqldumping.
Sean
Sean,
I agree with you -opt is a nice option that adds locking and does extended insert and other stuff. I will update the script with -opt option.
-A is good option but I prefer to backup individual database, as it offers the option of restoring individual databases.
mysqldump –help says –opt option is Enabled by default
so no need to change script
Appreciate your post.
Why is the size of the files created using mysqldump are far more than the database files itself?
Rather if someone simply copies all database files to the backup directory..is there any harm…jst a question…
SaM,
You need to compress sql files using gunzip or other available utilities.
mysqldump makes it easy to move data from one server to another.
Hope this helps
Nix,
You may be right… but I am using a bit un-usual way of backing up tha data.. what I have done is that, I have created the database structure sql file of all that dtabases we are using. And copies the data files on regular basis.
when it comes to retrieve the data, its a simple copy operation…off-cource need to change the ownership to mysql:mysql.
In case I need to trash the datanase and recreate the database, i have the sql file of the structures.
mysqldump also generates database, table structure along with data. Try out mysqldump command any one of the database you will see the difference.
This script does ftp and e-mail backups.
Sorry, here is the link
http://worldcommunity.com/opensource/utilities/mysql_backup.html
Another good solution for MySQL database backup is “AutoMySQL Backup” . It does all the things you need for daily backup.
http://members.lycos.co.uk/wipe_out/automysqlbackup/
jishin,
Thanks for autmmysqlbackup script link
Thanks for the script. I’ll give it a whirl.
if i also want to enter into database in this command “$ mysql -u root -h localhost -p -Bse ’show databases’” what can i do??
What about MySQLDumper? (backup, restore, FTP, mail, multipart, …)
Well the bad thing is the vulnerabilities, like the common XSS thing:
http://secunia.com/product/12282/?task=advisories
Automatic backup to ftp server…
This page has a pretty simple script for backing up specified directories, and all mysql databases to a ftp server….
nixcraft, your script backs-up everything needed for my setup. It was instant success using the generator.
Thank-you
Shaun Prince
Thanks for the script, is brilliant and does exactly what i need it to do.
Just one slight real easy thing i’m sure, but how can i set the FTP port rather than using the standard port 21. If i ncftp manually, i can specify the -P option. Is it easy to put in the script?
Thank you for the script.
So I backed up my MySQL database. How do I restore it? I have a mybackup.sql.gzip in my home. What do I do to restore it?
Can I restore it to another machine with the same MySQL Version?
Type the following commands restore the same:
gunzip mybackup.sql.gzipmysql -u USER -p dbname < mybackup.sql
You can copy file mybackup.sql.gzip using scp to another machine:
scp mybackup.sql.gzip user@machinetwo:/tmpLogin to machinetwo:
cd /tmp
gunzip mybackup.sql.gzip
mysql -u USER -p dbname < mybackup.sql
rm mybackup.sql
Further readings
(a) How can I restore a backup of a MySQL database?
(b) Copy MySQL database from one server to another remote server
HTH
Hi,
this is very nice but i get the following errors:
[: 47: ==: unexpected operator
at the start of the script
and
[: 79: ==: unexpected operator
at the end
therefor I always get email with BACKUP FAILED!.\
Line 47 is “fi”
and Line 79 is the last “fi” at the very end.
Thanx
Copy and paste script again from wizard
HTH
Thanx for the reply but I had more troubles
with the long names with the mysqldump.
Filezilla Server did not like the
(mysql-LongDBname.$NOW-$(date +”%T”).gz) but was very happy when I took out the $(date +”%T”) part, which is fine with me I don’t care for the exact time so much.
For the unexpected operator Error:
I have the feeling that the == sign is not the case with “$var1″ == “$var2″.
Therefore my system was complaining for syntax error(Ubuntu Server 7.04).
From what I have seen on the net:
you can say “$var1″ = “$var2″ OR $var1 == $var2.
I haven’t taste the second option myself.
But the first behaves very nice.
Finally I just took out the -g option from tar because I could not restore from a windows machine when I try it.
7zip could not understand the archive and could not extract the contents.
Lastly I added a Open Ldap database backup too, and send a mail also in success!. I simply wanna know what is is happening with the backup process.
Here it is now:
#!/bin/sh
### System Setup ###
DIRS="/etc /srv/www /var/www /home"
BACKUP="/tmp/backup.${$}"
NOW=$(date +%d-%m-%Y)
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +%a)
FULLBACKUP="Fri"
### MySQL Setup ###
MUSER="user"
MPASS="passwd"
MHOST="localhost"
### FTP server Setup ###
FTPD="/backup/Ubuntu-Server/incremental"
FTPU="user"
FTPP="passwd"
FTPS="192.168.2.50"
NCFTP=$(which ncftpput)
### Other stuff ###
EMAILID="user@domain.com"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" = "$FULLBACKUP" ];.
then
FTPD=”/backup/Ubuntu-Server/full”
FILE=”fs-full-${NOW}.tar.gz”
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +%Hh%Mm%Ss)
FILE=”fs-i-${NOW}-${i}.tar.gz”
tar zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS=”$(mysql -u ${MUSER} -h ${MHOST} -p${MPASS} -Bse ’show databases’)”
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW.gz
mysqldump -u $MUSER -h $MHOST -p$MPASS $db | gzip -9 > $FILE.
done
##Backup the Ldap Directory Database
slapcat -v -n 1 -l $BACKUP/LdapDirectory.ldif
## Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u$FTPU -p$FTPP $FTPS$T
echo “Hostname: $(hostname)” >>$T
echo “Backup succesfully Completed!” >>$T
mail -s “BACKUP COMPLETED” “$EMAILID” $T
echo “Hostname: $(hostname)” >>$T
echo “Backup failed” >>$T
mail -s “BACKUP FAILED” “$EMAILID”
Cheers A.
Sorry Here is the complete script hope this time is posted clear.
#!/bin/sh### System Setup ###
DIRS="/etc /srv/www /var/www /home"
BACKUP="/tmp/backup.${$}"
NOW=$(date +%d-%m-%Y)
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +%a)
FULLBACKUP="Fri"
### MySQL Setup ###
MUSER="user"
MPASS="passwd"
MHOST="localhost"
### FTP server Setup ###
FTPD="/backup/Ubuntu-Server/incremental"
FTPU="user"
FTPP="passwd"
FTPS="192.168.2.50"
NCFTP=$(which ncftpput)
### Other stuff ###
EMAILID="user@domain.com"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" = "$FULLBACKUP" ];.
then
FTPD=”/backup/Ubuntu-Server/full”
FILE=”fs-full-${NOW}.tar.gz”
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +%Hh%Mm%Ss)
FILE=”fs-i-${NOW}-${i}.tar.gz”
tar zcvf $BACKUP/$FILE $DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS=”$(mysql -u ${MUSER} -h ${MHOST} -p${MPASS} -Bse ’show databases’)”
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW.gz
mysqldump -u $MUSER -h $MHOST -p$MPASS $db | gzip -9 > $FILE.
done
##Backup the Ldpa Directory Database
slapcat -v -n 1 -l $BACKUP/LdapDirectory.ldif
## Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u$FTPU -p$FTPP $FTPS$T
echo “Hostname: $(hostname)” >>$T
echo “Backup succesfully Completed!” >>$T
mail -s “BACKUP COMPLETED” “$EMAILID” $T
echo “Hostname: $(hostname)” >>$T
echo “Backup failed” >>$T
mail -s “BACKUP FAILED” “$EMAILID”
However you got the my point!
maybe cut it in pieces
#!/bin/sh### System Setup ###
DIRS="/etc /srv/www /var/www /home"
BACKUP="/tmp/backup.${$}"
NOW=$(date +%d-%m-%Y)
INCFILE="/root/tar-inc-backup.dat"
DAY=$(date +%a)
FULLBACKUP="Fri"
### MySQL Setup ###
MUSER="user"
MPASS="passwd"
MHOST="localhost"
### FTP server Setup ###
FTPD="/backup/Ubuntu-Server/incremental"
FTPU="user"
FTPP="passwd"
FTPS="192.168.2.50"
NCFTP=$(which ncftpput)
### Other stuff ###
EMAILID="user@domain.com"
### Start Backup for file system ###
[ ! -d $BACKUP ] && mkdir -p $BACKUP || :
### See if we want to make a full backup ###
if [ "$DAY" = "$FULLBACKUP" ];.
then
FTPD=”/backup/Ubuntu-Server/full”
FILE=”fs-full-${NOW}.tar.gz”
tar -zcvf $BACKUP/$FILE $DIRS
else
i=$(date +%Hh%Mm%Ss)
FILE=”fs-i-${NOW}-${i}.tar.gz”
tar zcvf $BACKUP/$FILE $DIRS
fi
And the Second part:
### Start MySQL Backup #### Get all databases name
DBS="$(mysql -u ${MUSER} -h ${MHOST} -p${MPASS} -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/mysql-$db.$NOW.gz
mysqldump -u $MUSER -h $MHOST -p$MPASS $db | gzip -9 > $FILE.
done
##Backup the Ldap Directory Database
slapcat -v -n 1 -l $BACKUP/LdapDirectory.ldif
## Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u$FTPU -p$FTPP $FTPS
And the Third Part:
### Find out if ftp backup failed or not ###if [ "$?"="0" ];.
then
rm -f $BACKUP/*
echo “Date: $(date)”>$T
echo “Hostname: $(hostname)” >>$T
echo “Backup succesfully Completed!” >>$T
mail -s “BACKUP COMPLETED” “$EMAILID” $T
echo “Hostname: $(hostname)” >>$T
echo “Backup failed” >>$T
mail -s “BACKUP FAILED” “$EMAILID”
Hope now it works to see the hole script.
About date localization
The command DAY=$(date +%a) is sensible to the system language,
I prefer to use DAY=$(date +%u) to know the number of the day of the week
I was looking for something like this…
just one question: to send the email of a failed backup what program do I have installed in my pc?
Thankyou
dk,
use normal unix mail command
Thanks for the info.. just wondering, does it work with a regular ftp client? I’m on a shared hosting shell account and it doesn’t look like ncftp is installed.
Hi!
Thank you for this nice tutorial!!
I have a setup with 2 HDDs, having the second as active mysql database storage and another partition for this backup script.
I have modified it to just move the files to the second HDD instead of transfering via FTP and made it so that folders with $NOW are created before moving.
I am wondering how the $INCFILE works? If i want to restore the “fs” from a week before, do i have to use the initial (bigger) TAR which is in the first folder, or does it work with the smaller ones that are i.e. in the folder of today?
You need to restore the last full backup first (
$FULLBACKUP day), followed by each of the subsequent incremental backups to the present day in the correct order.
Here’s a script that can transfer all mysql databases to another server.
Hello,
When I run this script from the command line it works fine but when I run it as a cron job, the FTP connection seems to drop at some point and not all files are not copied over. The FTP server says “disconnected” so as ncftp client logs.
It happens all the time and it is a bit weird. Any clues?
Thanks,G
Heelo,
tks for the script.
I have a problem when running the script with a cron job, it will do the backup but it will not upload via http://ftp. Any idea why?
Best regards,
Nuno
Why not try to use mysqlhotcopy to make a backup
This looks great, but in Ubuntu 7.10, ncftp isn’t working.
the command
ncftp -u”$FTPU” -p”$FTPP” $FTPS<
gives an error:
wwwbackup.sh: 53: Syntax error: newline unexpected
If I remove the < at the end, it will log in, but then it won’t send the commands to ncftp (after NCFTP quits, it will try running those commands on my own shell!)
Are there ny other way to get ncftp to take commands from the shell script?
OK I think I solved my problem by using ncftpput*
I used this line instead of the ncftp lines you have:
ncftpput -u “$FTPU” -p “$FTPP” -m “$FTPS” $FTPD/$NOW $BACKUP/*
and commented out the ncftp command through EOF command.
* by the way, why do you get the path of ncftpput and never use it?
Berto,
It is a bug, I will try to fix it. Thanks for the heads up.
Even more information on my humiliations in Ubuntu:
They made dash the default shell script, not bash!* So you need to specify $!/bin/bash (not $!/bin/sh) for this to work properly. dash also had problems with the equivalency (==) operator at the end.
This will probably fix the ncftp problems, but since I already have ncftpput working, I’ll keep it.
*From http://ubuntuforums.org/showthread.php?t=265391
Firstly, Thank you so much for this script. I’ve learnt so much just from following it through, and it works perfectly on CentOS 4 after installing ncftp.
Is there a way you can tell it to exclude files? For example, I’ve set it to back up the whole of a certain folder, but there is one sub folder within that I do not want backing up… is this possible?
Thanks!
Hi,
I have two questions. I would have multiple databases to back up in most cases, and multiple users assigned to multiple databases. How would that work?
Many thanks!
Ken
I am getting an error
line53: syntax error near unexpected token ‘do
line53: ‘do
Very helpful. I’m experimenting with setting up a couple servers of my own, and this script is exactly what I was looking for. Thanks for sharing.
You can also use:
/usr/bin/md5sum -b $BACKUP/* >$BACKUP/backup$NOW-$i.md5
Between backup and transfer, so you can verify if the files aren’t modified during transfer or something.
This seems to work well, except when I run it it just does incremental backups. Of what, I’m not sure, as there has not been a full backup yet. Invariably it generates just a 48kb file. The database alone should be at least 800kb… Is there any way to force a full backup? How can I check that this script is working properly?
Thanks,
Adrian
Backup is compressed using gzip; just uncompressed and verify data. By default full backup is made on Sunday; Change FULLBACKUP=”Tue” variable.
thank you for a wonderful script. works perfectly
In this:
if [ "$?" == "0" ]; then
you are checking the return of ncftp, but it always returns “0″
I even altered the password to force it to error and still it returned “0″.
Has anyone confirmed that this will show anything other than “0″?
I did this to check before the if statement
echo “Return Code = $?”
for some unknown reason if i run the script manually it works as it should. everything is zipped up and sent to the ftp server.
However if i set up a cron job all the files are zipped up and placed in the /tmp dir (so i know the script is running) but the files are never sent to the ftp server.
i’ve tried this with several ftp servers etc. etc. with no luck.
Thanks heaps. Clever script. Worked fully once I gave mysql user LOCK privileges. Very helpful.
Might suggest putting php.txt link up at top of wizard page. I entered dummy info, not wanting to send IPs and logins to php script. I now have the script itself and could generate again.
Nice work and thanks for sharing.