Connect to mysql in a docker container from the ho

问题:

(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 achieve 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 doesn't actually is the same (and it doesn't have any answer anyway)
  • I've seen that there are images dedicated to mysql, but I didn't had more success with them
  • My grep -v may feel weird. Admittedly, there may be cleaner way to do it. But when I'm attached to 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'.

回答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.

回答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

回答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.

回答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

回答5:

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>

回答6:

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!

回答7:

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.

回答8:

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