How to Change MySQL Bind Address

MySQL is a popular database management system that has been available for years. Due to its flexibility and easy management, many small developments to massive projects use it.

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.

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

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.

bind-address        = 0.0.0.0

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:

sudo systemctl restart mysql.service

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:

sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT

Step 4 – Test connection

Once all the configurations are complete, you can test out the connection to the MySQL server.

mysql -u root -h [mysql_remote/-ip] -p

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.

from Linux Hint https://ift.tt/2U8d0cN