Remote access to MySQL – error 10061 can’t connect to MySQL server – host is not allowed – user is not allowed

I’ve just been reviving my ubuntu (linux) server on the LAN to do some interface development and testing, and thought it would be easier to work if I had remote access to the MySQL databases to use something a little more productive than phpMyAdmin.

Poof! What a palaver! All up and running now, but time to make some notes in case I ever have to do this again. In case it matters, it’s the long term support LTS version of Ubuntu 12.04 (purple pangolin IIRC).

Anyhow, the default install of a LAMP server (linux/apache/mysql/php) doesn’t allow remote access, so you have to set it up.

The original error was

SQL Error (2003): Can't connect to MySQL server on hostname (10061)

 

This just means there’s no communication, so after ruling out firewalls on client and server, it was necessary to check if the server was actually listening on port 3306 (the default for MySQL).

First try a telnet to the port and see if you get a response. If telnet in the windows command prompt doesn’t work, that’s cos it’s not installed by default any more, even on Pro, you need to go to control panel/programs & features/windows features. Having installed it, in a command prompt, type telnet hostname 3306 and check that gets no response.

On the server:

$ sudo netstat -tulpn | grep LISTEN

Gets a response like

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 2380/mysqld 
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 880/apache2 
tcp 0 0 0.0.0.0:21 0.0.0.0:* LISTEN 501/vsftpd

The top row shows you that the server is only listening locally on port 3306 so MySQL needs some reconfiguring

 sudo nano /etc/mysql/my.cnf

Gets you in to edit the MySQL config file, where you can change the bind address line to

bind-address = serverIPaddress (or 0.0.0.0)

You probably don’t want to use the second option unless the server is on a LAN behind a firewall! Then you need to restart MySQL so it picks up the config changes:

sudo service mysql restart

After this, you should get a new and different error when trying to connect remotely (actually with a MySQL tool or telnet)…

Error Host clientIPaddress is not allowed to connect to this server

So now you need to give privileges to access remotely, for a particular user and host. The example uses a wild card to allow access from anywhere in the same subnet.

$ sudo mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *.* to username@'192.168.1.%' IDENTIFIED BY 'your-password';
mysql> FLUSH PRIVILEGES;

And that’s all there is to it.

Tags: ,

2 Responses to “Remote access to MySQL – error 10061 can’t connect to MySQL server – host is not allowed – user is not allowed”

  1. Ant Eater says:

    Precise Pangolin, actually

  2. John says:

    precisely 😉

Leave a Reply