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.
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.
# 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.
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.
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.
mysql> GRANT ALL PRIVILEGES ON mydb.* TO bob@'220.127.116.11' 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 18.104.22.168.
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.