Detailed examples of steps to configure MySQL remote connection under Alibaba Cloud

As we all know, by default, our mysql installed on Alibaba Cloud does not support remote connections, but we also need to use some tools to connect to mysql, such as navicat. At this time, we need to modify the remote connection of mysql. . In this article, we will share with you the steps to configure MySQL remote connection under Alibaba Cloud.

Note: After we modify the operations related to mysql permissions, we must refresh the permissions table to make the configuration effective. Execution

flush privileges ;

Environmental prerequisite: centos7 mysql5.7

1. First log in to mysql on Alibaba Cloud:

mysql -u root -h localhost -p

2. Open the mysql database (you need to have permission to operate the mysql library, usually the root user of mysql)

use mysql

3. At this time, we have two ways to make modifications:

The first one is to directly modify the host recorded by the original user='root' and host='localhost' into % or the specified ip

1) Setting host to % means that any ip can connect to mysql

update user set host='%' where user='root' and host='localhost';

2) Of course, you can also specify host as a certain ip

update user set host='106.39.178.131' where user='root' and host='localhost';

3) After executing the above statement, then execute the following statement to refresh the permission table and make the configuration effective

flush privileges;

The second method is to add a new record

1) Add a new user newname (this new user name can also be root), the password is and set host to %, which means any IP can connect to mysql

 grant all on *.* to 'newname'@'%' identified by 'Navicat_123';

2) Add a new user newname, password is and host is set to the specified ip, which means that only this ip can connect to mysql

 grant all on *.* to 'newname'@'106.39.178.131' identified by 'Navicat_123';

3) After executing the above statement, then execute the following statement to refresh the permission table and make the configuration take effect

flush privileges;

Of course, if you want to change the local connection, you only need to change the corresponding user's host to localhost.

update user set host='localhost' where user='root' and host='106.39.178.131';

4. Don't go to navicat to connect now. You still need to do two things. Otherwise, you will be in a trap

1) Check whether port 3306 of the server firewall is open. If not, you need to open it

2) Check the security group of Alibaba Cloud Whether port 3306 is open in the rule,

How to check and configure reference document: [https://help.aliyun.com/document_detail/25471.html?spm=5176.100241.0.0.IneJPl]

5. Now it’s time to connect remotely. Enter the corresponding parameters in the tool

host: Alibaba Cloud server’s ip

port:3306

user name: If it is the first modification, the user is root. The second modification is the name you set yourself. For example, mine is newname

password: If it is the first method of modification, the password is the root password. The second modification is the password you set yourself. For example, mine is Navicat_123

You can follow the above method Realize the connection, I hope it can help everyone.

Related recommendations:

How to set up remote connection to the database in MySQL?

MySQL uses two methods to solve the problem of remote connection failure

How to remotely connect to the local MySql database

The above is the detailed content of Detailed examples of steps to configure MySQL remote connection under Alibaba Cloud. For more information, please follow other related articles on the PHP Chinese website!