TCP: Can't connect to mysql container behind and through traefik but can connect to tcp echo server and postgres

nolan · September 3, 2020, 6:55pm

Hello everyone. I have been banging my head against the wall all day yesterday and I finally decided to sell all my computer and tech stuff and I plan to leave the city Friday night to go and live in a small community farm where I am told I can earn my food and lodgings by doing field works (picking up potatoes and doing pest control by hand I am told, it's an organic farm).

Anyway, before closing that chapter of my life I would like to understand why I couldn't get a MySQL client to talk to a MySQL server running in a container behind Traefik2.

The client just hangs out, Wireshark shows that nothing is going on after the syn/ack handshake and I am dead inside now.

I set up a basic tcp echo server in the vain hope to rule out any Traefik misconfiguration. Alas that tcp echo server works as intended and parrots things I send him and though I have rarely used postgres I also can connect to a Postgres server running in a container behind Traefik. Over tee-see-pee.

Here are my precious files that all run with docker-compose, each service in its own folder (mysql, traefik2, postgres and tcp-echo-server) on a standard Debian installed in a VPS hosted in that cheese and wine country. It has an ipv4 address and some funny domain names but that's irrelevant to the matter at hand.

Traefik 2

Traefik2 `docker-compose.yml`

---                                                                                                    
networks:                                                                                              
  default:                                                                                             
    external:                                                                                          
      name: ${PREFIX}                                                                                  
                                                                                                       
services:                                                                                              
  web:                                                                                                 
    container_name: ${PREFIX}                                                                          
    environment:
      - OVH_APPLICATION_KEY=${OVH_APPLICATION_KEY}
      - OVH_APPLICATION_SECRET=${OVH_APPLICATION_SECRET}
      - OVH_CONSUMER_KEY=${OVH_CONSUMER_KEY}
      - OVH_ENDPOINT=${OVH_ENDPOINT}
    image: traefik:2.2.8
    labels:
      - traefik.enable=true
      - traefik.http.middlewares.${PREFIX}-auth.basicauth.users=${AUTH}
      - traefik.http.routers.traefik.entrypoints=https
      - traefik.http.routers.traefik.middlewares=${PREFIX}-auth
      - traefik.http.routers.traefik.rule=Host(`${HOST}`)
      - traefik.http.routers.traefik.service=api@internal
    ports:
      - "80:80"
      - "443:443"
      - "3306:3306"
      - "5432:5432"
      - "9000:9000"
    restart: always
    volumes:
      - ./acme.json:/acme.json
      - ./dynamic.yml:/etc/traefik/dynamic.yml
      - ./static.yml:/etc/traefik/traefik.yml
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro

Traefik2 `.env`

AUTH=admin:$apr1$password
HOST=traefik2.example.com
OVH_ENDPOINT=ovh-eu
OVH_APPLICATION_KEY=ovh_application_key
OVH_APPLICATION_SECRET=ovh_application_secret_key
OVH_CONSUMER_KEY=ovh_consumer_keyr
PREFIX=traefik2

Traefik2 `static.yml`

---
api:
  dashboard: true

certificatesresolvers:
  le:
    acme:
      caserver: "https://acme-v02.api.letsencrypt.org/directory"
      dnschallenge:
        provider: ovh
      email: mail@example.com
      storage: /acme.json

entrypoints:
  http:
    address: :80
    http:
      redirections:
        entrypoint:
          permanent: true
          to: https
          scheme: https
  https:
    address: :443
    http:
     tls:
       certresolver: le
       domains:
         - main: example.com
           sans:
             - "*.example.com"
  mysql:
    address: :3306
  tcp-echo-server:
    address: :9000
  postgres:
    address: :5432

log:
  level: DEBUG

providers:
  docker:
    exposedbydefault: false
  file:
    filename: /etc/traefik/dynamic.yml
  providersthrottleduration: 2s

Traefik2 and how it is run

me@vps:~/traefik2/ $ docker network create traefik2
me@vps:~/traefik2/ $ docker-compose up -d

Traefik dashboard is now available on https://traefik2.example.com

TCP echo server

TCP echo server `docker-compose.yml`

A nice and functional tcp echo server written in Go that was easier to setup than classic nc.

---
networks:
  default:
    external:
      name: ${DOCKER_PREFIX}

services:
  app:
    container_name: ${DOCKER_PREFIX}-app
    hostname: ${DOCKER_PREFIX}-app
    image: venilnoronha/tcp-echo-server:latest
    labels:
      - traefik.enable=true
      - traefik.tcp.routers.${DOCKER_PREFIX}-app.entrypoints=tcp-echo-server
      - traefik.tcp.routers.${DOCKER_PREFIX}-app.rule=HostSNI(`*`)
      - traefik.tcp.services.${DOCKER_PREFIX}-service.loadbalancer.server.port=9000

version: "3.4"

TCP echo server `.env` file

DOCKER_PREFIX=tcp-echo-server

TCP echo server and how it is run

me@vps:~/tcp-echo-server/ $ docker network create tcp-echo-server
me@vps:~/tcp-echo-server/ $ docker network connect tcp-echo-server traefik2
me@vps:~/tcp-echo-server/ $ docker-compose up -d

Hey, it works ! Running something like:

me@home:~ $ echo 'world'|nc 192.0.2.2 9000

returns hello world (the echo server adds an hello prefix). TCP reverse proxying ! w00t !

Postgres server

Now that we got basic TCP data flow going on let's try with something more beefy (disclaimer: I am a liar, all those stunts were performed in reverse order during the investigation).

Postgres `docker-compose.yml`

---
networks:
  default:
    external:
      name: ${DOCKER_PREFIX}

services:
  db:
    container_name: ${DOCKER_PREFIX}-db
    environment:
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
    hostname: ${DOCKER_PREFIX}-db
    image: postgres
    labels:
      - traefik.enable=true
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.entrypoints=postgres
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.rule=HostSNI(`*`)
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.service=${DOCKER_PREFIX}-service
      - traefik.tcp.services.${DOCKER_PREFIX}-service.loadbalancer.server.port=5432
    volumes:
      - db:/var/lib/postgresql/data

version: "3.4"

volumes:
  db:
    name: ${DOCKER_PREFIX}-db

Postgres `.env` file

POSTGRES_PASSWORD=postgres
DOCKER_PREFIX=postgres

Postgres and how it is run

me@vps:~/postgres $ docker network create postgres
me@vps:~/postgres $ docker network connect postgres traefik2
me@vps:~/postgres $ docker-compose up -d

Connecting with:

me@home:~ $ pgcli --host 192.0.2.2 --port 5432 --username postgres postgres

gives me a nice shell:

Password: 
Version: 1.6.0
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com

I made a liittle dance at this point ^^. So now I have a server running in a container behind traefik2 and data is flowing through TCP ! w00t ! w00t !

Mysql server

So, let's set up that MySQL server now... Should basically be the same thing, right ?

Mysql `docker-compose.yml`

---
networks:
  default:
    external:
      name: ${DOCKER_PREFIX}

services:
  db:
    container_name: ${DOCKER_PREFIX}-db
    environment:
      MYSQL_DATABASE: ${DOCKER_PREFIX}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_USER: ${MYSQL_USER}
    hostname: ${DOCKER_PREFIX}-db
    image: mysql:8
    labels:
      - traefik.enable=true
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.entrypoints=mysql
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.rule=HostSNI(`*`)
      - traefik.tcp.routers.${DOCKER_PREFIX}-db.service=${DOCKER_PREFIX}-service
      - traefik.tcp.services.${DOCKER_PREFIX}-service.loadbalancer.server.port=3306
    volumes:
      - db:/var/lib/mysql

version: "3.4"

volumes:
  db:
    name: ${DOCKER_PREFIX}-db

Notice how I am setting up the mysql-db service manually. That's because I read that the MySQL image exposes both 3306 and 33060 ports and that Traefik2 doesn't know what to do about that (I don't really get it since the loadbalancer.server.port tag redirects traffic to the 3306 port anyway but okay). I read about it in that thread everyone reads (Can't connect to MySQL server: Connection refused) when they try to make MySQL works in a container behind Traefik2.

I didn't do that for postgres and it works fine anyway. Whatever.

Mysql `.env` file

MYSQL_ROOT_PASSWORD=mysql_root_password
MYSQL_USER=mysql_user
MYSQL_PASSWORD=mysql_password
DOCKER_PREFIX=mysql
URL=mysql.example.com

MySQL and how it is run

me@vps:~/mysql $ docker network create mysql
me@vps:~/mysql $ docker network connect mysql traefik2
me@vps:~/mysql $ docker-compose up -d

Let's connect:

me@home:~ $ mysql --host 192.0.2.2 --protocol=tcp --port 3306 --user mysql_user -pmysql_password

Let's wait a gazillion years.

Ten minutes later the wireshark logging window is frozen in time:

86	14.712241694	192.168.1.56	192.0.02.2	  TCP	74	54258 → 3306 [SYN]      Seq=0 Win=64240 Len=0 MSS=1460 SACK_PERM=1 TSval=2038950184 TSecr=0 WS=128
87	14.732868264	192.0.02.2	    192.168.1.56  TCP	74	3306 → 54258 [SYN, ACK] Seq=0 Ack=1 Win=28960 Len=0 MSS=1452 SACK_PERM=1 TSval=535582122 TSecr=2038950184 WS=128
88	14.732971095	192.168.1.56	192.0.02.2    TCP	66	54258 → 3306 [ACK]      Seq=1 Ack=1 Win=64256 Len=0 TSval=2038950205 TSecr=535582122

What I tried

Following that other topic Can't connect to MySQL server: Connection refused I played around with explicitly adding the mysql service to 3306 (see the difference between postgres and mysql docker-compose).
I checked I could connect to that container by binding the container's 3306 port to the host's 3306's port, thus bypassing Traefik2. It works.
I checked I could connect from another container running in the same docker network. It works.
I tried to disable the 33060 port (which is actually for an X connection): it doesn't work.
I configured mysql to bind to 0.0.0.0.0: it doesn't work.

I took a look at iptables but I think it's fine (it can connect when not proxying anyway):

Chain INPUT (policy ACCEPT)
target     prot opt source               destination         

Chain FORWARD (policy DROP)
target     prot opt source               destination         
DOCKER-USER  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-1  all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED              
DOCKER     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED              
DOCKER     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED              
DOCKER     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED              
DOCKER     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere                                                      
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
DOCKER     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            
ACCEPT     all  --  anywhere             anywhere            

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain DOCKER (17 references)
target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:9000
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-rmtsys
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-update
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-bos
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-errors
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-volser
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-kaserver
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-vlserver
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-prserver
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-callback
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:afs3-fileserver
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:postgresql
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:mysql
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:https
ACCEPT     tcp  --  anywhere             172.25.0.2           tcp dpt:http

Chain DOCKER-ISOLATION-STAGE-1 (1 references)
target     prot opt source               destination         
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
DOCKER-ISOLATION-STAGE-2  all  --  anywhere             anywhere            
RETURN     all  --  anywhere             anywhere            

Chain DOCKER-ISOLATION-STAGE-2 (17 references)
target     prot opt source               destination         
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
DROP       all  --  anywhere             anywhere            
RETURN     all  --  anywhere             anywhere            

Chain DOCKER-USER (1 references)
target     prot opt source               destination         
RETURN     all  --  anywhere             anywhere

I don't need no TLS at the moment. This is a task I'll lay down with my shame at the feet of my sons and daugthers, hoping they can achieve what I couldn't.

If anyone could tell me why that TCP connection isn't working or what's wrong with the mysql config (forgot to mention I tried with mariadb:latest and mysql5 and 8) that would be, like, really cool.

Maybe it only works with TLS, I don't know. Help.

zespri · September 3, 2020, 8:43pm

Here is what I would try:

Look at my sql logs and confirm that it had no troubles starting up. If it's failing to start no wonder you cannot connect
Exposing mysql docker port in your compose files and connecting to that port directly. If it does not work either you can leave traefik alone and concentrate on your mysql / docker problems. If it works, however, that's when it becomes interesting.

Please report back on the above!

nolan · September 3, 2020, 9:31pm

Already try both. MySQL has not troubles at startup and I can connect to it from inside the vps with:

me@vps:~ $ docker run --rm -it --network=mysql mysql:5.7 mysql --user mysql_user -pmysql_password --host=mysql-db --port=3306

It works with both the hostname and the container's IP.

As well as from @home when the service publishes the port and map 3306:3306 (and 8000:3306 to bypass any TCP filtering on 3306 port).

I am thinking about writing a single minimal docker-compose file but it's late here, so that's gonna be in ~12 hours.

I am thinking that it's either a weird traefik2 bug (unlikely but who knows) or somehow mysql/mariadb refuses connection from public IP when it goes through the proxy (hence the 0.0.0.0 bind in config files but it doesn't change anything) but from the wireshark log (not an expert) it's like the connection is made to traefik and it never passes packets to the container.

