How to connect to docker mysql container on remote machine

Question

I have two machines. My machine with IP1(Europe), and other machine with public IP2(USA). On IP2 I have mysql container running with volume /var/lib/mysql set to be replicated in some folder on the host machine ~/mysqldatabase. Firewall rule for port 3306 is added. Also I have ssh connection to the machine. So I'm not sure where to start. Usually when there is not docker I add just

bind-address = 0.0.0.0

as configuration in mysql and I open the firewall port 3306 (or an other one that points to mysql) and the things work. So probably I can install mysql-server package (the host is ubuntu16.04) outside of docker on the IP2 machine and to set it to point to the ~/mysqldatabase folder, but is it really necessary to do that? Is there way to connect directly from IP1 to IP2:mysql_container:mysql_database

I run the mysql docker container in two ways. One is with docker file. And the other one is with systemctl service.

Part of the docker-compose.yml:

version: "3"
services:
  mysql:
    image: mysql:5.7
    volumes:
      - /home/username/mysqldatabase:/var/lib/mysql
    ports:
      - '3306:3306'
    environment:
        MYSQL_ROOT_PASSWORD: rootpass
        MYSQL_DATABASE: somedb
        MYSQL_USER: someuser
        MYSQL_PASSWORD: someuserpassword

mysql.service

[Unit]
Description=Run %p
Requires=docker.service
After=docker.service

[Service]
Restart=always
ExecStartPre=-/usr/bin/docker kill %p
ExecStartPre=-/usr/bin/docker rm -f %p
docker run --rm --name mysql -v /home/username/mysqldatabase:/var/lib/mysql -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=rootpass -e MYSQL_DATABASE=somedb -e MYSQL_USER=someuser -e MYSQL_PASSWORD=someuserpassword \
mysql:5.7
ExecStop=/usr/bin/docker stop %p

[Install]
WantedBy=multi-user.target

To make the things more simple lets say that I use only the second approach.

I DON"T have :

• 1.mysql-client, mysql-server or mysql on the IP2 machine
• 2.and I haven't set anywhere to bind to 0.0.0.0 because I want to connnect directly to the mysql container. Probably I have to set it to bind to 0.0.0.0 inside this container.

Result for the firewall

sudo netstat -tunlp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      24717/docker-proxy

Answer 1

for those who have not yet managed to connect even after all these tips. Consider using another port

Example:

ports:
      - '3308: 3306'

I solved my problem this way.

Answer 2

You do not specify how you run your mysql container...

You need to specify which port should be "published", i.e. which port should go from "outside" to the "inside" of your mysql container.

To achieve that, you either specify a -p option when you do docker run or add it to your docker-compose.yml.

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql:tag

or docker-compose.yml

version: '3.1'

services:
  db:
    image: mysql
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: example

Read more about publishing ports here.

Answer 3

I think your mysql container is listening on ipv6 (seeing your netstat result). In cause may be your docker configuration. I don't know how to resolve it, I personnally just turn off ipv6 on my docker hosts to avoid this problem.

Hope it helps.

Answer 4

You can modify your my.cnf and add bind_address=0.0.0.0

That is working for me. mysql configuration doc:bind_address

Answer 5

If mysql server and client are in version 8.x try

--protocol=TCP

to mysql client parameters

Answer 6

Docker port mapping is the only thing that you need to configure.

However, if you still can't assess MySQL remotely do check for firewall settings.