Jun 04

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