dbus.io

Mariadb replication using containers

Posted on Jan 1, 2023

Prepare data persistent directories on Host machine.

Execute following command on host bash shell

sudo mkdir -p /data/docker-vol/mariadb/{master,slave}
sudo chown -R 999:999 /data/docker-vol/mariadb

Run docker compose

docker-compose.yml

version: '3.8'

services:

  mariadb-master:
    container_name: mariadb-master
    image: mariadb:10.6
    restart: always
    environment:
      - MARIADB_USER=calvin
      - MARIADB_PASSWORD=password_of_calvin
      - MARIADB_DATABASE=test_db
      - MARIADB_ROOT_PASSWORD_HASH=*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      #- MARIADB_AUTO_UPGRADE: TRUE
    volumes:
      - /data/docker-vol/mariadb/master:/var/lib/mysql
      - /var/run/mysqld:/var/run/mysqld
    entrypoint: 
      - docker-entrypoint.sh
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      - --skip-character-set-client-handshake
      - --init-connect=SET NAMES utf8mb4;SET collation_connection = utf8mb4_unicode_ci;
      - --log-bin=ON
      - --log-basename=my-mariadb
      - --server-id=1
      - --gtid-domain-id=1
      - --max-allowed-packet=256M
      - --net-buffer-length=1048576
      - --binlog-row-metadata=MINIMAL
      - --binlog-format=MIXED
      - --expire-logs-days=10
      - --max-binlog-size=1024M
      - --max-connections=1048
      - --innodb-lock_wait-timeout=100
      - --innodb-log_buffer_size=128M
      - --innodb-buffer_pool_size=8192M
      - --performance-schema=1
      - --slow-query-log=1
      - --long-query-time=10
      - --slow-query-log-file=slow-query.log
    ports:
      - 3306:3306
    healthcheck:
      test: "/usr/bin/mysql --user=$$MARIADB_USER --password=$$MARIADB_PASSWORD --execute \"SHOW DATABASES;\""
      interval: 5s
      timeout: 20s
      retries: 10

  mariadb-slave:
    container_name: mariadb-slave
    image: mariadb:10.6
    restart: always
    environment:
      - MARIADB_USER=calvin
      - MARIADB_PASSWORD=password_of_calvin
      - MARIADB_DATABASE=test_db
      - MARIADB_ROOT_PASSWORD_HASH=*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      # - MARIADB_AUTO_UPGRADE=TRUE
    entrypoint: 
      - docker-entrypoint.sh
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      - --skip-character-set-client-handshake
      - --init-connect=SET NAMES utf8mb4;SET collation_connection = utf8mb4_unicode_ci;
      - --log-bin=ON
      - --log-basename=my-mariadb
      - --server_id=2
      - --gtid-domain-id=2
      - --max_allowed_packet=256M
      - --net_buffer_length=1048576
      - --binlog_row_metadata=MINIMAL
      - --binlog_format=MIXED
      - --expire_logs_days=10
      - --max_binlog_size=1024M
      - --max_connections=1048
      - --innodb_lock_wait_timeout=100
      - --innodb_log_buffer_size=128M
      - --innodb_buffer_pool_size=8192M
      - --performance_schema=1
      - --slow_query_log=1
      - --long-query-time=10
      - --slow_query_log_file=slow-query.log
      - --replicate-ignore-db=mysql
      - --replicate-ignore-db=test_db
    volumes:
      - /data/docker-vol/mariadb/slave:/var/lib/mysql
      #- /var/run/mysqld:/var/run/mysqld
    ports:
      - 13306:3306
    depends_on:
      - mariadb-master
    healthcheck:
      test: "/usr/bin/mysql --user=$$MARIADB_USER --password=$$MARIADB_PASSWORD --execute \"SHOW DATABASES;\""
      interval: 5s
      timeout: 20s
      retries: 10

The MARIADB_ROOT_PASSWORD_HASH can be generated by

select PASSWORD('123456')

docker compose up

Setup mariadb replication

Create user for replication in the master mariadb shell.

CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicator_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

In the slave mariadb shell

CHANGE MASTER TO master_host="mariadb-master", master_port=3306, master_user="replicator", MASTER_PASSWORD="replicator_password", master_use_gtid=current_pos, MASTER_CONNECT_RETRY=10;
START SLAVE;
SHOW SLAVE STATUS\G;

Misc

We created a user to monitor docker instance health.

CREATE DATABSE test_db;
CREATE USER 'calvin'@'%' IDENTIFIED BY 'password_of_calvin';
GRANT SELECT, SHOW VIEW ON test_db.* TO 'calvin'@'%';
FLUSH PRIVILEGES;

References