How to backup MySQL databases, web server files to a FTP server automatically

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

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…

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.

This script does ftp and e-mail backups.

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/

Thanks for the script. I’ll give it a whirl.

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

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.

Type the following commands restore the same:
gunzip mybackup.sql.gzip
mysql -u USER -p dbname < mybackup.sql

You can copy file mybackup.sql.gzip using scp to another machine:
scp mybackup.sql.gzip user@machinetwo:/tmp
Login 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

Copy and paste script again from wizard

HTH

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"

## 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"

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"

### 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

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.

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

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.

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.

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

Why not try to use mysqlhotcopy to make a backup

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?

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

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

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.

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

thank you for a wonderful script. works perfectly

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.

Hi
Well ncpftp is not available on my hosting I am using this on shared hosting.

I did change the bash script to

### Dump backup using FTP ###
#Start FTP backup using ncftp
#ncftp -u”$FTPU” -p”$FTPP” $FTPS<<EOF
# Login, run get files
ftp -inv $FTPS <<END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
mkdir $FTPD
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF

Hmm, don’t know what it did there, putting in that link, but I think you still get the gist of the problem

I get following error:
line 60: ncftp: command not found

how shall I fix it?

I just use MySQL administrator to run my daily backups. I can’t think of an easier solution. I have over 30 clients on a variety of shared MySQL databases, and VPS databases. I have the task scheduled to run at midnight every night. However I am searching for an automated web server solution for backups. Thus far I haven’t found anything. Would be great if I could find something like norton ghost but works via FTP. Making incremental backups every night. My hosting provider charges $25 / month for FTP backup. Its cheaper to go out and buy a 2 TB hard drives and run your own backups. If anyone has a more user friendly suggestion let me know.