Aug 24

It’s been a while i haven’t post anything 🙂 , i got this request on how to backup all of mysql databaseand since the hdd is not quite big it also required to delete the old backup. So here is the backup script

###################################################################################################

#!/bin/sh

DATE=`date +%d%b-%H%M%S`

unset PATH
# USER VARIABLES
MYSQLUSER=user
MYSQLPWD=password
MYSQLHOST=localhost
MYSQLBACKUPDIR=/home/backup/mysql/mysql_backup$DATE

FILE=$MYSQLBACKUPDIR.tgz; # No need to modify this one

# PATH VARIABLES – It depend on your server program
MK=/bin/mkdir;
RM=/bin/rm;
GREP=/bin/grep;
MYSQL=/usr/local/mysql/bin/mysql;
MYSQLDUMP=/usr/local/mysql/bin/mysqldump;
XARGS=/usr/bin/xargs;
FIND=/usr/bin/find;
TAR=/bin/tar;
UUENCODE=/usr/bin/uuencode;

# CREATE MYSQL BACKUP

# Delete the old ones. The +7 means 7 days ago
$FIND /home/backup/mysql/ -mtime +7 |$XARGS $RM -rf

# Create new backup dir
$MK -p $MYSQLBACKUPDIR

#Dump new files
for i in $(echo ‘SHOW DATABASES;’ | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v ‘^Database$’); do
$MYSQLDUMP                                                    \
-u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
-Q -c -C –add-drop-table –add-locks –quick –skip-lock-tables   \
$i > $MYSQLBACKUPDIR/$i.sql;
done;

# collect and compress the sqls
$TAR -cvzpf $MYSQLBACKUPDIR.tgz $MYSQLBACKUPDIR
$RM -r $MYSQLBACKUPDIR

###################################################################################################

Then save it , i save it as mysql_backup file. Then open the crontab to run it weekly

# crontab -e

then put this

0 0 * * 0 /root/mysql_backup > /dev/null 2>&1

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