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., 127.0.0.1) 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 127.0.0.1:3306          0.0.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 127.0.0.1 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 = 127.0.0.1  # comment this line out

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

bind-address = 0.0.0.0

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 0.0.0.0:3306, 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@'1.2.3.4' 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 1.2.3.4.

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.

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of Xmodulo.com. 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.
Your name can also be listed here. Write for us as a freelancer.

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 *