mysql5.7 remote access settings

Setting up remote access in mysql5.7 is not like what is said on the Internet that you can access it by simply creating a user and granting permissions. For example, the one below is to create a user and grant permissions. It may be possible in previous versions, but it never works on my MySQL. This has been bothering me for a long time! ! ! The project was delayed! !

1. The original way to set up remote access

Mysql cannot be accessed from a remote machine by default. Remote access can be enabled through the following configuration

On the MySQL Server side:

Execute the mysql command to enter the mysql command mode.

Sql code

mysql> use mysql; mysql> GRANT ALL ON *.* TO user@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;  

This sentence means that any computer with any IP address (the % above means this) is allowed to access this MySQL Server with the admin account and password (admin)

You must add an account like this to log in remotely. The root account cannot log in remotely, only locally.

Remote Access:

• mysql -h172.21.5.29 -uuser -p123456
• //172.21.5.29 is the IP address of the MySQL Server, and user is the remote access account just set up on 172.21.5.29

In addition, you can also simulate remote access on a machine by opening multiple terminals to test whether remote access is possible.

I found a problem. If you have executed the above command, you are in localhost and execute:

mysql -hlocalhost -uadmin -padmin  

The result was a failure.
It turns out that the % above does not include localhost

So you must also add the command:

mysql>GRANT ALL ON *.* TO admin@'localhost' IDENTIFIED BY 'admin' WITH GRANT OPTION;

2. Setting up remote access in mysql5.7

After I set it up according to the above method, I found that I could not access it remotely using mysql -h. I modified the /etc/my.cnf configuration file to

#skip-networking Comment out

Add bind-address=0.0.0.0

But it's no use!

I almost memorized it after reading it!

Later I went directly to look at his configuration file: in the /etc/mysql folder

Click on my.cnf in the picture: There are some words as follows:

#
# * IMPORTANT: Additional settings that can override those from this file!

# Configuration elsewhere can override the configuration in this file. # The files must end with '.cnf', otherwise they'll be ignored. 
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I was curious about the last two paths in the file, so I looked at them: conf.d/:

There is a configuration file in it. When I opened it, I found that there was only [mysql] and nothing else.

Here’s another one:

There is also a configuration file. When I opened it, I found a surprise. There is a sentence here:

I instantly felt like I saw the light of day! ! ! Just look at its comments and you will understand that it can only connect locally. This is the problem! !

Comment out bind-address: #bind-address=...

Restart mysql service, remote link:

mysql -h172.17.0.1 -uuser -p

My username is: user

Finally got in, haha! !

This is the end of this article about setting up remote access for mysql5.7. For more information about setting up remote access for mysql5.7, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!