---
title: "MySQL 主从复制"
date: 2019-10-30T17:33:13+08:00
lastmod: 2020-04-18T23:40:00+08:00
tags: ["mysql", "master", "slave", "gtid", "replication", "同步"]
categories: ["database"]
---

## 环境
- mysql 主服务器，centos7.4，192.168.1.10，端口 10000
- mysql 从服务器，centos7.4，192.168.1.6
- 要复制的数据库有 data_db、conf_db

## 在主服务器上创建用于备份的用户 replicator
    ```sql
    grant replication slave  on *.* to 'replicator'@'192.168.1.6' identified by 'password';
    flush privileges;
    ```

## 在主服务器上修改 my.cnf
    ```
    # 建议与本机ip地址最后一位一致,与其他互备服务器均不一致
    server-id = 10
    read-only = 0
    
    # 开启 binlog
    log-bin = /var/log/mysql-bin/master
    binlog-format = row
    # 关注要复制的数据库，存在跨库问题
    binlog-do-db = data-db
    binlog-do-db = conf-db
    # 忽略的数据库，存在跨库问题
    #binlog-ignore-db =
    #binlog 有效时间
    expire-logs-days =
    # GTID 复制模式
    #gtid-mode = ON
    #enforce-gtid-consistency=true
    # 双主或多主互备时，会用到以下配置
    # 自增主键初始值，与其他互备服务器一致
    #auto-increment-offset =
    # 自增主键等差值，与其他互备服务器均不一致
    #auto-increment-increment =
    ```

## 在主服务器上重启 mysql，获取 master 状态
    ```sql
    -- 如果 mysql 是全新安装，则无须导出数据库初态，直接查看 binlog pos 即可
    -- 锁定要导出的数据库表
    flush tables with read lock;
    ```

## 导出数据库初态
    ```bash
    # 在主服务器的另一个终端中运行
    mysqldump -uroot -p data_db > /tmp/data_db.sql
    mysqldump -uroot -p conf_db > /tmp/conf_db.sql
    # 复制到从服务器上
    scp /tmp/data_db.sql /tmp/conf_db.sql 192.168.1.6:/tmp/
    ```

## 查看 binary 日志位置
    ```sql
    show master status\G
    -- 记住输出里的如下类似两行（不记录也可以，这两个信息已经写入了导出的sql文件中）
    -- File: mysql-bin.000001
    -- Position: 137103822
    -- 解锁数据库表
    unlock tables;
    ```

## 在从服务器上编辑 my.cnf
    ```
    # 建议与本机ip地址最后一位一致,与其他互备服务器均不一致
    server-id = 6
    read-only = 1
    
    # 如果该 slave 中也运行了 master，或者使用了 gtid 模式复制，则开启 binlog
    #log-bin = mysql-bin
    #binlog-format = row
    # 把 slave 操作也计入 binlog，用于链式同步
    #log-slave-updates = ON
    # 指定要复制的数据库，存在跨库问题
    #replicate-do-db = data_db
    #replicate-do-db = conf_db
    # 指定要复制的数据表，无跨库问题
    replicate-do-table = db1.t1
    replicate-wild-do-table = db1.%
    # 忽略的数据库，存在跨库问题
    #replicate-ignore-db =
    # 忽略的数据表，无跨库问题
    #replicate-ignore-table = db1.t1
    #replicate-wild-ignore-table = db1.%
    # 中继日志
    relay-log = /var/lib/mysql-bin/slave
    # 多线程复制
    slave-parallel-type = logical-clock
    slave-parallel-workers = 4
    # GTID 模式
    #gtid-mode = ON
    #enforce-gtid-consistency=true
    # 双主或多主互备时，会用到以下配置
    # 自增主键初始值，与其他互备服务器一致
    #auto-increment-offset =
    # 自增主键等差值，与其他互备服务器均不一致
    #auto-increment-increment =
    ```

## 在从服务器上重启 mysql，导入初态
    ```sql
    -- 创建要导入的数据库
    create database data_db default charset utf8mb4;
    create database conf_db default charset utf8mb4;
    ```

## 导入数据库
    ```bash
    msyql -uroot -p data_db < /tmp/data_db.sql
    mysql -uroot -p conf_db < /tmp/conf_db.sql
    ```

## 开启同步
    ```sql
    -- 基于 binlog 文件位置复制
    change master to master_host = '192.168.1.10',
                            master_port = 3306,
                            master_user = 'replicator',
                            master_password = 'password',
                            master_log_file = 'mysql-bin.000001',
                            master_log_pos = 137103822;
    flush privileges;
    -- 基于 gtid 复制
    change master to master_host = '192.168.1.10',
                            master_port = 3306,
                            master_user = 'replicator',
                            master_password = 'password',
                            master_auto_position = 1;
    ```

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

