Can't connect to MySQL server: Connection refused

I've been trying for two days now to get a TCP connection to a docker service running MySQL. I have treated it the same as creating a https connection except that I have added HostSNI to my rule:

    labels:
      - traefik.tcp.routers.router01-mysql.entrypoints=mysql
      - traefik.tcp.routers.router01-mysql.tls=true
      - traefik.tcp.routers.router01-mysql.rule=HostSNI(
        `domain-mysql.test`)

I have added the certificates to the certs directory and provided this to my toml config:

 [[tls.certificates]]
    certFile = "/certs/domain-mysql-cert.pem"
    keyFile = "/certs/domain-mysql-key.pem"

Traefik confirms the TCP connection in its log:

time="2019-07-22T14:34:10Z" level=debug msg="Creating TCP server 0 at 192.168.48.4:3306" serverName=0 routerName=domain-mysql@docker entryPointName=mysql serviceName=mysql_myproject
time="2019-07-22T14:34:10Z" level=debug msg="Adding route domain-mysql.test on TCP" entryPointName=mysql routerName=domain-mysql@docker

Both containers are connected via a external network connection I created in docker called traefik and mysql has been initialized with bind-address = 0.0.0.0 in my .cnf file.
So when I run:
mysql -u root -h domain-mysql.test -p password
I would expect for it to connect, however mysql states:

ERROR 2003 (HY000): Can't connect to MySQL server on 'domain-mysql.test' (111 "Connection refused")

This error will be shown even if the mysql service is not running, which would lead me to think that I have not set traefik up correctly and that when I run the mysql command it can see a TCP connection running on port 3306 but traefik is not sending the data to the associated mysql service.

Can anybody work out where I am going wrong please? I have tried everything I can think of.

1 Like

Hi @JamesGreenaway, could you provide more context, aka. the full docker-compose.yml so we can check the configurations?

One thing in particular to check: Traefik infers the port to use for the backend service container from docker's metadatas. But this "automagic" behavior requires the backend (image + container) to expose only 1 port.

If you are using the official mysql Docker image, then this behavior won't work, as 2 ports are exposed: 3306 AND 33060 (I don't know what is the purpose of the port 33060 to be honest).
As it is not possible to know which one to use, so you need to "tell" Traefik that you want 3306:

  • Define a Load Balancer of a Traefik Service named mysql-svc to use the port 3306 by adding the following label to your MySQL container/service: - traefik.tcp.services.mysql-svc.loadbalancer.server.port=3306

  • Associate the service mysql-svc to the router router01-mysql you already defined through your actual labels:
    -traefik.tcp.routers.router01-mysql.service=mysql-svc

If you have any doubt (or if I did any typo) about the syntax, please check the reference documentation here: https://docs.traefik.io/v2.0/reference/dynamic-configuration/docker/

Another thing that is worth trying, while you have only 1 MySQL service behind Traefik: set the HostSNI rule to "catch all" (*) to remove the SNI part from the equation, time for you to validate MySQL initial link:

traefik.tcp.routers.router01-mysql.rule=HostSNI(`*`)

Let us know if you made it !?

container one:

version: "3.7"
services:
  ubuntu:
    image: ubuntu:latest
   networks: 
      - traefik
    command: sleep infinity
networks:
  traefik:
    external: true

container two:

version: "3.7"
services:
  dnsmasq:
    image: jpillora/dnsmasq:latest
    ports:
      - 53:53/udp
    networks:
      - traefik
    entrypoint: 
      - /bin/sh 
      - -c 
      - 'echo "address=/#/127.0.0.1" > /etc/dnsmasq.conf 
      && webproc --config /etc/dnsmasq.conf -- dnsmasq --no-daemon'
  traefik:
    image: traefik:v2.0.0-beta1
    depends_on:
      - dnsmasq
    ports:
      - 3306:3306
      - 8080:8080
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - ./traefik.toml:/traefik.toml:ro
      - ./certs:/certs:ro
  mysql: 
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    depends_on:
      - traefik
    labels:
      - traefik.tcp.routers.router01-mysql.entrypoints=mysql
      - traefik.tcp.routers.router01-mysql.tls=true
      - traefik.tcp.routers.router01-mysql.rule=HostSNI(
        `mysql.test`)
      - traefik.tcp.routers.router01-mysql.service=mysql-svc
      - traefik.tcp.services.mysql-svc.loadbalancer.server.port=3306
    networks:
      - traefik
    volumes:
      - ./my.cnf:/etc/mysql/conf.d/config-file.cnf
