Home / server / Questions / 1166156
Accepted
Jose Cabrera Zuniga
Asked: 2024-10-05 00:24:12 +0800 CST

Setting docker mariadb replication

  • 772

To understand mariadb replication I created the next docker containers:

docker run -e TZ=America/Denver --name mariadb1 --network=camino_nw \
  -e MARIADB_SERVER_ID=1 -e MARIADB_LOG_BIN=mysql-bin -e MARIADB_LOG_BASENAME=mariadb1 \
  -e MARIADB_BINLOG_FORMAT=mixed -p 127.0.0.1:53301:3306 \
  -v /home/jcz/Documents/dockerMariadbData1:/var/lib/mysql:z \
  -e MARIADB_ROOT_PASSWORD=S3cretPw -d mariadb:latest

docker run -e TZ=America/Denver --name mariadb2 --network=camino_nw \
  -e MARIADB_SERVER_ID=2 -e MARIADB_LOG_BIN=mysql-bin -e MARIADB_LOG_BASENAME=mariadb2 \
  -e MARIADB_BINLOG_FORMAT=mixed -p 127.0.0.1:53302:3306 \
  -v /home/jcz/Documents/dockerMariadbData2:/var/lib/mysql:z \
  -e MARIADB_ROOT_PASSWORD=S3cretPw -d mariadb:latest

docker run -e TZ=America/Denver --name mariadb3 --network=camino_nw \
  -e MARIADB_SERVER_ID=3 -e MARIADB_LOG_BIN=mysql-bin -e MARIADB_LOG_BASENAME=mariadb3 \
  -e MARIADB_BINLOG_FORMAT=mixed -p 127.0.0.1:53303:3306 \
  -v /home/jcz/Documents/dockerMariadbData3:/var/lib/mysql:z \
  -e MARIADB_ROOT_PASSWORD=S3cretPw -d mariadb:latest

The master is mariadb1 and for this one I executed:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'S3cretPw';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

For each of the slaves mariadb2 and mariadb3 I run:

CHANGE MASTER TO MASTER_HOST='mariadb1',  MASTER_USER='replication_user',  MASTER_PASSWORD='S3cretPw',  MASTER_PORT=3306, MASTER_CONNECT_RETRY=10;

CHANGE MASTER TO MASTER_USE_GTID = slave_pos;

I also started the slaves with START SLAVE;

After running the

SHOW SLAVE STATUS \G

I got the next error:

         Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MariaDB server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

How can this happen if I used

MARIADB_SERVER_ID=1
MARIADB_SERVER_ID=2
MARIADB_SERVER_ID=3

for each of my containers?

I am trying to follow:

https://mariadb.com/kb/en/setting-up-replication/

docker

1 Answers

  1. Best Answer

    Why do you think that setting the MARIADB_SERVER_ID environment variable will set the mariadb server id? I cannot find that mentioned in either the mariadb documentation or in the documentation for the container image (or in the list of environment variables). The same is true for most of your other MARIADB_* variables as well.

    You can get things working if you:

    1. Drop the erroneous variables,
    2. Use the documented variables for setting up replication, and
    3. Use command line options to set server configuration options

    That might look something like:

    docker network create camino_nw

docker run -d --name mariadb1 --network=camino_nw \
  -p 127.0.0.1:53301:3306 \
  -v mariadb1-data:/var/lib/mysql \
  -e TZ=America/Denver \
  -e MARIADB_ROOT_PASSWORD=secret \
  -e MARIADB_REPLICATION_USER=replication_user \
  -e MARIADB_REPLICATION_PASSWORD=secret \
  mariadb:10 --server-id=1 --log-bin --log-basename=mariadb1 --binlog-format=mixed

docker run -d --name mariadb2 --network=camino_nw \
  -p 127.0.0.1:53302:3306 \
  -v mariadb2-data:/var/lib/mysql \
  -e TZ=America/Denver \
  -e MARIADB_ROOT_PASSWORD=secret \
  -e MARIADB_MASTER_HOST=mariadb1 \
  -e MARIADB_REPLICATION_USER=replication_user \
  -e MARIADB_REPLICATION_PASSWORD=secret \
  mariadb:10 --server-id=2

docker run -d --name mariadb3 --network=camino_nw \
  -p 127.0.0.1:53303:3306 \
  -v mariadb3-data:/var/lib/mysql \
  -e TZ=America/Denver \
  -e MARIADB_ROOT_PASSWORD=secret \
  -e MARIADB_MASTER_HOST=mariadb1 \
  -e MARIADB_REPLICATION_USER=replication_user \
  -e MARIADB_REPLICATION_PASSWORD=secret \
  mariadb:10 --server-id=3

    This will automatically create the replication user for you (because we set MARIADB_REPLICATION_USER and MARIADB_REPLICATION_PASSWORD) and it will automatically configure the replicas to replicate from mariadb1 (because we also set MARIADB_MASTER_HOST for the replicas).

    This is, incidentally, a fantastic opportunity for using docker compose, because it makes these long docker run command lines much easier to manage. I put all of the above into a compose.yaml that looks like this:

    services:
  mariadb1:
    command:
      - --server-id=1
      - --log-bin
      - --log-basename=mariadb1
      - --binlog-format=mixed
    environment:
      MARIADB_REPLICATION_PASSWORD: ${MARIADB_REPLICATION_PASSWORD}
      MARIADB_REPLICATION_USER: ${MARIADB_REPLICATION_USER}
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      TZ: ${TZ}
    hostname: mariadb1
    image: mariadb:10
    ports:
      - 127.0.0.1:53301:3306
    volumes:
      - mariadb1:/var/lib/mysql
  mariadb2:
    command:
      - --server-id=2
    environment:
      MARIADB_MASTER_HOST: ${MARIADB_MASTER_HOST}
      MARIADB_REPLICATION_PASSWORD: ${MARIADB_REPLICATION_PASSWORD}
      MARIADB_REPLICATION_USER: ${MARIADB_REPLICATION_USER}
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      TZ: ${TZ}
    hostname: mariadb2
    image: mariadb:10
    ports:
      - 127.0.0.1:53302:3306
    volumes:
      - mariadb2:/var/lib/mysql
  mariadb3:
    command:
      - --server-id=3
    environment:
      MARIADB_MASTER_HOST: ${MARIADB_MASTER_HOST}
      MARIADB_REPLICATION_PASSWORD: ${MARIADB_REPLICATION_PASSWORD}
      MARIADB_REPLICATION_USER: ${MARIADB_REPLICATION_USER}
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      TZ: ${TZ}
    hostname: mariadb3
    image: mariadb:10
    ports:
      - 127.0.0.1:53303:3306
    volumes:
      - mariadb3:/var/lib/mysql
volumes:
  mariadb1: null
  mariadb2: null
  mariadb3: null

    I have a .env file that looks like this:

    MARIADB_ROOT_PASSWORD=secret
MARIADB_MASTER_HOST=mariadb1
MARIADB_REPLICATION_USER=replication_user
MARIADB_REPLICATION_PASSWORD=replication
TZ=America/Denver

    This allows me to simply docker compose up and docker compose down the whole environment. You can find the files referenced in this answer in this repository.

    • 3