One of the most irritating aspects of the web is that you so often find (almost) exactly what you’re looking for, but seldom just exactly. There’s bits and pieces out there and it can be terribly frustrating to combine information from multiple sources since they don’t always merge well or are compatible. Enough talk. Here’s the free stuff! : mysqlTuner!

I’ve written and compiled a script that will automatically keep your MySQL database in tip-top condition, and tried my best to keep the installation process to a bare minimum…

How it works, and why you should care

To cut to the chase, databases delete, add and update data so often that eventually a lot of redundant data is created. This data does not only take up space, it also makes your queries run slower. For a more in-depth view on MySQL, please do read my entry on it. You can get rid of this by doing something called “table optimization”. This can be done in a few different ways, phpMyAdmin among other things.

mysqlTuner relies on one of two things. It can either be called through a PHP script which you can bookmark for easy access, or set up through a server scheduler. (Cron is the most common one). There’s also no need to worry about your database information to be compromised since it is accessed with arguments instead of storing data somewhere. If you have access to cron, there’s actually not even any need to put the file that is accessible from the web whatsoever.

How do I use it?!

mysqlOptimizer is a shell script that runs on your linux server with command line arguments. To optimize the table my_blog, you need the username and password of a user that has access to that database. After you fetch that, you can run the shell script from the command line as;

sh mysqlOptimizer.sh username password databasename

Logging in to run it every time can get old fast however, so I advice you’d do one of these two:

Option 1: You have access to Cron either through a control panel or SSH

Easy peasy. Put the file in a directory of your choice and password protect it or block web access through .htaccess. Schedule the file to run through cron at an interval you see fit (here is a tutorial on how to use cron):

30 2 * * * sh /path/mysqlOptimizer.sh user password database

The example above will run at 2.30 AM (02.30 for Euros :D) every day, optimizing your stuff for you! ;)

Option 2: No Cron Access! :(

Don’t despair! You can create a php file to run the shell file through exec(). I have included some information regarding this in the README.TXT, but you need to at least have some basic PHP knowledge. Also, if your server has safe_mode on, you need to make sure you are running the file in a dir defined by safe_mode_exec_dir.

Can it mess up my data??

Short answer is no. The OPTIMIZE command is non destructive and can’t mess anything up by itself. As long as you read the docs, you are fine. /hug

I don’t know how to do this :(

Feel free to ask, but most importantly, google is your friend! Usually your host support techs will be happy to help you set up a cron job too. After all, you are paying them for it! :D There’s a few additional pointers in the README.TXT.

Share, steal, copy, improve, break. I really don’t care! If you like it, let me know!

Download here (4Kb) (300 downloads)