Getting mysql connection issue when scaling the mysql container to more than 1 in docker swarm

Question

I have a host machine running in swarm mode. I am running it on single machine now, no clusters (no multiple machine).

The services are running fine. I have created a volume for the mysql container. I believe when the mysql container is scaled they all will read from the same volume.

Here is the docker-compose. Which works Great and no mysql connection issue but when I scale the mysql container to 2

version: "3.4"

services:
  node:
    image: prod_engineering_node:v7
    networks:
      - backend
    volumes:
      - ./codebase:/usr/src/app
    ports:
      - "8082:8082"
    depends_on:
      - engineeringmysql
    deploy:
      mode: replicated
      replicas: 1 
      placement:
        constraints:
          - node.role == manager

  mysql:
    image: prod_engineering_mysql:v1
    command: mysqld --default-authentication-plugin=mysql_native_password
    networks:
      - backend
    ports:
      - "3309:3306"
    environment:
      MYSQL_ROOT_PASSWORD: main_pass
      MYSQL_DATABASE: engineering
      MYSQL_USER: user
      MYSQL_PASSWORD: pass
    volumes:
      - ./sqldata:/var/lib/mysql:rw
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.role == manager

  nginx:
    image: prod_engineering_nginx:v1
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./angular_build:/var/www/html/studydote_v2/frontend:rw
      - ./laravel_admin:/var/www/html/dev/backend/public:rw
    networks:
      - backend
    depends_on:
      - engineeringphpfpm
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.role == manager


  phpfpm:
    image: prod_engineering_phpfpm:v1
    ports:
      - "9001:9000"
    depends_on:
      - engineeringmysql
    networks:
      - backend
    volumes:
      - ./angular_build:/var/www/html/studydote_v2/frontend:rw
      - ./laravel_admin:/var/www/html/dev/backend/public:rw
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints:
          - node.role == manager

networks:
  backend:
    driver: overlay

This is how i scaled the mysql container.

docker service scale servicename=2

Now I get the db connections issue.

Can anyone help me with it? What might be the issue? If this is the wrong way to scale mysql db, please suggest me what are the better ways.

Answer 1

When you start a service, Docker swarm will assign a virtual IP address to each service, and load-balance all requests to that IP to each of the replica-containers.

What probably happens (but it's hard to see without the full logs), is that the tcp connection gets loadbalanced across both DBs: the first connection would go to nr1, the second one to nr2 etc.

However, mysql connections are stateful, not stateless. So this way is scaling your db isn't going to work. Also note that Docker won't handle the Mysql replication work for you. What people typically do, is:

• avoid having to run multiple DB instances if you don't need to
• run 2 mysql services: a mysql-master and a mysql-slave, each with their own config
• do some intelligent service discovery in a startup script in your mysql image