高并发，高性能，高可用MySQL 实战-搭建三高架构的基础-如何实现数据冗余

1. 三高架构之路

1. 什么是三高

• 高并发：同一时刻能处理的事务数高
• 高性能：事务/sql执行速度高
• 高可用：系统可用时间高

2. 如何达到三高
   复制---扩展---切换

2. 复制有哪些类型

1. 复制的基本原理
   
   主从复制用四个字概括就是：binlog 传送

2. 复制的类型：

• 异步复制
  
  异步复制原理简单，网络延迟较小，不能保证日志被传送到了备库，有可能数据丢失

• 半同步复制
  
  半同步复制原理简单，但是对网络延迟有一定要求，最好在同一个机房，可以保证日志被传送到了备库，不易丢失数据
  rpl_semi_sync_master_timeout 参数可以调整脱扣时间，也就是说如果从库一直不返回ACD给主库，主库就不等了，直接返回

• 组复制
  
  组复制原理比较复杂，需要依赖共识算法，实际应用较少，是数据库走向原生分布式的示范

3. 总结：
   按照同步类型可以分为：异步复制、半同步复制、组复制
   半同步复制是兼顾数据安全与效率的较好选择
   组复制虽然使用较少，是newsql数据库的前奏

主从复制配置实战

1. /etc/mysql/my.cnf 主库
   log-bin=/var/lib/mysql/mysql-bin
   server-id=123454

2. /etc/mysql/my.cnf 从库
   log-bin=/var/lib/mysql/mysql-bin
   server-id=111111

3. 主库全局上锁
   flush tables with read lock;

4. 查看主库binlog状态
   show master status\G

5. 备份主库
   mysqldump -h xxxx -P 33306 -u root --all-databases --master-data > dbdump.sql -p

6. 将备份文件复制到从库
   cp ../mysql8/dbdump.sql .

7. 释放主库锁
   unlock tables;

8. 进入从库msyql客户端，导入备份文件
   source dbdump.sql;

9. 查看从库状态
   show slave status\G

10. 修改从库对应的主库
    change master to master_host="xxxx", master_user="root", master_log_file="mysql-bin.000001", master_log_pos=156;

11. 开启从库
    start slave;

12. 停止从库、重置从库
    stop slave;
    reset slave;

13. 重新配置从库对应的主库，需要输入端口号和密码
    change master to master_host="4xxxxxxx", master_port=xxxx, master_user="root", master_password='xxxx', master_log_file="mysql-bin.000001", master_log_pos=156;

14. 向主库插入一条数据，从库也跟着有了
    主库和从库的状态是一致的
    show master status;
    show slave status;

默认主从采用的异步复制

15. 如何使用半同步复制，主要在主和从配置文件中加入三行
    plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    rpl-semi-sync-master-enabled = 1
    rpl-semi-sync-slave-enabled = 1

16. 查看主从脱库时间(只有半同步复制才有这个时间，异步复制没有的)
    show variables like "rpl_semi_sync_master_timeout";

17. 查看正在做的线程
    show processlist\G

到此主从复制完成了。

怎么使得配置的复制更方便

1. 主库和从库配置中都打开GTID配置
   gtid_mode=on
   enforce_gtid_consistency=on

2. 重启主库和从库
   sudo docker-compose restart

3. 停止从库
   stop slave;

4. 使用GTID进行主从复制
   change master to master_host="xxxxx", master_port=33306, master_user="root", master_password='xxxxx.', master_auto_position=1;

5. 开启从库
   start slave;

GTID主从复制完成，向主库中插入一条数据试试吧。

6. 总结：

• GTID可以给事务分配全局唯一ID
• GTID方便了主从复制的配置，推荐打开
• GTID对主从切换，故障恢复也有很大的意义

为什么binlog格式会影响复制

1. 设置从库只读选项，只需要在从库的配置文件中加入

innodb_read_only=1
read_only=1
super_read_only=1
transaction_read_only=1

2. statement格式的binlog

• 5.0之前的mysql默认使用了statement格式的binlog
• binlog记录的是sql语句原文
• 由于主备库对sql语句的执行不一致，可能有数据安全风险

3. row格式的binlog

• 不记录sql语句原文
• 记录数据行的变化
• 不是物理日志，还是逻辑日志
• 占用空间较大

4. 查看binlog中记录的是啥东西

show master status\G
show binlog events in "mysql-bin.000003"\G

5. mixed格式的binlog

• 两种格式混合使用
• 有数据风险的语句使用ROW
• 无风险的使用statement

6. 基于语句或行的复制

• 基于语句的复制-statement
• 基于行的复制=row

7. 总结：

• statement格式记录了实际执行的sql语句
• row格式按照整行记录了数据的变化
• 推荐直接使用row格式的binlog

备库延迟太大怎么办

1. 为什么会有延迟呢
   
   即使是半同步复制，主库dump_thread等待binlog传送给从库io_thread之后是写入到了中继日志，并没有落盘到binlog日志
   sql_thread将中继日志复制到binlog也是需要时间的，

2. 备库延迟的原因

• binlog传送开销较小，主要是重放relay log耗时
• 备库性能不如主库
• 备库承担了很多分析SQL
• 主库的长事务未提交

3. 处理方法

• 主备使用相同配置的机器
• 备库关闭log实时落盘
• 增加从库数量，应对分析sql
• binlog传送至大数据系统供分析
• 大事务-分多

