Overview

In MySQL table optimization process you just simply de-fragmenting a mysql table and this is very useful especially for the tables that are frequently updating and/or deleting.

Code

A simple “OPTIMIZE TABLE $table” can do the optimization but a numerous number of tables and database it would be hard to do it manually. SO the way you can automate the optimization is to use a script.

Running the script should cause you no downtime, No downtime means you dont have to stop mysql service. Any tables being optimized will be locked for the duration of the operation (could be a longer time depends on your table).

It is very important that you do not interrupt the optimize operation, coz if you do that it could leave the table in a broken state.

Code Snippet

#!/bin/bash

PWD=`cat /etc/.mysqlpw`
MYSQL_LOGIN='-u root -p'$PWD

for db in $(echo "SHOW DATABASES;" | mysql -N $MYSQL_LOGIN | grep -v -e "Database" -e "^information_schema$" -e "^performance_schema$" -e "^mysql$")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | mysql -N $MYSQL_LOGIN )
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
mysql $MYSQL_LOGIN $db -e "OPTIMIZE TABLE $table" > /dev/null
echo "done."
done
done