Connect to mysql in a docker container from the host

Question

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

Answer 1

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.

Answer 2

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

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

Answer 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

Answer 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

Answer 6

if you running docker under docker-machine?

execute to get ip:

docker-machine ip <machine>

returns the ip for the machine and try connect mysql:

mysql -h<docker-machine-ip>

Answer 7

In your terminal run: docker exec -it container_name /bin/bash Then: mysql

Answer 8

You should inspect the IP address assigned to the running container, and ther connect to that host:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <container name or id>

than you can connect with:

mysql -h <IP provided by inspect command> -P <port> -u <user> -p <db name>

https://mariadb.com/kb/en/installing-and-using-mariadb-via-docker/#connecting-to-mariadb-from-outside-the-container

Answer 9

• docker run -e MYSQL_ROOT_PASSWORD=pass --name sql-db -p 3306:3306 mysql

• docker exec -it sql-db bash

• mysql -u root -p

Answer 10

I do this by running a temporary docker container against my server so I don't have to worry about what is installed on my host. First, I define what I need (which you should modify for your purposes):

export MYSQL_SERVER_CONTAINER=mysql-db
export MYSQL_ROOT_PASSWORD=pswd 
export DB_DOCKER_NETWORK=db-net
export MYSQL_PORT=6604

I always create a new docker network which any other containers will need:

docker network create --driver bridge $DB_DOCKER_NETWORK

Start a mySQL database server:

docker run --detach --name=$MYSQL_SERVER_CONTAINER --net=$DB_DOCKER_NETWORK --env="MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD" -p ${MYSQL_PORT}:3306 mysql

Capture IP address of the new server container

export DBIP="$(docker inspect ${MYSQL_SERVER_CONTAINER} | grep -i 'ipaddress' | grep -oE '((1?[0-9][0-9]?|2[0-4][0-9]|25[0-5])\.){3}(1?[0-9][0-9]?|2[0-4][0-9]|25[0-5])')"

Open a command line interface to the server:

docker run -it -v ${HOST_DATA}:/data --net=$DB_DOCKER_NETWORK --link ${MYSQL_SERVER_CONTAINER}:mysql --rm mysql sh -c "exec mysql -h${DBIP} -uroot -p"

This last container will remove itself when you exit the mySQL interface, while the server will continue running. You can also share a volume between the server and host to make it easier to import data or scripts. Hope this helps!

Answer 11

mysql -u root -P 4406 -h localhost --protocol=tcp -p

Remember to change the user, port and host so that it matches your configurations. The -p flag is required if your database user is configured with a password

Answer 12

For conversion，you can create ~/.my.cnf file in host:

[Mysql]
user=root
password=yourpass
host=127.0.0.1
port=3306

Then next time just run mysql for mysql client to open connection.

Answer 13

I was able to connect mysql server 5.7 running on my host using the below command:

mysql -h 10.10.1.7 -P 3307 --protocol=tcp -u root -p  

where the ip given is my host ip and 3307 is the port forwaded in mysql docker. After entering the command type the password for mysql. That is it. Now you are connected the mysql docker container from the your host machine.

Answer 14

run following command to run container

docker run --name db_name -e MYSQL_ROOT_PASSWORD=PASS--publish 8306:3306 db_name

run this command to get mysql db in host machine

mysql -h 127.0.0.1 -P 8306 -uroot  -pPASS

in your case it is

mysql -h 127.0.0.1 -P 12345 -uroot  -pPASS

Answer 15

First, see the logs Are there any errors or not.

docker ps -a
docker logs container_name

If there is any error, search for the solution of that particular error, if there are no errors you may proceed to the next step.

Remember after starting your container MySQL take a few minutes to start so run those commands after 3-4 minutes.

docker exec -it container_name bash
# ONCE YOU ARE INSIDE CONTAINER 
mysql -u root -p
SHOW DATABASES;
exit

To connect from terminal or command prompt

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

Answer 16

I have tried the same thing, and I was facing same problem, but I have noticed one thing, whenever I was trying to run docker container with /bin/bash like as below:

docker container run -it --name test_mysql -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 mysql:latest /bin/bash

