MySQL 学习（11）—— 主从复制高级进阶 | 8月更文挑战

开坦克的贝吉塔
432 阅读7分钟

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

1. 延时从库

  • 理念：控制 SQL 线程延时，数据已经写入 relaylog 中了，SQL 线程“慢点”执行
  • 经验：一般企业建议 3-6 个小时，具体看公司运维人员对于故障的反应时间
mysql> stop slave;

mysql> change master to master_delay=300;

mysql> start slave;

mysql> show slave status\G
                    SQL_Delay: 300  -- 延时时间
          SQL_Remaining_Delay: NULL  -- 最近一个事务执行还剩多少秒

mysql> create database test2;  -- 主库

mysql> show slave status\G
                    SQL_Delay: 300
          SQL_Remaining_Delay: 287 -- 剩余时间变了

mysql> drop database test2; -- 主库

mysql> stop slave sql_thread;

此时主库执行了删库操作，发现第一步我们的操作应该是停掉 SQL 线程，执行 stop slave sql_thread;。然后我们应该模拟 SQL 线程执行数据。

1.2 延时从库处理逻辑故障

1.2.1 延时从库的恢复思路

  1. 监控到数据库逻辑故障

  2. 停从库 SQL 线程，记录已经回放的位置点（截取日志起点）

    1. stop slave sql_thread;
    2. Relay_Log_File: VM-0-3-centos-relay-bin.000002
    3. Relay_Log_Pos: 644

  3. 截取 relaylog

    1. 起点：show slave status\G

      1. Relay_Log_File、Relay_Log_Pos

    2. 终点：drop 之前的位置点

      1. show relaylog events in 'VM-0-3-centos-relay-bin.000002';
      2. 进行截取

  4. 模拟 SQL 线程回放日志

    1. 从库 source

  5. 恢复业务：

    1. 情况一：就一个库，从库替代主库
    2. 情况二：有多个库，从库导出故障库，还原到主库中

1.2.2 故障演练

  • 主库：
mysql> create database delay charset utf8mb4;
mysql> use delay;
mysql> create table t1(id int);
mysql> insert into t1 values (1),(2),(3);
mysql> drop database delay;

然后我们检测到 relay 这个数据库没有了。我们去主库查看一下，数据库确实没有了。

  • 从库
  1. 停止从库 SQL 线程，获取 relay 位置点
mysql> stop slave sql_thread; -- 停止从库 SQL 线程
mysql> show slave status\G -- 获取起点位置
               Relay_Log_File: VM-0-3-centos-relay-bin.000003
                Relay_Log_Pos: 472
  1. 找到 relay 的截取终点
mysql> show relaylog events in 'VM-0-3-centos-relay-bin.000003';
+--------------------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name                       | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------------------------+------+----------------+-----------+-------------+---------------------------------------+
| VM-0-3-centos-relay-bin.000003 |    4 | Format_desc    |         8 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| VM-0-3-centos-relay-bin.000003 |  123 | Previous_gtids |         8 |         154 |                                       |
| VM-0-3-centos-relay-bin.000003 |  154 | Rotate         |         7 |           0 | mysql-bin.000001;pos=5949             |
| VM-0-3-centos-relay-bin.000003 |  201 | Format_desc    |         7 |           0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| VM-0-3-centos-relay-bin.000003 |  320 | Anonymous_Gtid |         7 |        6014 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| VM-0-3-centos-relay-bin.000003 |  385 | Query          |         7 |        6101 | drop database test3                   |
| VM-0-3-centos-relay-bin.000003 |  472 | Anonymous_Gtid |         7 |        6166 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| VM-0-3-centos-relay-bin.000003 |  537 | Query          |         7 |        6279 | create database delay charset utf8mb4 |
| VM-0-3-centos-relay-bin.000003 |  650 | Anonymous_Gtid |         7 |        6344 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| VM-0-3-centos-relay-bin.000003 |  715 | Query          |         7 |        6443 | use `delay`; create table t1(id int)  |
| VM-0-3-centos-relay-bin.000003 |  814 | Anonymous_Gtid |         7 |        6508 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| VM-0-3-centos-relay-bin.000003 |  879 | Query          |         7 |        6581 | BEGIN                                 |
| VM-0-3-centos-relay-bin.000003 |  952 | Table_map      |         7 |        6627 | table_id: 146 (delay.t1)              |
| VM-0-3-centos-relay-bin.000003 |  998 | Write_rows     |         7 |        6677 | table_id: 146 flags: STMT_END_F       |
| VM-0-3-centos-relay-bin.000003 | 1048 | Xid            |         7 |        6708 | COMMIT /* xid=632 */                  |
| VM-0-3-centos-relay-bin.000003 | 1079 | Anonymous_Gtid |         7 |        6773 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| VM-0-3-centos-relay-bin.000003 | 1144 | Query          |         7 |        6868 | drop database delay                   |
+--------------------------------+------+----------------+-----------+-------------+---------------------------------------+
17 rows in set (0.00 sec)

