Monday, May 30, 2011

BASH script for taking MySQL dump

#!/bin/bash

#  Script to do MySQL dump


MyUSER="XXXXXX"     # USERNAME
MyPASS="XXXXXX"     # PASSWORD
MyHOST="localhost"     # Hostname

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="<Destination folder for backing up Mysqldump>"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y"+Second_Backup)"

# File to store current backup file
FILE=""
# Store list of databases
DBS="<List of Databases to be backed up. Can be found by using show databases command>"

# DO NOT BACKUP these databases
IGGY="test"

[ ! -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.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 | $GZIP -9 > $FILE
    fi
done

Popular Posts

About Me

My photo
I have started this blog to share my work experience and spread some smart solutions on Linux to Internet community. I'm hoping more people will get benefited from this blog. Brief about me: I have 14+ years experience working as System Admin and currently work with VMware.