How to Bind a MySQL Server to an IP Address?

By Alain Francois

In a web server, you have code written in languages like PHP or Python that tell the database where the important information is stored. Since storing data assets of corporations is the priority of database professionals, MySQL offers exceptional security features that ensure absolute data protection. We recommend not accessing the data server from everywhere (especially over the Internet), as it contains crucial data. When configuring a MySQL database server, it's important to bind it to an IP address by respecting some security strategies. This article explains how to bind a MySQL server to an IP address.

Creating an Ubuntu 20.04 Instance

The following step-by-step guide explains how to create an Ubuntu 20.04 instance from the Alibaba Cloud console.

Create a new ECS instance. In our case, it will be a Pay-As-You-Go instance:

Choose the image Ubuntu 20.04:

Continue with the configuration of your instance until the end:

Installing MySQL

First, you need to update the cache of your Ubuntu server by running the following command:

$ sudo apt update

After that, install MySQL in your instance:

$ sudo apt mysql-server

Now, check the status to if the service is running:

$ sudo systemctl status mysql-server

Initializing MySQL

After installing the MySQL database, you need to initialize some configurations to set the default parameters as the password policy. Then, it will allow root login to the database remotely. Let's run the following command to start the configuration:

$ sudo mysql_secure_installation

You should set a different authentication method for the MySQL root user. MySQL is using the UNIX auth_socket plugin by default. This means db_users using it will be authenticated by the system user credentials.

First, go to MySQL:

$ sudo mysql

Then, you can display the default plugin used for authentication:

mysql> SELECT User, Host, plugin FROM mysql.user;

We will make MySQL use the mysql_native_pasword for the MySQL root user:

mysql> USE mysql;

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';

mysql> FLUSH PRIVILEGES;

We recommend creating an alternate MySQL root user to avoid using the default one. You should use this user if you want to access your database remotely:

mysql> CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'MYsql123p@$$word';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%';
mysql> FLUSH PRIVILEGES;
mysql> exit

Now, you can connect to MySQL with your new user:

$ mysql -u mysqluser -p

Configuring MySQL and Binding an IP Address

After initializing your MySQL server, you should indicate how it will work. A database server should not be accessed directly on the Internet. It's safe to make it accessible remotely in a local network (like a private VPC network) where it is locally connected to other servers. You can make it accessible remotely from one server among the local servers of your private VPC. This means you should access another server remotely first. Then, you can connect to your database server from that server.

Binding an IP to MySQL consists of setting up from which IP/interface your database server should listen to the request. If you have many interfaces (too many IP addresses), you need to indicate the default one from which all the requests will go through. You need to edit the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf and change the default IP address 127.0.0.1 to configure MySQL and bind an IP. This way, it will use the new one from now on:

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = X.Y.Z.T

MySQL can bind to zero networks, all networks (0.0.0.0), or a specific network (public or private.)

After editing the configuration file, restart the service for it to take effect:

$ sudo systemctl restart mysql

You need to open the port on the server for MySQL to be accessible remotely. You should also open port 22 if you have done so:

$ sudo ufw allow 22,3306/tcp

Now, restart your firewall:

$ sudo ufw disable

$ sudo ufw enable

Vheck the status of your firewall:

$ sudo ufw status
Status: active

To                         Action      From
--                         ------      ----
22,3306/tcp                ALLOW       Anywhere                  
22,3306/tcp (v6)           ALLOW       Anywhere (v6)

Conclusion

The bind-address setting tells MySQL whether it can listen to a TCP socket for new connections. If the application is on the same server as the database, you can stop MySQL from binding to any network. You can also bind to the private network IP address of MySQL to only listen to the private network.

Community

How to Bind a MySQL Server to an IP Address?

Creating an Ubuntu 20.04 Instance

Installing MySQL

Initializing MySQL

Configuring MySQL and Binding an IP Address

Conclusion

Read previous post:

Read next post:

Alibaba Cloud Community

You may also like

Comments

Alibaba Cloud Community

Related Products

Elastic IP Address (EIP)

PolarDB for MySQL

Bring Your Own IP Addresses Solution (BYOIP)

AnalyticDB for MySQL