左边 Pos 表示的是 relaylog 的起点，右边 End_log_pos 表示的是 binlog 的起点。我们看 relaylog 就可以（看左边就可以）。

在上图中，1144 是 drop 的起点，也是上一条语句的终点，所以我们选择这个 Pos。

  1. 截取 relay
# mysqlbinlog --start-position=472 --stop-position=1144 VM-0-3-centos-relay-bin.000003 > /tmp/relay.sql

截取完了一定要看看 drop 操作没在文件中

  1. 恢复 relay 到从库
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;

接下来我们就按照上述我们说的，该怎么恢复主库，就怎么恢复主库了。

我选择备份从库数据，恢复到主库：

# mysqldump -uroot -S /data/3308/mysql.sock -B delay > /tmp/delay_bak.sql

主库：

mysql> set sql_log_bin=0;
mysql> source /tmp/delay_bak.sql
mysql> set sql_log_bin=1;
mysql> show databases;
mysql> use delay;
mysql> select * from t1;

从库重启一下同步：

mysql> stop slave;
mysql> start slave;

2. 过滤复制

2.1 快速恢复测试环境

  • 从库
mysql> drop database delay;
mysql> stop slave;
mysql> reset slave all;
  • 主库：
mysql> reset master;
  • 从库：
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=154,
  MASTER_CONNECT_RETRY=10;

mysql> start slave;

2.2 过滤复制应用

主库方面过滤，DUMP_T 线程不传送某个库的更新信息（一般不使用这种方式）

主库：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • Binlog_Do_DB：白名单，表示要复制的数据库
  • Binlog_Ignore_DB：黑名单，表示要忽略的数据库

一般情况下，配置其中一个即可。

  • 从库
mysql> show slave status\G
              Replicate_Do_DB:  -- 白名单（库级别）
          Replicate_Ignore_DB:  -- 黑名单（库级别）
           Replicate_Do_Table:  -- 白名单（表级别）
       Replicate_Ignore_Table:  -- 黑名单（表级别）
      Replicate_Wild_Do_Table:  -- 白名单（表级别，可匹配多个表）
  Replicate_Wild_Ignore_Table:  -- 黑名单（表级别，可匹配多个表）

vim /data/3308/my.cnf
replicate_do_db=repl

systemctl restart mysqld3308

此时，我们查看一下主库信息：

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

再查看一下从库信息：

mysql> show slave status\G
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 479

              Replicate_Do_DB: repl

可以看到主从正常同步，且只同步 repl 数据库。

此时我们在主库创建一个其他数据库：create database aa;。我们再去从库查看一下：show databases;，发现没有 aa 这个数据库。

此时我们在主库创建一个其他数据库：create database repl;。我们再去从库查看一下：show databases;，发现有了 repl 这个数据库。

3. GTID

3.1 GTID 介绍

GTID（Global Transaction ID）是对于一个已提交事务的唯一编号，并且是一个全局（主从复制）唯一的编号。

它的官方定义如下：

GTID = source_id ：transaction_id

3.2 GTID 环境准备

