MySQL usage
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