Ubuntu Linux Backup MySQL server Shell Script

by Vivek Gite · 7 comments

Q. I’m new to Linux and I’ve dedicated VPS server running Ubuntu Linux. I’m using CMS software and MySQL act as database server. Can you explain how can I backup all mysql server databases to ftp server IP address called 10.1.5.2?

A. You can use mysqldump command to backup database. The mysqldump client is a backup program. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server. The dump contains SQL statements to create the table or populate it, or both.

Once database is dumped, you need to upload the same to ftp server. Use lftp client to upload all files.

Install lftp

lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:

sudo apt-get install lftp

Shell script to backup MySQL database server

Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server. You need to setup correct username and password before using the script:

#!/bin/bash
### MySQL Server Login Info ###
MUSER="root"
MPASS="MYSQL-ROOT-PASSWORD"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
FTPU="FTP-SERVER-USER-NAME"
FTPP="FTP-SERVER-PASSWORD"
FTPS="FTP-SERVER-IP-ADDRESS"
NOW=$(date +"%d-%m-%Y")

[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BAK/$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

lftp -u $FTPU,$FTPP -e "mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

Save script as /home/your-name/mysql.backup.sh file. Setup executable permission:
$ chmod +x /home/your-name/mysql.backup.sh
To backup MySQL, enter:
/home/your-name/mysql.backup.sh
OR
sudo /home/your-name/mysql.backup.sh

Run MySQL backup script as cron job

To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
$ sudo crontab -e
Append following cron job:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
Save and close the file. Please note that above script should work with other Linux distros or UNIX like oses.

Featured Articles:

Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our daily email newsletter to make sure you don't miss a single tip/tricks. Subscribe to our weekly newsletter here!

{ 7 comments… read them below or add one }

1 davidevans 02.07.08 at 6:42 pm

I’m trying to invoke a lftp command within a shell script from a sqr. The idea is to lftp a file from unix to the mainframe. Unfortunately, I’ve receive the error message, “Unexpected Error”. The sqr code is as follows:
let $script = ‘/server/peoplesoft/tmp/lftp.ssh’
call system $script #status
*******Unix Script*****
#!/bin/ksh
lftp -u USERIDSAM, PASSWORD LFTP.TEST.IT.EDU
put testfile.txt testtmp.txt
exit
#ption: This shell script will lftp a file from unix to the mainframe

2 The Traveller 05.06.08 at 6:02 pm

Thanks for this code! Its really help me!!!

Regards!

3 ravi 08.27.08 at 6:53 am

hi friends i m new to shell scripting. i want some help if u can. i want to take binary backup (not the simple dump)of mysql database and store it in a folder named of current date and this script should run at early hours of each day.

4 TimeWaster 07.19.09 at 6:55 pm

THANX! works like a charm!

5 Naveen 07.31.09 at 9:41 am

I want to take backup for MYSQL server using java code. I use a backup file name – backup20060731.bak. Now I want to execute this file so that I coule place it into a .zip file and sore it to to a repository using FTP.
I want this type of command for executing the backup(“.bak”) file

Runtime rt = Runtime.getname();
Runtime rt = Runtime.getname();
rt.exec(“exp User/Password file = ” + backupFileName + “log = ” + backupFileName + “.log FULL = y”);

The above command is for ORACLE database. Can any body give me the command for MYSQL database or SQL server 2000 database or POSTGRES database.
Please help me, I need it urgently.

6 ZYV 08.28.09 at 8:04 pm

[ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

Most stupid idea ever. Just accidentally wiped my ~/bin directory. Ah, well…

7 skyper 09.21.09 at 8:33 pm

But how to create with proftpd?

Leave a Comment

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Previous FAQ:

Next FAQ:

nixCraft FAQ PDF Collection Now Available To All