Analysis on how to solve the problem of Navicat Premium connecting to MySQL 8.0 and reporting error "1251"

If people have been idle for too long, they will think they are working hard when they make an effort.

1. Problem

Navicat Premium reports an error when connecting MySQL 8.0 :

1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

2. Reasons

MySQL 8.0 changed the password authentication method.

The password authentication method in MySQL 8.0 is:

mysql_native_password

In order to provide a more secure password encryption method, starting from MySQL 8.0 , the default password authentication method is:

caching_sha2_password

The reason for error 1251 is also obvious:

Client does not support authentication protocol requested by server;

Translated into Chinese, it means: " The client does not support the authentication protocol requested by the server ."

I use Navicat Premium 11 to connect to MySQL 8.0 . Because the version is too low, it does not support the password encryption method of caching_sha2_password ! !

In the MySQL command line terminal, you can view the version and default password authentication method:

root@A day for programmers:/#mysql -u root -p   
Enter password: 
Welcome to the MySQL monitor. 

mysql> 
mysql> 
# Check the versionmysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> 
# View the default encryption method mysql> 
mysql> use mysql;

Database changed

mysql> 
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql>

As you can see, the default password encryption method for all users MySQL 8.0 is caching_sha2_password .

3. Solution

There are two ways to solve this problem.

• Method 1: Modify the password authentication method of MySQL 8.0 .
• Method 2: Install the new version Navicat Premium .

Both methods are feasible, but it is best not to change the encryption rules randomly, as problems may easily occur. Therefore, it is recommended to use "Method 2".

3.1. Modify the password authentication method MySQL 8.0

MySQL command line terminal performs the following operations respectively:

# Update the root user's password to "123456",
# Note that you need to replace "123456" with your own password. Do not use weak passwords!
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# Refresh permissions to make the password update effective FLUSH PRIVILEGES;

A simple demonstration:

Step 1: Enter mysql command line terminal.

root@ed2a490912e5:/#mysql -u root -p
Enter password: 

mysql>

Step 2: Check the default password authentication method.

mysql> use mysql;
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql>

As you can see, root user password authentication method is caching_sha2_password .

Step 3: Change root user password authentication method. If it is another user, just replace root with another username. It is equivalent to updating your password!

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.05 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

Step 4: Check root user password authentication method again.

mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

Note that root user password authentication method has changed mysql_native_password .

Use Navicat Premium 11 to connect to MySQL 8.0 again, the connection is successful !

Once again, changing the password authentication method can easily cause problems and is not recommended!

3.2. Install the new version Navicat Premium

According to my test, Navicat Premium 12 and above versions support MySQL 8.0 connection. The latest version is Navicat Premium 15 , official website download address:

http://www.navicat.com.cn/download/navicat-premium

The installation is very simple. Just run the installation package and follow the prompts to install it step by step. Unfortunately, Navicat Premium is not free software, and the price of a perpetual license is quite expensive... The official website price is: RMB 4,449.0 , and the enterprise version is even over RMB 10,000. Poverty makes me cry.

Fortunately, it can be cracked! ! !
For details, please refer to: Navicat Premium 15 permanent crack installation tutorial

Disclaimer: This is for technical exchange only. Any legal disputes will have nothing to do with me. If there is any infringement, please contact us to delete it!

Attached is a rendering:

This is the end of this article about the analysis and solution of the error "1251" when connecting to MySQL 8.0 with Navicat Premium. For more related content about the error when connecting to MySQL 8.0 with Navicat Premium, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!