MySQL——高可用_mysql高可用

高可用性（High Availability）指的是通过尽量缩短因日常维护操作（计划）和突发的系统崩溃（非计划）所导致的停机时间，以提高系统和应用的可用性。

如何实现高可用？

• 避免导致系统不可用的因素（服务器磁盘空间耗尽、性能糟糕的SQL、表结构和索引没有优化、主从数据不一致、人为的操作失误等等），减少系统不可用的时间；
  • 建立完善的监控及报警系统；
  • 定时的对备份数据进行恢复测试；
  • 对不需要的数据进行归档和清理；
• 增加系统的冗余，保证发生系统不可用时可以尽快恢复；
  • 避免存储单点故障（单纯的主从复制架构并不能解决主库的单点问题）；
  • 主从切换及故障转移；

如何解决 MySQL 库的单点故障？

• 主库切换后，如何通知新的主服务器的IP地址？
• 如何检查 MySQL 主库是否可用？
• 如何处理从服务器和新主服务器之间的那种复制关系？

MMM架构

MMM（Multi-Master Replication Manager）是 MySQL 多组复制的简称，它是由 Perl 语言开发的用于管理 MySQL 主主同步架构的工具集。主要作用就是监控和管理 MySQL 的主主复制拓扑，并在当前的主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作。

MMM 提供了什么功能？

• 监控 MySQL 主从复制健康情况（MMM 架构同一时间只有一台主服务器对外提供服务，另外的主备服务器只能用于查询）；

• 在主库出现宕机时进行故障转移并自动配置其他从对新主的复制。

  • 如何找到从库对应的新的主库日志点的日志同步点？如果存在多个从库出现数据不一致的情况如何处理？MMM 对这一块的处理并不安全。MMM 只是简单粗暴的找到新的主库当前的日志点，然后使从库对这个日志点进行同步。在一个繁忙的系统中使用 MMM 很有可能造成数据丢失的情况。

• 提供了一个写虚拟 IP 和多个读虚拟 IP，写虚拟 IP 只能在主数据库之间进行切换，读虚拟 IP 则可以在服务器所有主从节点进行切换。在主从服务器出现问题时可以自动迁移虚拟 IP。

MMM 架构图：

MMM 部署所需资源：

MMM 架构优点：

• 提供了读写 VIP (虚拟IP)，使服务器角色的变更对前端应用透明；
• 提供了对从服务器的延迟监控；
• 提供了主数据库故障转移后，从服务器对新主服务器的重新同步功能；
• 很容易对发生故障的主数据库重新上线；

MMM 架构缺点：

• 发布时间比较早，不支持 MySQL 新的复制功能；
• 在进行主从切换时，容易造成数据丢失；
• MMM监控服务存在单点故障；
• 没有读负载均衡的功能。

MHA架构

MHA（Master High Availability）也是由 Perl 语言开发的，在 MySQL 高可用方面是一个相对成熟的解决方案。

MHA 完成主从切换超高效，基本上在 30 秒内完成主从切换，并且在切换过程中最大程度的保证数据一致性。达到真正意义上的高可用。

MHA 提供了什么功能？

1. 监控主数据库服务器是否可用；
2. 当主DB不可用时，MHA可以从多个从服务器中选举出一个新的主数据库服务器；
3. 提供了主从切换和故障转移功能；

MHA 是如何进行主从切换的？

1. 尝试从出现故障的主数据库中保存二进制日志（与MMM最大的不同，这一步不是总能成功的，当主数据库硬件本身无法访问时，则不能成功保存二进制日志）；
2. 从多个可用的从服务器中识别出含有最新更新的那个从服务器，并把这个从服务器作为新的备选主服务器；
3. 在备选主服务器和其他从服务器之间同步差异二进制数据；
4. 新的备选主服务器会存放从原主服务器上保存下来的二进制日志（前提是保存下来了，这一步如果出现错误，例如重复的主键等，会使MHA停止进行故障转移）；
5. 提升备选主服务器作为新的主服务器；
6. 迁移集群中其他的从服务器作为新的主服务器的从服务器。

MHA 架构图：

MHA 支持 GTID 的复制，而 MMM 不支持，GTID 也是安全性比较高的一种复制模式，配合 MHA 使用也是更加的合理。

MHA 配置步骤：

1. 配置集群内所有主机的 SSH 免认证登陆（比如故障转移过程中保存原主服务器二进制日志，配置虚拟IP地址等）；
2. 所有 DB 服务器安装 MHA-node软件包，监控服务器安装 MHA-manager 软件包；
3. 建立主从复制集群（MHA支持基于日志点的复制和基于GTID的复制，这里推荐使用基于GTID的复制）；
4. 配置 MHA 管理节点；
5. 使用 masterha_check_ssh 和 masterha_check_repl 对配置进行检验；
6. 启动并测试 MHA 服务。

MHA 架构优点：

• 使用Perl脚本语言开发及完全开源；
• 可以支持基于 GTID 的复制模式；
• MHA 在进行故障转移时更不易产生数据丢失；
• 同一个监控节点可以监控多个集群；