networks:
  traefik:
    external: true

traefik.toml:

[global]
  checkNewVersion = true
  sendAnonymousUsage = true

[entryPoints]
  [entryPoints.mysql]
    address = ":3306"

[providers]
  [providers.docker]
    watch = true
    endpoint = "unix:///var/run/docker.sock"
    exposedByDefault = true
  [providers.file]
    directory = "/config"
    watch = true

[tls]
  [[tls.certificates]]
    certFile = "/certs/mysql-cert.pem"
    keyFile = "/certs/mysql-key.pem"
  [[tls.certificates]]
    certFile = "/certs/localhost-cert.pem"
    keyFile = "/certs/localhost-key.pem"


[api]
  dashboard = true

[log]
  level = "DEBUG"

api/rawdata:

{
   "routers":{
      "traefik_tcp-traefik@docker":{
         "service":"traefik_tcp-traefik",
         "rule":"Host(`traefik-tcp-traefik`)",
         "status":"enabled"
      }
   },
   "services":{
      "traefik_tcp-traefik@docker":{
         "loadBalancer":{
            "servers":[
               {
                  "url":"http://192.168.48.3:80"
               }
            ],
            "passHostHeader":true
         },
         "status":"enabled",
         "usedBy":[
            "traefik_tcp-traefik@docker"
         ],
         "serverStatus":{
            "http://192.168.48.3:80":"UP"
         }
      }
   },
   "tcpRouters":{
      "router01-mysql@docker":{
         "entryPoints":[
            "mysql"
         ],
         "service":"mysql-svc",
         "rule":"HostSNI( `mysql.local`)",
         "tls":{
            "passthrough":false
         },
         "status":"enabled"
      }
   },
   "tcpServices":{
      "mysql-svc@docker":{
         "loadBalancer":{
            "servers":[
               {
                  "address":"192.168.48.4:3306"
               }
            ]
         },
         "status":"enabled",
         "usedBy":[
            "router01-mysql@docker"
         ]
      }
   }
}

Thanks for the quick response @dduportal! I have tried adding the service you suggested but this has not worked either, I still get a similar response:

ERROR 2003 (HY000): Can't connect to MySQL server on 'mysql.test' (111)

Are you able to explain to me if I have the concept right? I would like multiple instances of MySQL running on the same port. I do not want to spend my time setting different ports and so I am under the impression that I can use traefik to set all my MySQL databases to port 3306 and distinguish what data gets sent where by determining which domain name given in Traefik's HostSNI rule matches and routing the data to it accordingly. The connection needs to be tls so that I can use a specific domain name but once the data is processed by traefik it will be send to its respective contiainer decrypted (unless specified with the passthrough option to remain encrypted).

Is this all correct?
Does this mean that I only need to provide the cert and key file to traefik so that it can recieve the data encrypted and then once I has reached traefik it will do the rest? Or do I need to send mysql a certificate and key as well? If so do you know how I would go about doing this?

Thanks again for your help.

1 Like

Hi @JamesGreenaway, here is my first try at using MySQL 5.7 with Traefik v2.0.0-beta1, without TLS for now:

  • The docker-compose.yml file I'm using:
version: '3'

services:
  edge:
    image: traefik:v2.0.0-beta1
    command:
    - --api
    - --providers.docker
    - --entrypoints.mysql.address=:3306
    ports:
      - "8080:8080"
      - "3306:3306"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
  db:
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=root
    labels:
      - "traefik.tcp.routers.mysql.rule=HostSNI(`*`)"
      - "traefik.tcp.routers.mysql.entrypoints=mysql"
      - "traefik.tcp.routers.mysql.service=mysql-svc"
      - "traefik.tcp.services.mysql-svc.loadbalancer.server.port=3306"
  • The commands:
$ docker-compose up -d
...
# Testing from inside Docker's network
$ docker run --rm -ti --network=mysql_default mysql:5.7 mysql --host=edge --port=3306 --user=root --password
Enter password:  #root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> exit
Bye
# Testing from my macOS client, using `mysql-client` package from homebrew
$ mysql --host=127.0.0.1 --port=3306 --user=root --password
Enter password:  #root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> exit
Bye