After hitting the above command, my container getting start but facing the same error" ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)"

Now, I have tried instead of above command, I have used below command:

docker container run -itd --name test_mysql -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 mysql:latest

docker container exec test_mysql /bin/bash

then using the command

mysql -uroot -P3306 -p

then able to connect, Hope This idea will work in your case.

Thanks Love Arora

Answer 17

According to the photos, go to the project -> MySQL -> inspect tab in the docker desktop app and modify the information about the database .env file.

.env file example:

DB_CONNECTION=mysql
DB_HOST=0.0.0.0:3306
DB_PORT=3306
DB_DATABASE=testdb
DB_USERNAME=sail
DB_PASSWORD=password

Answer 18

In case this will help somebody:

My prefered solution

add ~/.my.cnf with contents

[client]
user=<yourusername>
password=typethepasswordhere
host=127.0.0.1
port=3306

then from the terminal run >$ mysql and you should get in your sql cmd.

The hardcore alternative

you can also connect like this

docker exec -it containername mysql  

To get into the sql cmd

or

docker exec -it contaiinername bash (or sh) 

to connect in the container and then run >$ mysql

Answer 19

None of the other answers worked for me running on Ubuntu 22.04.2 LTS but I finally cobbled together a way to get connected.

First of all, I followed the docs to get the Docker Container running MySql 8.1 Enterprise server at this official MySql link.

Next, I had to create a new container like the following to expose MySql:

docker run --name=mysql1 -p 3306:3306 -d container-registry.oracle.com/mysql/enterprise-server:8.1

I discovered this command here in this SO answer.

After that when I ran the command $ sudo docker container list I saw something like the following:

e733323573 container-registry.oracle.com/mysql/enterprise-server:8.1 "/entrypoint.sh mysq…" 23 hours ago Up 6 hours
0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060-33061/tcp mysql1

The emphasis is on the bold part, because before that the ports didn't show up.

I assumed this meant that MySql was exposed me via 0.0.0.0:3306 or 127.0.0.1:3306 because that was what everyone was saying.

However, I discovered (very painfully ala trial & error) that this wasn't true.

I then discovered the next part I needed on this SO answer.

I needed to allow the "outer" IP Address to MySql which is running in the Docker instance.

1. So I started up the Docker instance.
2. Logged into MySql as root.
3. SELECT host FROM mysql.user WHERE User = 'root';

When you run the select command you'll probably see something like the following:

+------------+
| host       |
+------------+
| localhost  |
+------------+

That means that a user named root, coming from 127.0.0.1 will be able to log in.

However, in our case my ASP.NET Core 6.x WebAPI will be attempting to access the Docker Container from a different address.

How Can You See The Docker Container Address? $ sudo docker inspect <your-docker-instance-name> In the case of the one above we will run the following:

$ sudo docker inspect mysql1

When that runs you will see some JSON that contains something like the following:

"Gateway": "172.17.0.1",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"IPAddress": "172.17.0.2",
"IPPrefixLen": 16,
"IPv6Gateway": "",

The Gateway address is actually the one you want to add to your MySql user table so that a user from outside the Docker container can connect.

So, once you're logged into MySql in the Docker container as root, you can do the following (the password between single-quotes will be your password, of course):

CREATE USER 'root'@'172.17.0.1' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.1';
FLUSH PRIVILEGES;

If you do that initial query again, you should now see:

+------------+
| host       |
+------------+
| 172.17.0.1 |
| localhost  |
+------------+

Now, you can create a proper connection string using the ip address (not the Gateway address) from above and it "should" work.

Server=172.17.0.2;Database=<your-db-name>;port=3306;uid=root;pwd=some-pass;SslMode=preferred;

So, what you've done is allowed traffic from the Gateway address ( 172.17.0.1) to connect to MySql and you've connected to MySql by connecting to the Ip Address (172.17.0.2) of MySql Docker Container.

That's it. It finally all worked.

If It Still Doesn't Allow Access

If you still can't get connected you can try opening up all access (all ip addresses on all DBs & Tables):

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Answer 20

change "localhost" to your real con ip addr
because it's to mysql_connect()