Problem
MySQL is a powerful database server for most users. The best part, it is free for personal user. However, if you need to connect multiple computers to one MySQL database server, it is not straight forward. I went through some troubles to get it to work. I found out that MySQL’s old default was “skid-networking”, which it did not listen to any requests from any computers on the network. Now, MySQL defaults to bind-address to 127.0.0.1, which is the local host on the computer MySQL runs on. With this configuration, it does not accept remote connection request as well.
Here are the steps to make MySQL to accept remote requests.
Steps
- Modify MySQL configuration file for it to bind to its IP address instead of the localhost.
Edit mysql configuration file - bind your mysql server to its IP address. Comment out 127.0.0.1 and replace it with you server’s actual IP address.
Mysql bind to NIC address - Create a new user for your remote user with its IP address. You can assign any privileges to your remote user.
Create remote user - Restart MySQL server for it to use new configuration
Restart mysql - Now you can connect to your MySQL server from the computer with IP address 192.168.10.5 with user name “remote” and its password as:
mysql -u remote -p password -h 192.168.10.1
Notes:
- The above steps are for internal connection only. You should use your firewall to limit external access to your MySQL server.
Reference