Setup MariaDB Master/Slave Replication for Docker MariaDB

Setup MariaDB master container

1. ~/container-data/mariadb: MariaDB data files mount folder
2. ~/container-data/mariadb-log: MariaDB log files mount folder
3. U need to specific a log-basename and server-id for master

docker run --name medical-analysis-mariadb -v ~/container-data/mariadb:/var/lib/mysql -v ~/container-data/mariadb-log:/var/log/mysql -e MYSQL_USER=user -e MYSQL_ROOT_PASSWORD=root -e MYSQL_PASSWORD=123456 -p 3306:3306 -d mariadb:10.5.3 --log-bin --binlog-format=MIXED --log-basename=111 --server-id=111

Retrieve Binlog position

Execute into master db container

docker exec -it medical-analysis-mariadb /bin/bash

Login to mariadb

mysql -u root -p

Closes all open tables and locks all tables for all databases with a global read lock

FLUSH TABLES WITH READ LOCK;

Check the master server status

SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |      450 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

450 is the position for Binlog file

Backup master DB

Backup master db

mysqldump -h <ip-address> -P 3306 -u root -p --all-databases --single-transaction --quick > ~/mariadb-backup/backup-111-$(date +%FT%T).sql

Setup MariaDB slave containers

1. ~/container-data/mariadb-edge01: MariaDB data files mount folder
2. ~/container-data/mariadb-log-edge01: MariaDB log files mount folder

docker run --name mariadb-edge01 -v ~/container-data/mariadb-edge01:/var/lib/mysql -v ~/container-data/mariadb-log-edge01:/var/log/mysql -e MYSQL_USER=user -e MYSQL_ROOT_PASSWORD=root -e MYSQL_PASSWORD=123456 -p 3307:3306 -d mariadb:10.5.3 --log-bin=/var/lib/mysql/mysql-bin --log_bin_index=/var/lib/mysql/mysql-bin --binlog-format=MIXED --server-id=1111 --relay_log=/var/lib/mysql/relay-bin relay_log_index=/var/lib/mysql/relay-bin.index relay_log_info_file=/var/lib/mysql/relay-bin.info

Restore backup to slave db

mysql -h <ip-address> -P 3307 -u root -p < ~/mariadb-backup/backup-111-<date>.sql

Configure slave DB

Execute into slave db container

docker exec -it mariadb-edge01 /bin/bash

Login to mariadb

mysql -u root -p

Set privileges of user for restored database

GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'%';
FLUSH PRIVILEGES;

Stop and Reset the slave

STOP SLAVE;
RESET SLAVE;

Configure the slave to use the master
set MASTER_LOG_POS to Binlog position

CHANGE MASTER TO MASTER_HOST='<ip-address>', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='root', MASTER_CONNECT_RETRY=3600, MASTER_LOG_FILE='111-bin.000001', MASTER_LOG_POS=450;

Start the slave

START SLAVE;

Check slave status to make sure eveything works then unlock the master db tables**

slave

SHOW SLAVE STATUS\G;

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

master

UNLOCK TABLES;