MySQL高可用架构

MySQL主从复制原理

MySQL主从复制是构建高可用MySQL的基础，复制就是让一台服务器的数据和其它服务器保持同步，一台主库可以同步到多台备库上面，备库也可以作为另一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

主从复制

1)、主库记录二进制日志，每次准备提交事物完成数据库更新前，先记录二进制日志，记录二进制日志后，主库会告诉存储引擎可以提交事物了

2)、备库将主库的二进制日志复制到本地的中继日志中，首先，备库会先启动一个工作进程，称为IO工作线程，负责和主库建立一个普通的客户端连接。如果该进程追赶上了主库，它将进入睡眠状态，直到主库有新的事件产生通知它，他才会被唤醒，将接收到的事件记录到中继日志中。

3)、备库的SQL线程执行最后一步，该线程从中继日志中读取事件并且在备库执行，当SQL线程赶上IO线程的时候，中继日志通常记录在系统缓存中，所以中继日志的开销很低。SQL线程也可以根据配置选项来决定是否写入其自己的二进制日志中。

半同步复制

如何解决MySQL主库宕机导致的数据丢失情况？

使用半同步复制。在主库commit之前，需要先将binlog同步到从库，主库可以设置同步binlog的过期时间，在binlog复制到从库之后，从库后续会自行重放中继日志。不过这样也增加了客户端的延迟。另外这个需要安装下MySQL的插件。

MySQL的半同步插件为：semisync_xx.so

具体如何操作，参考我之前的博客：MySQL复制详解

复制方式

基于GTID和日志

• 日志：传统的方式，默认的方式。依赖二进制日志，根据日志的偏移量。事务不断提交，二进制日志的偏移量也会不断的变化。需要从库告诉主库，自己明确复制到了偏移量的什么位置。

• GTID: 全局事务ID，在一个集群内的一个GTID是唯一的， GTID= source_id:transcation_id，source_id为那一台机器上的，slave增量复制还未同步的GTID即可。

建议优先使用GTID方式，可以更安全的进行故障转移。

主从复制延迟

产生延迟原因？

• 主节点如果执行一个很大的事务(更新千万行语句，总之执行很长时间的事务)，那么就会对主从延迟产生较大的影响

• 网络延迟，日志较大，slave数量过多。

• 主上多线程写入，从节点只有单线程恢复

处理办法：

• 大事务：将大事务分为小事务，分批更新数据。

• 减少Slave的数量，不要超过5个，减少单次事务的大小。

• MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构

参考

• MySQL复制详解

MySQL高可用架构对比

MMM与MHA以及MGR，高可用架构都有如下的共同点：

• 对主从复制集群中的Master节点进行监控

• 自动的对Master进行迁移，通过VIP。

• 重新配置集群中的其它slave对新的Master进行同步

MMM

需要两个Master，同一时间只有一个Master对外提供服务，可以说是主备模式。

需要基础资源：

故障转移步骤：

• Slave服务器上的操作

• 完成原主上已经复制的日志恢复

• 使用Change Master命令配置新主

• 主服务器上操作

• 设置read_only关闭

• 迁移VIP到新主服务器

优点：

• 提供了读写VIP的配置，试读写请求都可以达到高可用

• 工具包相对比较完善，不需要额外的开发脚本

• 完成故障转移之后可以对MySQL集群进行高可用监控

缺点：

• 故障简单粗暴，容易丢失事务，建议采用半同步复制方式，减少失败的概率

• 目前MMM社区已经缺少维护，不支持基于GTID的复制

适用场景：

• 读写都需要高可用的

• 基于日志点的复制方式

MHA

需要资源：

MHA采用的是从slave中选出Master，故障转移：

• 从服务器：

• 选举具有最新更新的slave

• 尝试从宕机的master中保存二进制日志

• 应用差异的中继日志到其它的slave

• 应用从master保存的二进制日志

• 提升选举的slave为master

• 配置其它的slave向新的master同步

优点：

• MHA除了支持日志点的复制还支持GTID的方式

• 同MMM相比，MHA会尝试从旧的Master中恢复旧的二进制日志，只是未必每次都能成功。如果希望更少的数据丢失场景，建议使用MHA架构。

缺点：

MHA需要自行开发VIP转移脚本。

MHA只监控Master的状态，未监控Slave的状态

MGR

