How to allow remote access to MySQL server

Last updated on January 18, 2021 by Dan Nanni

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 means that mysqld (MySQL server daemon) is listening on the loopback address 127.0.0.1 for any incoming MySQL connection. If a remote MySQL client residing outside the server attempts to access the server, its access request will receive a timeout error.

If you would like the local MySQL server to be accessed from any external host, you can follow the steps below to allow remote access to MySQL server.

Step One: Edit MySQL Bind Address

The first step is to edit the MySQL server's bind address which is currently set to loopback. For this, open the MySQL server's configuration file, and modify it 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 line to my.conf.

bind-address = 0.0.0.0

Then, restart MySQL server:

$ sudo /etc/init.d/mysql restart (Debian/Ubuntu)
$ sudo systemctl restart mysql (Debian/Ubuntu with systemd)
$ sudo systemctl restart mysqld (Fedora or CentOS/RHEL 7 or later)
$ sudo /etc/init.d/mysqld restart (CentOS/RHEL 6 or earlier)

Now if you re-run the above netstat command, you will see that mysqld is listening on 0.0.0.0:3306, or <public-ip-address-of-your_machine>:3306. This means that you have successfully changed the MySQL binding address for remote access.

Step Two: Grant Remote Access to MySQL User

The next step is to grant remote access to the MySQL user who needs remote access. For this, you need to log in to the MySQL server and issue appropriate commands.

$ sudo mysql -u root -p

After login, run the following command at the MySQL prompt:

mysql> GRANT ALL PRIVILEGES ON *.* TO alice@'%' IDENTIFIED BY "alice_password";

The above command grants a privilege to the MySQL user alice to allow her to access all MySQL databases from any remote host.

Alternatively, you can selectively grant remote access privilege as follows.

mysql> GRANT ALL PRIVILEGES ON mydb.* TO bob@'1.2.3.4' IDENTIFIED BY 'bob_password';

In the above case, it allows the MySQL user bob to access a database called mydb from a remote host with IP address 1.2.3.4.

Finally, run the following command to enable the privilege change to take effect.

mysql> FLUSH PRIVILEGES;

As a final note, 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.

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.