---
title: "MariaDB 主从复制"
date: 2020-04-13T18:00:00+08:00
lastmod: 2020-04-13T18:00:00+08:00
tags: ["mariadb", "master", "slave", "gtid", "replication", "同步"]
categories: ["database"]
---

## 环境
- 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 开启成功！
```

## 参考
- [MariaDB 复制](https://mariadb.com/kb/en/standard-replication/)
- [MariaDB 多源复制](https://mariadb.com/kb/en/multi-source-replication/)