Next step: trying with tls, I'll keep you informed.

1 Like

I would never have thought that this was the way to do it! So what is going on here? Are you running another instance of MySQL as a client and using the --network command add it to the default network created by docker for the original MySQL image? Also, I never though of using the service name as the host for MySQL, however this begs the question: When using a domain name with a TLS connection what is it's purpose? How does traefik no which container to send the data to? Really appreciate your help on this one. :+1:

Yes, this is exactly what you explained :+1: . This is a usage pattern with Docker I've used since day 1, and it's really handy to avoid installing gazillions of packages on your host. It also ensures that both server and client have the same version, as the same docker image is used.

You can see it's also documented on the README of the official mysql Docker image here: https://hub.docker.com/_/mysql#connect-to-mysql-from-the-mysql-command-line-client .

The reason why I used this trick initially is that I'm using macOS with Docker4Mac, and I had issues with my installed mysql. I found later that Homebrew installed default configuration that were used behind my back, always trying to reach the mysql server using Unix socket (instead of using the CLI flags I used).

Now I've installed the right mysql-client package, and it also works.

This is the key part of the TCP + SNI routing introduced in Traefik v2.0. How does it work:

  • If you are using HTTP (with a traefik.http.router), then Traefik uses the HTTP header Host to determines the hostname, as it was the case with v1.x of Traefik.
  • If you are using TCP, then there is no such things as "Host", "header", etc. So by default, you are restricted to "catch all request incoming to a port, and forward it as it to 1 the backend service", which the example I gave you below. "Catch all" means having the rule HostSNI(`*`).
  • However, when the Layer-7 protocol, passing through the TCP Layer 4, is using TLS for encrypting the requests, then TCP can catch the first packets and get the "Server Name Indication" (aka. SNI) during the TLS handshake. So in this specific case, Traefik can use this "SNI" to route to different backend services. In this case, you can use a value in the router rule's HostSNI(xxx). This would be you case with TLS encryption between the mysql and Traefik (or between mysql client and server with TLS passtrough).