4. 依然存在问题

• 备库对硬件资源利用天然不如主库
  
• 备库单线程执行，主库是多线程执行

5. 总结：

• 备库延迟的主要原因是备库执行慢（主库binlog传送至备库relay log快，但是备库重放relay log到备库的binlog慢）
• 提升性能，升级备库应将，关闭Log实时落盘
• 减少负担：增加数量，使用其它组件
• 依然存在备库单线程的问题

6. 多线程写binlog，单线程重放relay log,解决办法-多线程重放relay log
   

7. 并行复制的思路
   

8. mysql5.6并行复制

• mysql5.6使用按库并行的策略
• 优点：分发选择快，支持各种log格式（statement、row）
• 缺点：库粒度太大，很难负载平衡
  // 从库配置
• slave-parallel-type = DATABASE

9. mysql5.7使用按事务组并行的策略

• binlog刷盘其实是两步动作
• 先把binlog从binlog cache中写到内存的binlog文件
• 调用fsync持久化到磁盘

10. 事务组图谱
    

11. 使用事务组并行的策略

• binlog_group_commit_sync_delay: 延迟多少微秒后才调用fsync
• binlog_group_commit_sync_no_delay_count: 累计多少次后才调用fsync
• 两个条件是或的关系

12. mysql5.7使用了按事务组并行的策略

• mysql5.7使用按事务组并行的策略
• 同时处于prepare状态的事务，在备库执行是可以并行的
  // 备库开启并行
• slave-parallel-type = LOGICAL_CLOCK

13. mysql5.7.22并行复制

• binlog-transaction-dependency-tracking参数：
  • COMMIT_ORDER: 按事务组并行（5.7）
  • WRITESET: 没有修改相同行的事务可以并行
  • WRITESET_SESSION: 同一个线程先后执行的两个事务不能并行

14. 总结：

• 备库延迟的根本原因是主库多线程，备库单线程
• 并行复制思路：按表复制、按行复制
• 5.7借鉴了事务组的思想，将同一个事务组的relay log并行
• 5.7.22引入了没有修改相同行的事务并行的方法
• 推荐使用5.7事务组并行的方法

如何在备库读到最新数据

1. 如何判断备库已经追上

• 强制延时
• seconds_behind_master=0
• 对比binlog执行位点
• 对比GTID执行情况

2. 备库延迟理论上无法消灭

• binlog传送、中继日志重放需要时间
• 理论上，备库延迟只能减小无法消灭
• 在备库读取数据时，永远面临数据延迟问题

3. 判断具体的事务是否已经重放

• 等待binlog位点
• select master_pos_wait(file, pos[, timeout]); 默认是阻塞的，重放好了就解堵塞
• 等待GTID（5.7.6之后可以返回每次的GTID），select wait_for_executed_gtid_set(gtid_set, 1);

4. 总结：

• 可以通过对比执行位点的方法判断从库延时
• 从库延时可能无法消灭
• 可以通过具体的某一个binlog位点是否已经执行，和某一个GTID是否执行的方法来等待从库更新成跟主库一致的状态

怎么样实现最简单的高可用架构

1. 主主复制架构

• 两个节点均为master
• 两个节点互为slave
• 一个节点出现故障时无须切换
  
  

2. 主主架构实现就简单了
   前面主从已经实现了，现在让主也去跟踪从，就变成了主主
   在主服务器上执行

show slave status\G
stop slave;
change master to master_host="xxxx", master_port=33307, master_user="root", master_password="xxxx", master_auto_position=1;
start slave;
show slave status\G

主主架构完成，在两台服务器上新增修改数据试试吧。

3. 主主架构的问题

• 数据冲突问题
  两边插入相同ID时，就会出现冲突
  两边约定好插入相同ID
  只写一个主，另一个只读
  有切换过快的数据丢失问题
• 客户端切换
  应用自己切换比较麻烦
  使用keepalived等手段可以完成自动切换
• 循环复制
  理论有此问题
  未开GTID,使用serverID过滤
  GTID天然避免

4. 总结

• 主主架构是最简单的高可用架构
• 存在数据冲突，客户端切换不便等问题

本章小结

1. 复制有哪些类型

• 按照同步类型可分为：异步、半同步、组复制
• 半同步复制是数据安全与效率的较好选择

2. GTID(global transaction identifier)

• GTID可以给事务分配全局唯一ID
• GTID方便了主从复制的配置，推荐打开
• GTID对主从切换，故障恢复也有很大的意义

3. binlog格式

• statement格式记录了实际执行的sql语句
• row格式按照整行记录了数据的变化
• 推荐直接使用row格式 （row格式虽然占用的数据空间大，但是一致性方面是更好的）

4. 备库延迟

• 提升性能：设计本库硬件，关闭log实时刷盘
• 减少负担：增加数量，使用其它组件
• 并行复制

5. 并行复制

• 备库延迟的根本原因是主库多线程，备库单线程
• 并行复制思路：按表复制，按行复制
• 按事务组复制，将同一个事务组的relay log并行

6. 判断主备同步情况

• 可以通过对比执行位点的方法判断从库延时
• 从库延时可能无法取消
• 可以通过binlog位点和GTID等待方法等待具体事务

7. 主主复制架构

• 主主架构是最简单的高可用架构
• 存在数据冲突、客户端切换不便等问题