Mariadb10.7 Master Replication

2023-10-02 22:48:52

Setup master #1

master1.cnf
[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

docker run -d --name=db1 -h db1 -p 13306:3306 -e MYSQL_ROOT_PASSWORD=root1 -e MYSQL_ROOT_HOST='%' -v /opt/conf1:/etc/mysql/conf.d -v /opt/data1:/var/lib/mysql mariadb:10.7.1

Create user for replicator

mysql -h 127.0.0.1 -P 13306 -uroot -proot1

CREATE USER 'copy2'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'copy2'@'%';

Getting the Master’s Binary Log Co-ordinates

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |      639 |              |                  |
+--------------------+----------+--------------+------------------+

Setup Replicator #2

slave2.cnf
[mariadb]
server-id=2
read_only=1

docker run -d --name=db2 -h db2 -p 23306:3306 -e MYSQL_ROOT_PASSWORD=root2 -e MYSQL_ROOT_HOST='%' -v /opt/conf2:/etc/mysql/conf.d -v /opt/data2:/var/lib/mysql mariadb:10.7.1

Start replicator #2

mysql -h 127.0.0.1 -P 23306 -uroot -proot2

CHANGE MASTER TO
  MASTER_HOST='192.168.0.149',
  MASTER_USER='copy2',
  MASTER_PASSWORD='123456',
  MASTER_PORT=13306,
  MASTER_LOG_FILE='master1-bin.000001',
  MASTER_LOG_POS=639,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=slave_pos;

If you are starting a slave against a fresh master that was configured for replication from the start, then you don’t have to specify MASTER_LOG_FILE and MASTER_LOG_POS.

START SLAVE;
SHOW SLAVE STATUS \G;
show variables like '%read_only%';

If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Setup Replicator #3

slave3.cnf
[mariadb]
server-id=3
read_only=1

docker run -d --name=db3 -h db3 -p 33306:3306 -e MYSQL_ROOT_PASSWORD=root3 -e MYSQL_ROOT_HOST='%' -v /opt/conf3:/etc/mysql/conf.d -v /opt/data3:/var/lib/mysql mariadb:10.7.1

Start Replicator #3

mysql -h 127.0.0.1 -P 33306 -uroot -proot3

CHANGE MASTER TO
  MASTER_HOST='192.168.0.149',
  MASTER_USER='copy2',
  MASTER_PASSWORD='123456',
  MASTER_PORT=13306,
  MASTER_LOG_FILE='master1-bin.000001',
  MASTER_LOG_POS=1240,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=slave_pos;

If you are starting a slave against a fresh master that was configured for replication from the start, then you don’t have to specify MASTER_LOG_FILE and MASTER_LOG_POS.

START SLAVE;
SHOW SLAVE STATUS \G;
show variables like '%read_only%';

If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

码农公寓