not able to connect to mysql docker from local

Question

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'.

Answer 1

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

Edit: Is not in development anymore, stable release launched April 19, 2018

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

Answer 2

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.

Answer 3

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

Answer 4

Answer from psychemedia almost worked for me (fresh install on win 10). Also one very important thing I had to do, because this was not my first try. If you have attached data folder, in my example mysql volume first, empty/delete content of this folder. My working docker-compose file:

version: '3'
services:
    mysql-development:
      image: mysql:8.0.19
      container_name: mysql
      command: --default-authentication-plugin=mysql_native_password
      environment:
        MYSQL_ROOT_PASSWORD: XXX
        MYSQL_DATABASE: YYY
        MYSQL_USER: ZZZ
        MYSQL_PASSWORD: WWW
      ports:
        - "3306:3306"
        - "33060:33060"
      working_dir: /var/lib/mysql
      volumes:
        - "./mysql:/var/lib/mysql:rw"

• Update environment variables in docker-compose file.
• Make folder for mysql data (volumes): mkdir mysql
• Check syntax: docker-compose.exe config
• Build it: docker-compose.exe build
• Run it: docker-compose.exe up

Answer 5

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

Answer 6

Great info above, but here is an additional thought that might help some folks. The root account is not able to connect remotely by default. To enable that, you must include host permissions. In version 8 (I am using 8.0.25), this can be done by setting MYSQL_ROOT_HOST. The relevant section of a 3.8 docker-compose.yml looks like this for me:

mysqldb:
    image: $REPONAME/mysql:$MYSQLDB_VERSION
    build:
      context: ./docker/mysqldb
      args:
        MYSQLDB_VERSION: $MYSQLDB_VERSION
    environment:
      MYSQL_ROOT_PASSWORD: $MYSQL_ROOT_PASSWORD
      MYSQL_ROOT_HOST: "%"
      MYSQL_DATABASE: dbname
      MYSQL_USER: dbuser
      MYSQL_PASSWORD: dbpass
    command: [mysqld, --default-authentication-plugin=mysql_native_password, --character-set-server=utf8mb4, --collation-server=utf8mb4_unicode_ci, --innodb_monitor_enable=all, --max-connections=1001]
    ports:
      - 3306:3306
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      timeout: 10s
      retries: 10

Answer 7

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

Answer 8

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.

Answer 9

This worked for me in April 2022

1. Get your databases container id

docker ps

2. In another terminal tab, tap into the db container:

docker exec -it RUNNING_DB_CONTAINER_ID_HERE bash
mysql --user=root --password
update mysql.user set host='%' where user='root';
flush privileges;

3. Connect to mysql from Sequel Ace or similar

host: 127.0.0.1
user: root
password: docker (as defined in your docker compose file)
Port: 4407 (as defined in your docker compose file)

Docker compose for reference:

db:
    container_name: docker_database
    image: mysql/mysql-server:8.0.29
    ports:
        - "4407:3306"
    volumes:
        - ./db:/var/lib/mysql
    environment:
        - MYSQL_ROOT_PASSWORD=docker
        - MYSQL_DATABASE=your_database_here
        - TZ=Europe/London
    command: ['mysqld', '--default-authentication-plugin=mysql_native_password']