How can Navicat connect to MySQL

This article will introduce in detail how Navicat connects to MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

Brief description

Navicat is a set of fast, reliable and comprehensive database management tools, specifically Used to simplify database management and reduce management costs. The Navicat graphical interface is intuitive and provides easy management methods to design and operate data for MySQL, MariaDB, SQL Server, Oracle, PostgreSQL and SQLite.

When using Navicat to remotely connect to the MySQL database, some errors often occur. Today we will share our experience.

New connection

Open Navicat, select: Connection->MySQL, a new window will appear, allowing you to enter some basic Information:

#After completing the input, click the "Test Connection" button to test whether the connection can be normal!

Common errors

In the process of connecting to the database, some errors often occur. Below we have listed specific error messages and corresponding solutions!

Error 1

When connecting for the first time, it is very likely that:

1130- Host xxx is not allowed to connect to this MySQL server

This means that the connected account does not have remote connection permissions and can only log in on this machine (localhost) .

At this time, you need to change the host item in the user table in the MySQL database and rename localhost to %:

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

Error 2

Connect again, and then the following message will appear:

2059 - authentication plugin 'caching_sha2_password' cannot be loaded

# #This is because the encryption rule in versions before MySQL8 was mysql_native_password, but in later versions the encryption rule became caching_sha2_password.

To solve this problem, you can restore the MySQL encryption rules to mysql_native_password:

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Password@123456';

Note: Password@123456 is the password to log in to the database, which can be set according to your own situation.

Connect to MySQL

After all the above errors have been resolved, you can connect to MySQL normally:

Note: The password here is the password changed above (for example: Password@123456).

Related recommendations: "

mysql tutorial"

The above is the detailed content of How can Navicat connect to MySQL. For more information, please follow other related articles on the PHP Chinese website!