zespri · September 3, 2020, 9:54pm

Yes, would be good to have a minimal repro, thanks. I cannot look at it right now, but hope to be able to have a look over the weekend, especially if get me the minimal compose file No promises, though.

nolan · September 3, 2020, 10:07pm

In the meantime I enabled logging by adding this to docker-compose.yml:

command: --general-log=1 --general-log-file=/var/log/mysql/general-log.log
volumes:
  - ./mysql.log:/var/log/mysql/general-log.log

me@vps:~ touch mysql/mysql.log
me@vps:~ chown mysql:mysql mysql/mysql.log && chmod 777 mysql/mysql.log

And here's what it says when connecting from a temporary container inside that mysql network then from outside through the published port and then from outside through traefik:

/usr/sbin/mysqld, Version: 8.0.21 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2020-09-03T21:59:32.348989Z         8 Connect   user@172.26.0.4 on  using SSL/TLS
2020-09-03T21:59:32.373955Z         8 Query     select @@version_comment limit 1
2020-09-03T21:59:43.827623Z         8 Query     SHOW databases
2020-09-03T21:59:47.944647Z         8 Quit
2020-09-03T22:00:02.423368Z         9 Connect   user@192.0.2.2 on  using SSL/TLS
2020-09-03T22:00:02.442277Z         9 Query     select @@version_comment limit 1
2020-09-03T22:00:08.682486Z         9 Query     show databases
2020-09-03T22:00:12.422817Z         9 Quit

