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
The intent of this blog is to share my work experience and spread some smart solutions on Linux to System Administrators. I'm hoping the solutions shared in this Blog would be helpful and come as a handy for Viewers. Brief about me: I have 18+ years work experience in System and Cloud Administration domain, primarily works on VMware Cloud Products (vSphere, vCloud Director, vRealize Automation, NSX Adv. Load Balancer, vROps).