“这是我参与8月更文挑战的第10天，活动详情查看：8月更文挑战”

1. 主从复制学习步骤

搭建主从复制
主从原理熟悉
主从的故障处理
主从延时
主从的特殊架构的配置使用
主从架构的演变

2. 主从复制介绍

主从复制基于 binlog 来实现的
主库发生新的操作，都会记录 binlog
从库取得主库的 binlog 进行回放
主从复制的过程是异步的

3. 主从复制的前提（搭建主从复制）

2 个或以上的数据库实例
主库需要开启二进制日志
server_id 要不同，区分不同的节点
主库需要建立专用的复制用户（replication slave）
从库应该通过备份主库，恢复的方法进行补数据
人为告诉从库一些复制信息（ip、port、user、password、二进制日志起点）
从库应该开启专门的复制线程

4. 主从复制搭建过程（生产）

4.1 准备多实例

4.2 检查配置文件

查看二进制文件是否开启
查看 server_id 是否不同

# cat /data/3307/my.cnf
[mysqld]
basedir=/home/service/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
复制代码

# cat /data/3308/my.cnf
[mysqld]
basedir=/home/service/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
复制代码

4.3 主库创建复制用户

# mysql -S /data/3307/mysql.sock
复制代码

mysql> create user repl@'172.21.0.3';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repl@'172.21.0.3';
Query OK, 0 rows affected (0.01 sec)
复制代码

4.4 提前备份主库数据

主库：

# mysqldump --master-data=2 -A -S /data/3307/mysql.sock --single-transaction -R -E --triggers > /tmp/3307_full.sql
复制代码

从库：

# mysql -S /data/3308/mysql.sock
复制代码

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/3307_full.sql

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
复制代码

4.5 告诉从库信息

mysql> help change master to;

CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
复制代码

修改成如下信息：

CHANGE MASTER TO
  MASTER_HOST='172.21.0.3',
  MASTER_USER='repl',
  MASTER_PASSWORD='',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=3596,
  MASTER_CONNECT_RETRY=10;
复制代码

MASTER_LOG_FILE 文件查看备份文件 vim /tmp/3307_full.sql，找到下面这行信息，替换上面的 MASTER_LOG_FILE 参数和 MASTER_LOG_POS 参数。

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3596;
复制代码

执行修改完的配置：

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='172.21.0.3',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='',
    ->   MASTER_PORT=3307,
    ->   MASTER_LOG_FILE='mysql-bin.000001',
    ->   MASTER_LOG_POS=3596,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
复制代码

4.6 开启复制线程（IO，SQL）

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
复制代码

4.7 检查主从复制状态

方法一：

mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
复制代码

方法二：

主库：

mysql> create database account;
Query OK, 1 row affected (0.01 sec)
复制代码

从库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| account            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
复制代码

5. 主从复制原理

5.1 主从复制中涉及的文件

主库：
- binlog
从库：
- relaylog：中继日志
- master.info：主库信息文件
- relaylog.info：relaylog 应用的信息

5.2 主从复制中涉及的线程

主库：
- Binlog_Dump Thread：DUMP_T
从库：
- SLAVE_IO_THREAD：IO_T
- SLAVE_SQL_THREAD：SQL_T

5.3 主从复制工作（过程）原理

从库执行 change master to 命令（主库的连接信息 + 复制的起点）
从库会将以上信息，记录到 master.info 文件中
从库执行 start slave 命令，立即开启 IO_T 和 SQL_T
从库 IO_T，读取 master.info 文件中的信息，获取到 IP、PORT、User、Password、binlog 的位置信息
从库 IO_T 请求连接主库，主库专门提供一个 DUMP_T，负责和 IO_T 交互
IO_T 根据 binlog 的位置信息（mysql-bin.000001），请求主库的新的 binlog
主库通过 DUMP_T 将最新的 binlog 通过网络传送给从库的 IO_T
IO_T 接收到新的 binlog 日志，存储在 TCP/IP 缓冲中，立即返回 ACK 给主库，并更新 master.info。
IO_T 将 TCP/IP 缓冲中的数据，转存到磁盘 relaylog 中。
SQL_T 读取 relaylog.info 中的信息，获取到上次已经应用过的 relaylog 的位置信息
SQL_T 会按照上次的位置点回放到最新的 relaylog，并再次更新 relaylog.info 信息
从库会自动 purge 应用过 relay 进行自动清理。

补充说明

一旦主从复制构建成功，主库当中发生了新的变化，都会通过 DUMP_T 发送信号给 IO_T，增强了主从复制的实时性。

主从复制关键点

主库线程信息：

mysql> show processlist;
+----+------+------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host             | db   | Command     | Time  | State                                                         | Info             |
+----+------+------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| 44 | repl | 172.21.0.3:41020 | NULL | Binlog Dump | 18197 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 45 | root | localhost        | NULL | Query       |     0 | starting                                                      | show processlist |
+----+------+------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
复制代码

从库线程信息：

mysql> show processlist;
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time  | State                                                  | Info             |
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
| 34 | root        | localhost | mysql | Query   |     0 | starting                                               | show processlist |
| 35 | system user |           | NULL  | Connect | 18202 | Waiting for master to send event                       | NULL             |
| 36 | system user |           | NULL  | Connect | 18102 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+-------+---------+-------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
复制代码

5.4 主从复制监控

mysql> show slave status\G
复制代码

主库信息

               主库信息：
                  Master_Host: 172.21.0.3
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3764
复制代码

重点关注 Master_Log_File 信息和 Read_Master_Log_Pos 信息。我们希望的是和主库的信息保持一致。主库的信息我们可以通过 show master status; 命令查看。

