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

[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 #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=%).

[ralyodio]

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

[lwb58]

I tried to connect mysql container in my python application on ubuntu,but I got this "Access denied for user 'root'@'172.17.0.1'",but Actually I just want to connect 172.17.0.2,That is my mysql container's IP.please help me

[rezdh]

Considering this,

docker exec -it mysql1 mysql -u root -p
update mysql.user set host='%' where user='root' and host = 'localhost';
flush privileges;

change mysql1 to your container name.

[jonnylink]

echo "RENAME USER 'root'@'oldhost' TO 'root'@'%';" | docker exec -i YOUR_CONTAINER mysql -u root (not prod safe of course, but fine for local dev work)

EDIT: this also assume you have no password set. Otherwise add that flag in.
Also it looks like the env MYSQL_ROOT_HOST=% should do the trick too, but for some reason that doesn't seem to be taking, however MYSQL_ROOT_HOST=172.*.*.* does seem to work.

[MashinaMashina]

You can create second user without host prohibitions:
create user 'newuser'@'%' identified by '1';
grant all privileges on *.* to 'newuser'@'%';

By default root user created as 'root'@'localhost', so root user accepts only 127.0.0.1 IP. You connect to mysql docker from 172.17.0.2.

[yosifkit]

By default root user created as 'root'@'localhost', so root user accepts only 127.0.0.1 IP. You connect to mysql docker from 172.17.0.2.

That is incorrect for these images. The password for root is set for both % and localhost:

The default "host" is %:

mysql/template/docker-entrypoint.sh

Line 218 in 37981f6

file_env 'MYSQL_ROOT_HOST' '%'

This is where MYSQL_ROOT_HOST is used. It is only skipped if you set MYSQL_ROOT_HOST=localhost (since root@localhost already exists from mysqld --initialize-insecure and needs to be altered instead of created.

mysql/template/docker-entrypoint.sh

Lines 266 to 273 in 37981f6

	if [ -n "$MYSQL_ROOT_HOST" ] && [ "$MYSQL_ROOT_HOST" != 'localhost' ]; then
	# no, we don't care if read finds a terminating character in this heredoc
	# https://unix.stackexchange.com/questions/265149/why-is-set-o-errexit-breaking-this-read-heredoc-expression/265151#265151
	read -r -d '' rootCreate <<-EOSQL || true
	CREATE USER 'root'@'${MYSQL_ROOT_HOST}' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}' ;
	GRANT ALL ON *.* TO 'root'@'${MYSQL_ROOT_HOST}' WITH GRANT OPTION ;
	EOSQL
	fi

And here it also sets the password to the same for root@localhost:

mysql/template/docker-entrypoint.sh

Line 278 in 37981f6

ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}' ;

[Ahmadnurahsan]

hi ihave the same problem

if i use --rm and then restart the pc after that my phpmyadmin cant find the container even i make new one so i remove the -rm .

but if i make a network for mysql container it can be accesed with my phpmyadmin but cant connect with cli like this
mysql -uroot -p -P4000 -h127.0.0.1 or mysql -uroot -P4000 -h172.18.0.2(the ip adress mysql container )