环境

  • mariadb 主服务器,centos7.8,192.168.1.141
  • mariadb 主服务器,centos7.8,192.168.1.142
  • mariadb 从服务器,centos7.8,192.168.1.143
  • 忽略系统数据库: information_schema, mysql, performance_schema

在两个主服务器上创建用于备份的用户 replicator

```sql
grant replication slave on *.* to 'replicator'@'%' identified by 'password';
flush privileges;
```

在两个主服务器上修改 my.cnf

```
# 唯一 ID
# 192.168.1.141 配置 
server-id = 141
# 192.168.1.142 配置 
server-id = 142
read-only = 0

# binlog
# MariaDB 默认已开启 GTID
log-bin          = /var/lib/mysql-bin/master
binlog-format    = row
sync_binlog      = 1
expire-logs-days = 3
gtid-strict-mode = ON

# relay log
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = init_sql.%
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = performance_schema.%
relay-log                   = /var/lib/mysql-bin/slave
relay-log-recovery          = TRUE
# 复制线程数不超过 cpu 核数
slave-parallel-threads      = 4

# 双主或多主互备时,可能会用到以下配置
# 自增主键初始值,与其他互备服务器一致
#auto-increment-offset =
# 自增主键等差值,与其他互备服务器均不一致
#auto-increment-increment =
# 该环境中,双主配合 keepalived 实现高可用,无需配置自增
```

开启互主同步

```sql
-- 在 192.168.1.141 上启动 mariadb,配置同步 192.168.1.142:
change master to master_host = '192.168.1.142',
                        master_port = 3306,
                        master_user = 'replicator',
                        master_password = 'password',
                        master_use_gtid = slave_pos;

-- 在 192.168.1.142 上启动 mariadb,配置同步 192.168.1.141:
change master to master_host = '192.168.1.141',
                        master_port = 3306,
                        master_user = 'replicator',
                        master_password = 'password',
                        master_use_gtid = slave_pos;
```

在两个主服务器上启动 slave ,查看 slave 状态

```sql
start slave;
-- 查看 slave 状态
show slave status\G
-- 如果看到
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 则表示 slave 开启成功!
```

在从服务器上编辑 my.cnf

```
# 唯一 ID
server-id = 143
# 从服务器只做查询,无增删改
read-only = 1

# 忽略的数据表
#replicate-ignore-table     = db1.t1
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = init_sql.%
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = performance_schema.%
relay-log                   = /var/log/mysql-bin/slave
slave-parallel-threads      = 4
```

开启同步

```sql
change master 'db141' to master_host = '192.168.1.141',
                         master_port = 3306,
                         master_user = 'replicator',
                         master_password = 'password',
                         master_use_gtid = slave_pos;

change master 'db142' to master_host = '192.168.1.142',
                         master_port = 3306,
                         master_user = 'replicator',
                         master_password = 'password',
                         master_use_gtid = slave_pos;
```

在从服务器上启动 slave ,查看 slave 状态

```sql
start all slaves;
-- 在从服务器上查看 slave 状态
show all slaves status\G
-- 如果看到
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 则表示 slave 开启成功!
```

参考