In most cases, the MySQL server and the main application are hosted on the same machine. Thus, MySQL listens for connections from the local machine only. However, with the rise of distributed systems where the application and the database are hosted in separate servers, listening on localhost is not very ideal.
If such instances occur, developers need to ensure that MySQL listens for remote connections or simply connections outside the local machine. You can do this in two ways:
- Change the bind-address in the MySQL configuration file, or
- Access the MySQL server via an SSH tunnel.
In this guide, we will look at how to edit the MySQL configuration file to change the bind address of the MySQL server.
Pre-requisites
Before we get started, ensure you have:
- MySQL or MariaDB server installed.
- A root user or an account with sudo privileges.
Once you have the above requirements met, we can proceed.
Step 1 – Edit the MySQL Configuration
The first step to modifying the bind address of the MySQL server is editing the configuration file.
By default, the MySQL configuration file is located in /etc/mysql/mysql.conf.d/mysqld.conf for Ubuntu 20.10
The location of the configuration file may change depending on the MySQL server installed and the Linux distribution.
While the file is open, search for the entry with the content as (bind-address) and change the value to the IP address on which the server should listen.
By default, the value is set to localhost:
In my example, I will change the bind-address to all, which allows the MySQL server to listen on all IPv4 addresses.
NOTE: If you use MySQL server version 8.0 and higher, the bind-address entry might not be available. In that case, you can add it under the [mysqld] section.
Once you are satisfied with the changes to the config file, save the changes and close the editor.
Step 2 – Restart MySQL
To apply the changes to the config files requires you to restart the MySQL server service. You can do this using systemd as:
Step 3 – Allow Firewall
By default, MySQL listens on 3306, which your firewall can block sometimes. To allow the MySQL server port, use the IP tables command as:
Step 4 – Test connection
Once all the configurations are complete, you can test out the connection to the MySQL server.
If you have the server configured correctly, you should get the password prompt for the specific user.
Conclusion
For this quick tutorial, we looked at how to change the MySQL bind-address to allow the MySQL server to listen for connections outside the local machine. This can be very useful when working with distributed systems.
Thank you, and share if the tutorial helped you.
