Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Bind MySQL Server to More Than One IP Address

How to Bind MySQL Server to More Than One IP Address

This article explains how to bind a MySQL server to multiple IP addresses.

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.

Log in to your Alibaba Cloud account and go to Elastic Compute Service (ECS):

1

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

2

Choose the image Ubuntu 20.04:

3

Continue with the configuration of your instance until the end:

4

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

5

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

6

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.

Related Articles

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.

Linux MySQL Server Database Tutorials IP Address
0 0 0
Share on

Read previous post:

Finding Out What User Apache Is Running As

Read next post:

How Do I Find All Files Containing Specific Text on Linux?

Alibaba Cloud Community

1,042 posts | 256 followers

You may also like

Comments

Alibaba Cloud Community

1,042 posts | 256 followers

Related Products

More Posts by Alibaba Cloud Community

See All