Mysql has no logs about the third kind of connection.

(hey, log says it's using SSL/TSL)

edit: So I set up the command like this:

    command: --skip-ssl --general-log=1 --general-log-file=/var/log/mysql/general-log.log

but:

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

So I changed it to this:

    command: --default-authentication-plugin=mysql_native_password --skip-ssl --general-log=1 --general-log-file=/var/log/mysql/general-log.log

Off to bed.

nolan · September 4, 2020, 1:28pm

Here's a minimal repro (there's a catch though):

---

services:
  traefik:
    image: traefik:2.2.8
    command:
      - --api.dashboard=true
      - --entrypoints.http.address=:80
      - --entrypoints.mysql.address=:3306
      - --entrypoints.tcp-echo.address=:9000
      - --log.level=debug
      - --providers.docker.exposedbydefault=true
    labels:
      - traefik.http.routers.traefik.entrypoints=http
      - traefik.http.routers.traefik.rule=PathPrefix(`/`)
      - traefik.http.routers.traefik.service=api@internal
    ports:
      - "80:80"
      - "3306:3306"
      - "9000:9000"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro

  tcp-echo:
    image: venilnoronha/tcp-echo-server:latest
    labels:
      - traefik.tcp.routers.tcp-echo.entrypoints=tcp-echo
      - traefik.tcp.routers.tcp-echo.rule=HostSNI(`*`)
      - traefik.tcp.services.tcp-echo.loadbalancer.server.port=9000

  mysql:
    command:
      - --general-log=1
      - --general-log-file=/var/lib/mysql/general.log # because mysqld has the right to write in this directory
    environment:
      MYSQL_ROOT_PASSWORD: admin
    hostname: mysql
    image: mysql:8
    labels:
      - traefik.tcp.routers.mysql.entrypoints=mysql
      - traefik.tcp.routers.mysql.rule=HostSNI(`*`)
      - traefik.tcp.services.mysql.loadbalancer.server.port=3306
    ports:
      - 8001:3306

