when using Navicat premium to remotely connect to MySQL via IP, it fails, indicating “is not allowed to connect to this MySQL server”, but it is normal to use localhost instead of IP locally.
*Operate on the server side
modify the user table in the MySQL library, change the host value corresponding to the user root from localhost to%, and restart the MySQL service to take effect.
Method 1 (simplest): use Navicat premium local login to modify.
You can log in successfully with localhost locally. Open the user table in the MySQL library, change the host of the corresponding root user to%, submit and restart the MySQL service.
Method 2: login and modify with MySQL command line.
1. Connect to server: MySQL – U root – P
2. View all current databases: show databases（ Note the comma at the end)
3. Enter MySQL database: use MySQL;
4. View all the tables in MySQL database: show tables;
5. View the data in the user table:
select Host, User,Password from user;
6. Modify the host in the user table.
update user set Host=’%’ where User=‘root’;
7. Refresh: flush privileges;
8. The test connection is successful
Link to the original text: https://blog.csdn.net/quan278905570/article/details/106327719
- MySQL local connection Error 1130_ The solution of MySQL 1130 error report when remote connection through Navicat for MySQL
- MYSQL login error: mysqladmin: connect to server at’localhost’ failed
- Host is not allowed to connect to this MySQL server
- Solution to MySQL Error 1130 problem
- MySQL password setting error message: error 1054 (42s22): unknown column ‘password’ in ‘field list’
- 2059 error in connecting to database by Navicat premium
- Error 1364 (HY000): field ‘SSL_ cipher’ doesn’t have a default value
- Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: no)
- Navicat premium essentials for Mac database settings
- Error 1045 (28000) access denied for user ‘root’ @’localhost ‘appears in MySQL under Windows system
- MySQL Workbench Failed to Connect to MySQL at 127.0.0.1:3306 with user root Bad handshake
- Solve the problem of MySQL 1251 client does not support
- Solve MySQL error 1698 (28000): access denied for user ‘root’ @’localhost ‘
- Error 1045 (28000): solution to access denied for user ‘root’ @’localhost ‘(using password: no)
- ERROR 2000 (HY000): Unknown MySQL error
- MySQL error — multiple methods of failed to find valid data directory and MySQL setting password appear
- Linux-mysql8.0 password reset problem – error 1396 (HY000): Operation alter user failed for ‘root’ @’localhost ‘
- Connection between PHP 7.4 and MySQL (MariaDB) under Ubuntu (kali Linux)
- [error handling] when logging into MySQL with CentOS command, an error 1045 (28000) is reported
- After installing MySQL again under Linux, the solution of ‘MySQL module failed to start’ appears