How to configure MySQL remote connection under Alibaba Cloud?

Everyone knows that Alibaba Cloud cannot use the client to connect remotely by default, but recently due to work needs, I have to implement remote connection. The following article mainly introduces the steps to configure MySQL remote connection under Alibaba Cloud. , friends in need can refer to it, let’s take a look below.

Preface

As we all know that by default, our mysql installed on Alibaba Cloud does not support remote connections, but we still need To connect to mysql through some tools, such as navicat, we need to modify the remote connection of mysql.

Note: After we modify the mysql permission-related operations, we must refresh the permission table to make the configuration take effect, execute

flush privileges ;

Environmental premise: 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 is to directly record the original user='root' and host='localhost' Modify host to % 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 the 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 take effect

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, the password is and set host to the specified ip Indicates 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 need to do two things, otherwise you will be in a trap

1) Check the server firewall port 3306 Is it open? If not, you need to open it.

2) Check whether port 3306 is open in Alibaba Cloud’s security group rules,

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 second modification One way of modification, the password is the root password, the second modification is the password you set yourself, for example, mine is Navicat_123

At this point, the moment you connect, are you very excited? .

Summarize

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