version: "3.4"

All containers are running in the same default network generated when running docker-compose.

me@vps:~/mysqldebug docker-compose up -d

Run this command on the host to watch the logs (from the directory where the docker-compose file is):

me@vps:~/mysqldebug $ watch -n1 "docker exec ${PWD##*/}_mysql_1 /bin/sh -c 'tail /var/lib/mysql/general.log'"

This is reachable over the internet so the command to test things from @home are:

me@home:~ nc 192.0.2.2 9000  
me@home:~ mysql --host  192.0.2.2 --protocol=tcp --port 8001 --user root -padmin
me@home:~ mysql --host  192.0.2.2 --protocol=tcp --port 3306 --user root -padmin

and from the VPS:

me@vps:~/mysqldebug docker run --rm -it --network=${PWD##*/}_default mysql:8 mysql --user user -puser --host=${PWD##*/}_mysql_1 --port=3306

Everything's working as expected and there's no "but".
That's the catch.
The MySQL client can connect from @home and traefik picks up the packets and sends it to the container.

So, I deleted all those containers and went back to the files of the first post to look for the differences (I wasn't expecting my first minimal setup to work).

And now it works fine.

I have been thinking hard for the last 10 minutes and since I didn't change anything in the compose, .env and mounted files I can only pinpoint the problem to the mysql volume that somehow must have had some data or configuration that prevented the connection (most likely if it was run with mysql:5.7/8/mariadb at some points and I didn't deleted it when switching the base image). Or maybe I forgot to connect the mysql network to traefik but that's highly unlikely since I run all the connect command in a bash batch file (in case I removed the traefik container, I don't want to reconnect the networks to the container by hands).

So, I can't repro. And I have the nagging feeling I'll never really know why it didn't work.

