Connect to mysql in a docker container from the host

Question

(It's probably a dumb question due to my limited knowledge with Docker or mysql administration, but since I spent a whole evening on this issue, I dare to ask it.)

In a nutshell

I want to run mysql in a docker container and connect to it from my host. So far, the best I have achieved is:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

More details

I'm using the following Dockerfile:

FROM ubuntu:14.04.3
RUN apt-get update && apt-get install -y mysql-server

# Ensure we won't bind to localhost only
RUN grep -v bind-address /etc/mysql/my.cnf > temp.txt \
  && mv temp.txt /etc/mysql/my.cnf

# It doesn't seem needed since I'll use -p, but it can't hurt
EXPOSE 3306

CMD /etc/init.d/mysql start && tail -F /var/log/mysql.log

In the directory where there is this file, I can succesfully build the image and run it with:

> docker build -t my-image .
> docker run -d -p 12345:3306 my-image

When I attach to the image, it seems to work just fine:

# from the host
> docker exec -it <my_image_name> bash

#inside of the container now
$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
[...]

However I don't have that much success from the host:

> mysql -P 12345 -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Even more details

• I've seen that there's a question which looks like mine. However, it isn't the same (and it doesn't have any answers anyway)
  • I've seen that there are images dedicated to mysql, but I didn't have more success with them
  • My grep -v may feel weird. Admittedly, there may be cleaner way to do it. But when I attach my image, I can observe it actually worked as expected (ie: removed the bind-address). And I can see in the container /var/log/mysql/error.log:

Server hostname (bind-address): '0.0.0.0'; port: 3306 - '0.0.0.0' resolves to '0.0.0.0'; Server socket created on IP: '0.0.0.0'.

Solution 1

If you use "127.0.0.1" instead of localhost mysql will use tcp method and you should be able to connect container with:

mysql -h 127.0.0.1 -P 3306 -u root

Solution 2

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

Change 3306 to port number you have forwarded from Docker container (in your case it will be 12345).

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 3

I recommend checking out docker-compose. Here's how that would work:

Create a file named, docker-compose.yml that looks like this:

version: '2'

services:

  mysql:
    image: mariadb:10.1.19
    ports:
      - 8083:3306
    volumes:
      - ./mysql:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: wp

Next, run:

$ docker-compose up

Notes:

• For latest mariadb image tag see https://hub.docker.com/_/mariadb/

Now, you can access the mysql console thusly:

$ mysql -P 8083 --protocol=tcp -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.5-10.1.19-MariaDB-1~jessie mariadb.org binary distribution

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

Notes:

• You can pass the -d flag to run the mysql/mariadb container in detached/background mode.

• The password is "wp" which is defined in the docker-compose.yml file.

• Same advice as maniekq but full example with docker-compose.

Solution 4

The simple method is to share the mysql unix socket to host machine. Then connect through the socket

Steps:

• Create shared folder for host machine eg: mkdir /host
• Run docker container with volume mount option docker run -it -v /host:/shared <mysql image>.
• Then change mysql configuration file /etc/my.cnf and change socket entry in the file to socket=/shared/mysql.sock
• Restart MySQL service service mysql restart in docker
• Finally Connect to MySQL servver from host through the socket mysql -u root --socket=/host/mysql.sock. If password use -p option

Solution 5

OK. I finally solved this problem. Here follows my solution used in https://sqlflow.org/sqlflow.

The Complete Solution

To make the demo self-contained, I moved all necessary code to https://github.com/wangkuiyi/mysql-server-in-docker.

The Key to the Solution

I don't use the official image on DockerHub.com https://hub.docker.com/r/mysql/mysql-server. Instead, I made my own by installing MySQL on Ubuntu 18.04. This approach gives me the chance to start mysqld and bind it to 0.0.0.0 (all IPs).

For details, please refer to these lines in my GitHub repo.

SQLFLOW_MYSQL_HOST=${SQLFLOW_MYSQL_HOST:-0.0.0.0}

echo "Start mysqld ..."
sed -i "s/.*bind-address.*/bind-address = ${SQLFLOW_MYSQL_HOST}/" \
    /etc/mysql/mysql.conf.d/mysqld.cnf
service mysql start

To Verify My Solution

1. Git clone the aforementioned repo.

   git clone https://github.com/wangkuiyi/mysql-server-in-docker
   cd mysql-server-in-docker
   

2. Build the MySQL server Docker image

   docker build -t mysql:yi .
   

3. Start MySQL server in a container

   docker run --rm -d -p 23306:3306 mysql:yi
   

4. Install the MySQL client on the host, if not yet. I am running Ubuntu 18.04 on the host (my workstation), so I use apt-get.

   sudo apt-get install -y mysql-client
   

5. Connect from the host to the MySQL server running in the container.

   mysql --host 127.0.0.1 --port 23306 --user root -proot
   

Connect from Another Container on the Same Host

We can run MySQL client from even another container (on the same host).

docker run --rm -it --net=host mysql/mysql-server mysql \
   -h 127.0.0.1 -P 13306 -u root -proot

Connect from Another Host

On my iMac, I install the MySQL client using Homebrew.

brew install mysql-client
export PATH="/usr/local/opt/mysql-client/bin:$PATH"

Then, I can access the above Ubuntu host (192.168.1.22).

mysql -h 192.168.1.22 -P 13306 -u root -proot

Connect from a Container Running on Another Host

I can even run MySQL client in a container running on the iMac to connect to the MySQL server in a container on my Ubuntu workstation.

docker run --rm -it --net=host mysql/mysql-server mysql \
    -h 192.168.1.22 -P 13306 -u root -proot

A Special Case

