Back to blog

Access MySQL from a remote machine


If you want to access your MySQL server from a remote machine you must adjust some settings first. Be warned though, remote access might attract some unwanted visitors :-)
Although if you want to access your MySQL server from a machine within your LAN you still need to adjust these settings.

Connect to your machine running the MySQL server.
Edit the my.cnf configuration file, which is located in /etc or /etc/mysql in my case (Debian Lenny) with your favorite editor.

vi /etc/mysql/my.cnf

Add the following line under [mysqld]

bind-address    = ip-address-of-this-machine
# skip-networking

So for example if your machine is directly connected to the Internet and your IP-address is: 62.218.240.192

bind-address    = 62.218.240.192
# skip-networking

For internal use do the following:

bind-address    = 192.168.199.5
# skip-networking

Assuming your MySQL server runs on a machine with the IP-address 192.168.199.5

bind-address: The IP address to bind
skip-networking: (comment this, notice the #). When you remove the # skip-networking will be enabled and there will be no network connectivity possible at all. This is recommended for MySQL servers which listen only to local requests.

Then restart the MySQL server:

/etc/init.d/mysql restart

Or on Redhat based systems:

service mysqld restart

Open up the mysql console:

mysql -p -u root mysql

This will connect you to the MySQL server, with the user root and right in the database ‘mysql’.

Grant remote access to a new MySQL database

If you want to add a new database to a specific username and host do the following:

mysql> CREATE DATABASE remote;
mysql> GRANT ALL ON remote.* TO cleverdude@'213.65.88.44' IDENTIFIED BY 'foo123';

The above example will create a database ‘remote’, a user ‘cleverdude’ with the password ‘foo123’ and give access to the host ‘213.65.88.44’ only!

Grant remote access to an existing MySQL database

mysql> UPDATE db SET Host='213.65.88.44' WHERE Db='existingdatabase';
mysql> UPDATE user SET Host='213.65.88.44' WHERE user='existingusername'
mysql> exit

The above example will give the host 213.65.88.44 access to the ‘existingdatabase’ and the ‘existingusername’.
Also make sure your Firewall (you do have one right?) has a NAT rule which allows TCP connections on port 3306 to your server.

For example:

Any -> 3306 -> MySQL Server

Logon to the remote machine and test it:

mysql -p -u existingusername -h 62.218.240.192

This will connect to the remote MySQL server (62.218.240.192) with the username ‘existingusername’.

Or use telnet

telnet 62.218.240.192 3306

Which will return some rubbish upon a successful connection, and that’s good!

Good luck!

Labels: linux mysql

Drop a note