edit: ahaaaa... restarting all other container and services on the VPS triggered the problem. I hunted down the culprit and am investigating why it prevents connection (because that container is running an apache server that doesn't publish ports and certainly not 3306).

I am going to say the problem is solved as far as TCP connecting is concerned.

Thanks everyone .

For the curious, here's the container inspect output:

{
    "AppArmorProfile": "",
    "Args": [
        "apache2-foreground"
    ],
    "Config": {
        "AttachStderr": false,
        "AttachStdin": false,
        "AttachStdout": false,
        "Cmd": [
            "apache2-foreground"
        ],
        "Domainname": "",
        "Entrypoint": [
            "docker-php-entrypoint"
        ],
        "Env": [
            "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
            "PHPIZE_DEPS=autoconf \t\tdpkg-dev \t\tfile \t\tg++ \t\tgcc \t\tlibc-dev \t\tmake \t\tpkg-config \t\tre2c",
            "PHP_INI_DIR=/usr/local/etc/php",
            "APACHE_CONFDIR=/etc/apache2",
            "APACHE_ENVVARS=/etc/apache2/envvars",
            "PHP_EXTRA_BUILD_DEPS=apache2-dev",
            "PHP_EXTRA_CONFIGURE_ARGS=--with-apxs2 --disable-cgi",
            "PHP_CFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64",
            "PHP_CPPFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64",
            "PHP_LDFLAGS=-Wl,-O1 -pie",
            "GPG_KEYS=CBAF69F173A0FEA4B537F470D66C9593118BCCB6 F38252826ACD957EF380D39F2F7956BC5DA04B5D",
            "PHP_VERSION=7.3.17",
            "PHP_URL=https://www.php.net/get/php-7.3.17.tar.xz/from/this/mirror",
            "PHP_ASC_URL=https://www.php.net/get/php-7.3.17.tar.xz.asc/from/this/mirror",
            "PHP_SHA256=6a30304c27f7e7a94538f5ffec599f600ee93aedbbecad8aa4f8bec539b10ad8",
            "PHP_MD5="
        ],
        "ExposedPorts": {
            "80/tcp": {}
        },
        "Hostname": "fd4873ec6628",
        "Image": "wp-php7.3:prod",
        "Labels": {
            "com.docker.compose.config-hash": "105052de58c00a06d1c1fa5cb51b121140c5dbc38b7471cbd8fc231bc06e3057",
            "com.docker.compose.container-number": "1",
            "com.docker.compose.oneoff": "False",
            "com.docker.compose.project": "wpdemo",
            "com.docker.compose.service": "web",
            "com.docker.compose.version": "1.21.0",
            "traefik.enable": "true",
            "traefik.http.routers.wpdemo-web.entrypoints": "https",
            "traefik.http.routers.wpdemo-web.rule": "Host(`wpdemo.example.com`)",
            "traefik.http.routers.wpdemo.tls.certresolver": "le",
            "traefik.http.services.wpdemo-web.loadbalancer.server.port": "80"
        },
        "OnBuild": null,
        "OpenStdin": false,
        "StdinOnce": false,
        "StopSignal": "SIGWINCH",
        "Tty": false,
        "User": "",
        "Volumes": {
            "/var/www/.wp-cli": {},
            "/var/www/html": {}
        },
        "WorkingDir": "/var/www/html"
    },
    "Created": "2020-09-04T13:57:51.770697725Z",
    "Driver": "overlay2",
    "ExecIDs": null,
    "GraphDriver": {
        "Data": {
            "LowerDir": "/var/lib/docker/overlay2/b2a411e32e38014a4a00b1b8c3117a309d0ce239292242e07ca6d51cae082540-init/diff:/var/lib/docker/overlay2/8caa55a0ee4f69a0f66009f58dfd6c6c66967acc6f0351944ba0c3c3f02d4129/diff:/var/lib/docker/overlay2/8ea030d58f9117a454d829b866856e2de342f12d1b36a6a11e6414c5357cac33/diff:/var/lib/docker/overlay2/71fd87590dfc58adcf0217c58f3efb23eb0a16bda561e56bab5362fd0f256e91/diff:/var/lib/docker/overlay2/2fce9a2871893f6374c53c7bad8585fb380b394aa1748aebb9d1684d7b54b1ac/diff:/var/lib/docker/overlay2/e92c87991ddc7cdf655945cde690da5638181532f619238aecb096a8d26e1d6f/diff:/var/lib/docker/overlay2/59854ccca04ed3fe51566ee627a8823065f9e7b63f4d6176c6793e1a9091e6b5/diff:/var/lib/docker/overlay2/6e6caabc3cfab9d95ff10e7ade7b579ca1d6f372e7c32ddcf773dcb2cdd244c1/diff:/var/lib/docker/overlay2/227f473c57e8c72ca3ec54379db7a8357af94db5e85caad683773a46af40460a/diff:/var/lib/docker/overlay2/e71c3cc8849bd2c5e03f6f86d0ee26bf011bbb502c29945f4d8796259483b57f/diff:/var/lib/docker/overlay2/9cd5446bfef8bbb3e77102d2063047e7677838572dff638199d996da5ddee491/diff:/var/lib/docker/overlay2/7040c1cf4f47594bf52a40bfab9de517ba8710187d93e9cd3b67c585b961acd0/diff:/var/lib/docker/overlay2/16f694f1f95c15aef5bf2e6f717443708863dd6c5ee147385f5629c48a466ebf/diff:/var/lib/docker/overlay2/bc77743fe12622f20f32f7b5819696f83eebd9c6c3753f5fa6c6c84974d1441e/diff:/var/lib/docker/overlay2/4a2245201de949913b0ceb8f8e37bccdcba003899d3b91da6b422ee17adf4ee7/diff:/var/lib/docker/overlay2/afde68502ae4cc4107ab321819fa3947f5e792906adba3a125969a83d91cd8a4/diff:/var/lib/docker/overlay2/c0a959f1a7899ef884b984728600047c080da3249e9009bceee5a8f6f7a7876c/diff:/var/lib/docker/overlay2/fee3b97a0c7503db842bff0f6482288c0dec41c795ef1492948a87da393dd581/diff:/var/lib/docker/overlay2/1352d8666e8932132d2c8aef3c45e530ad76760739ef9d7371e21dca390fff0d/diff:/var/lib/docker/overlay2/df4b428b095fffc2c7569dfd7f5600431dc4c48a0c764e646eda35c5a8cfbc54/diff:/var/lib/docker/overlay2/f47660b0fa90095fc4eabea0b45d2f00c123bab1870b4636650dc17b9d095bc1/diff:/var/lib/docker/overlay2/f936bead8868be2374256c2b2a02dc094dfe56937c4d5b67fa95869f2c1226dd/diff:/var/lib/docker/overlay2/a77abb1ea10ef6ddbbf6f070f4462823fb785a5296e567d4aab36cb7923b6843/diff:/var/lib/docker/overlay2/c18f68ab76806ce3bd447194a3f591615935b9e3e26cbb7b871e8a712c0496c4/diff:/var/lib/docker/overlay2/065870953d98caec2f0a457de7eeba85416c89ba7757fc05c4746b4cb1c94115/diff:/var/lib/docker/overlay2/1ad1af75285288bb7390333fa51aacdc4ead9765f2c043592fe7be473576c6a6/diff:/var/lib/docker/overlay2/2ffb17359eec94eba17e0ad5daea5069f5f5a53e480a159e6e46262b787ffb39/diff:/var/lib/docker/overlay2/5491292ea9c0d3f9c7ebe6daac0c3a3d680cb95ff16e3279095651d086fac896/diff:/var/lib/docker/overlay2/3e6ead7243743886622cecd50c993a6078028d4eaacce2ad5aae65b3f4758401/diff:/var/lib/docker/overlay2/be286539f13536083c57dfb266ddf4f66d64bc06f7d897d4fb1c54af15c5b06b/diff:/var/lib/docker/overlay2/b4d5ff4580262e45b0d4edab79831f1e5c8473ec4c2d7b204c24082f64b665f1/diff:/var/lib/docker/overlay2/0e8b07c19d38c1b8e2fd83ef862ac456c5be2357f1e4adb828885d5f388035f5/diff:/var/lib/docker/overlay2/22d55ad6d71cab9987c25501abc3aa0cd908ab75ca412ed7d3f14b24ab866486/diff",
            "MergedDir": "/var/lib/docker/overlay2/b2a411e32e38014a4a00b1b8c3117a309d0ce239292242e07ca6d51cae082540/merged",
            "UpperDir": "/var/lib/docker/overlay2/b2a411e32e38014a4a00b1b8c3117a309d0ce239292242e07ca6d51cae082540/diff",
            "WorkDir": "/var/lib/docker/overlay2/b2a411e32e38014a4a00b1b8c3117a309d0ce239292242e07ca6d51cae082540/work"
        },
        "Name": "overlay2"
    },
    "HostConfig": {
        "AutoRemove": false,
        "Binds": [
            "wp-cli-cache:/var/www/.wp-cli:rw",
            "wpdemo-web:/var/www/html:rw"
        ],
        "BlkioDeviceReadBps": null,
        "BlkioDeviceReadIOps": null,
        "BlkioDeviceWriteBps": null,
        "BlkioDeviceWriteIOps": null,
        "BlkioWeight": 0,
        "BlkioWeightDevice": null,
        "CapAdd": null,
        "CapDrop": null,
        "Capabilities": null,
        "Cgroup": "",
        "CgroupParent": "",
        "ConsoleSize": [
            0,
            0
        ],
        "ContainerIDFile": "",
        "CpuCount": 0,
        "CpuPercent": 0,
        "CpuPeriod": 0,
        "CpuQuota": 0,
        "CpuRealtimePeriod": 0,
        "CpuRealtimeRuntime": 0,
        "CpuShares": 0,
        "CpusetCpus": "",
        "CpusetMems": "",
        "DeviceCgroupRules": null,
        "DeviceRequests": null,
        "Devices": null,
        "Dns": null,
        "DnsOptions": null,
        "DnsSearch": null,
        "ExtraHosts": null,
        "GroupAdd": null,
        "IOMaximumBandwidth": 0,
        "IOMaximumIOps": 0,
        "IpcMode": "shareable",
        "Isolation": "",
        "KernelMemory": 0,
        "KernelMemoryTCP": 0,
        "Links": null,
        "LogConfig": {
            "Config": {},
            "Type": "json-file"
        },
        "MaskedPaths": [
            "/proc/asound",
            "/proc/acpi",
            "/proc/kcore",
            "/proc/keys",
            "/proc/latency_stats",
            "/proc/timer_list",
            "/proc/timer_stats",
            "/proc/sched_debug",
            "/proc/scsi",
            "/sys/firmware"
        ],
        "Memory": 0,
        "MemoryReservation": 0,
        "MemorySwap": 0,
        "MemorySwappiness": null,
        "NanoCpus": 0,
        "NetworkMode": "wpdemo",
        "OomKillDisable": false,
        "OomScoreAdj": 0,
        "PidMode": "",
        "PidsLimit": null,
        "PortBindings": {},
        "Privileged": false,
        "PublishAllPorts": false,
        "ReadonlyPaths": [
            "/proc/bus",
            "/proc/fs",
            "/proc/irq",
            "/proc/sys",
            "/proc/sysrq-trigger"
        ],
        "ReadonlyRootfs": false,
        "RestartPolicy": {
            "MaximumRetryCount": 0,
            "Name": ""
        },
        "Runtime": "runc",
        "SecurityOpt": null,
        "ShmSize": 67108864,
        "UTSMode": "",
        "Ulimits": null,
        "UsernsMode": "",
        "VolumeDriver": "",
        "VolumesFrom": []
    },
    "HostnamePath": "/var/lib/docker/containers/fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c/hostname",
    "HostsPath": "/var/lib/docker/containers/fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c/hosts",
    "Id": "fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c",
    "Image": "sha256:42706480dfc330dd68e1aa5c6c06576d066b61d2823ee4c9173620ebfd4062bf",
    "LogPath": "/var/lib/docker/containers/fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c/fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c-json.log",
    "MountLabel": "",
    "Mounts": [
        {
            "Destination": "/var/www/.wp-cli",
            "Driver": "local",
            "Mode": "rw",
            "Name": "wp-cli-cache",
            "Propagation": "",
            "RW": true,
            "Source": "/var/lib/docker/volumes/wp-cli-cache/_data",
            "Type": "volume"
        },
        {
            "Destination": "/var/www/html",
            "Driver": "local",
            "Mode": "rw",
            "Name": "wpdemo-web",
            "Propagation": "",
            "RW": true,
            "Source": "/var/lib/docker/volumes/wpdemo-web/_data",
            "Type": "volume"
        }
    ],
    "Name": "/wpdemo-web",
    "NetworkSettings": {
        "Bridge": "",
        "EndpointID": "",
        "Gateway": "",
        "GlobalIPv6Address": "",
        "GlobalIPv6PrefixLen": 0,
        "HairpinMode": false,
        "IPAddress": "",
        "IPPrefixLen": 0,
        "IPv6Gateway": "",
        "LinkLocalIPv6Address": "",
        "LinkLocalIPv6PrefixLen": 0,
        "MacAddress": "",
        "Networks": {
            "wpdemo": {
                "Aliases": [
                    "fd4873ec6628",
                    "web"
                ],
                "DriverOpts": null,
                "EndpointID": "",
                "Gateway": "",
                "GlobalIPv6Address": "",
                "GlobalIPv6PrefixLen": 0,
                "IPAMConfig": null,
                "IPAddress": "",
                "IPPrefixLen": 0,
                "IPv6Gateway": "",
                "Links": null,
                "MacAddress": "",
                "NetworkID": "9f513b146514b7f4fa8bb5d9f0f7006e94b92225ee935c90c618c5d39a7220f3"
            }
        },
        "Ports": {},
        "SandboxID": "7567940ea5f90b192351cfdcc24439169b477483e5558811a651fd13323661c1",
        "SandboxKey": "/var/run/docker/netns/7567940ea5f9",
        "SecondaryIPAddresses": null,
        "SecondaryIPv6Addresses": null
    },
    "Path": "docker-php-entrypoint",
    "Platform": "linux",
    "ProcessLabel": "",
    "ResolvConfPath": "/var/lib/docker/containers/fd4873ec6628d9d2dcaabfc0a163ddf294116c61b27dafcf800b21859f953d9c/resolv.conf",
    "RestartCount": 0,
    "State": {
        "Dead": false,
        "Error": "",
        "ExitCode": 0,
        "FinishedAt": "2020-09-04T13:58:32.274357468Z",
        "OOMKilled": false,
        "Paused": false,
        "Pid": 0,
        "Restarting": false,
        "Running": false,
        "StartedAt": "2020-09-04T13:58:21.479515366Z",
        "Status": "exited"
    }
}

edit2: finally found the faulty thing. Here's that service yml definition:

  web:
    container_name: ${DOCKER_PREFIX}-web
    image: wp-php7.3:prod
    labels:
      - traefik.enable=true
      - traefik.http.routers.${DOCKER_PREFIX}-web.entrypoints=https
      - traefik.http.routers.${DOCKER_PREFIX}-web.rule=Host(`${WP_URL}`)
      - traefik.http.routers.${DOCKER_PREFIX}.tls.certresolver=le
      - traefik.http.services.${DOCKER_PREFIX}-web.loadbalancer.server.port=80
    volumes:
      - web:/var/www/html
      - wp-cli-cache:/var/www/.wp-cli

Commenting out - traefik.http.routers.${DOCKER_PREFIX}.tls.certresolver=le solves the problem. This line is a leftover from when my traefik setup didn't use a wild card certificate. Now how that line prevents connection on tcp/3306... I might try to do a minimal repro later next week unless it's so obvious to anyone but me.

Anyway, I got a one-way ticket to farmville to sell, departure tonight. PM if interested.

edit3: According to traefik dashboard the web container gets all entrypoints defined when that line is present.

nolan · September 5, 2020, 3:12pm

So. I finally understood what was going on (not really sure of the explanation though).

Here's the minimal repro, no need to run it over the internet:

---

services:
  traefik:
    image: traefik:2.2.8
    command:
      - --api.dashboard=true
      - --api.insecure=true
      - --entrypoints.http.address=:80
      - --entrypoints.mysql.address=:3306
      - --entrypoints.tcp-echo.address=:9000
      - --providers.docker.exposedbydefault=true
    labels:
      - traefik.http.middlewares.strip.stripprefix.prefixes=/traefik
      - traefik.http.routers.traefik.entrypoints=http
      - traefik.http.routers.traefik.middlewares=strip
      - traefik.http.routers.traefik.rule=PathPrefix(`/traefik`) || PathPrefix(`/api`)
      - traefik.http.routers.traefik.service=api@internal
    ports:
      - "80:80"
      - "3306:3306"
      - "9000:9000"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro

  mysql:
    environment:
      MYSQL_ROOT_PASSWORD: admin
    image: mysql:8
    labels:
      - traefik.tcp.routers.mysql.entrypoints=mysql                                                    
      - traefik.tcp.routers.mysql.rule=HostSNI(`*`)                                                    
      - traefik.tcp.services.mysql.loadbalancer.server.port=3306                                       
    ports:                                                                                             
      - 8001:3306                                                                                      
                                                                                                       
  tcp-echo:                                                                                            
    image: venilnoronha/tcp-echo-server:latest                                                         
    labels:                                                                                            
      - traefik.tcp.routers.tcp-echo.entrypoints=tcp-echo                                              
      - traefik.tcp.routers.tcp-echo.rule=HostSNI(`*`)                                                 
      - traefik.tcp.services.tcp-echo.loadbalancer.server.port=9000                                    
    ports:                                                                                             
      - 9001:9000                                                                                      
                                                                                                       
  whoami:                                                                                              
    image: containous/whoami
    labels:
#     - traefik.http.routers.whatever.tls=true #uncommenting this line triggers the problem
      - traefik.http.routers.whoami.entrypoints=http
      - traefik.http.routers.whoami.rule=Path(`/`)
      - traefik.http.services.whoami.loadbalancer.server.port=80

version: "3.4"

When adding the tls label on the whoami container you can see in the traefik dashboard (http://127.0.0.1/traefik) that a new router is created and gets all the entrypoints defined.

Until that label is removed you won't be able to connect through traefik with:

$ mysql --host 127.0.0.1 --protocol=tcp --port 3306 --user root -padmin

The echo server can still be accessed though:

$ nc 127.0.0.1 9000

Creating the container without tls (traefik.http.routers.whatever=true) doesn't trigger the problem. but that doesn't mean the problem necessarily lies with TLS.
I also don't understand why the echo server keeps on working but not the mysql server (even when the mysql container isn't down).

PS: can't edit previous post: Notice in my previous post how the ${DOCKER_PREFIX} is missing the -web part in the problematic line and creating the faulty router .

PS: minimal setup without traefik http interface and tcp echo server

---
services:
  traefik:
    image: traefik:2.2.8
    command:
      - --entrypoints.http.address=:80
      - --entrypoints.mysql.address=:3306
      - --providers.docker.exposedbydefault=true
    ports:
      - "80:80"
      - "3306:3306"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro

  mysql:
    environment:
      MYSQL_ROOT_PASSWORD: admin
    image: mysql:8
    labels:
      - traefik.tcp.routers.mysql.entrypoints=mysql
      - traefik.tcp.routers.mysql.rule=HostSNI(`*`)
      - traefik.tcp.services.mysql.loadbalancer.server.port=3306
    ports:
      - 8001:3306

  whoami:
    image: containous/whoami
    labels:
      - traefik.http.routers.whatever.tls=true # disable this line to trigger the problem
      - traefik.http.routers.whoami.entrypoints=http
      - traefik.http.routers.whoami.rule=Path(`/`)
      - traefik.http.services.whoami.loadbalancer.server.port=80

zespri · September 6, 2020, 8:45pm

Good work on solving this, sorry did not get around to looking into over weekend. I just skimmed through what you wrote about without due attention, I might revisit it a bit later, a question though, why TLS? TLS router basically means that TLS is terminated by traefik, so it expects the client connection to be TLS, but server connection by default won't be TLS since TLS is already terminated by traefik. So I would not expect this to work, for postgres, or tcp ping or mysql. I'll need to check your config from above, but perhaps postgres and ping were not TLS?

In any case, what's your reasoning for using TLS router? Who does the TLS encryption on the client side?

Topic		Replies	Views
Traefik with TCP Routing for Postgres Docker Container Traefik v2 (latest) docker , file , tcp	0	413	May 2, 2022
Problem enabling tcp router to postgres Traefik v2 (latest) tcp	0	855	January 30, 2020
Help in TCP configure of traefik mapped postgresql container - hostSNI Traefik v2 (latest) docker	6	5740	February 16, 2022
Error connect mysql via TCP by traefik Traefik v2 (latest) docker , file	1	1167	March 16, 2021
MySQL/Mariadb behind Traefik Reverse Proxy Traefik v2 (latest) docker , tcp	3	6053	August 10, 2021

TCP: Can't connect to mysql container behind and through traefik but can connect to tcp echo server and postgres

Traefik 2

Traefik2 docker-compose.yml

Traefik2 .env

Traefik2 static.yml