从库 relaylog 应用信息

               Relay_Log_File: VM-0-3-centos-relay-bin.000002
                Relay_Log_Pos: 650
复制代码

从库线程运行状态

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
复制代码

过滤复制有关的信息

              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
复制代码

从库延迟的时间（秒）

        Seconds_Behind_Master: 0
复制代码

延时从库（防止误操作操作，比如延时 1 小时，误操作 drop 操作，可以有反应时间）

                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
复制代码

GTID 复制有关的状态信息

           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
复制代码

5.5 主从复制故障

主要关注从库线程：

5.5.1 IO 线程故障

IO 线程主要负责如下工作：

连接主库（connecting）
1. 可能出现的问题：
  1. 网络
  2. 连接信息错误或变更了
  3. 防火墙
  4. 连接数上限
2. 排查方案：
  1. 使用复制用户手工登录
3. 解决方案：
  1. stop slave;
  2. reset slave all;
  3. change master to;
  4. start slave;
请求 binlog
1. 可能出现的问题：
  1. binlog 没开
  2. binlog 损坏，不存在
  3. reset master;（主库日志可能太多了，想清除一下）
2. 解决方案：
  1. stop slave;
  2. reset slave all;
  3. chanege master to;
  4. start slave;
3. 存储 binlog

5.5.2 SQL 线程故障

relay-log 损坏
回放 relay-log
研究一条 SQL 语句为什么执行失败？

insert delete update -> 表不存在
create table t1 -> t1 表已存在
约束冲突（主键、唯一键、非空…）

问题一，表不存在

场景：

开发人员手动在从库创建了一个 test 数据库 create database test;
开发人员发送连接数据库错误，应该在主库上创建，然后去主库上执行创建数据库操作 create database test charset utf8mb4;
开发人员发现这个库是主从复制的，查看从库是否有数据库，mysql> show databases;，发现有 test 数据库。
开发人员在主库上创建表 create table t1(id int);。
开发人员去从库上查询表，发现没有。
1. use test
2. show tables;

解决方案

我们执行 show slave status\G 查看主从复制信息

发现有如下错误信息，Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test charset utf8mb4'。我们初步可以判断从库已经有 test 数据库了。

正确做法

在从库删除 test 库。

drop database test;
stop slave;
start slave;

把握一个原则，一切以主库为准进行解决，如果出现问题，尽量进行反操作。最直接的办法，从新构建主从

暴力方法（不推荐，了解即可）

方法一：忽略错误信息。将同步指针向下移动一格，如果多次不同步，可以重复操作。
- stop slave;
- set global sql_slave_skip_counter=1;
- start slave;
方法二：忽略错误信息
- vim /etc/my.cnf
  - slave-skip-errors=1032,1062,1007
- 常见错误代码：
  - 1007：对象已存在
  - 1032：无法执行 DML
  - 1062：主键冲突，或约束冲突

再次说一下，上面的操作是有风险的，最安全的做法是重新构建主从，把握一个原则，一切以主库为主。

问题二，主键冲突

场景

主库：create table t2(id int primary key not null auto_increment);
主库：insert into t2 values (1);
从库：insert into t2 values (2);
主库：insert into t2 values (2);

错误信息：Could not execute Write_rows event on table test.t2; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 5189

解决方案

校验两个数据的差异性
将从库不同的数据部分更新成和主库一样的
跳过这条报错
1. stop slave;
2. set global sql_slave_skip_counter=1;
3. start slave;

为了很大程度的避免 SQL 线程故障

从库只读
1. read_only
2. super_read_only
使用读写分离中间件
1. atlas
2. mycat
3. ProxySQL
4. MaxScale

5.6 主从延时监控及原因

5.6.1 主库方面原因

binlog 写入不及时
1. 解决方案：
  1. sync_binlog=1：写入日志必写磁盘
默认情况下 DUMP_T 是串行传输 binlog，在并发事务量大时，由于 DUMP_T 是串行工作的，导致传送日志较慢
1. 解决方案：
  1. 必须 GTID，使用 Group Commit 方式，可以支持 DUMP_T 并行操作
大事务，同上
主库及其繁忙
1. 慢语句
2. 锁等待
从库较多
网络延时

5.6.2 从库方面原因

传统复制（Classic）中，如果主库并发事务量很大，或者出现大事务，由于是单 SQL 线程，导致不管传的日志有多少，只能一次执行一个事务。
1. 解决方案：
  1. 5.6 版本，有了 GTID，可以实现多 SQL 线程，但是只能基于不同库的事务进行并发回放。同一个库还是串行的。（database）
  2. 5.7 中，有了增强的 GTID，增加了 seq_no，增加了新型的并发 SQL 线程模式（logical_clock），MTS

并行工作线程

mysql> show variables like '%worker%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.01 sec)
复制代码

组提交

mysql> show variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
复制代码

主从硬件差异太大
主从的参数配置不一致
从库和主库的索引不一致
版本差异

5.6.3 主从延时监控

主从延时监控：（查看延时时间，判断有没有延时，不太准确）

mysql> show slave status;
Seconds_Behind_Master：0
复制代码

主库方面原因监控：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     5473 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show slave status;
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5473
复制代码

从库方面原因监控

取了多少：

              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5473
复制代码

执行了多少：

               Relay_Log_File: VM-0-3-centos-relay-bin.000003
                Relay_Log_Pos: 573

          Exec_Master_Log_Pos: 547
              Relay_Log_Space: 1207
复制代码

Relay_Log_Pos 和 Relay_Log_Space 对应。

如果 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 差异较大的话，就主从延迟了。

如何排查：

查看 Exec_Master_Log_Pos 正在执行的号码正在做什么。

文章版权归作者所有，未经允许请勿转载。

THE END

后端