How to Bind MySQL Server to More Than One IP Address

By Alain Francois

The MySQL server listened to incoming connection requests on one or all configured network interfaces by default. It could be useful to allocate more than one network interface for specific use. This way, it can allow specifying bind-addresses for several network interfaces.

Run Your MySQL Server

The following step-by-step guide explains how to run your MySQL server from the Alibaba Cloud console.

Create a new instance. We will choose a Pay-As-You-Go instance:

Choose the image Ubuntu 20.04:

Continue with the configuration of your instance until the end:

Check Your IP Interfaces and IP Addresses

Before binding your IP addresses to MySQL, it is important to check your IP so you will not make a misconfiguration afterward:

$ ip a

You can see that our server has the following IP addresses:

172.16.212.134
172.16.212.135
192.168.8.106

Now, you can configure the binding.

Configure MySQL Binding for Multiple IP Addresses

If you want to configure MySQL and bind the IP addresses, you should edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf and change the default IP address value by separating each address with a comma. The directive to edit is the bind-address.

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

Restart the MySQL service to save the modifications:

$ sudo systemctl restart mysql

NB: The mysqlx-bind-address directive is for the X plugin, which is the interface between the MySQL server and the clients.

Make sure you have your remote database user:

Allow Remote Access for the MySQL User

You should make sure the database user you are using is allowed remote access to your database. You can connect to MySQL and allow remote access. We will create the database and the user below to show you the process:

mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'your_db_password';
mysql> CREATE DATABASE userdb;
mysql> GRANT ALL PRIVILEGES ON userdb.* TO 'user'@'%';
mysql> FLUSH PRIVILEGES;
mysql> exit;

Configure the Firewall

Now that you have configured MySQL and the user for the remote database access, you need to allow the MySQL protocol within your firewall. We will only allow MySQL port 3306 to the specified IP address (interfaces) that we have checked before for additional security.

Instead of using the IP address, we will use the interface name corresponding to each IP address:

$ sudo ufw allow in on ens33 to any port 3306

$ sudo ufw allow in on ens34 to any port 3306

$ sudo ufw allow in on ens35 to any port 3306

Then, restart the firewall service:

$ sudo ufw disable && sudo ufw enable

Conclusion

MySQL tried to bring more facilities by allowing multiple addresses to listen, but there is a side effect. Every created server socket counts against the limit specified by the –open-files-limit option. Theoretically, if too many interfaces were specified by the –bind-address option, it could result in the “Too many open files” error if the MySQL server configuration had already opened a large number of files.

How to Bind a MySQL Server to an IP Address?

The related article above explains how to bind a MySQL server to an IP address. When configuring a MySQL database server, it is important to bind it to an IP address by respecting some security strategies.

Community

How to Bind MySQL Server to More Than One IP Address

Run Your MySQL Server

Check Your IP Interfaces and IP Addresses

Configure MySQL Binding for Multiple IP Addresses

Allow Remote Access for the MySQL User

Configure the Firewall

Conclusion

Related Articles

Read previous post:

Read next post:

Alibaba Cloud Community

You may also like

Comments

Alibaba Cloud Community

Related Products

Elastic IP Address (EIP)

Alibaba Cloud Linux

PolarDB for MySQL

Bring Your Own IP Addresses Solution (BYOIP)