准备三台机器：（一主二从）

  1. 创建 mysql 用户

    1. useradd -s /sbin/nologin mysql

  2. 下载 mysql 5.7.26 安装包并解压，解压到 /home/mysql/mysql-5.7.26 目录下

  3. 创建数据目录

    1. mkdir /data/mysql/data -p

  4. 修改配置文件（三台机器分别修改，一主二从）

  • 主库
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/home/mysql/mysql-5.7.26
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db-master [\\d]>
EOF
  • 从库 1
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/home/mysql/mysql-5.7.26
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db-slave1 [\\d]>
EOF
  • 从库 2
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/home/mysql/mysql-5.7.26
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db-slave2 [\\d]>
EOF

  1. 创建 binlog 目录

    1. mkdir /data/binlog

  2. 初始化 MySQL，无密码登录

    1. mysqld --initialize-insecure --user=mysql --basedir=/home/mysql/mysql-5.7.26 --datadir=/data/mysql/data

  3. 修改文件权限

    1. chown -R mysql.mysql /home/mysql/mysql-5.7.26
    2. chown -R mysql.mysql /data/

  4. 启动 MySQL

    1. /home/mysql/mysql-5.7.26/support-files/mysql.server start

3.3 构建主从

  • 主库：

    • grant replication slave on *.* to repl@'10.20.1.5%' identified by 'wys';

  • 从库：

CHANGE MASTER TO
  MASTER_HOST='10.20.2.92',
  MASTER_USER='repl',
  MASTER_PASSWORD='wys',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;

查看从库状态：

db-slave1 [(none)]> start slave;
db-slave1 [(none)]>show slave status\G

db-slave2 [(none)]> start slave;
db-slave2 [(none)]>show slave status\G

3.4 GTID 核心参数

# 开启 GTID，否则就是普通的复制架构
gtid-mode=on

# 强制 GTID 一致性
enforce-gtid-consistency=true

# 强制从库刷新 binlog 日志
log-slave-updates=1

# 读取 relaylog 最后一个事务的 GTID
MASTER_AUTO_POSITION=1

3.5 总结

区别（同普通主从复制）

  1. 在主从复制环境中，主库发生过的事务，在全局都是由唯一 GTID 记录的，更方便 Failover
  2. 额外功能参数（3 个）
  3. change master to 的时候不再需要 binlog 文件名和 position 号，使用 MASTER_AUTO_POSITION=1。
  4. mysqldump 备份时，默认会将备份中包含的事务操作，以以下方式：
    1. SET @@GLOBAL.GTID_PURGED='b6148b54-6684-11eb-946f-c81f66c506f5:1';
    2. 这行命令是告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个 GTID 开始请求 binlog 就行。

细节：

  1. 构建主从时，要先把主库备份，然后再去做主从构建
  2. mysqldump --set-gtid-purged=auto，默认是 auto，如果我们设置了 off，就没有 SET @@GLOBAL.GTID_PURGED='b6148b54-6684-11eb-946f-c81f66c506f5:1' 这行信息了。不应该添加 off。

讨论：

为什么设置了 OFF 就构建不了主从了？

如果我们设置了 OFF，那么就没有 SET @@GLOBAL.GTID_PURGED='b6148b54-6684-11eb-946f-c81f66c506f5:1-11' 这行数据了。

假设我们有 11 个事务（1-11），此时备份文件已经有了这 11 个事务了。假设把这行注释掉了，那么从库就不知道已经有了这 11 个事务了。而我们又设置了 MASTER_AUTO_POSITION=1 了，那我们意思是从主库的第一个事务开始请求，但是我们已经有了前 11 个事务了，然后就重复了，就无法构建成功。因此，不能加 OFF，默认设置 AUTO。

4. 半同步

解决主从复制数据一致性问题

ACK

从库 relay 落地，IO 线程会返回一个 ACK，主库有个 ACK_receiver 线程。接收到了信号，主库事务才会提交，如果一直 ACK 没有收到，超过 10 秒钟会切换为异步复制。

5. 高可用 MHA

avatar
文章
阅读
粉丝