Introduction

MariaDB is an open-source relational database management system (RDBMS) that is a fork of MySQL. It was created by the original developers of MySQL after concerns over the acquisition of MySQL by Oracle Corporation.

MariaDB replication

MariaDB replication is a process that allows you to create and maintain a copy of a database on another server, which is known as a replica. Replication in MariaDB can be useful for various purposes, including load balancing, high availability, and backup.

Here are the key components and concepts related to MariaDB replication:

Master Server: The master server is the primary server that contains the original data. Changes made to the master server are replicated to one or more slave servers.
Slave Server: The slave server is a copy of the master server. It replicates changes from the master, making it a duplicate of the master’s data. Multiple slave servers can replicate from the same master.
Binary Log: The binary log is a file on the master server that records changes to the database. It contains a record of all modifications to the database, such as inserts, updates, and deletes.
Replication Events: Changes made to the master server are captured in the binary log as replication events. These events are then transmitted to the slave server(s), where they are applied to replicate the changes.
Replication Channels: MariaDB supports multiple replication channels, allowing for the replication of different databases or sets of databases.

Docker

Docker is a platform for developing, shipping, and running applications in containers. Containers are lightweight, portable, and self-sufficient units that can run applications and their dependencies. Docker provides a way to package applications with their dependencies into a standardized unit, known as a container, that can run consistently on any environment that supports Docker.

Setup MariaDB replication on docker.

Requirement

Docker and Docker compose

Preparation

Login to the master server and slave server, and create the same docker workspace folder called: dockerdata, and sub directories as the following:

mkdir dockerdata
cd dockerdata
mkdir mariadb
cd mariadb
mkdir data
mkdir log
mkdir run
mkdir snapshots

Configure the Master Server

Create a passwords.sh file on /dockerdata/mariadb directory, and put the credential to it.

ROOT_PASSWORD="strongsecretpassword"  # database admin
REPL_PASSWORD="strongsecretpassword"  # replication user

Create a docker-compose.yaml file on the same directory, and put the following:

version: "3.3"

services:
  mariadb-master:
    image: yidigun/mariadb-replication:10.7
    restart: unless-stopped
    hostname: ${HOSTNAME}
    ports:
      - "3306:3306/tcp"
    environment:
      - TZ=Asia/Jakarta
      - REPL_MODE=master
      - REPL_SERVER_ID=1
      - REPL_USERNAME=repl
    volumes:
      - /dockerdata/mariadb/data:/var/lib/mysql
      - /dockerdata/mariadb/log:/var/log/mysql
      - /dockerdata/mariadb/run:/run/mysqld
      - /dockerdata/mariadb/snapshots:/snapshots
    secrets:
      - passwords

secrets:
  passwords:
    file: /dockerdata/mariadb/passwords.sh

run docker compose

docker compose up -d

Ensure that the new mariadb container is succesfully created and accesible.
Import your database to it, and ensure that all imported databases are accesible and in a good condition.
Create the snapshot of all databases, and copy to the slave server.

docker-compose exec mariadb-master backup-master --no-lock

(cd /dockerdata/mariadb/snapshots/snapshot-`date +%Y%m%d`; tar cf - *) | \
          ssh slave-dbserver ' \
              sudo mkdir -p /dockerdata/mariadb/data; \
              (cd /dockerdata/mariadb/data; sudo tar xf -) && \
              sudo chown -R 999:999 /dockerdata/mariadb/data'

Configure the Slave Server

Create a passwords.sh

REPL_PASSWORD="strongsecretpassword"  # replication user

Create a docker-compose.yaml

version: "3.3"

services:
  mariadb-slave:
    image: yidigun/mariadb-replication:10.7
    restart: unless-stopped
    hostname: ${HOSTNAME}
    ports:
      - "3306:3306/tcp"
    environment:
      - TZ=Asia/Jakarta
      - REPL_MODE=slave
      - REPL_SERVER_ID=2
      - REPL_USERNAME=repl
      - REPL_MASTER_HOST=server1.amanulloh.com
      - REPL_MASTER_PORT=3306
    volumes:
      - /dockerdata/mariadb/data:/var/lib/mysql
      - /dockerdata/mariadb/log:/var/log/mysql
      - /dockerdata/mariadb/run:/run/mysqld
    secrets:
      - passwords