MGR是基于现有的MySQL架构实现的复制插件，可以实现多个主对数据进行修改，使用paxos协议复制，不同于异步复制的多Master复制集群。

支持多主模式，但官方推荐单主模式：

• 多主模式下，客户端可以随机向MySQL节点写入数据

• 单主模式下，MGR集群会选出primary节点负责写请求，primary节点与其它节点都可以进行读请求处理.

// 查看MGR的组员
select * from performance_schema.replication_group_members;
// 查看MGR的状态
select * from performance_schema.replication_group_member_stats;
// 查看MGR的一些变量
show variables like 'group%';
// 查看服务器是否只读
show variables like 'read_only%';

优点：

• 基本无延迟，延迟比异步的小很多

• 支持多写模式，但是目前还不是很成熟

• 数据的强一致性，可以保证数据事务不丢失

缺点:

• 仅支持innodb

• 只能用在GTID模式下，且日志格式为row格式

适用的业务场景：

• 对主从延迟比较敏感

• 希望对对写服务提供高可用，又不想安装第三方软件

• 数据强一致的场景

读写负载大问题

读负载大：

• 增加slave

• 加中间层(MyCat，ProxySQL，Maxscale)

• 读写分离

  
  

关于写负载大：

• 分库分表

• 增加中间层

最后

参考慕课网课程，https://s.imooc.com/S8KFBvs

MySQL管理监控

数据库管理的关键是要能提前发现问题，而要想提前发现问题，必须要有监控系统，开源的系统有

• Zabbix

• Nagios

不过这些系统，一般都是直接使用，重点是监控了什么指标，下面聚焦下具体是什么指标，而不是什么用了什么监控软件。

MySQL常用监控指标

非功能指标：

• QPS：数据库每秒钟处理的请求数量，包括DML，DDL这样才能体现数据库的性能

• TPS：数据库每秒处理的事务数量

• 并发数：数据库当前的并行处理的会话数量

• 连接数：连接到数据库会话的数量

• 缓存命中率：InnoDB的缓存命中率

功能指标：

• 可用性：数据库是否正常对外提供服务

• 阻塞：当前是否有阻塞的会话，锁住了别人需要的资源

• 死锁：当前事务是否产生了死锁，相互锁住了对方的资源

• 慢查询：实时慢查询监控

• 主从延迟：在异步复制架构中需要

QPS=(Queries1 - Queries2)/时间间隔

show global status where variable_name in ('Queries','uptime');
// 第一次查询
Queries3532976857
Uptime32769425
// 第二次查询
Queries3532977031
Uptime32769452

QPS = ( 3532977031 - 3532976857 ) / (32769452 - 32769425) = 6.44

TPS=sum(删除，增加，更新)/时间间隔

show global status where variable_name in ('com_insert','com_update','com_delete','uptime');

Com_delete1721
Com_insert3404633923
Com_update30505256
Uptime32769668

Com_delete1721
Com_insert3404633970
Com_update30505314
Uptime32769706

TPS = ((3404633970 - 3404633923) + (30505314 - 30505256 ) ) / (32769706 - 32769668) = 2.76

并发数，一般并发数越多，负载越大, 最大连接数为配置的max_connection变量

show global status where variable_name in ('Threads_running','Threads_connected');

Threads_connected5
Threads_running3

InnoDB缓存命中率，缓存命中就可以省去读磁盘

(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests

innodb_buffer_pool_read_requests：从缓存池中读取的次数

Innodb_buffer_pool_reads: 表示从物理磁盘读取的次数

show global status where variable_name like 'innodb_buffer_pool_read%'
Innodb_buffer_pool_read_requests122914434867
Innodb_buffer_pool_reads47710383

命中率 = (122914434867 - 47710383) / 122914434867 = 0.99

非功能指标

可用性，周期的连接服务器执行select @@version

阻塞，多个线程对同一个资源加排它锁导致的

• 大于5.7版本，查sys.innodb_lock_waits表，wait_started > 多少秒

死锁：

• Pt-deadlock-logger工具监控, 更直观一些

• set global innodb_print_all_deadlocks=on，打印的日志比较多，需要自己分析

慢查询：通过日志监控，也可以查information_schema.processlist 表

主从延迟

show slave status;

参考

• MySQL会发生死锁吗？

• 产品监控指标

• MySQL面试指南