not able to connect to mysql docker from local

问题:

I am trying to connect to mysql database from docker image. However it's throwing errors.

following is the docker image I am using. https://hub.docker.com/_/mysql/

And following is the command I have used to run the docker image.

docker run -p 3306:3306 --name mysql_80 -e MYSQL_ROOT_PASSWORD=password -d mysql:8

Following is the output of docker ps command

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                                                              NAMES
9f35d2e39476        mysql:8             "docker-entrypoint.s…"   5 minutes ago       Up 5 minutes        0.0.0.0:3306->3306/tcp

if I check the IP using docker inspect and ping that IP, it shows IP is not reachable.

docker inspect 9f35d2e39476 | grep -i ipaddress

And if i try to connect using localhost and 127.0.0.1 I am getting following error.

Unable to load authentication plugin 'caching_sha2_password'.

回答1:

First of all, be aware that you're using non stable software, so there can be major changes between releases and unexpected behaviour.

Secondly, you cannot ping directly your container, it's in other net, but you can easily use another container to ping him.

mysql 8 uses caching_sha2_password as the default authentication plugin instead of mysql_native_password. More info here.

Many mysql drivers haven't added support for caching_sha2_password yet.

If you're having problems with it, you can change to the old authentication plugin with something like this:

docker run -p 3306:3306 --name mysql_80 -e MYSQL_ROOT_PASSWORD=password -d mysql:8 mysqld --default-authentication-plugin=mysql_native_password

回答2:

I had the same problem, but this didn't do it for me with a Docker container running mysql 8.X. I loged in the container

docker exec -it CONTAINER_ID bash

then log into mysql as root

mysql --user=root --password

Enter the password for root (Default is 'root') Finally Run:

ALTER USER 'username' IDENTIFIED WITH mysql_native_password BY 'password';

You're all set.

This has already been answered here: post

回答3:

I found a fix here when firing up from docker-compose:

services:

  db:
    image: mysql
    command: mysqld --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

That is, revert from the new MySQL password/auth mechanism when starting MySQL.

回答4:

In case you are trying to connect to MySQL using the terminal itself, you might have a buggy build. But if you are trying to connect to MySQL using a GUI client, for example, Sequel Pro, it might not support the new auth feature with MySQL 8.

As a workaround for this, you start your docker container with --default-authentication-plugin=mysql_native_password command at the end and it will default the MySQL to use the old authentication:

docker run -p 3306:3306 --name mysql_80 -e MYSQL_ROOT_PASSWORD=password -d mysql:8 --default-authentication-plugin=mysql_native_password

回答5:

This is probably considered solved, but I wanted to document what it took for me to overcome this. I was having a similar problem, and reverting back to the old password standard was not a solution. I needed to use the caching_sha2_password, so none of the above worked for me and I had to use this command:

docker run -it --rm mysql mysql -h 172.31.116.20 -p -P6603

Where the 172.31.116.20 is my local IP address where the container is running and -P6603 is the port it's running on.

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
5092251b3dbf        mysql               "docker-entrypoint..."   16 minutes ago      Up 16 minutes       33060/tcp, 0.0.0.0:6603->3306/tcp   test1-mysql

I found this solution on the Docker site for the MySQL container: https://hub.docker.com/_/mysql/

It's in the "Connect to MySQL from the MySQL command line client" section.

回答6:

If you want to use MySQL 8.0 and not get the "caching_sha2_password plugin" error, then check out a couple blog posts I wrote on how to setup MySQL 8.0 in Docker with persistent data, as well as a post on how to run your MySQL 8.0 container with mysql_native_password.

In short, you can create a local "my.cnf" config file:

$ sudo nano /usr/local/opt/mysql/config/my.cnf`

Add the necessary config statement to it:

[mysqld]
default-authentication-plugin=mysql_native_password

And then include that file as a volume bind in your "docker run" statement:

$ docker run --restart always --name mysql8.0 - 
v/usr/local/opt/mysql/8.0:/var/lib/mysql -v 
/usr/local/opt/mysql/config:/etc/mysql/conf.d -p 3306:3306 -d -e 
MYSQL_ROOT_PASSWORD=your_password mysql:8.0

You can read more detail on these steps here:

https://medium.com/@crmcmullen/how-to-run-mysql-in-a-docker-container-on-macos-with-persistent-local-data-58b89aec496a

https://medium.com/@crmcmullen/how-to-run-mysql-8-0-with-native-password-authentication-502de5bac661