MHA 架构缺点：

• MHA 的配置中没有主从 VIP 的配置，需要编写脚本或利用第三方工具来实现 VIP 的配置；
• MHA 启动后只会对主数据库进行监控，无法发现复制链路中的问题（例如主从延迟增大，复制链路中断等）；
• 需要基于 SSH 免认证配置，存在一定的安全隐患；
• 没有读负载均衡的功能。

数据库监控

对于任何系统来说，监控都是重要的组成部分。数据库是一切系统的核心组件，数据库的稳定性从一定程度上决定了系统的稳定性，所以，对于数据库的监控，就显得尤为重要了。常见的开源监控软件有 Nagios、Zabbix。这些监控软件，或是提供了数据库监控插件，或是允许用户以插件的形式开发自己对数据库的监控脚本，并且支持的脚本语言也是多种多样的，用户完全可以按照自己的习惯，来选择自己的监控软件，以及编写适合自己的监控脚本。

对于 MySQL 来说，最基本的监控应该包含以下内容：

• 对数据库服务可用性进行监控（通过网络连接到数据库并且确定数据库是可以对外提供服务的）；
• 对数据库性能进行监控（QPS、TPS、并发线程数量的监控）；
• 对主从复制进行监控（主从复制链路状态的监控、主从复制延迟的监控、定期的确认主从复制的数据是否一致）；
• 对服务器资源的监控（磁盘空间的监控（无论是数据目录还是日志目录的空间被占满，都会导致 MySQL 不可用）、CPU 的使用情况、内存的使用情况、Swap 分区的使用情况以及网络 IO 的情况等）；

数据可用性监控

首先我们先来看下如何确认数据库是否可以通过网络进行连接。使用 MySQL 的本地的 SQL 文件连接数据库服务器，并不意味着可以通过网络 TCP/IP 协议能连接到 MySQL。确认数据库是否可以通过网络进行连接，通常使用以下几种方式中的一种：

• 方案一：利用 mysqladmin -umonitor_user -p -h ping 命令在远程服务器上执行，来确认被监控的服务器是否可以连接；
• 方案二：利用 telnet ip db_port 命令手动确认被监控的服务器是否可以连接；
• 方案三：使用程序通过网络建立数据库连接来确认被监控的服务器是否可以连接，这是最好的方式。

可以连接到数据库并不代表数据库就是可用的，所以还需要确认数据库是否可以读写。

如何确认数据库是否可读写？

• 定期检查主数据库的 read_only 参数是否为 off；
• 建立监控表并对表中数据进行更改；
• 如果只是监控数据库是否可读只需要执行简单的查询 select @@version；

可以连接到MySQL 的线程数是有限制的，如何监控数据库的连接数？

show variables like 'max_connections';   //获取MySQL能接受最大连接数的数量
show global status like 'Threads_connected';   //获取系统变量Threads_connected的值，记录了当前数据库的连接数量
1
2

例如报警就可以当 Threads_connected / max_connections > 0.8 时，就需要报警。

数据性能监控

性能监控不同于可用性监控，性能监控更关注的是数据库性能的变化趋势，所以在进行性能监控的脚本开发时，就需要注意记录好性能监控过程中所采集到的数据库的状态信息，以便分析数据库性能变化趋势时使用。

对于性能监控来说，可能关注最多的就是 QPS 和 TPS。

QPS = (Queries2 - Queries1) / (Uptime_since_flush_status2 - Uptime_since_flush_status1)

TPS = ((Com_insert2 + Com_update2 + Com_delete2) - (Com_insert1+ Com_update1 + Com_delete1)) /
(Uptime_since_flush_status2 - Uptime_since_flush_status1)

这几个参数获取方式：

show global status like 'Queries'
show global status like 'Uptime_since_flush_status'
show global status like 'Com_insert'
show global status like 'Com_update'
show global status like 'Com_delete'
1
2
3
4
5

如何监控数据库的并发请求数量？

通常情况下，数据库系统的性能会随着并发处理请求数量的增加而下降。所以并发请求数量通常还需要和 CPU 的使用率等指标结合起来分析。

数据库当前并发请求数量可以通过 show global status like ‘Threads_running’ 获取。并发处理的数量通常会远小于同一时间连接到数据库的线程的数量。

通常情况下并发请求数量是很稳定的，如果我们发现某一时刻并发量突然间增大，那么就需要检查是否出现了数据库的异常，比如数据库出现大量阻塞的情况下，就很有可能出现这种现象。

如何监控 InnoDB 的阻塞？

查询阻塞时间超过 20 秒的 SQL：

SELECT b.trx_mysql_thread_id AS '被阻塞线程'
		,b.trx_query AS '被阻塞SQL'
		,c.trx_mysql_thread_id AS '阻塞线程'
		,c.trx_query AS '阻塞SQL'
		,(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间'
FROM information_schema.innodb_lock_waits a
JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id
JOIN information_schema.innodb_trx c ON a.blocking_trx_id=c.trx_id
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>20

1
2
3
4
5
6
7
8
9
10