Hi @JamesGreenaway, there is indeed something wrong when we start to enable TLS for MySQL encryption, in order to benefit from the HostSNI routing. This issue (https://github.com/containous/traefik/issues/5155) tracks the problem, putting together 3 examples (Plain TCP, TLS termination at Traefik and TLS termination at MySQL ) in the gist in the issue.

1 Like

@JamesGreenaway, for information, I now confirm that you must have 1 external port for each MySQL instance (they cannot share the same couple IP + port), even with TLS enabled, as MySQL does not support the SNI in its protocol (see the links I've put on the issue).

So you have to stick with the plain TCP example I gave you, as MySQL, likewise SSH, is the culprit here :slight_smile:

1 Like

@dduportal,

I see, really appreciate all the help you have given me on this one! So I guess this is now in the hands of MySQL and depends on whether they fix this bug?

1 Like

It's not a bug :slight_smile: It's a feature that MySQL projects (same for MariaDB) does not consider worth adding (and maintaining). Raising your voice on their issue tracker by describing your architecture and why using SNI proxying is mandatory for you (e.g. 1 couple of IP + port as unique entrypoint for all your MySQL instances because it's not possible in your case to use different ports for each instance, or different IPs).

As for today, proxying with TCP works on an instance with Traefik. If you want multiple instances, you need to define multiple entrypoints.

The only behavior I'm not sure if it's a bug of it there is a reason behind, is related to forcing TLS encryption, which hangs for today: this is why I re-opened the issue on Traefik. We'll see later if it's a bug, or if I missed something obvious in the config or in MySQL ecosystem :slight_smile:

What do you think?

Yeah I have no idea, it will be interesting if Traefik could find away around this problem. I will keep an eye on the bug report you created and I will add my support to those you created for mysql. :crossed_fingers:

MySQL bug about implementing SNI : https://bugs.mysql.com/bug.php?id=84849
And for MariaDB : https://jira.mariadb.org/browse/MDEV-10658

It's marked as "major" in MariaDb bugtracker, hopefully it will come soon!

2 Likes

I'm also looking to connect to an instance of MySQL server via Traefik proxy, but I have not yet been able to actually connect to a single instance using a tcp.routers configuration similar to the one shown here. The only difference I can think of is the port mapping I use is -p 3307:3306 (host port 3307 maps to the 3306 port of the MySQL server container).

I made a few attempts to connect to MySQL server instance via Traefik (entrypoint's address :3306) but without success (it always fails to connect). Making a connection from host's port 3307 did work (as expected).

After looking at Traefik debug's log, I'm not even convinced that a request actually goes through to the corresponding tcp.routers / backend service... My guess is that using the same port on the host and the container may not be an ideal scenario to verify if proxying to a MySQL server instance actually works.

Did you try mapping a different host port (other than 3306)?

Hi Spinico, can you share your configuration please?

Just tested with the final 2.0.0 version with the following snippet and it works very well:

version: '3'

services:
  edge:
    image: traefik:v2.0.0
    command:
    - --providers.docker
    - --entrypoints.mysql.address=:3306
    ports:
      - "3306:3306"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock

  db:
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=root
    labels:
      - "traefik.tcp.routers.mysql.rule=HostSNI(`*`)"
      - "traefik.tcp.routers.mysql.entrypoints=mysql"
       # MySQL official image exposes 2 ports so we have to specify the right one to use
      - "traefik.tcp.routers.mysql.service=mysql-svc"
      - "traefik.tcp.services.mysql-svc.loadbalancer.server.port=3306"
docker run --rm -ti --network=mysql_default mysql:5.7 \
    mysql --host=edge --port=3306 --user=root --password --ssl-mode=DISABLED

Hi dduportal, here's the corresponding snippet of my configuration files (TOML) format:

# traefik.toml (static config)
[entrypoints]

  [entrypoints.mysql]
    address = ":3306"
# mysql.toml (dynamic config)
[tcp]

  [tcp.routers]	
    [tcp.routers.datastore]	
      rule = "HostSNI(`*`)" 
      service = "datastore"
      entrypoints = ["mysql"]	
		
  [tcp.services]
    [tcp.services.datastore]
      [tcp.services.datastore.loadbalancer]       
        [[tcp.services.datastore.loadbalancer.servers]]
          address = "127.0.0.1:3307"

The mysql service is exposed on port 3307 on the host (not the default 3306). I tried the latest traefik v2.0.0 release without success.

I use MySQL Workbench to test for connection and I get a connection error:

Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060

But when connecting directly using port 3307 it works fine.

Hi @Spinico, I'm missing context here: are the mysqld process in the same container as traefik's binary, or are these separate containers?

Because the IP 127.0.0.1, in the context of Traefik in the "classic" docker container, will never reach to any mysql container with the default Docker network configuration. As the IP of doccker container are always changing, you cannot "guess" it in advance: this is why Traefik has a Docker provider, which takes care of creating the services/router dynamically, filed with the right IP address retrieved from Docker API.

Another important thing: with Traefik 2.0, you must separate static and dynamic configurations, as explained in the page https://docs.traefik.io/v2.0/getting-started/configuration-overview. In the snippet you're giving, the tcp router and services are dynamic configuration (ref. https://docs.traefik.io/v2.0/reference/dynamic-configuration/file/), and should be in the same file as the entrypoints (which is static configuration - https://docs.traefik.io/v2.0/reference/static-configuration/file/).

Let us know more about the context you're having by providing a full reproduction case as only the TOML is only a piece of information :slight_smile:

@dduportal. Thanks for the static/dynamic clarifications. In my scenario, Traefik is running on a Windows Server host (outside any container) and the MySQL (mysqld) server is in a container.

Maybe it's the way Docker works on Windows, but I am able to reach my container's services (mysqld) from the host simply by using the exposed port (3307), this is why I can refer to it by using 127.0.0.1:3307.

I have made a few more tests and I finally got it to work with the configuration as shown, the problem was an upstream (not on my host machine) firewall rule that blocked port 3306. Things can get confusing when multiple firewalls must be set...

Thanks for the help.

You are welcome @Spinico! Without knowing the setup topology, it's always hard to guess :sweat_smile:

Happy to hear that you found your issue!

somebody knows how can i connect a web service running in a container behind traefik v2.2, to a mysql instance running in the host?

Thanks in advance.