Detailed steps to configure MySQL remote connection under Alibaba Cloud

Preface

As we all know, by default, the MySQL installed on Alibaba Cloud does not support remote connection, but we still need to use some tools to connect to MySQL, such as Navicat. At this time, we need to modify the remote connection of MySQL.

Note: After modifying the MySQL permissions, you must refresh the permissions table to make the configuration effective.

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 database, usually the root user of mysql)

use mysql

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

The first method is to directly change the host of the original user='root' and host='localhost' record to % or the specified IP

1) Setting host to % means that any IP address 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 statements, execute the following statements to refresh the permission table and make the configuration effective.

flush privileges;

The second method: add a new record

1) Add a new user newname (this new user name can also be root) with a password of and set the host to % to indicate that any IP address can connect to MySQL.

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

2) Add a new user newname, with a password of and set the host to the specified IP address, indicating that only this IP address can connect to MySQL.

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

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

flush privileges;

Of course, if you want to change to a 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 connect to Navicat right now. You need to do two more things, otherwise you will fall into the trap.

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

2) Check whether port 3306 is open in the Alibaba Cloud security group rules.

How to check and configure reference documents: [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: the IP address of the Alibaba Cloud server

port:3306

user name: If you modify it in the first way, the user is root. If you modify it in the second way, the user is the name you set yourself. For example, mine is newname.

Password: If you modify it in the first way, the password is the root password. If you modify it in the second way, the password you set is your own password. For example, mine is Navicat_123

At this point, the moment the connection was made, weren’t you excited?

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.