A lot of you may have a server and may run MySQL. That’s not something out of the ordinary, also having 1-2 and even more databases, again its something normal. Having tables in those databases, again is quite normal, sometimes even 50-100 tables, in some case. The problem starts when you wanna make sure that those tables in those databases are working properly and they are fully optimized.

Here my problem started also, having 50-100 databases and God knows how many tables, as this aren’t my databases, are my users databases. This became an issue, how to keep them clean or optimized?

Now optimizing a table or some tables is not a big deal, there is a simple command in MySQL which can do that. Also this can be done just by command line or using phpMyAdmin, but the problem is which table to optimize from which database. I mean you could optimize all the tables in all of the database, but that is not such a good idea, there can be hundreds of tables and databases and can take a lot of you hardware and MySQL. There can be a lot of ways to do this, I suppose, but my way, for me is simple, a script which will look which table from which database needs to be optimized and then, of course, optimized.

I need it this to be automatic and so I created the script and added it to crontab, to run every night. For me it works, 3 servers and no issues until now. Now I wanna post this script so all of you can test this script and maybe even let me know how to improve it, I’m not the best “bash/sh” scripting guy out there, so of course I could use some tips and tricks.

So check out the script, maybe use it and let me know how it works for you:

The code:

#!/bin/sh
#
# MySQL table optimization script.
# Created by Robert Gabriel <robert@linux-source.org>
# Date: Sun Jan 11 17:27:25 CET 2009
# Web: http://www.visualserver.org
#

SQLUSER=root
SQLPASS=thepassword
SQLCMD=$(which mysql)
DB=$(($SQLCMD -u ${SQLUSER} -p${SQLPASS} -Bse “SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'mysql’) AND Data_free > 0″|awk ‘{print $1}’|uniq) 2>/dev/null)
SQLCHECK=$($SQLCMD -u ${SQLUSER} -p${SQLPASS} -Bse “SHOW PROCESSLIST” >/dev/null 2>&1; echo $?)
SQLVER=$(($SQLCMD -u ${SQLUSER} -p${SQLPASS} -Bse “SHOW VARIABLES”|grep version|grep -i “[.]“|awk ‘{print $2}’) 2>/dev/null)
SQLHOST=$(hostname -f)

if [ ! -x "$SQLCMD" -o "$SQLCMD" == "" ]; then
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] Error: mysql command not found. Please check configuration.”
exit 1
fi

if [ ! $SQLCHECK -eq 0 ]; then
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] Error: mysql user or password incorrect. Please check configuration.”
exit 1
fi

DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] *** $0 started ***”
echo “[ $DATE ] Running MySQL $SQLVER on $SQLHOST”
echo “[ $DATE ] Starting mysql database and table optimization.”

if [ ! "$DB" == "" ]; then
DBCOUNT=0
TBCOUNT=0
COUNT=0
for sql_db in $DB; do
DBCOUNT=$(expr $DBCOUNT + 1)
TB=$($SQLCMD -u ${SQLUSER} -p${SQLPASS} -Bse “SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'mysql’) AND Data_free > 0 AND TABLE_SCHEMA=’$sql_db’”)
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] – Starting optimization on database $sql_db”
for sql_tb in $TB; do
COUNT=$(expr $COUNT + 1)
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo -en “[ $DATE ] \t- Optimizing table $sql_tb…”
$SQLCMD -u ${SQLUSER} -p${SQLPASS} ${sql_db} -Bse “OPTIMIZE TABLE $sql_tb” >/dev/null 2>&1; ERR=$?
if [ $ERR -eq 0 ]; then
echo -e ” done”
else
echo -e ” error”
fi

done
TBCOUNT=$(expr $COUNT + 0)
done
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] Optimized ${TBCOUNT} table(s) from ${DBCOUNT} database(s).”
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] Done mysql database and table optimization…”
echo “[ $DATE ] *** $0 stopped ***”
exit 0
else
DATE=$(date +%d-%m-%Y” “%H:%M:%S)
echo “[ $DATE ] Nothing to optimize… exiting.”
echo “[ $DATE ] *** $0 stopped ***”
exit 1
fi

You can same this script someplace with some name, chmod +x scriptname or chmod 555 scriptname and maybe if it work you may add it to crontab, like this:

0 1 * * *  ( /path/to/the/script/scriptname >> /var/log/optimize-mysql.log 2>&1 ) >/dev/null 2>&1

So try it, give it a go and let me know if it works right…

P.S. This script was tested on MySQL 5.0.X and 5.1.X and worked properly. Also you will need to use root user for MySQL.