Ubuntu Linux Backup MySQL server Shell Script
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
ORsudo /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.