## MySQL8 gtid 互为主从配置
    ```
    [mysqld]
    # ---- 固定配置 ----
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    pid-file = /var/lib/mysql/mysql.pid
    log-timestamps = SYSTEM
    log-error = /var/log/mysql/error.log
    slow-query-log = TRUE
    slow-query-log-file = /var/log/mysql/slow.log
    default-authentication-plugin = mysql_native_password
    
    # ---- 动态配置 ----
    mysqlx = OFF
    character-set-server = utf8mb4
    default-storage-engine = innodb
    lower-case-table-names = 1
    #skip-name-resolve = 1
    #max-user-connections = 600
    #innodb-buffer-pool-size = 8G
    #innodb-buffer-pool-instances = 8
    
    # master
    # 确认不同节点该 id 唯一
    server-id = 1
    log-bin = /var/lib/mysql-bin/master
    binlog-format = ROW
    #binlog-do-db = db1
    binlog-expire-logs-seconds = 172800
    gtid-mode = ON
    enforce-gtid-consistency = TRUE
    
    # slave
    replicate-wild-ignore-table = information_schema.%
    replicate-wild-ignore-table = mysql.%
    replicate-wild-ignore-table = performance_schema.%
    replicate-wild-ignore-table = sys.%
    slave-parallel-workers = 2
    log-slave-updates = FALSE
    relay-log = /var/lib/mysql-bin/slave
    relay-log-recovery = TRUE
    #read-only = ON
    ```

## MySQL8 gtid 多源复制从库配置
- 修改 my.cnf
    ```
    [mysqld]
    # ---- 固定配置 ----
    datadir = /data/mysql
    socket = /var/lib/mysql/mysql.sock
    pid-file = /var/lib/mysql/mysql.pid
    log-timestamps = SYSTEM
    log-error = /var/log/mysql/error.log
    slow-query-log-file = /var/log/mysql/slow.log
    slow-query-log = TRUE
    default-authentication-plugin = mysql_native_password
    
    # ---- 动态配置 ----
    mysqlx = OFF
    character-set-server = utf8mb4
    default-storage-engine = innodb
    lower-case-table-names = 1
    #skip-name-resolve = 1
    #max-user-connections = 600
    #innodb-buffer-pool-size = 8G
    #innodb-buffer-pool-instances = 8

    # master
    server-id = 39
    log-bin = /var/lib/mysql/master
    binlog-format = ROW
    #binlog-do-db = db1
    gtid-mode = ON
    enforce-gtid-consistency = TRUE
    binlog-expire-logs-seconds = 172800
    
    # slave
    #replicate-wild-do-table = db1.%
    replicate-wild-ignore-table = information_schema.%
    replicate-wild-ignore-table = mysql.%
    replicate-wild-ignore-table = performance_schema.%
    replicate-wild-ignore-table = sys.%
    slave-parallel-workers = 2
    log-slave-updates = FALSE
    relay-log = /var/lib/mysql/slave
    relay-log-recovery = TRUE
    super_read_only = ON
    master_info_repository  = table
    relay_log_info_repository = table
    ```

- 导出主库数据
    ```bash
    mysqldump -uroot -h<主库1> -p --single-transaction --set-gtid-purged=on --databases db1 > 1_db1.sql
    mysqldump -uroot -h<主库2> -p --single-transaction --set-gtid-purged=on --databases db2 > 2_db2.sql
    ```

- 在导出的文件(1_db1.sql,2_db2.sql)中找到 "SET @@GLOBAL.gtid_purged ..." 语句，记录下来，并在该文件中删除
- 导入 1_db1.sql 和 2_db2.sql
    ```bash
    mysql -uroot -p < 1_db1.sql
    mysql -uroot -p < 2_db2.sql
    ```

- 合并这两个 "SET @@GLOBAL.gtid_purged ..." 语句(gtid set 做并集)，导入 gtid set 并集
    ```sql
    set @@global.gtid_purged = '<gtid set 并集>'
    ```

- 加入两个主库的同步配置
    ```sql
    change master to master_host='<主库1>',
        master_port=3306,
        master_user='<主库上的 replication 账户>',
        master_password='<主库上的 replication 账户的密码>',
        master_auto_position=1 for channel '<master_1>';
    change master to master_host='<主库2>',
        master_port=3306,
        master_user='<主库上的 replication 账户>',
        master_password='<主库上的 replication 账户的密码>',
        master_auto_position=1 for channel '<master_2>';
    ```

- 启动从库
    ```sql
    start slave for channel '<master_1>';
    start slave for channel '<master_2>';
    ```

- 查看从库
    ```sql
    show slave status for channel '<master_1>'\G
    show slave status for channel '<master_2>'\G
    ```

- 停止从库
    ```sql
    -- 停止全部 slave
    stop slave;
    -- 停止指定 slave
    stop slave for channel '...';
    ```

- 重置从库
    ```sql
    -- 重置全部 slave
    reset slave;
    -- 重置指定 slave
    reset slave for channel '...';
    ```

- 监控表: performance_schema.replication_connection_status

