---
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/)

