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
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.
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
my.conf, you can add the following line to
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
<public-ip-address-of-your_machine>:3306. This means that you have successfully changed the MySQL binding address for remote access.
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.
Log in to the MySQL server from the command line as follows.
$ sudo mysql -u root -p
After login, run the following command at the MySQL prompt:
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]'%' 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 [email protected]'18.104.22.168' 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
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.
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.
Xmodulo © 2021 ‒ About ‒ Write for Us ‒ Feed ‒ Powered by DigitalOcean