Mariadb でマスター・スレーブのシンプルなレプリケーションを設定する。

Ubuntu にMariaDBをインストール

マスター側にユーザー作成。

grant replication slave on *.* to 'user_replication'@'localhost' identified by 'pass_replication';
grant replication slave on *.* to user_replication@'127.0.0.1' identified by 'pass_replication';
grant replication slave on *.* to user_replication@'192.168.0.0/255.255.0.0' identified by 'pass_replication';

50-server.confを編集。

vi /etc/mysql/mariadb.conf.d/50-server.conf
# master
[mysqld]
log-bin=mysql-bin
server-id=100
log-slave-updates

# slave
[mysqld]
log-bin=mysql-bin
server-id=105
log-slave-updates

# bind-address = 0.0.0.0            <= LXC container Freeze!!!!!
# bind-address = 127.0.0.1      <= Comment out = 0.0.0.0

データの同期

mariabackup

# Master
mariabackup --backup -u root --target-dir ~/backup
mariabackup --prepare --target-dir ~/backup

rsync -e "ssh" -avz ~/backup/* root@192.168.24.6:~/backup

# Slave
systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir ~/backup
chown -R mysql:mysql /var/lib/mysql/*
systemctl start mariadb

マスター側にてGTID 取得

SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 955
Binlog_Do_DB:
Binlog_Ignore_DB:

select binlog_gtid_pos("mysql-bin.000002", 955);
+------------------------------------------+
| binlog_gtid_pos("mysql-bin.000002", 955) |
+------------------------------------------+
| 0-100-4                                  |
+------------------------------------------+

スレーブにてGTID のセット

set global gtid_slave_pos = "0-100-4";

change master to master_host="192.168.23.7",
master_port=3306,
master_user='user_replication',
master_password='pass_replication',
master_use_gtid=slave_pos;

start slave;
show slave status\G;

うまくできたらOK