Connect to Docker MySQL container from localhost?

Question

I have a docker mysql image running, following is what the docker-compose.yml file looks like:

db:
  image: mysql
  environment:
    MYSQL_ROOT_PASSWORD: ""
    MYSQL_ALLOW_EMPTY_PASSWORD: yes
  ports:
    - "3306:3306"

This works fine.

My question is: How can I connect to the MySQL instance running on that container from the command line mysql client on my the host (my macbook)?

To clarify:

• I have a macbook with Docker installed
• I have a docker container with mysql
• I want to connect to the mysql instance running on the aforementioned container from the Terminal on my macbook
• I do NOT want to user a docker command to make this possible. Rather, I want to use the mysql client directly from the Terminal (without tunneling in through a docker container).

I don't have MySQL running locally, so port 3306 should be open and ready to use.

The command I am using to start the container is: docker-compose run

Solution 1

Using docker-compose up

Since you published port 3306 on your docker host, from that host itself you would connect to 127.0.0.1:3306.

Using docker-compose run

In that case the port mapping section of the docker-compose.yml file is ignored. To have the port mapping section considered, you have to add the --service-ports option:

docker-compose run --service-ports db

Additional note

Beware that by default, the mysql client tries to connect using a unix socket when you tell it to connect to localhost. So do use 127.0.0.1 and not localhost:

 $ mysql -h 127.0.0.1 -P 3306 -u root

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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>

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

Solution 2

A simple way to login to MySQL inside a Docker image is:

sudo docker exec -it <CONTAINER_ID> mysql -u root -p

for mySQL's root account by default password is not set, its BLANK, just press enter/return key, unless you have changed root password.

On successful execution, above command gives you mysql prompt.

Cheers!

Solution 3

I got it!! The answer is to use the --service-ports option when running docker-compose:

docker-compose run --service-ports db (the original docker-compose.yml file works fine)

Thanks to all for the help!

Solution 4

If your Docker MySQL host is running correctly you can connect to it from local machine, but you should specify host, port and protocol like this:

mysql -h localhost -P 3306 --protocol=tcp -u root

Because you are running MySQL inside Docker container, socket is not available and you need to connect through TCP. Setting "--protocol" in the mysql command will change that.

Solution 5

You can just Use --network="host" in your docker run command, then 127.0.0.1 or localhost in your docker container will point to your docker host.

docker run --network="host" -p 8080:8080 <your-docker-Image>

View more solutions

Comments

[Caleb]

thanks for the tip. I tried using 127.0.0.1 (and 192.168.59.103), neither worked :(

[Caleb]

Any other ideas? @Thomasleveil

[Thomasleveil]

sorry I never used boot2docker myself. Maybe github.com/docker/docker/issues/4007 will help

[Caleb]

FWIW, I'm trying with docker-machine now ... unfortunately, I'm still stuck. But, your link looks promising, so I'm trying that now. Thanks for the help, regardless.

[Caleb]

any advice on how this might work using Docker toolbox? I'd be happy to ditch boot2docker

[Thomasleveil]

github.com/boot2docker/boot2docker#container-port-redirectio‌​n might be it. Best of luck ;)

[Caleb]

Ok, check this out: If I run the mysql server this way, I can connect fine: docker run -p 0.0.0.0:3306:3306 -e MYSQL_ROOT_PASSWORD=foo -d mysql The PORTS column looks like this when I run docker ps: 0.0.0.0:3306->3306/tcp However, specifying the ports in docker compose as - "0.0.0.0:3306:3306" doesn't work. The PORTS column looks like this, as opposed to the aforementioned example: 3306/tcp Any idea on how to specify the ports in docker-compose so that it results in the 0.0.0.0 mapping? Thanks a bunch!

[Thomasleveil]

make sure you have the latest version of docker-compose

[Caleb]

Bah! got it!! The answer is to use the --service-ports option when running docker-compose: docker-compose run --service-ports db (the original docker-compose.yml file works fine) Couldn't have made it there without you! Thanks for the pro tips!

[Thomasleveil]

please add to your question the original command line you were using to start the container docker-compose run. Then answer your own question and accept it so others can easily get to it

[YarGnawh]

I tried connecting with 127.0.0.1 and got this error. Any ideas why? ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

[Harlin]

Good stuff. Didn't know about the localhost vs 127.0.0.1 thing re: Mysql client. Appreciate that part especially.

[Bartosz X]

Can you explain it in more details how exactly does it work and where was it used?

[Swannie]

thats the location where mysql is installed. so the command "/usr/local/mysql/bin/mysql" will run the mysql executable. just mysql on its own wont work in some cases as it depends on how you installed mysql on your mac

[Swannie]

I used it on a mysql 5.7 container running via docker-compose and used it with a docker swarm using docker-machine

[mastier]

good answer, default mysql docker image has mysql-client tools (checked on tag 5.6, 5.7)

[slashdottir]

This worked, but now the container is unkillable? How do you stop it?

[Chathuranga Wijeratna]

127.0.0.1 did not work for me either. I had to inspect the container and get the "IPAddress". ex. sudo docker inspect mysql-containerName and then use the IPAddress used there.

[Stevey]

The original question very specifically states that they do NOT want to do this.

[simplytrue]

this answer work for me. I was running mysql workbench and although my host were different in workbench localhost still worked to connect.

[David Maze]

Host networking doesn't work on MacOS or Windows hosts (this question specifically mentions a MacOS host). Since it totally disables Docker's networking layer, it's not usually a recommended approach.