Setup MariaDB Master/Slave Replication for Docker MariaDB mariadbdocker

Setup MariaDB master container

~/container-data/mariadb: MariaDB data files mount folder
~/container-data/mariadb-log: MariaDB log files mount folder
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

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 -P 3306 -u root -p --all-databases --single-transaction --quick > ~/mariadb-backup/backup-111-$(date +%FT%T).sql

Setup MariaDB slave containers

~/container-data/mariadb-edge01: MariaDB data files mount folder
~/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 -P 3307 -u root -p < ~/mariadb-backup/backup-111-.sql

Configure slave DB
Execute into slave db container

docker exec -it mariadb-edge01 /bin/bash

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='', 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

【Setup MariaDB Master/Slave Replication for Docker MariaDB】master