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/
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/