secrets:
  passwords:
    file: /srv/dockerdata/mariadb/passwords.sh

Run docker compose

docker compose up -d

Ensure that the new mariadb-slave container is succesfully created, and accesible.
You will see all databases will be the same between master and slave.

Start Slave Database.

docker compose exec mariadb-slave start-slave

Check the Replication Status

On Master server

docker exec mariadb-master show-status

Result:

############### Server Status ###############

[mysqladmin] Uptime: 826  Threads: 4  Questions: 40  Slow queries: 4  Opens: 76  Open tables: 70  Queries per second avg: 0.048

############### Replication Status ###############

Replication Mode: master

[mysql] *************************** 1. row ***************************
[mysql]             File: mysqld-bin.000005
[mysql]         Position: 343
[mysql]     Binlog_Do_DB:
[mysql] Binlog_Ignore_DB:

On Slave Server

docker exec mariadb-slave show-status

Result:

############### Replication Status ###############

Replication Mode: slave

[mysql] *************************** 1. row ***************************
[mysql]                 Slave_IO_State: Waiting for master to send event
[mysql]                    Master_Host: lsws1.amanulloh.com
[mysql]                    Master_User: repl
[mysql]                    Master_Port: 3306
[mysql]                  Connect_Retry: 60
[mysql]                Master_Log_File: mysqld-bin.000005
[mysql]            Read_Master_Log_Pos: 343
[mysql]                 Relay_Log_File: mysqld-relay-bin.000003
[mysql]                  Relay_Log_Pos: 643
[mysql]          Relay_Master_Log_File: mysqld-bin.000005
[mysql]               Slave_IO_Running: Yes
[mysql]              Slave_SQL_Running: Yes
[mysql]                Replicate_Do_DB:
[mysql]            Replicate_Ignore_DB:
[mysql]             Replicate_Do_Table:
[mysql]         Replicate_Ignore_Table:
[mysql]        Replicate_Wild_Do_Table:
[mysql]    Replicate_Wild_Ignore_Table:
[mysql]                     Last_Errno: 0
[mysql]                     Last_Error:
[mysql]                   Skip_Counter: 0
[mysql]            Exec_Master_Log_Pos: 343
[mysql]                Relay_Log_Space: 1253
[mysql]                Until_Condition: None
[mysql]                 Until_Log_File:
[mysql]                  Until_Log_Pos: 0
[mysql]             Master_SSL_Allowed: No
[mysql]             Master_SSL_CA_File:
[mysql]             Master_SSL_CA_Path:
[mysql]                Master_SSL_Cert:
[mysql]              Master_SSL_Cipher:
[mysql]                 Master_SSL_Key:
[mysql]          Seconds_Behind_Master: 0
[mysql]  Master_SSL_Verify_Server_Cert: No
[mysql]                  Last_IO_Errno: 0
[mysql]                  Last_IO_Error:
[mysql]                 Last_SQL_Errno: 0
[mysql]                 Last_SQL_Error:
[mysql]    Replicate_Ignore_Server_Ids:
[mysql]               Master_Server_Id: 1
[mysql]                 Master_SSL_Crl:
[mysql]             Master_SSL_Crlpath:
[mysql]                     Using_Gtid: No
[mysql]                    Gtid_IO_Pos:
[mysql]        Replicate_Do_Domain_Ids:
[mysql]    Replicate_Ignore_Domain_Ids:
[mysql]                  Parallel_Mode: optimistic
[mysql]                      SQL_Delay: 0
[mysql]            SQL_Remaining_Delay: NULL
[mysql]        Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[mysql]               Slave_DDL_Groups: 0
[mysql] Slave_Non_Transactional_Groups: 0
[mysql]     Slave_Transactional_Groups: 0

You can also login to the slave Mariadb server and run the following to monitor the mariadb-slave status:

docker exec -it mariadb-slave bash
mysql u user -p # this require the credential.
SHOW SLAVE STATUS\G;

Testing

To test the replication, you can either create a new database on the master server, or create any changes on the existing database on the master server. If no issue found, the new database or any changes on the master server will be immediatelly synced to the slave server.

Introduction#

MariaDB replication#

Docker#

Setup MariaDB replication on docker.#

Requirement#

Preparation#

Configure the Master Server#

Configure the Slave Server#

Start Slave Database.#

Check the Replication Status#

On Master server#

On Slave Server#

Testing#