mysql optimization

Recently did quite a lot of mysql related administration. Found quite a few ways to optimise that may make future administration easier.

1) If you are going to add/drop databases very frequently, you might want to add innodb_file_per_table=1 into your my.cnf config file. This will allow mysql to keep individual innoDB files for each database. When you drop the database, the innoDB file is deleted. The default behaviour is to store all data into a single innoDB file. Even when the database is dropped, the innoDB file does not shrink. Over time, it can balloon into an unmanageable size. I have seen 400+GB file shrink down to 30GB after optimization.

If you wish to apply this on an existing database, the easiest way I found was to dump all your data, remove the mysql data directory, reinstall mysql and import the sql dump.

2) If all applications which require database access reside on localhost, you can add skip-networking to my.cnf. Doing so stops mysql from listening on port 3306, thus reducing the vector of attack available to hackers.

3) If you are using phpmyadmin, you might consider adding $cfg['ForceSSL'] = true; to config.inc.php. This forces phpmyadmin to use HTTPS. After all, we dont want to be transmitting passwords in plain text over the wire. Note: Your webserver needs to have SSL configured.