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

Ivan Beldad · Accepted Answer · 2020-02-19 13:19:47Z

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

the param --default-authentication-plugin helped me a lot, cause I was installing the sha 256 by default and trying change later and loosing all the acess with the container even through the mysqlshel. Thanks
With MySQL 8.4, this has changed to mysqld --mysql-native-password=ON

psychemedia · Accepted Answer · 2018-04-27 21:47:25Z

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.

Right answer for the docker guys until PDO update their authentication method
With MySQL 8.4, this has changed to mysqld --mysql-native-password=ON

axelferreira · Accepted Answer · 2018-05-02 09:11:58Z

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

I will have to re-do this everytime the container is restarted. How do you config it so it's permanent?
The changes persist while restarting the container. You'll have to re-do this every time you spawn a new container. If you really need to, you might be able to use a script that runs on start/restart, take a look at this post stackoverflow.com/questions/28522607/…

Franci · Accepted Answer · 2021-05-19 12:47:47Z

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

You sir, are a life saver! Indeed the command "command: --default-authentication-plugin=mysql_native_password" is critical as WELL as deleting the contents of the database if you have the attached data folder which I did, literally just delete everything so it can recreate the database correctly with the mysql_native_password setting instead. This also fixes it so that Sequel Pro will work for mac!

Gooze · Accepted Answer · 2024-05-05 20:23:19Z

if you still want to use native_password authentication for mysql 8.4 and newer versions see: https://github.com/docker-library/mysql/issues/1048

TL;DR: add --mysql-native-password=ON to your args along with and remove the --default-authentication-plugin=mysql_native_password

Rohan · Accepted Answer · 2019-09-10 21:45:04Z

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

Beel · Accepted Answer · 2021-06-12 19:19:26Z

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

Casey McMullen · Accepted Answer · 2018-12-22 17:07:26Z

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

Octavian, thank you for your nice comment. It was voted down by the channel overseer because I referenced my own blog post and that's not considered helpful. Apparently if you've got additional helpful content somewhere else, it's expected that it be pasted into your reply here, otherwise it's considered self serving.

Haroldo · Accepted Answer · 2022-04-28 10:47:50Z

This worked for me in April 2022

Get your databases container id

docker ps

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;

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

user3894983 · Accepted Answer · 2022-08-25 12:14:45Z

IN the Dockerfile

... CMD ["mysqld", "--authentication-policy=mysql_native_password"] ..

according to specification

dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

--default-authentication-plugin will be removed

Chris Clark · Accepted Answer · 2018-09-17 13:44:27Z

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.

Bhavesh Prajapati · Accepted Answer · 2024-01-17 09:25:13Z

Use this compose file to create container it will work in all case.

# Use root/example as user/password credentials version: '3.1' services: db: image: mysql container_name: mysql command: --default-authentication-plugin=mysql_native_password restart: unless-stopped ports: - 3307:3306 environment: MYSQL_ROOT_PASSWORD: rootroot MYSQL_AUTH_PLUGIN: caching_sha2_password volumes: - ./dump:/dump - ./data:/var/lib/mysql adminer: image: adminer restart: unless-stopped ports: - 8080:8080

Collectives™ on Stack Overflow

not able to connect to mysql docker from local

12 Answers 12

3 Comments

2 Comments

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

3 Comments

2 Comments

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Related