Script for MYSQL maintenance (Check,Analyze & Optimize)

Like other relational databases MYSQL do requires scheduled maintenance for optimal performance and catch corruption proactively.As a best practice there should be scheduled maintenance job in place.

Quick benefits of database regular maintenance:

•  Performance enhancement
•  Optimal use of hardware resource (return white space to OS by reducing fragmentation,reduce IOPS utilization)
•  Corruption can be reviewed proactive for databases
•  Enhance replication sync speed (Master-slave architecture)
•  Reduce bottleneck of resources (CPU,IO,MEMORY)

Script: I wrote a single generic script which can be use for check, analyze and optimize with few additional parameters which make the script not to write binary logging while maintenance job is running.

How to use: Please refer below the quick snapshot and make use of functions used in script and you can schedule in your cronjob.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#!/bin/bash

#MYSQL maintenance analyze,optimize and check

#prints usage

usage()

{

    echo version: "$VERSION"

    echo "

Usage: `basename $0` [command] [options]

Commands:

    check                       check all database

    analyze                     analyze all database

    optimize                    optimize all database

    "

}

# creates defaults values

initialize()

{

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

#manual entries by user

    WORKINGDIR=/tmp

    user=root

    pass="google@123"

    VERSION=1.0

#Manual entries section ends

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

        CURDATE=`date +%y%m%d_%H%M%S`

#pre-defined defaults, can be overriden

    dLOGDIR=$WORKINGDIR/log

        dCHECKLOG=check_log

        dANALYZELOG=analyze_log

        dOPTIMIZELOG=optimize_log

}

#check if script is already running

if [[ -f /tmp/myscript.running ]] ; then

echo "Script is already running"

#mailx -s "Maintenance script on `hostname` already running"  jasjeet9800@gmail.com

exit

fi

#touch /tmp/myscript.running

if [[ ! -e /tmp/log ]]; then

            mkdir -p /tmp/log

fi

#parses options

parse_options()

{

    if [ -z "$1" ]

    then

        usage

        exit 1

    fi

    case $1 in

    check)                  COMMAND=do_check;;

    analyze)            COMMAND=do_analyze;;

    optimize)           COMMAND=do_optimize;;

    *)                     usage; exit 1;;

    esac

    shift

OPTIONS=$@

    #setting environment variables

        if [ -z $LOGDIR ]

    then

        LOGDIR=$dLOGDIR

    fi

        if [ -z $CHECKLOG ]

    then

        CHECKLOG=${dCHECKLOG}.${CURDATE}

    fi

                if [ -z $ANALYZELOG ]

    then

        ANALYZELOG=${dANALYZELOG}.${CURDATE}

    fi

                if [ -z $OPTIMIZELOG ]

    then

        OPTIMIZELOG=${dOPTIMIZELOG}.${CURDATE}

    fi

    }

#Check All Databases

do_check()

{

touch /tmp/myscript.running

DBNAMES=`mysql -u$user -p$pass -BNse 'show databases'`

for i in $DBNAMES

do

echo "**************************************************************************************** " >>$LOGDIR/$CHECKLOG 2>&1

echo "Checking up $i database (starting time $SECONDS)"        >>$LOGDIR/$CHECKLOG 2>&1

mysqlcheck -u$user -p$pass --check $OPTIONS --skip-write-binlog "$i" >>$LOGDIR/$CHECKLOG 2>&1

echo "Checking on $i database completed (ending time $SECONDS)"  >>$LOGDIR/$CHECKLOG 2>&1

echo "**************************************************************************************** " >>$LOGDIR/$CHECKLOG 2>&1

done

echo $LOGDIR/$CHECKLOG

rm -f /tmp/myscript.running

# mailx -s "MYSQLCHECK for checkdb on `hostname` has been completed" -a "$LOGDIR/$CHECKLOG" jasjeet9800@gmail.com

}

#Analyze all Databases

do_analyze()

{

touch /tmp/myscript.running

DBNAMES=`mysql -u$user -p$pass -BNse 'show databases'`

for i in $DBNAMES

do

echo "**************************************************************************************** " >>$LOGDIR/$ANALYZELOG 2>&1

echo "Analysing up $i database (starting time $SECONDS)"        >>$LOGDIR/$ANALYZELOG 2>&1

mysqlcheck -u$user -p$pass --analyze $OPTIONS --skip-write-binlog "$i" >>$LOGDIR/$ANALYZELOG 2>&1

echo "Analysing on $i database completed (ending time $SECONDS)"  >>$LOGDIR/$ANALYZELOG 2>&1

echo "**************************************************************************************** " >>$LOGDIR/$ANALYZELOG 2>&1

done

echo $LOGDIR/$ANALYZELOG

rm -f /tmp/myscript.running

 #mailx -s "MYSQLCHECK for analyze on `hostname` has been completed" -a "$LOGDIR/$ANALYZELOG" jasjeet9800@gmail.com

}

#Optimize all Databases

do_optimize()

{

touch /tmp/myscript.running

DBNAMES=`mysql -u$user -p$pass -BNse 'show databases'`

for i in $DBNAMES

do

echo "**************************************************************************************** " >>$LOGDIR/$OPTIMIZELOG 2>&1

echo "Checking up $i database (starting time $SECONDS)"        >>$LOGDIR/$OPTIMIZELOG 2>&1

mysqlcheck -u$user -p$pass --optimize $OPTIONS --skip-write-binlog "$i" >>$LOGDIR/$OPTIMIZELOG 2>&1

echo "Checking on $i database completed (ending time $SECONDS)"  >>$LOGDIR/$OPTIMIZELOG 2>&1

echo "**************************************************************************************** " >>$LOGDIR/$CHECKLOG 2>&1

done

echo $LOGDIR/$OPTIMIZELOG

rm -f /tmp/myscript.running

 #mailx -s "MYSQLCHECK for optimize on `hostname` has been completed" -a "$LOGDIR/$OPTIMIZELOG" jasjeet9800@gmail.com

}

initialize

parse_options $@

$COMMAND

exit 0

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Following are quick links as references to study and have understanding on maintenance in deep.

https://dev.mysql.com/doc/refman/5.6/en/optimization.html
https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html
https://dev.mysql.com/doc/refman/5.6/en/mysqlcheck.html
https://dev.mysql.com/doc/refman/5.6/en/myisam-table-maintenance.html
http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/