How to allow remote access to MySQL server

If you have installed MySQL server fresh, the MySQL server binds on local loopback address (i.e., by default. This means that you can access a MySQL server only from the local host, but not from any remote host. You can verify this by running netstat as follows.

$ sudo netstat -nap | grep mysql
tcp      0    0*               LISTEN      23799/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     180374   23799/mysqld        /var/run/mysqld/mysqld.sock

The above netstat output says that mysqld (MySQL server daemon) is listening on the loopback address for any incoming MySQL connection.

If you would like to access the local MySQL server from any external host, you can allow remote access to MySQL server as follows.

$ sudo vi /etc/mysql/my.conf
# bind-address =  # comment this line out

If you cannot locate "bind-address" in my.conf, you can add the following.

bind-address =

Then, restart MySQL server.

$ sudo /etc/init.d/mysql restart

If you re-run the above netstat command, and see that mysqld is listening on, or <public_ip_address_of_your_machine>:3306, that means you successfully changed the MySQL binding address for remote access.

Finally, one last step needed is to grant remote access to the MySQL user within MySQL server as below.

$ sudo mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO alice@'%' IDENTIFIED BY “alice_password”;

In the above command, I allow the MySQL user "alice" to access all MySQL databases from any remote host.

Alternatively, you can selectively allow remote access as follows.

$ sudo mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON mydb.* TO bob@'' IDENTIFIED BY 'bob_password';

In the above case, I allow the MySQL user "bob" to access a database called mydb from a remote host with IP address

Be aware that opening up your MySQL server to the world like this can be a source of security vulnerabilities. A more secure way to access MySQL server remotely is via an SSH tunnel. See this post for more detail.

Subscribe to Xmodulo

Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.

Support Xmodulo

Did you find this tutorial helpful? Then please be generous and support Xmodulo!

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of He is a Linux/FOSS enthusiast who loves to get his hands dirty with his Linux box. He likes to procrastinate when he is supposed to be busy and productive. When he is otherwise free, he likes to watch movies and shop for the coolest gadgets.

2 thoughts on “How to allow remote access to MySQL server

  1. Worked great thank you. I had to change to non-british quotes though. The straight ones ie. ' ". I guess it depends on character encoding.

  2. Thanks, worked like a charm! Now I am able to save and retrieve data in the remote MySQL db from an external application on a local machine.

Leave a comment

Your email address will not be published. Required fields are marked *