5 July 2005

MySQL revisited

Today the big issue in our office is software and our lab images for this September. Being public sector we always need more than we can afford. As a result the excellent but no-longer-free MySQL-front has had to go. Adrian was not impressed to hear the license was about 14 items below the cut-off point in the priorities list.

All is not lost though. A while back I was looking at alternative front-ends and this seemed like a good day to have another look at sqlyog. It's been a while since I looked at it, but since this is what I recommended Helen should use last month I thought it would be useful to try some basic functions.

Despite not using mysql for a while I didn't have any problems performing basic functions in yog. I managed to create tables, add data, export it as a sql dump file, delete it and then re-import it again. If anything yog is possibly slightly easier than front imho.

The interesting bit occured when I tried to access an external server. It looks like port 3306 (mysql default) is blocked at the firewall or at some point along the route. I could login using SSH and tried changing the default port in the my.cnf file to a 5-digit number. Sadly this port was also blocked which lead to me investigating other methods of remote admin on mysql servers.

The first method I attempted is actually built into yog. Html tunnelling uses a php web-page which you copy to your user area on the mysql server (I think this assumes the mysql and php are hosted on the same machine). It looked like an interesting idea until I discovered this feature is only available on the retail version (so back to square one).

Yog's big mistake was to mention another system on their web-site called PHPmyAdmin. This is a php web-site which you put into a directory on the host (again will need to check if this works when the sql and php hosts differ). All I needed to do was edit the config.inc.php file and enter the host name and mysql credentials and it just worked.

The interface may be a little slower than using yog locally, but I managed to dump an external database to my local machine without too much fuss. It is worth remembering I did this as admin so we'll need to check if students could use this remotely.