From ben90.com
MySQL
I would like to point out here quickly that I assume that you have root access in this article. One of my regular tasks is to optimize MySQL installations (primarily on shared cPanel servers). I use a number of utilities in order to identify areas in which I could optimize the MySQL installations, the main one being MySQLTuner. MySQLTuner is a nice little script which will give you quite a bit of information on your MySQL install and the databases running on it. It’s probably worth saying here that tuning MySQL in a shared hosting environment is an on-going task and your config will need tweaking regularly!
I assume that it is fairly common knowledge that it’s worth optimizing your fragmented database tables. I’m not going to go into the why’s as that surpasses the scope of this post. The fact is that most users could do it quite easily and regularly, but they don’t as it’s just another thing to remember, I’ll admit that even I’m included in that statement on my personal sites. Just a quick note: you might find it interesting to read the OPTIMIZE TABLE entry in the MySQL Reference Manual if you don’t understand what it does or just need to refresh
So, more to the point, about 6 months or so ago, I decided that I would like to optimize all of the tables on one of the servers that I manage. With almost 10,000 tables, it wouldn’t have be realistic to do it manually, so I got/built the following script ( I cant remember if I wrote or found it ) and I have been using it to optimize all database tables on a few of the servers that I manage (on a weekly basis). The script is below. Essentially, it will just go through all of your MySQL tables and optimize them.
#!/bin/bash
########################################
# SCRIPT ACQUIRED FROM BEN90.COM #
########################################
echo ""
if test $# -eq 2
then # user and pass provided
MYSQL_LOGIN='-u $1 --password=$2'
elif test $# -eq 1
then # user and no pass provided
MYSQL_LOGIN='-u $1'
else # no credentials provided, use default
echo "no username or pass provided. Using default options";
echo ""
MYSQL_LOGIN='-u root'
fi
for db in $(echo "SHOW DATABASES;" | \
mysql $MYSQL_LOGIN | \
grep -v -e "Database" -e "information_schema")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | \
mysql $MYSQL_LOGIN | \
grep -v Tables_in_)
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
echo "USE $db; OPTIMIZE TABLE $table" | \
mysql $MYSQL_LOGIN >/dev/null
echo "done."
done
done
echo ""
The first thing that you will need to do is save the script on your server. I have the script in with a few of my other management scripts just in /root/scripts/ and have named it optimizetables.sh although you could name it anything you want and put it anywhere (be sensible though!).
Once you have saved the script on your server, you will need to give it execute permissions, so cd to where you put the script and run
chmod +x optimizetables.sh
Now, it’s time to run it. How you run it will really depend on your requirements. The script takes between 0 and 2 paramaters, you can use any of the following parameter sets. If you need to provide a username and password to access your MySQL install, you need this one
./optimizetables.sh username password
If you just need a username to access it, use this one
./optimizetables.sh username
The script will default to just using the user root in this one, and this one should work in most cases.
./optimizetables.sh
