Tag Archives: MySQL Connect Error

[Solved] MySQL Connect Error: Can’t connect to MySQL server on ‘ ‘(61)

There is an account TT that can’t connect to MySQL server on ‘< remote-ip>’ ( 61)

1. Check whether the user has remote login permission

mysql> SELECT User, Host FROM mysql.user;
+-----------+-----------+
| User      | Host      |
+-----------+-----------+
| tt        | %         |
| mysql.sys | localhost |
| root      | localhost |
| Laily     | ％        |
| ttt       | ％        |
+-----------+-----------+
5 rows in set (0.00 sec)

Obviously, TT is allowed to log in from other servers

2. Check whether MySQL server has monitored port 3306

[root@centos-linux ~]# netstat -tulpen
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode      PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      0          16801      1507/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      0          17222      1970/master
tcp6       0      0 :::3306                 :::*                    LISTEN      27         46396      22054/mysqld
tcp6       0      0 :::22                   :::*                    LISTEN      0          16803      1507/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      0          17223      1970/master
udp        0      0 0.0.0.0:68              0.0.0.0:*                           0          49600      22999/dhclient
udp        0      0 0.0.0.0:43504           0.0.0.0:*                           0          48850      22999/dhclient
udp6       0      0 :::47875                :::*                                0          48851      22999/dhclient

I have no problem here. If I don’t monitor port 3306 or only monitor localhost (0.0.0.0 means monitor all), add the following line in My. CNF 

bind-address = 0.0.0.0

3. If the server is centos7, add MySQL service to the firewall

[root@centos-linux ~]# sudo firewall-cmd --zone=public --permanent --add-service=mysql
success
[root@centos-linux ~]# sudo systemctl restart firewalld

After I restart the firewall, I can access it normally

MySQL Connect Error: Authentication plugin ‘caching_sha2_password’ cannot be loaded

When many users connect to MySQL database with Navicat premium 12, authentication plugin ‘caching will appear_ sha2_ Error in password ‘cannot be loaded

The reason for this is that the encryption rule in versions before mysql8 is mysql_ native_ After mysql8, the encryption rule is caching_ sha2_ Password, there are two ways to solve the problem, one is to upgrade the Navicat driver, the other is to restore the MySQL user login password encryption rules to MySQL_ native_ password.

Here is the second way. The solution is as follows

1. Run the command prompt with administrator permission, log in MySQL (remember to add environment variables)

mysql -u root -p

Password: # login to MySQL

2. Modify the account password encryption rules and update the user password

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER; # Modify encryption rules

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_ native_ password BY ‘password’; # Update the user’s password

3. Refresh permissions and reset password

FLUSH PRIVILEGES; # Refresh permissions

Screenshots of the above two steps

Separate password reset command: alter user ‘root’ @’localhost ‘identified by’ 111111 ‘

Now open Navicat premium 12 again to connect to the MySQL problem database, and you will find that the connection is successful