I’ve been experimenting with running MySQL on different operating systems and LAMP/WAMP/OAMP platforms and as I always wanted to connect to the database server from a PC on my LAN I have kept coming across connection errors.

So far I’ve figured out that the following steps fix most of the connection issues. I should say that these connection errors are not really errors but due to MySQL being locked down for security. I’m not advising anyone to take these steps on a production server until they’ve researched the implications but for me experimenting with lots of different MySQL installs safely behind my company firewall it’s a quick fix to get started.

1. IP Binding

Most default MySQL installs seem to lock the server down to only accept connections from the localhost 127.0.0.1 address. If you want to access the MySQL server from anywhere else you’ll have to edit this line in the my.cnf or my.ini file:

bind-address=127.0.0.1

I usually just enter a # at the beginning of the line for a quick and dirty fix but you might want to look into more secure options on a production server.

2. Allowed users and hosts

Secondly most MySQL installs only allow the root user to connect from certain hosts. The quick and dirty fix for this is to add a wildcard hosts entry for the root user. To do this at the command line type:

 mysql --user=root mysql

or if there is a root password try:

 mysql --user=root --password=some_pass mysql

Then at the mysql prompt type:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 

That gets me remote root access on just about every installation I’ve tried so far. There are probably easier ways to do it on Windows but as most of the test rigs I’ve setup have had some flavour or Linu/BSD on them the command line was all I had!

If any MySQL Gurus want to comment they’re welcome – As I said these are quick solutions to get me remote access to a lot of MySQL servers I was rapidly setting up and testing with no thought to security!