Ubuntu Linux Backup MySQL server Shell Script
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.
Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
Related Linux / UNIX FAQ:
- How can I restore a backup of a MySQL database?
- Howto: Use mysql or run mysql queries from shell script
- Restore a backup of a MySQL Database Server
- How do I start MySQL server without using startup script /etc/init.d/mysql?
- MySQL startup script under BSD/Linux
Discussion on This FAQ
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Please do not use the comment form to ask for help / question. Ask your question on the excellent Linux tech support forum. Thank you very much for stopping by our site!
Tags: backup_mysql_server, lftp_command, mysqldump_command, mysql_database_server, shell_script, ubuntu_linux ~ Last updated on: October 3, 2007
February 7th, 2008 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
May 6th, 2008 (1 weeks ago) at 6:02 pm
Thanks for this code! Its really help me!!!
Regards!