/ mozey.co / blog

mysql remote access

July 1, 2013

🔗 Remote access for specified user / IP address

To enable remote access we have to grant this privilege to an existing user

grant all on myDatabaseName.* to 'myUserName'@'myIPAddress';

Or create a new user and give it access to all databases

grant all privileges on *.* to 'myUserName'@'myIPAddress' identified by 'myPassword' with grant option;

flush privileges;

Note that instead of ‘myIPAddress’ we can specify ‘%’ to allow access from any IP Address.

Additionally it might be required to open port 3306 on the firewall.

Then configure the MySQL bind address

sudo vi /etc/mysql/my.cnf

To listen on the servers public IP address

bind-address = xxx.xxx.xxx.xxx

And restart the database service

sudo service mysql restart

🔗 MySQL root access from all hosts

Grant privileges

grant all privileges on *.* to 'root'@'%' identified by 'password';

The easiest way is to comment out the line in your my.cnf file:

#bind-address = 127.0.0.1

and restart mysql

service mysql restart

To check where mysql service has binded

netstat -tupan | grep mysql

🔗 Test remote access using telnet

telnet example.com 3306

On success should display Connected to example.com, otherwise telnet: Unable to connect to remote host