This post is probably only helpful to a system administrator of large shared servers that happen to have a lot of MySQL databases.
These scripts are meant to administrate all of the databases and tables on a single server currently, I might refine them to also work only database or table name’s supplied.
One of the scripts, tableCheck, will access mysql using the username and password in /root/.mysql.cnf, and run a “check table $name” command, and error out if a table has been corrupted.
Another of the scripts, probably a bit heavy-handed, tableRepair, doing the same thing as tableCheck only running a repair query.
tableOptimize runs a “optimize table $name” query on all tables available, which is nice to do occasionally to speed up queries due to nasty things like table fragmentation.
Lastly, findLargeDatabases is a simple script that’ll show you the largest databases you have in order of size, so you can refine your efforts to optimizing those.
Here’s the goods!
scripts.tgz
{ 4 comments… read them below or add one }
Thanks for these scripts and the mysqlfragfinder!
After some minor adaptations they work on FreeBSD too.
Let me know what you needed to do, and I’ll patch my versions.
[1] On FreeBSD, MySQL is install in /var/db/mysql – I changed all “lib” to “db”.
[2] For the mysqlfragfinder I deleted all “mysqlHost”, I use sockets not networking. Also “awk -R” didn’t work. Plain awk did.
I’ll think about releasing an update for it — I realize the one I use, and the one I published are quite different.