Mysql: Can't connect with mysql-cli from host to mysql docker container

[keltik85]

Hi,

I did the following to start a MySQL docker container:

docker run --name=my_mysql_instance -d mysql/mysql-server:latest

I verified that its running with:

docker ps -a

CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                    PORTS                 NAMES
92de8d5bdb67        mysql/mysql-server:latest   "/entrypoint.sh mysq…"   About an hour ago   Up 10 minutes (healthy)   3306/tcp, 33060/tcp   my_mysql_instance

And also logged in to the conainer with:

docker exec -it my_mysql_instance mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.13

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

What i could not achieve is connecting through the docker HOST mysql-cli to the docker container like this:

mysql -h localhost -P 3306 -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2

This did not work either:

mysql -h 127.0.0.1 -P 3306 -u root
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

This did also not work:

mysql -h localhost -P 3306 --protocol=tcp -u root
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)

I found out the ip of the docker container over the docker bridge:

docker inspect my_mysql_instance | grep IPAddress
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAddress": "172.17.0.2",

But using the IPAdress did also not work:

mysql -h 172.17.0.2 -P 3306 --protocol=tcp -u root
ERROR 1130 (HY000): Host '172.17.0.1' is not allowed to connect to this MySQL server

Am I too stupid to use docker or did I get something conceptually wrong or something else.

Thanks for enlightenment!

[wglambert]

This isn't the image for this repo mysql/mysql-server, it seems that image has a different config which disallows some connections. Using the mysql image works fine after waiting for the container to begin accepting connections.

$ docker run --rm -dit -e MYSQL_ROOT_PASSWORD=pass --name mysql mysql:5.7
dbb50e8b064e160c9db788107c5d1caac478c7706074932ee843c68fbc98b1e9

$ docker logs mysql | tail -n 2
2018-12-17T20:28:31.848032Z 0 [Note] mysqld: ready for connections.
Version: '5.7.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

$ mysql -h172.17.0.2 -uroot -ppass
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

[NischayaSharma]

I did docker pull mysql
and then ran the container with this command docker run --name=mysql1 -e MYSQL_ROOT_PASSWORD=password123# -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci because I want to use this mysql with my frappe bench but it couldn't connect.
I then tried running mysql -u root -p and then entered the password but it still didn't connect.
Then I tried all of the things listed by @keltik85 like using port, localhost, IP address, using protocol=tcp, but nothing works.
Please help.

[tianon]

I'd recommend trying the Docker Community Forums, the Docker Community Slack, or Stack Overflow for help debugging your setup/environment.

[itinance]

I followed the post from @wglambert, tried to find out the IP-address with docker inspect mysql | grep IPAddress, got 172.17.0.2 and tried to connect with mysql-client. It hangs and will timeout some minutes later. Any ideas?

♠ mysql --version
mysql  Ver 8.0.16 for osx10.14 on x86_64 (Homebrew)

[itinance]

Just figured it out, on Ubuntu it works. Not on Mac.

On Mac even telnet fails:

♠ telnet localhost 3306
Trying ::1...
Connection failed: Connection refused
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

Just found out, using docker image named with "mysql-5.7" works perfectly.

[rrubiorr81]

I'm coming across the same issue @itinance was having. I have tried [same as @wglambert]:

$ docker run --rm -dit -e MYSQL_ROOT_PASSWORD=pass --name mysql mysql:5.7
$ docker logs mysql | tail -n 2
## got the ipaddress from the container:
$ docker inspect mysql | grep Address

172.17.0.3

But the if i try

mysql -h172.17.0.3 -uroot -ppass

i get

ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.3' (60)

my mysql client

mysql --version
mysql  Ver 14.14 Distrib 5.7.10, for osx10.10 (x86_64) using  EditLine wrapper

On the other hand if i go into the container, i can connect to this mysql instance, with no issues.

Should i use a different mysql version?, client? server?

Ideas?

[wglambert]

You'll want to wait for the second instance of [Note] mysqld: ready for connections., as the entrypoint will restart the server when creating the database so the first instance of "ready for connections" is false

With the refactored entrypoint https://github.com/docker-library/mysql/pull/471 you can grep for MySQL init process done. Ready for start up.

