When I was developing my own project, I needed to use MySQL for unit testing, so I started a MySQL container locally using docker, imitating the way of go-txdb. When I executed the test, the following error occurred.

1
2

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

Cause Analysis

Conclusion: The local client version is too low and does not support the server-side version of the authentication method.

Environment

Client.

  • mysql Ver 14.14 Distrib 5.7.13, for osx10.11 (x86_64) using EditLine wrapper
  • macOS 10.14.6 Mojave

Server.

  • version: 8.0.25
  • runtime environment: docker container

Reason

MySQL has used caching_sha2_password as the authentication method by default since version 8.0, and 5.7 does not support this authentication method (5.7 uses mysql_native_password by default).

Solution

Solution 1: Upgrade the client version

This is relatively simple, so I won’t explain it here.

Option 2: Set up the old mysql_native_password method

  1. First log in to the container shell via the command line.

    1

    docker exec -it mysql /bin/bash # mysql is the name of your container, mine is mysql

  2. Use the mysql command inside the container to connect to the server.

    1

    mysql -uroot # You may need other connection parameters, mine doesn't have a password

  3. Modify the authentication method of the root account.

    1

    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '';

Done!