Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

background

After building mysql container with Docker, the connection encounters the following problems

The problem

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
1
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
1
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
1
Copy the code

MySQL official description

Solution 1(Docker)

Applicable scenario

  1. First build container/install
  2. A user is added after the installation is complete

configuration

Configure mysql.cnf configure the default authentication plug-in

[mysqld]
default_authentication_plugin = mysql_native_password
12
Copy the code

Verify whether it takes effect

Log in to MySQL using the CLI

$ mysql -u root -p
1
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT Host, User, plugin from user;+-----------+------------------+-----------------------+ | Host | User | plugin | +-----------+------------------+-----------------------+ | % | root | mysql_native_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 the set (0.00 SEC) of 12345678910111213141516Copy the code

The root user’s authenticator plug-in has changed to mysql_native_password

Solution 2

Applicable scenario

  1. MySQL has been successfully installed

View the authentication type

mysql> use mysql;
Database changed

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)
1234567891011121314
Copy the code

The root user validates caching_sha2_password

Changing the authentication type (changing the password)

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

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; Query OK, 0 rows affected (0.01sec) 12345Copy the code

To take effect

mysql> FLUSH PRIVILEGES;
1
Copy the code

Verify whether it takes effect

mysql> SELECT Host, User.plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_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)
Copy the code