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.
The following step-by-step guide explains how to create an Ubuntu 20.04 instance from the Alibaba Cloud console.
Log in to your Alibaba Cloud account and go to the Elastic Compute Service (ECS) service:
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:
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
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
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)
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.
Using EasyDispatch to Enhance Accuracy and Time Efficiency for Service and Logistic Industries
1,011 posts | 247 followers
FollowAlibaba Cloud Community - January 25, 2022
Alibaba Clouder - September 10, 2018
Alibaba Clouder - February 26, 2018
Alibaba Clouder - February 13, 2019
Alibaba Clouder - June 3, 2019
ApsaraDB - February 21, 2023
1,011 posts | 247 followers
FollowAn independent public IP resource that decouples ECS and public IP resources, allowing you to flexibly manage public IP resources.
Learn MoreAlibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn MoreMigrate to the cloud with your own IP addresses.
Learn MoreAnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.
Learn MoreMore Posts by Alibaba Cloud Community