Get the App
SLTechnology News&Howtos  ›  Database  › 

MySQL usage

Shulou Source: shulou.com Published: 2022-06-01 07:07:22 11月19日 Update

The following error was found when connecting to MySQL remotely:

java.sql.SQLException: null, message from server: "Host '192.168.30.23' is not allowed to connect to this MySQL server"

Solution:

Enter MySQL bin directory and execute the following command:

mysql> update user set host = '%' where user = 'root';

mysql> flush privileges;

Many users get the Authentication plugin 'caching_sha2_password' cannot be loaded error when connecting to MySQL databases using Navicat Premium 12.

The reason for this is that the encryption rule before mysql8 is mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password, there are two ways to solve the problem, one is to upgrade the navicat driver, one 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. Administrator privileges Run command prompt, log in MySQL (remember to add environment variables)

2. Modify account password encryption rules and update user passwords

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 password

3. Refresh permissions and reset password

FLUSH PRIVILEGES; #Refresh permissions

Tags: Passwords rules encryption users methods permissions commands errors updates reasons variables prompts data databases methods versions environments directories administrators accounts Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno Shulou Technology macOS Linux Microsoft MySQL