How to optimize mysql database
Recently I did run into issue where I just needed to optimize my database. Have few of them in mysql and then each of those databases have all bunch of tables. Now, I can issue:
mysql -u root -ppassword -e ‘show databases’
this will display list of all my databases, then I can connect to each individual database and issue the following sql statement:
OPTIMIZE TABLE tbl_name;
But this is quite manual process. Easier way is to just use mysqlcheck utility on the whole database rather than each individual tables.
mysqlcheck -o dspam -u root -ppassword
The mysqlcheck program can also be used to check (-c/-m/-C), repair (-r) and analyze (-a) the tables in a database. These commands can be done by using the CHECK, REPAIR and ANALYZE MySQL commands.
So anyway I did put this into some script and can easily execute it on every database that I have in my mysql
-=-=-=- END OF SCRIPT -=-=-=-
#!/usr/bin/ksh
STAMP=`date +%y%m%d.%H%M%S`
HOSTNAME=`/usr/bin/uname -n`
REMOTEHOSTNAME=”localhost”
mysqlpwd=’password’ ###This is why the file should be chmod 700
###for the mysql user we are backing up.
for database in `/usr/local/mysql/bin/mysql -h $REMOTEHOSTNAME -u root -p$mysqlpwd -e ‘show databases’ | sed s/[\+\|]//g`
do
if [ $database == Database ] || [ $database == test ]
then
echo $database
continue
else
now=`date`
echo “==========================================”
echo “Optimizing $database on $REMOTEHOSTNAME:”
echo “Start: $now”
/usr/local/bin/mysqlcheck -h $REMOTEHOSTNAME -o $database -u root -p$mysqlpwd
if [ $? -eq 0 ]
then
now=`date`
echo “End: $now”
echo “Optimization of $database Successful.”
else
echo “!!! There was an error optimizing up MySQL $database on $REMOTEHOSTNAME”
fi
echo “”
echo “==========================================”
echo “”
echo “”
fi
done
-=-=-=- END OF SCRIPT -=-=-=-