In the case that we run MySQL client and server in separate containers running on the same host -- this could happen when we are setting up a CI, we don't need to build our own MySQL server Docker image. Instead, we can use the --net=container:mysql_server_container_name when we run the client container.

To start the server

docker run --rm -d --name mysql mysql/mysql-server

To start the client

docker run --rm -it --net=container:mysql mysql/mysql-server mysql \
 -h 127.0.0.1 -P 3306 -u root -proot

View more solutions

Comments

[kiltek]

Probably not so dumb. I stumbled upon this for the 10th time now and finally got the time to try it out at home.

[Brad Parks]

NOTE: some people may end up here, and want to connect to a mysql image run in docker, but the port isn't exposed. When you run it using the docker run command, make sure you open the port, like so, docker run -p 3306:3306 ... or you wont be able to connect. Of course this can be done in a Dockerfile too, but you don't need a custom docker image to do this!

[Charlie L]

--protocol=tcp fixed everything for me. Thank you!

[Lucas]

Can you clarify why the mysql socket is not available? Your command works, but I'm wondering if there's a way to mount the mysql socket to the host from the container.

[jozala]

I am not an expert in Unix communication, but from what I understand socket is a connection represented as a file. Since socket file is not shared between Docker container and host machine MySQL client cannot use one from inside of the Docker container. To connect to the MySQL server inside Docker container from host machine you could: 1. Set MySQL server to put socket in the specified place --socket=/var/run/mysqld/mysqld.sock 2. Mount this file outside of the Docker container 3. Specify path to socket in MySQL client with --socket=/host/mysql.sock

[Vuong]

Solved in Docker-Toolbox :) Thank you very much.

[Stephane]

Do you have to do this if the MySQL client is in another container ?

[Jobin]

I don't know that. I opted this method because tcp-ip port method didn't work for me. If you are using two containers for server and client, you can share a common directory to both containers and use Unix socket to connect to MySQL.

[Stephane]

The answer to my question is no when I use docker-compose with a link on the containers.

[Andru]

@maniekq Great comment! But did you verify all your three possibilities? Do they all really work?

[panepeter]

After I long struggle I found this golden answer, --protocol=tcp finally made the connection work. Thanks @maniekq for both the nice answer and your explanation on sockets via your comment!

[andrew lorien]

I don't believe this is the case. Not downvoting because I'm not 100% sure, but in my experience localhost and 127.0.0.1 always attempt to use a socket unless you add --protocol=tcp

[Craig Wayne]

I can verify that once i changed localhost to 127.0.0.1 and removed the protocol flag, it worked the same

[rezam]

I think this is the short and best answer ;)

[kiltek]

It should not be the accepted answer, since he explicitly states that he has no idea of UNIX file communication.

[John]

Might be short but it's not the answer, given that TCP is the slower method to communicate with mysql. (more cpu and higher latency)

[Vasili Pascal]

@John you are right, it's slower but it does exist. Of course you can share /var/run/mysqld/mysqld.sock between host and container.

[John]

That's what I'd recommend for any higher duty DB. it's as simple as "-v socketfile.sock" and then mysql --socket /path/file.sock without polluting the tcp/ip stack.

[dotancohen]

I believe that if you use the host 127.0.0.1 instead of localhost then you will be using the TCP stack instead of the unix socket, thus making the --protocol flag unnecessary.

[Don]

Can confirm that using 127.0.0.1 for the host property fixes the error but using localhost leaves me with the same problem. On OSX 10.13.6

[Enerccio]

that will end with ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

[Ajay Singh]

@Enerccio Could you try again?

[Zack Xu]

I'm using Rails and I had trouble running rake db:setup from outside docker connecting to the mysql database. Specifying the host 127.0.0.1 solves it.

[jiriki]

Thanks for this answer, I had same issue as in topic but after using --protocol=tcp it fixed. I thought something was wrong with docker network.

[asherbret]

This worked for me. I just needed to add -p at the end so MySQL will ask for a password.

[Rich]

This only works if you use 127.0.0.1 as the address. If you use the host's non-local address you need to add the --protocol=tcp flag as described in several other comments/answers. In my case it was extra confusing because I had a local, non-dockerized, mysql instance running as well. By default, even when the port is specified, even if the port is incorrect, the 'mysql' command will connect via the socket file to the local instance.

[NITHIN RAJ T]

@Rich so what did you do to solve that problem? Even I'm having similar setup, one MySql running on host and another in docker container.

[Rich]

@NITHINRAJT there are 2 ways to accomplish this AFTER you make sure your docker instance isn't listening on the same port (e.g., like in this answer, 8306). (1) Try this answer, the one we're commenting on (e.g., connecting to 127.0.0.1. (2) Try the next answer - mysql -u root -P <EXPOSED_DOCKER_PORT_HERE> -h <YOUR_HOST_IP_ADDRESS_HERE> --protocol=tcp -p. The flag, --protocol=tcp is the key to making this work.

[l3x]

You're welcome @tiago-elias. As a heads up, I hope to release another chapter, "Deep Data Analysis," in my book soon: leanpub.com/dataengineeringhandbook

[duchuy]

if u run on windows, u cannot run this on git-bash, but on cmd ms-dos

[Long]

works fine without protocol, basically what i did is mysql -h localhost -P 12345 -u root -p password

[Andresa Martins]

Not setting the IP address explicitly did the trick for me, thank you!

[serah]

I did not have to set the protocol, works fine without protocol. But does mysql know to connect to container as what we are saying is localhost but the IP of the container could be different. Also the host and the container are not on the same network I presume?

[Marvo]

The question says they want to connect from the host, not from within the container.

[Marvo]

The question asks how to connect from the host. The problem description states that they're able to connect from within the container.

[Marvo]

This looks like you're connecting from within the container. The question is about trying to connect to MySQL in a Docker container from the host.