MySQL allows us to define an IP address to listen. You can provide the IP address of LAN network, which allow access MySQL from local network only. To allow the public network, you can simply define all zero’s (0.0.0.0) as an IP address to allow MySQL remote connection for any host. This tutorial will help you to configure MySQL server to allow connections from remote systems.
Step 1 – Configure MySQL Service
The default MySQL server listen on localhost interface only. You need to change this to listen on all interface. Edit MySQL database server configuration file in a text editor. The Debian based systems have MySQL configuration fiel at /etc/mysql/mysql.conf.d/mysqld.cnf. Let’s Edit MySQL configuration file: Search for the bind-address setting in file. The default bind-address is set to 127.0.0.1 as below:
Change bind-address value to 0.0.0.0 to enable MySQL database server listen on all network interfaces.
After change the value will look like above. Save the configuration file and close it. Restart MySQL service to apply changes. Now, verify the changes and MySQL is listenning on from all remote hosts. Execute the following command to view service status: You will see that MySQL is now listening on 0 0.0.0.0:3306 local address. It means, server is now listening on all network interfaces on port 3306.
Step 2 – Create MySQL User with Remote Access
Next, you need to create MySQL user to connect from remote host.
username – The name of user account used for login to MySQL server remote_server_ip_or_hostname – This is the ip address or hostname of remote host from where user is allowed to connect mysql server.
You can also use ‘%’ instead of remote host to allow any remote host. For example: Alternatively, you can create a new user account that will only connect from the remote host with the following command: Reload the privileges to running MySQL server. At this point, you have configured your MySQL server to listen from remote hosts. Created a MySQL user account used for authentication from remote hosts. Next, is to adjust security groups or system firewall to open MySQL port for remote systems.
Step 3 – Adjust Firewall
Before changing system firewall, the system running in cloud hosting environments must have update security group to open MySQL port based on hosting services. Then, you need to open port in system firewall (if active). For example, most of the Linux system opted firewalld and some of system like Debian based systems uses UFW firewall. Update the firewall based on your operating systems.
Using FirewallD
The FirewallD is the default firewall services in CentOS and RedHat based systems. You can use the following command to open MySQL port 3306 for everyone, but it it not recommended for production servers. The production server are recommended to open port for required hosts only. The FirewallD uses can use rich rules to open specific port for specific ip address or network only. For example: After adding required rule in firewalld, make sure to reload to apply changes at runtime.
Using UFW
The UFW (Uncomplicated Firewall) is the default firewall tool in Ubuntu and Debian systesm. You can use the following command to open port 3306 from everyone. But the productions users are recommended to open port for specific ip or network only. To Allow access from a specific IP addresses use command like: Make sure to change 192.168.0.0/24 with your network or system’s IP address.
Step 4 – Connect Remote MySQL
You must have mysql client packages installed on your system to connect remote MySQL server. where:
-h Hostname or IP address of the remote MySQL server (default: localhost) -p Port number of the MySQL server (default: 3306) -u MySQL username -p Password for the defined username, No space between -p and the password
Conclusion
This tutorial helped you to setup MySQL serer to accept remote connection from remote hosts. Also created a MySQL user account allowed to connect from remote hosts.
