MySQL 技术内幕——MySQL Cluster

高可用性（High Availability）指的是通过尽量缩短因日常维护操作（计划）和突发的系统崩溃（非计划）所导致的停机时间，以提高系统和应用的可用性。常见的高可用衡量指标有 5 个 9、4 个 9、3 个 9，例如 5 个 9 即 99.999%，意味着每年只能有 (365 * 24 * 60) * (1 - 0.99999) = 5.256 分钟不可用。高可用的指标需要结合业务和成本来选择。

如何实现高可用？

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

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

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

下面来看一下这些问题。

1.MMM架构

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

MMM 提供了什么功能？

• 监控 MySQL 主从复制健康情况（MMM 架构同一时间只有一台主服务器对外提供服务，另外的主备服务器只能用于查询）；
• 在主库出现宕机时进行故障转移并自动配置其他从对新主的复制。
  • 如何找到从库对应的新的主库日志点的日志同步点？如果存在多个从库出现数据不一致的情况如何处理？MMM 对这一块的处理并不安全。MMM 只是简单粗暴的找到新的主库当前的日志点，然后使从库对这个日志点进行同步。在一个繁忙的系统中使用 MMM 很有可能造成数据丢失的情况。
• 提供了一个写虚拟 IP 和多个读虚拟 IP，写虚拟 IP 只能在主数据库之间进行切换，读虚拟 IP 则可以在服务器所有主从节点进行切换。在主从服务器出现问题时可以自动迁移虚拟 IP。

MMM 架构图：

MMM 部署所需资源：

资源名称	数量	说明
主DB服务器	2	用于主备模式的主主复制配置
从DB服务器	0-N	可以配置0台或多台服务器，但不建议太多
监控服务器	1	用于监控MySQL复制集群
IP地址	2 * (N+1)	N为MySQL服务器的数量
监控用户	1	用于监控数据库状态的MySQL用户(replication client)
代理用户	1	用于MMM代理的MySQL用户(super, replication client, process)
复制用户	1	用于配置MySQL复制的MySQL用户(replication slave)

MMM 架构优点：

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

MMM 架构缺点：

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

2.MHA架构

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

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

MHA 提供了什么功能？

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

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 免认证配置，存在一定的安全隐患；
• 没有读负载均衡的功能。

3.读写分离和负载均衡

为什么要读写分离？

写负载是不能够分担的，而且只能在主库上进行写操作。读操作主从上都可以。为了分担主库的读负载，就需要进行读写分离，写操作只能在主库上进行，而读操作尽量的在从库上完成。

而完成读写分离后，对于读操作，如何分配多个服务器的读操作，这就需要我们进行读的负载均衡了。

目前实现读写分离有两种方式：

• 由程序实现读写分离；
• 由中间件实现读写分离，例如 mysql-proxy（高并发下存在一定的问题）、maxScale（相对来说性能损耗小）；

-	优点	缺点
由程序实现读写分离	1、由开发人员控制什么样的查询在从库中执行，比较灵活； 2、由程序直接连接数据，性能损耗比较少； 3、架构简单；	1、增加了开发的工作量，使程序代码更加复杂； 2、人为控制，容易出现错误；
由中间件实现读写分离	1、由中间件根据查询语法分析，自动完成读写分离； 2、对程序透明，对于已有程序不用做任何调整；	1、由于增加了中间层，所以对查询性能有损耗（经测试，QPS可能降低50%-70%）； 2、对于延迟敏感的业务，无法自动在主库执行，不灵活；

4.分表分库

随着业务的不断增长，数据库中的数据也会越来越多，数据库的压力会越来越大，我们会发现，在业务繁忙的时候，数据库的性能会直线下降，这时为了保证良好的性能，需要想办法分担数据库的压力。分担数据库的读负载可以使用主从复制的方式，增加只读从数据库，通过读写分离的方式把数据库的读负载分担到不同的从数据库中，这时在一段时间内已经可以解决问题了。随着业务的发展，会发现，单一的主数据库已经无法承担写负载了，那么这时就需要对单一的主数据库进行拆分了，通常来说，对主数据库的拆分有下面几种方式：

• 把一个实例中的多个数据库拆分到不同的实例；
• 把一个库中的表分离到不同的数据库中；

1. 数据库分片

为了解决上面的问题，我们需要对一个库中的相关表进行水平拆分到不同实例的数据库中，即需要进行分片处理，我们通常所说的分库分表，大多数情况下指的就是这种方式。

数据库分片后，数据通常是存放在不同的物理节点上，数据库的分片并不像我们前面所说的第二种分库分表的方式容易实现，要对原来一个独立的数据库进行分片，我们需要考虑很多问题，通常来说，不是万不得已，不建议对数据库进行分片。分片后数据库会变得难以维护。

例如对订单表进行分片，会将订单表分成多个相同表结构的订单表，多个订单表可能会分布在不同的物理节点中：

数据库分片前的准备：

在进行数据库分片前，最重要的一项工作就是如何选择分区键。分区键决定了我们如何对数据库进行分片，以及分片后如何查询数据。分区键选择的是否合适直接决定了分区后数据库的性能，对于分区键的选择，我们应该做到以下几点：

• 分区键要能尽量避免跨分片查询的发生；
• 分区键要能尽量使各个分片中的数据平均。

如何存储无需分片的表？

• 方案一：每个分片中存储一份相同（冗余）的数据。这种方式可以更好的提高查询效率。如果使用这种方式，对于维护每个分片中相同表的一致性就显得非常重要了，一般可以采用多写的方式维护数据；
• 方案二：使用额外的节点统一存储。好处是不存在数据冗余问题。如果分片的表与统一存储的表需要关联查询时，就只能由程序分别查询后进行合并操作了，查询效率比方案一要差一些。

如何在节点上部署分片？

• 方案一：每个分片使用单一数据库，并且数据库名也相同；
• 方案二：将多个分片表存储在一个数据库中， 由于数据的表不能重名，所以需要在表名上加入分片号后缀；
• 方案三：在一个节点中部署多个数据库，每个数据库包含一个分片。

如何分配分片中的数据？

• 方案一：按分区键的 Hash 值取模来分配分片数据。优点是可以相对平均的，在各个分片中分配数据，缺点是很难人为的控制什么样的数据分配到哪个分片中；
• 方案二：按分区键的范围来分配分片数据。常用于分区键是日期类型或数值类型的情况，优点是可以很清楚的知道某条数据分配到了哪个分片中，缺点是很容易产生数据分配不平均和数据访问量不平均的情况；
• 方案三：利用分区键和分片的映射表来分配分片数据，这张映射表需要使用缓存的方式进行缓存，否则这张映射表就可能会成为系统的瓶颈。

如何生成全局唯一ID？

• 方案一：使用 auto_increment_increment 和 auto_increment_offset 参数；
• 方案二：使用全局节点来生成ID；
• 方案三：使用 Redis 等缓存服务器中创建全局 ID。

分片工具？oneProxyp。

5.数据库监控

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

本篇主要关注 MySQL 数据库我们都要监控些什么？和怎么对这些要监控的资源进行监控？

了解了这些之后，不管使用任何监控软件，都可以自己完成对 MySQL 脚本的开发与部署。

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

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

1.数据可用性监控

首先我们先来看下如何确认数据库是否可以通过网络进行连接。使用 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的值，记录了当前数据库的连接数量

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

2.数据性能监控

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

对于性能监控来说，可能关注最多的就是 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'

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

通常情况下，数据库系统的性能会随着并发处理请求数量的增加而下降。所以并发请求数量通常还需要和 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