Here is my script to backup a certain MySql db and certain directory files
FYI: this script only can working on Unix/Linux environtment
Example, We will backup one mysql db & directory files under /var/www and transfer it to remote ftp to another server and save it on incremental folder by date
db : test
user : root
pass : passwordrootmysql
Remote Ftp Server (Vsftpd):
ip : 10.10.1.3
user : usertest
pass : usertestpassword
Here is my script
#!/bin/bash
# Shell script to backup MySql database
# By Adityo
MyUSER=”root” # USERNAME
MyPASS=”passwordrootmysql” # PASSWORD
MyHOST=”localhost” # Hostname
# Linux bin paths, change this if it can’t be autodetected via which command
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
GZIP=”$(which gzip)”
# Get data in dd-mm-yyyy format
NOW=”$(date +”%d-%m-%Y”)”
# Backup Dest directory, change this if you have someother location
DEST=”/backup/mysqlscript”
# Main directory inside the backup Dest directory (DEST) where backup will be stored
MBD=”$DEST/mysql/$NOW”
# Get hostname
HOST=”$(hostname)”
# File to store current backup file
FILE=””
# Store list of databases
DBS=””
# DO NOT BACKUP these databases , you can choose another db that you did not want to backup and split it with space in this example i exclude the mysql , information_schema and backup database an di only backup the test db
IGGY=”mysql information_schema backup”
[ ! -d $MBD ] && mkdir -p $MBD || :
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
# Get all database list first
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”
for db in $DBS
do
skipdb=-1
if [ “$IGGY” != “” ];
then
for i in $IGGY
do
[ “$db” == “$i” ] && skipdb=1 || :
done
fi
if [ “$skipdb” == “-1″ ] ; then
FILE=”$MBD/$db.$HOST.$NOW.sql.gz”
# do all inone job in pipe,
# connect to mysql using mysqldump for select mysql database
# and pipe it out to gz file in backup dir 🙂
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db –lock-tables=false | $GZIP -9 > $FILE
fi
done
### Start backup files on didirectory /var/www
mkdir -p /backup/mysqlscript/data/$NOW
cd /backup/mysqlscript/data/$NOW
FILE=”blogbinusian-file-www-$NOW.tar.gz”
tar -cvf $FILE /var/www
### FTP server Setup ###
FTPD=”//incremental”
FTPU=”usertest”
FTPP=”usertestpassword”
FTPS=”10.10.1.3″
NCFTP=”$(which ncftpput)”
### Dump backup using FTP ###
#Start FTP backup using ncftp and transfer file to remote server
ncftp -u”$FTPU” -p”$FTPP” $FTPS<<EOF # connect to target ftp service
mkdir $FTPD/$NOW/ # it will create directory /home/usertest/incremental/$NOW
cd $FTPD/$NOW/ # it will changes directory /home/usertest/incremental/$NOW
lcd /backup/mysqlscript/mysql/$NOW # it will goes to source server directory /backup/mysqlscript/mysql/$NOW
mput * # upload all files on /backup/mysqlscript/mysql/$NOW
lcd /backup/mysqlscript/data/$NOW # it will goes to source server directory /backup/mysqlscript/data/$NOW
mput * # upload all files on /backup/mysqlscript/mysql/$NOW
quit # stop
EOF