$ docker run --rm -d -e MYSQL_ROOT_PASSWORD=pass --name mysql mysql:5.7
a40695221b16dcede76c80e792a9327a61d7bc569f359b7c88d76444c50034c7

$ docker inspect mysql | grep -i ipaddress
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAddress": "172.17.0.2",

$ grep -i "ready for start up" <(docker logs mysql 2>&1)
2019-12-04 18:00:59+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

$ mysql -h172.17.0.2 -uroot -ppass 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

[rrubiorr81]

thanks @wglambert for your response.

Still i get the same result:

the response

ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.3' (60)

takes like a minute to pop but it invariably does.

[wglambert]

Oh you're using Docker for Mac, that container is in a VM so expose the port https://docs.docker.com/docker-for-mac/networking/

[rrubiorr81]

@wglambert .. exposing the port:

docker run -p 3326:3306  --rm -dit -e MYSQL_ROOT_PASSWORD=pass --name mysql mysql:5.7

if now i specify that port, i still get:

Also tried specifying the protocol

[wglambert]

When you forward the port with -p you can connect to the host on that port and it'll route you to the container. The permissions might not allow for remote root though so if that's the case then create a user and try that

[rrubiorr81]

that did not work either.. for some reason..

$ docker ps

139b581e1d04        mysql:5.7             "docker-entrypoint.s…"   4 minutes ago       Up 4 minutes        33060/tcp, 0.0.0.0:3356->3306/tcp   mysql

inside the box

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'pass1';

tested it from inside the box and works... the back to the host machine:

docker inspect mysql | grep IPAddress
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.3",
                    "IPAddress": "172.17.0.3",

mysql -h172.17.0.3 -uuser -ppass1 -P3356
mysql: [Warning] Using a password on the command line interface can be insecure.
[after ~ 1 minute]

ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.3' (60)

lsof -i :3356
COMMAND    PID   USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
com.docke 6913 -myuser-   33u  IPv6 0x940d04ba2a0ebd35      0t0  TCP *:upnotifyps (LISTEN)

[wglambert]

Try connecting to the host 127.0.0.1 on the forwarded port instead of the container's ip 172.17.0.3

[rrubiorr81]

that quickly fails:

[wglambert]

It fails with access denied not can't connect, looks like your user account uses the password pass1

[rrubiorr81]

thanks @wglambert, for your help. For some reason the host is supposed to be 127.0.0.1 instead of the virtual ip docker assigns to this box, as you suggested in one of your entries.

so this did work:

docker run --rm -d -e MYSQL_ROOT_PASSWORD=pass -p 3325:3306 --name mysql_test mysql:5.7

mysql -uroot -ppass -h127.0.0.1 -P3325

[joeengressia]

set container name as host, for example your container name is 'mysql'

docker exec -it mysql bash

mysql -h mysql-u yourmysqlusername -p

this image is an example and my container name is mariadb-server

if your application is also in the docker, then you can set the url name "mysql" as the host.

[imzhizi]

may u guys could try this, it works for me.

docker exec -it mysql bash
mysql -h mysql-u yourmysqlusername -p

## change the host to blank
update mysql.user set host = ' '  where user = 'root';
flush privileges;

[mmanikkoth]

Solution:

ENV MYSQL_ROOT_HOST=%

Please refer:
https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/docker-mysql-more-topics.html

Here's the relevant section:
MYSQL_ROOT_HOST: By default, MySQL creates the 'root'@'localhost' account. This account can only be connected to from inside the container as described in Connecting to MySQL Server from within the Container. To allow root connections from other hosts, set this environment variable. For example, the value 172.17.0.1, which is the default Docker gateway IP, allows connections from the host machine that runs the container. The option accepts only one entry, but wildcards are allowed (for example, MYSQL_ROOT_HOST=172...* or MYSQL_ROOT_HOST=%).

[chovy]

I had to add this to mysql service in docker-compose:

     ports:
       - 127.0.0.1:33061:3306

Then I could connect with mysql -P 33061 -h 127.0.0.1

[damiencuvillier]

If you do not want to use docker compose, you can use -p param for port binding

docker run --rm -dit -e MYSQL_ROOT_PASSWORD=pass --name mysql -p 3306:3306 mysql:5.7

And do not use "localhost" but 127.0.0.1

mysql -h 127.0.0.1 -u root -p