移动云基于 MySQL Galera 的 PXC 运维实战

本文由 dbaplus 社群授权转载。

前言

在众多的 MySQL 开源软件中，Galera 是非常有特色的，它的特点及优势是具有良好的并发性和一致性。Galera Cluster 的主要用途是为 MySQL 提供一致性的集群化解决方案，以一个 dlopenable 通用复制库的形式提供给 MySQL，并通过自身的 Write-Set 提供复制服务，实现 MySQL 的多线程并行复制。此外，它自带集群节点管理机制，可以主动监测集群节点状态，自动管理有问题的数据节点，同时也可以实现集群的多点写入和平滑扩容。它对待事务的行为时，要么在所有节点上执行，要么都不执行，这种实现机制决定了它对待一致性的行为非常严格，能够非常完美地保证 MySQL 集群的数据一致性。

目前，对 Galera Cluster 的封装有两个，虽然名称不同，但实质都是一样的，使用的都是 Galere 群集。一个是 MySQL 的创始人 Monty 在自己全新的 MariaDB 上实现的 MariaDB Cluster，一个是著名的 MySQL 服务和工具提供商 Percona 实现的 Percona Xtradb Cluster，简称为 PXC。

从 2016 年开始，我参与了“移动云”的 MySQL 数据库运维管理工作。“移动云”是一个不断发展壮大的云服务供应商，订单和用户数据非常重要，随着“移动云”在网用户数量的不断增长，对数据库的高可用性和数据一致性提出了更高的要求。经过长期研究，不断地试错，终于在 Galera 的基础上，实现了一套自己的 MySQL 运维方案，截止到现在，已经有相当数量的线上集群运行着经过标准化改造的 PXC，在这个过程中，我们也积累了很多 Galera 的技术经验，希望这些经验也能帮助其他 Galera 使用者解决疑难或规避问题。

PXC

Percona XtraDB Cluster 是一个完全开源的 MySQL 的高可用性解决方案。它将 Percona Server 和 Percona XtraBackup 与 Galera 库集成，以实现同步多主复制。集群由节点组成，其中每个节点包含同一组数据同步的跨节点。推荐的配置是至少有 3 个节点。每个节点都是常规的 MySQL 服务器实例（例如 Percona Server）。可以将现有的 MySQL 服务器实例转换为节点，并使用此节点作为基础来运行集群。还可以从集群中分离任何节点，并将其用作常规的 MySQL 服务器实例。

集群为多主的模式，三个节点之间完全是对等的，都可以作为主节点，用户可以使用结构化查询语言（SQL）对数据进行修改和查询。该系统采用 share nothing 的架构，每个节点都可以提供读写服务。任何节点的修改都会自动同步到所有节点，当有客户端在某个节点写入数据时，集群会将新数据自动同步到其它节点，具有严格的数据一致性。

High Availability

集群节点间通过同步复制进行数据同步，通过心跳实现异常节点的检测和剔除。配合上层的负载均衡，可以实现集群的高可用，单个节点宕机不会影响服务。在具有 3 个节点的基本设置中，将任何节点关闭，Percona XtraDB Cluster 仍可以继续工作。在任何时间点，可以关闭任何节点以执行维护或更改配置。即使在计划外情况（如节点崩溃或它网络变得不可用），群集会继续工作，可在工作节点上运行查询。

如果在节点关闭时对数据进行了更改，则节点在加入时可以使用两个选项加入集群：

1、State Snapshot Transfer（SST） 是将所有数据从一个节点复制到另一个节点的过程。当新节点加入群集并从现有节点接收所有数据时，通常使用 SST。Percona XtraDB 群集中有三种 SST 方法：

• mysqldump

• rsync

• xtrabackup

mysqldump 和 rsync 的缺点是，你的集群在数据存在时变成 READ-ONLY 复制（SST 应用 FLUSH TABLES WITH READ LOCK 命令）。

SST 使用 XtraBackup 不需要 READ LOCK 命令整个同步过程中，只要同步.FRM 文件（一样定期备份）。

2、Incremental State Transfer（IST） 是指仅将增量更改从一个节点复制到另一个节点。即使在群集没有锁定为只读状态，SST 在正常操作服务时可能会侵入和干扰。IST 可以避免这样。如果 1 个节点在很短的时间出现故障，它只能获取发生在它失效时发生的那些更改。IST 是在节点上使用高速缓存机制来实现的。每个节点包含一个缓存，环形缓冲区（大小可配置）最后 N 个更改，并且节点间能够传输该高速缓存的一部分。显然，只有当转移所需的变化量小于 N 时 IST 才能完成。如果它超过 N 则加入节点必须执行 SST。

可以使用以下命令监视节点的当前状态：

SHOW STATUS LIKE 'wsrep_local_state_comment';

当节点处于 Synced（6）状态时，它是集群的一部分并准备处理流量。

Multi-Master Replication

多主复制意味着可以写入任何节点并确保写入对集群中所有节点都是一致的。这与常规 MySQL 复制不同，在常规 MySQL 复制中，您必须将写入应用于 master 以确保它被同步。

PXC 同步复制原理

• 事务在本地节点执行时采取乐观策略，成功广播到所有节点后再做冲突检测；

• 检测出冲突是，本地事务优先被回滚；

• 每个节点独立、异步执行队列中的 write set；

• 事务在本地节点执行成功返回客户端后，其他节点保证该事务一定会被执行，因此有可能存在延时，即虚拟同步。

PXC 的复制架构图（摘自官方文档）

对于多主复制，任何写入都在所有节点上提交或根本不提交。所有查询都在节点上本地执行，并且仅在 COMMIT 上有特殊处理。当 COMMIT 查询发出时，事务必须通过所有节点上的认证。如果它没有通过，你会收到 ERROR 作为响应。通过之后，事务在本地节点上应用。COMMIT 的响应时间包括以下内容：

• 网络往返时间；

• 认证时间；

• 本地申请。

注意：在远程节点上应用事务不会影响 COMMIT 的响应时间。

这种架构有两个重要的后果：

• 可以并行使用几个应用程序。这实现了真正的并行复制。使用 wsrep_slave_threads 变量配置的线程从机可以有多个并行。

• slave 可能有一个小的时间段不同步。这是因为 master 可以申请事件比 slave 更快。如果你从 slave 读取，可以读取尚未更改的数据。但可以通过设置 wsrep_causal_reads = ON 变量来更改。在这种情况下，在 slave 上读取将等待，直到事件被应用（会明显增加读取的响应时间）。Slave 和 Master 之间的差距是这种复制被称为虚拟同步的原因，而不是真正的同步复制。

所以如果运行写事务到两个不同的节点，集群将使用乐观锁。事务在个别查询期间不会检查可能的锁冲突，而是在 COMMIT 阶段，可能会收到 ERROR 响应。

Flow Control

前面了解了 PXC 是虚拟同步，事务在本地节点提交成功时，其他节点保证执行该事务。在提交事务时，本地节点把事务复制到所有节点，之后各个节点独立、异步地进行 certification test、事务插入待执行队列、执行事务。

然而由于不同节点之前执行事务的速度不一样，长时间运行后，慢节点的待执行队列可能会越积越长，最终导致事务丢失。PXC 继承了 Galera 的 flow control 机制，作用是协调各个节点，保证所有节点执行事务的速度大于队列增长的速度。实现原理是，集群中同时只有一个节点可以广播消息，每个节点都会获得广播消息的机会，当慢节点的执行队列超过一定长度后，它会广播一个 FC_PAUSE 消息，其他节点收到消息后会暂缓广播消息，知道该慢节点的执行队列长度减少到一定程度后，集群数据同步又开始恢复。

部署架构案例

PXC 部署架构分本地存储和网络存储两种情况。其中，采用本地存储的架构，其架构图如下图：

采用网络存储的架构，其架构图如下：

介绍完 PXC 的原理和架构，下面看一下具体的日常运维工作。

数据库巡检

数据库巡检的内容通常涵盖主机硬件、操作系统和 MySQL 巡检项。其中，主机/os 巡检主要包括：主机的硬件配置、CPU/内存/磁盘使用率以及磁盘的 I/O 使用情况；MySQL 巡检项包括：数据库配置、用户权限、大表数据量、业务表主键和自增长情况、数据库的并发性、当前和历史连接情况统计、备份执行情况以及日志记录和慢 SQL 的分析优化等。

1、查看 MySQL 服务器配置信息及运行状况

通过 show variables 来查看 mysql 服务器配置信息，例如 show variables like ‘%slow%’；用于查看慢查询，show variables like ‘max_connections’;；用于查看最大连接数。

通过 ps -ef | grep mysql 查看 mysql 进程运行状况。

2、通过 show status 统计各种 SQL 的执行频率

通过 show status 可以查看服务器状态信息。show status 可以根据需要显示 session 级别的统计结果和 global 级别的统计结果。

1）以下几个参数对 Myisam 和 Innodb 存储引擎都计数：

• Com_select 执行 select 操作的次数，一次查询只累加 1；

• Com_insert 执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次；

• Com_update 执行 update 操作的次数；

• Com_delete 执行 delete 操作的次数。

2）以下几个参数是针对 Innodb 存储引擎计数的，累加的算法也略有不同：

• Innodb_rows_read 执行 select 查询返回的行数；

• Innodb_rows_inserted 执行 insert 操作插入的行数；

• Innodb_rows_updated 执行 update 操作更新的行数；

• Innodb_rows_deleted 执行 delete 操作删除的行数。

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

3）以下几个参数便于我们了解数据库的基本情况：

• Connections 试图连接 Mysql 服务器的次数；

• Uptime 服务器工作时间；

• Slow_queries 慢查询的次数。

3、通过 show status 判断系统瓶颈

1）QPS(每秒 Query 量)

QPS = Questions(or Queries) / secondsmysql > show global status like 'Question%';

2）TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / secondsmysql > show global status like 'Com_commit';mysql > show global status like 'Com_rollback';

3）key Buffer 命中率

mysql>show global status like 'key%';key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

4）InnoDB Buffer 命中率

mysql> show status like 'innodb_buffer_pool_read%';innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads /   innodb_buffer_pool_read_requests) * 100%

5）Query Cache 命中率

mysql> show status like 'Qcache%';Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

6）Table Cache 状态量

mysql> show global status like 'open%';

比较 open_tables 与 opend_tables 值。

7）Thread Cache 命中率

mysql> show global status like 'Thread%';mysql> show global status like 'Connections';Thread_cache_hits = (1 - Threads_created / connections ) * 100%

创建用来处理连接的线程数。如果 Threads_created 较大，你可能要增加 thread_cache_size 值。缓存访问率的计算方法 Threads_created/Connections。

8）锁定状态

mysql> show global status like '%lock%';

Table_locks_waited/Table_locks_immediate 如果这个比值比较大的话，说明表锁造成的阻塞比较严重。

Innodb_row_lock_waits：innodb 行锁，太大可能是间隙锁造成的。

Table_locks_waited：不能立即获得的表的锁的次数。如果该值较高并且有性能问题，应首先优化查询，然后拆分表或使用复制。

9）Tmp Table 状况(临时表状况)

mysql >show status like 'Created_tmp%';

Created_tmp_disk_tables/Created_tmp_tables 比值最好不要超过 10%，如果 Created_tmp_tables 值比较大，可能是排序子句过多或者是连接子句不够优化。

10）Binlog Cache 使用状况

mysql > show status like 'Binlog_cache%';

如果 Binlog_cache_disk_use 值不为 0 ，可能需要调大 binlog_cache_size 大小。

11）Innodb_log_waits

mysql > show status like 'innodb_log_waits';

Innodb_log_waits 值不等于 0 的话，表明 innodb log buffer 因为空间不足而等待。

12）连接数大小——max_connections

mysql> show variables like 'max_connections';+-----------------------+-------+| Variable_name   | Value |+----------------------+--------+| max_connections | 500   |+---------------------+--------+        mysql> show global status like 'max_used_connections';+------------------------------+--------+| Variable_name        | Value |+------------------------------+--------+| Max_used_connections | 498   |+-----------------------------+--------+

设置的最大连接数是 500，而响应的连接数是 498 。

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

13）Handler_read_rnd

mysql> show status like 'Handler_read_rnd';

如果 Handler_read_rnd 太大 ，则你写的 SQL 语句里很多查询都是要扫描整个表，而没有发挥键的作用。

14）Key_reads

mysql> show status like 'key_read%';+--------------------------+---------+| Variable_name     | Value  |+--------------------------+---------+| Key_read_requests  | 1190  || Key_reads         | 2     |+--------------------------+---------+

如果 Key_reads 太大，则应该把 my.cnf 中 key_buffer_size 变大，可以用 Key_reads/Key_read_requests 计算出 cache 失败率。

15）Handler_read_rnd

mysql> show status like 'Handler_read_rnd';

根据固定位置读一行的请求数。如果正执行大量查询并需要对结果进行排序该值较高。可能使用了大量需要 MySQL 扫描整个表的查询或连接没有正确使用键。

16）Handler_read_rnd_next

mysql>show status like 'Handler_read_rnd_next';

在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

17）Select_full_join

mysql>show status like 'Select_full_join';

没有使用索引的联接的数量。如果该值不为 0, 你应仔细检查表的索引。

18）Select_range_check

mysql>show status like 'Select_range_check';

在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为 0 ，你应仔细检查表的索引。

19）Sort_merge_passes

mysql>show status like 'Sort_merge_passes';

排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加 sort_buffer_size 系统变量的值。

20）Handler_read_first

mysql>show status like 'Handler_read_first';

索引中第一条被读的次数。如果较高，它表明服务器正执行大量全索引扫描。例如， SELECT col1 FROM foo ，假定 col1 有索引。

21）Handler_read_key

mysql>show status like 'Handler_read_key';

根据键读一行的请求数。如果较高，说明查询和表的索引正确。

22）Handler_read_next

mysql>show status like 'Handler_read_next';

按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

23. Handler_read_prev

mysql>show status like 'Handler_read_prev';

按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY … DESC 。

24）Handler_read_rnd

mysql>show status like 'Handler_read_rnd';

根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要 MySQL 扫描整个表的查询或你的连接没有正确使用键。

4、打开相应的监控信息

1）error log

在配置文件 my.cnf 中进行配置，在运行过程中不能改变。

2）打开慢查询

set global slow_query_log='ON';

3）设置慢查询的时间阈值

set global long_query_time = 0.1;

4）未使用索引的 sql 语句打开

set global log_queries_not_using_indexes='ON

早期我们通过执行自动化脚本来输出巡检报告，巡检报告内容需要人工去检查和确认，执行巡检脚本主要包括以下步骤：

• 首先是，在本地下载 python3.5 并安装，打开后进入 python 解析器；

• 通过 pip 方式安装 python 依赖包；

• 因为脚本中需要获取 graphid，要登录 zabbix 管理网站去获取；

• 然后登录堡垒机，配置端口转发策略；

• 登录每一套数据库，创建数据库巡检账号；

• 最后在本地执行巡检脚本，并最终合成 word 巡检报告。

虽然比手工执行巡检，要简化很多，并且可以按资源池批量执行巡检，但总的来说过程还是略繁琐。目前我们在做的是采用平台化的方式，取代传统的脚本和工具巡检。通过搭建数据库管理平台，实现巡检管理，其中一键巡检功能用于数据库的例行巡检。可选择多台数据库批量执行巡检。巡检结果能在 web 页面上查看。并针对每个数据库实例会生成一份标准格式的巡检报告，报告可以从 web 页面直接下载。

另外，平台也提供了快速巡检的功能，用于对一些常规项巡检进行快速排查，常规巡检项主要是 DBA 根据以往故障处理的经验，总结下来的一些常用的排查项目，例如：processlist 当前连接情况核查，并且可以保留快照，数据库阻塞的情况核查、流控的情况检查、锁争用的核查以及一些临时表和询缓存等情况的检查，下图中的例子是对当前连接情况的排查，可以看到什么用户正连接数据库在执行哪些操作。

另外，作为对常规巡检项的补充，也提供了自定义巡检项的功能。可以执行一些临时的查询语句，并且结合了语义审核和转译的功能，能够保证 sql 语句的正确使用，查询结果可以从 web 页面上导出。下图中的例子是临时对数据库账号的进行查询，并导出结果。

复制引擎监控管理

1、打开复制引擎的调试信息-wsrep_debug

在运行过程中，可以通过 set global wsrep_debug = ‘ON’；来动态地打开 wsrep 的调试信息（调试信息会输入到错误日志中），可以帮助复制引擎定位问题。

2、Galera 集群监控

1）监控集群的一致性

mysql>show status like 'wsrep_cluster_state_uuid';

通过检查变量 wsrep_cluster_state_uuid 的值，确认此节点是否属于正确的集群。该变量的值在集群的各个节点中必须相同，如果某个节点出现不同的值，说明此节点没有连接到集群中。

mysql>show status like 'wsrep_cluster_conf_id';

通过检查变量 wsrep_cluster_conf_id 的值，用于查看集群发生变化的总数，同时确认此节点是否属于主集群。该变量的值在集群的各个节点中必须相同，如果某个节点出现不同的值，说明此节点脱离集群了，需要检查网络连接等将其恢复到一致的状态。

mysql>show status like 'wsrep_cluster_size';

通过检查变量 wsrep_cluster_size 的值，查看集群节点的总数。

mysql> show status like 'wsrep_cluster_status';

通过检查变量 wsrep_cluster_status 的值，查看节点的状态是否为 Primary，若不为 Primary，表示集群部分节点不可用，甚至可能是集群出现了脑裂。

如果所有节点的状态都不为 Primary，就需要重置仲裁，如果不能重置仲裁，就需要手动重启。

第一步，关闭所有节点

第二步，重启各个节点，重启过程中可以参考 wsrep_last_committed 的值确定主节点。

注：手动重启的缺点是会造成缓存丢失，从而不能做 IST。

2）监控节点状态

mysql> show status like 'wsrep_ready';

通过检查变量 wsrep_ready 的值，查看该节点的状态是否可以正常使用 SQL 语句。如果为 ON，表示正常，若为 OFF，需进一步检查 wsrep_connected 的值。

mysql> show status like 'wsrep_connected';

如果此变量的值为 OFF，说明该节点还没有加入到任何一个集群组件中，这很可能是因为配置文件问题，例如 wsrep_cluster_address 或者 wsrep_cluster_name 值设置错误，也可以通过查看错误日志进一步定位原因。

如果节点连接没有问题，但 wsrep_ready 的值还为 OFF，检查 wsrep_local_state_comment 的值。

mysql> show status like 'wsrep_local_state_comment';

当节点的状态为 Primary 时，wsrep_local_state_comment 的值一般为 Joining, Waiting for SST, Joined, Synced 或者 Donor，如果 wsrep_ready 为 OFF，并且 wsrep_local_state_comment 的值为 Joining, Waiting for SST, Joined 其中一个，说明此节点正在执行同步。

当节点的状态不为 Primary 时，wsrep_local_state_comment 的值应该为 Initialized。任何其他状态都是短暂的或临时的。

3）检测复制的健康状态

mysql> show status like 'wsrep_flow_control_paused';

通过检查变量 wsrep_flow_control_paused 的值，可以确认有多少 slave 延迟在拖慢整个集群的，从而查看复制的健康状态。这个值越接近 0.0 越好，优化的方法主要通过增加配置文件中 wsrep_slave_threads 的值，或者将复制很慢的节点剔除出集群。wsrep_slave_threads 取值可以参考 wsrep_cert_deps_distance，wsrep_cert_deps_distance 表示并发事务处理数的均值，wsrep_slave_threads 的值不应该比 wsrep_cert_deps_distance 高很多。

4）检测网络慢的问题

mysql> show status like 'wsrep_local_send_queue_avg';

通过检查变量 wsrep_local_send_queue_avg 的值，可以检测网络状态。如果此变量的值偏高，说明网络连接可能是瓶颈。造成此情况的原因可能出现在物理层或操作系统层的配置上。

5）集群监控通知扩展

通过 wsrep_notify_cmd 参数调用命令脚本的二次扩展。

wsrep 状态监控

mysql> show status like '%wsrep%';+------------------------------------------+-------------------------------------------------------+| Variable_name                | Value                                |+------------------------------------------+-------------------------------------------------------+| wsrep_local_state_uuid         | e8149a5c-636a-11e5-8b4b-67b16bb666a4   || wsrep_protocol_version         | 7                                    || wsrep_last_committed          | 526498                               || wsrep_replicated              | 526498                               || wsrep_replicated_bytes         | 238196578                            || wsrep_repl_keys              | 1926403                              || wsrep_repl_keys_bytes         | 27520685                             || wsrep_repl_data_bytes         | 176980021                            || wsrep_repl_other_bytes        | 0                                    || wsrep_received               | 7970                                 || wsrep_received_bytes          | 64791                                || wsrep_local_commits          | 526357                               || wsrep_local_cert_failures       | 0                                    || wsrep_local_replays           | 0                                    || wsrep_local_send_queue       | 0                                    || wsrep_local_send_queue_max   | 2                                    || wsrep_local_send_queue_min   | 0                                    || wsrep_local_send_queue_avg   | 0.000041                             || wsrep_local_recv_queue       | 0                                    || wsrep_local_recv_queue_max   | 4                                    || wsrep_local_recv_queue_min   | 0                                    || wsrep_local_recv_queue_avg   | 0.034504                              || wsrep_local_cached_downto    | 1                                    || wsrep_flow_control_paused_ns  | 22690449177                          || wsrep_flow_control_paused     | 0.000005                             || wsrep_flow_control_sent       | 0                                    || wsrep_flow_control_recv       | 371                                  || wsrep_cert_deps_distance      | 74.734609                            || wsrep_apply_oooe            | 0.000000                             || wsrep_apply_oool             | 0.000000                             || wsrep_apply_window          | 1.000000                             || wsrep_commit_oooe           | 0.000000                             || wsrep_commit_oool           | 0.000000                             || wsrep_commit_window        | 1.000000                             || wsrep_local_state             | 4                                    || wsrep_local_state_comment    | Synced                               || wsrep_cert_index_size        | 43                                   || wsrep_cert_bucket_count      | 126282                               || wsrep_gcache_pool_size       | 261431296                            || wsrep_causal_reads           | 0                                    || wsrep_cert_interval           | 0.000002                          || wsrep_incoming_addresses     | 10.130.7.5:3306,,10.130.7.4:3306      || wsrep_evs_delayed           |                                  || wsrep_evs_evict_list          |                                  || wsrep_evs_repl_latency       | 0/0/0/0/0                           || wsrep_evs_state             | OPERATIONAL                    || wsrep_gcomm_uuid          | e813b31f-636a-11e5-90c7-0f6d378e1dfb || wsrep_cluster_conf_id        | 5                                  || wsrep_cluster_size           | 3                                  || wsrep_cluster_state_uuid      | e8149a5c-636a-11e5-8b4b-67b16bb666a4 || wsrep_cluster_status          | Primary                            || wsrep_connected            | ON                                || wsrep_local_bf_aborts        | 0                                  || wsrep_local_index           | 2                                  || wsrep_provider_name        | Galera                              || wsrep_provider_vendor       | Codership Oy <info@codership.com>    || wsrep_provider_version       | 3.11(rXXXX)                       || wsrep_ready                | ON                                |+----------------------------------------+---------------------------------------------------+58 rows in set (0.12 sec)

wsrep 相关参数含义介绍：

wsrep_local_state_uuid：存储于该节点的 UUID 状态

wsrep_protocol_version：wsrep 协议使用的版本

wsrep_last_committed：最后提交事务的序列号

wsrep_replicated：发送到其他节点的 writesets 总数

wsrep_replicated_bytes：发送到其他节点的 writesets 总字节数

wsrep_repl_keys：复制 keys 总数

wsrep_repl_keys_bytes：复制 keys 总字节数

wsrep_repl_data_bytes：复制数据的总字节数

wsrep_repl_other_bytes：其他复制的总字节数

wsrep_received：从其他节点接收的 writesets 总数

wsrep_received_bytes：从其他节点接收的 writesets 总字节数

wsrep_local_commits：该节点提交的 writesets 总数

wsrep_local_cert_failures：认证测试中失败的 writesets 总数

wsrep_local_replays：因非对称锁粒度回放的事务数

wsrep_local_send_queue：当前发送队列的长度，表示等待被发送的 writesets 数

wsrep_local_send_queue_avg：网络瓶颈的预兆。如果这个值比较高的话，可能存在网络瓶

wsrep_local_recv_queue：当前接收队列的长度，表示等待被使用的 writesets 数

wsrep_local_recv_queue_avg：表示 slave 事务队列的平均长度，slave 瓶颈的预兆

wsrep_local_cached_downto：gcache 的最小序列号，这个变量可以用来判断是用 IST，还是 SST。如果此值为 0，表示 gcache 中没有 writesets

wsrep_flow_control_paused_ns：表示复制停止了多长时间，以纳秒为单位

wsrep_flow_control_paused：表示复制停止了多长时间。即表明集群因为 Slave 延迟而慢的程度，值为 0~1，越靠近 0 越好，值为 1 表示复制完全停止。可优化 wsrep_slave_threads 的值来改善

wsrep_flow_control_sent：表示该节点已经停止复制了多少次

wsrep_flow_control_recv：表示该节点已经停止复制了多少次

wsrep_cert_deps_distance：有多少事务可以并行应用处理。wsrep_slave_threads 设置的值不应该高出该值太多

wsrep_apply_oooe：并发执行效率，writesets 应用于 out-of-order 的频率

wsrep_apply_oool：大序列值的 writeset 比小序列值的 writeset 多出的执行频率

wsrep_apply_window：同时使用的最高序列值和最小序列值间的平均差值

wsrep_commit_oooe：事务脱离队列的频率

wsrep_commit_window：同时提交的最大序列值和最小序列值间的平均差值

wsrep_local_state：galera 状态值

1 - Joining (requesting/receiving State Transfer) –表示此节点正在加入集群

2 - Donor/Desynced –表示正在加入的节点是 donor

3 - Joined –表示节点已经加入集群 r

4 - Synced –表示节点已经和集群同步

wsrep_local_state_comment：galera 状态，如果 wsrep_connected 为 On，但 wsrep_ready 为 OFF，则可以从该项查看原因

wsrep_cert_index_size：certification 索引的 entries 数量

wsrep_cert_bucket_count：哈希表中 certification 索引的 cells 数

wsrep_gcache_pool_size：page pool 或者为 gcache 动态分配的字节数

wsrep_causal_reads：writesets 处理数

wsrep_incoming_addresses：以逗号分隔显示集群中的节点地址

wsrep_evs_repl_latency：提供集群节点间通信复制延迟信息

wsrep_evs_delayed：被剔除出集群的 UUID

wsrep_evs_evict_list：有延迟的节点列表

wsrep_evs_state：EVS 协议状态

wsrep_gcomm_uuid：galera 的 view_id，不同于集群的 uuid，在 gvwstate.dat 可以查看到

wsrep_cluster_conf_id：集群成员发生变化的数目，正常情况下所有节点上该值是一样的。如果值不同，说明该节点被临时"分区"了。当节点之间网络连接恢复的时候应该会恢复一样的值

wsrep_cluster_size：集群中的节点数目，如果这个值跟预期的节点数一致，则所有的集群节点已经连接

wsrep_cluster_state_uuid：集群的 UUID 值，在集群所有节点的值应该是相同的，有不同值的节点，说明其没有连接入集群

wsrep_cluster_status：集群节点的状态。如果不为"Primary"，说明出现"分区"或是"split-brain"状况，可能的取值为:Primary、Non-Primary、Disconnected

wsrep_connected：节点是否连接到集群，如果该值为 Off，且 wsrep_ready 的值也为 Off，则说明该节点没有连接到集群。(可能是 wsrep_cluster_address 或 wsrep_cluster_name 等配置错造成的。具体错误需要查看错误日志)

wsrep_local_bf_aborts：被其他节点上的事务终止的正在执行的本地事务数

wsrep_local_index：集群节点索引

wsrep_provider_name：wsrep 程序提供者

wsrep_provider_vendor：wsrep 供应商

wsrep_provider_version：wsrep 程序提供者的版本

wsrep_ready：节点是否可以提供查询。该值为 ON，则说明可以接受 SQL 负载。如果为 Off，则需要检查 wsrep_connected

PXC 备份管理

对于我们来说，数据库的备份和恢复是很日常的工作。因为平时难免会遇到服务器宕机、磁盘损坏等情况，在这种情况下，要保证数据不丢失或者最小程度的丢失，平时进行有效的备份就显得非常重要了。

因此对于 DBA 来说，备份工具的使用、备份策略的选择以及备份系统的完善都是需要特别关注的，另外，像备份文件校验通常是比较容易忽视的问题，出现故障时发现备份文件不可用，会造成很严重损失。

我们平时常用的备份工具是 mysqldump、Percona Xtrabackup，分别用于逻辑备份和物理备份，其实大多数 DBA 的备份/恢复体系都是围绕这两个工具展开的。

早期我们通过在数据库本地，定期执行脚本的方式进行备份，策略是：每周日凌晨 2 点执行一次全量备份，周一到周六每天凌晨 2 点执行增量备份，在本地存储空间充足的情况下，要求至少要求保留 1 个月的备份数据，备份恢复测试是通过在备份恢复测试机上面执行‘测试脚本’，每周六分时段从各个数据库拉取备份文件到本机进行恢复测试，并通过日志记录恢复操作是否成功。

那当面对成百上千个 MySQL 实例的维护，以上备份恢复方式会有哪些问题呢？我们具体来看一下。

1、首先是对本地存储空间需求较大，并且占用服务器系统总线，内存，CPU，磁盘 IO 资源，使得备份对线上业务有一定的影响。

在现网环境中，由于本地磁盘空间有限，通常本地仅保留一个月左右的备份数据，对于更早的数据如无特殊需求，到后期会自动删除，对于较重要的数据要保留更久要通过远程备份实现，不过在远程备份时，备份传输引发的网卡流量会对线上业务造成影响，需要考虑到网卡的能力。这时可以考虑使用双网卡，一块用于备份，一块用来提供线上服务。如果没有这个条件，要通过在备份时限速来达到目的。

2、其次是，集中化管理缺失，备份节点较多，备份方式多样化，备份完成情况、占用空间大小、完成时间以及校验结果等内容的记录和呈现也不够直观，缺少图形化界面。突发故障或变更前的临时备份依然靠手工执行，存在效率低和安全性差的问题。

3、集群备份节点选择问题。备份或多或少对线上业务都有影响，建议备份任务在 slave 或只读节点上执行， 那么当集群发生主备切换，如果备份节点没有动态进行切换，导致在写库上进行备份，使线上业务受备份操作影响。

为了解决上述问题，我们还是采用平台化的方式，通过开发来实现集中化管理，包括：备份执行与恢复管理、历史备份查看以及备份策略修改和管理等功能。

另外想要提一下的是容灾，容灾是指在备份的基础上建立一个异地的数据系统，这个系统是本地关键应用数据的一个实时复制。

在灾难发生时，可以支持自动和手工灾备切换功能，保正业务的连续性。

PXC 常见故障排查和处理方法

节点宕机

当集群中出现读写服务节点宕机的情况时，应该按如下所述步骤进行处理，以对外提供服务。

1）读写服务节点宕机

① 查看集群各节点状态：ps –ef | grep rdb

结果：读写节点(RW3)进程不存在，其他节点服务正常

② 查看 error log 日志，检查宕机原因

③ 重启 RW3

④ 启动完成后，确认 RW3 状态是否正常

2）某两个读写服务节点宕机

① 查看集群各节点状态：ps –ef | grep rdb

结果：读写节点(RW2、RW3)进程不存在，RW1 节点服务正常

② 查看 error log 日志，检查宕机原因

③ 此时，RW1 读写服务节点正常工作，重启 RW2 和 RW3

④ 启动完成后，确认 RW2 和 RW3 状态是否正常

3）读写服务节点都宕机

① 查看集群各节点状态：ps –ef | grep rdb

结果：读写节点（RW1、RW2、RW3）进程均不存在，此时集群无法提供正常服务

② 查看 error log 日志，发现最后宕机的是 RW3

③ 关闭集群：kill -15 PID

④ 重新启动最后宕掉的读写服务节点 RW3

⑤ RW3 状态恢复正常后，根据实际负载情况判断是否继续启动 RW2 和 RW1，预判如果可能做 SST，由于做 donor 的节点无法提供服务，服务恢复时间比较长，可以先不起后面的节点，暂时只让 RW3 提供服务，闲时再启动其他节点，这种情况下要注意限制数据库的连接数。启动 RW2 节点

⑥ 待数据同步结束，RW2 状态恢复正常后，启动 RW1 节点

⑦ 检查各个节点的状态，是否能正常提供服务

节点无响应

当集群中出现任一读写节点无响应时，应该按如下所述步骤进行处理，以对外提供服务。

1）负载高

主要查看以下几项：CPU 使用率，内存使用率，操作系统 IO，网络 IO，网络连接数等。对应的命令和工具为：SystemTap，LatencyTOP，vmstat, sar, iostat, top, tcpdump 等。通过观察这些指标，我们就可以定位系统的性能问题。具体检查顺序可参看下述步骤：

① 先看 CPU 使用率，如果 CPU 使用率不高，但系统的 Throughput 和 Latency 上不去，这说明应用程序并没有忙于计算，而是忙于别的一些事，比如 IO。（另外，CPU 的利用率还要看内核态的和用户态的，内核态的上去了，整个系统的性能就下来了。而对于多核 CPU 来说，CPU 0 是相当关键的，如果 CPU 0 的负载高，那么会影响其它核的性能，因为 CPU 各核间是需要有调度的，这靠 CPU0 完成）。

② 查看一下 IO 大不大，IO 和 CPU 一般是反着来的，CPU 利用率高则 IO 不大，IO 大则 CPU 利用率就低。关于 IO，我们要看三个事，一个是磁盘文件 IO，一个是驱动程序的 IO（如：网卡），一个是内存换页率。这三个事都会影响系统性能。

③ 查看一下网络带宽使用情况，在 Linux 下，你可以使用 iftop, iptraf, ntop, tcpdump 这些命令来查看，或是用 Wireshark 来查看。

④如果 CPU 不高，IO 不高，内存使用不高，网络带宽使用不高，但是系统的性能上不去。这说明你的程序有问题，比如，你的程序被阻塞了。可能是因为等哪个锁，可能是因为等某个资源，或者是在切换上下文。

通过了解操作系统的性能，我们才知道性能的问题，比如：带宽不够，内存不够，TCP 缓冲区不够等等，很多时候，不需要调整程序的，只需要调整一下硬件或操作系统的配置就可以了。

注：OS 常用查看命令

cpu – vmstat、top、sar

内存– ipcs、free

io – iostat、sar

网络– tcpdump、netstat –i、sar

预防措施：

• 合理调整数据库的参数；

• 应用上线前进行测试，优化后上线。防止应用大批量处理 sql，insert、select 等语句；

• 监控系统资源负荷情况；

• 限制报表并发查询数量，参照业务吞吐量。

处理方法：

① 排查执行时间较长的 sql 语句，步骤如下：

在各节点执行 show full processlist；将执行时间较长的 sql 语句及执行该语句的线程 ID(show full processlist 显示结果中的列 Id 值)记录下来；

② 针对记录下来的 sql 语句，与应用相关人员确认是否能够终止；

说明：对于发现执行时间较长且仍在执行的 select 语句，为了降低风险，在必要情况下可以直接终止；对于涉及数据更新的语句，需要根据实际情况进行相关处理（比如记录下 SQL 语句以便后续分析）；

③ 终止执行时间较长且已得到终止确认的 sql 语句，kill QUERY ID 或者 KILL ID(执行 sql 语句的线程 ID)；

④ 确认集群是否正常响应。

2）连接满

当连接数满时，用户连接不上数据库，当前正在接受读写的节点达到最大连接值会无法连接数据库，按照以下方法处理：

根据历史统计信息修改 max_connections。

方法一：

① 第 max_connections+1 连接只能由拥有 super privileges 用户登录，当连接数满时，拥有 super_priv 的用户登陆数据库修改 max_ connections 值：

set GLOBAL max_connections=XXXX;修改完成后实时生效，无需重启数据库。

② 进入配置文件 my.cnf，设置 max_connections 值，该值与步骤一的值相同。

方法二：

进入配置文件 my.cnf，设置 max_connections 值，并重启该节点服务。

3）有锁表情况

预防措施：

① 监控锁等待数量，暂设置 10 个报警；

② 避免业务忙时进行批量更新操作。

当出现有锁表情况而导致数据库响应慢的情况时，应该按如下所述步骤进行处理：

方法一：

① 获取锁表情况的相关信息，步骤如下：

• (a) 以 root 用户登陆到各主节点机器上；

• 调用客户端连接工具；

• 在各主节点的 mysql 提示符下执行 use information_schema；

• 在各主节点的 mysql 提示符下执行如下 sql 语句；

select a.trx_id ,a.trx_query , b.lock_datafrom innodb_trx a ,innodb_locks bwhere a.trx_id = b.lock_trx_id;

② 针对锁信息查询语句的查询结果，与应用相关人员确认是否可以 kill

③ 如果应用确认可以 kill，则 kill 对应的 sql

方法二：

调整 innodb_lock_wait_timeout 值。

集群分裂无法提供服务

当集群出现分裂的情况时，应该按如下所述步骤进行处理，以对外提供服务。

1）集群中有节点状态是 Primary

① 使用以下命令查看集群状态，查找状态为 non-Primary 的节点

SHOW STATUS where Variable_name="wsrep_cluster_status";+------------------------------+----------+| Variable_name        | Value   |+-----------------------------+----------+| wsrep_cluster_status |non- Primary |  +--------------------------+---------------+

② 重启状态为 non-Primary 的节点

③ 检查各个节点的状态，是否能正常提供服务

2）集群中所有节点状态都不是 Primary

① 使用以下命令查看集群状态，发现集群已经整个分裂，状态均为 non-Primary

SHOW STATUS where Variable_name="wsrep_cluster_status";+-----------------------------+----------------+| Variable_name        | Value      |+-----------------------------+----------------+| wsrep_cluster_status   |non- Primary |     +-----------------------------+----------------+

② 重新执行选主。选主规则：以最后提交事务的数据库节点选为主，作为启动集群的主节点。同时，主节点对应的 seqno 也是最大的，可以通过以下命令查看

SHOW STATUS LIKE 'wsrep_last_committed';+---------------------------+---------+|Variable_name       |Value  |+--------------------------+---------+|wsrep_last_committed|409745|

③ 如果 RW1 是当前的主节点，则在 RW1 下执行下面的命令，重新引导 RW1 为 primary：

set global wsrep_provider_options =“pc.bootstrap=1”;Kill -15 pid 关闭其他节点，关闭后可以通过 grastate.dat 里的 uuid 和 seqno 判断是否会做 SST，kill -15 关闭的一般不会有 SST，只有 IST，可以立即重启其他节点

④ 首先启动 RW2 节点

⑤ 待数据同步结束，RW2 状态恢复正常后，启动 RW3 节点

⑥ 检查各个节点的状态，是否能正常提供服务

其他异常

1）集群因断电宕机

① ping 服务器端 IP 地址失败

② 联系网络管理员或系统管理员进行处理，重启服务器

③ 重启数据库

通过查看各节点的日志，假设最后关闭节点是 RW1

重新启动最后关闭的读写服务节点 RW1

待数据同步结束，RW1 状态恢复正常后，启动 RW2 节点

待数据同步结束，RW2 状态恢复正常后，启动 RW3 节点

检查各个节点的状态，是否能正常提供服务

2）网络交换机故障造成集群对外连接中断

① ping 服务器端 IP 地址失败

② 查看/var/log/messages

③ 联系网络管理员或系统管理员进行处理

3）网络交换机故障造成集群内部连接中断

① ping 服务器端 IP 地址成功

② ping 集群内部各节点 IP 失败

③ 查看/var/log/messages

④ 联系网络管理员或系统管理员进行处理

4）磁盘故障造成集群无法提供服务

① 使用 smartctl 检测磁盘健康状态

② 联系系统管理员更换磁盘

③ 重启数据库

案例 1

去年 9 月 8 号，晚上 11 点左右，研发人员对数据库进行操作时，执行了 1 个事务，向用户注册表添加数据，这是一条 insert 语句，但是忘了提交，然后又执行了另一条 sql，去修改同一张表的表结构，前面没有提交的 insert 语句已对用户注册表添加了排他锁，导致后续大量 sql 语句等待执行，引发数据库阻塞，直到 30min 后第 1 个事务超时，数据库阻塞才解除。

我们当时在 11：08 收到 zabbix 告警，显示数据库活跃线程数已达到 139，一般活跃线程数超过 32 就会开始积压，这个跟 CPU 能处理的线程数有关，因此告警值设置为 32。初步排查原因为元数据索导致，11:07 用户开了一个 insert 语句没有提交，导致元数据锁。

元数据锁产生的原因，简单来说就是修改表数据的同时，修改表结构。为了避免这种情况，mysql innodb 在执行写入操作时会对表，添加排它锁，修改表结构，要等待锁释放后才能执行。这次故障处理，没有直接 kill 掉阻塞线程，因为按以往经验，这种方式可以解决阻塞，但也有一定概率会引起数据文件损坏，所以在阻塞事务即将超时的情况下，并没有做任何操作，而是等待事务超时后，数据库自动恢复。

案例 2

第二个例子是，去年 11 月 1 号，研发人员在数据库执行查询操作，因为使用排序产生临时表，又因为 instance 表，跟关联查询语句中的任何表都没有关联关系，导致笛卡尔积，生成的临时表文件过大最终将/目录占满，从而引发故障。

案例 3

第三个案例是由于网络设备故障引起的，存储网卡闪断导致数据库宕机，在去年 6 月 1 日上午 10:46，数据库进程故障，有 12 台数据库同时宕机，一线接到客户投诉‘华北节点控制台无法打开’，当时看了一下，由于 mysqld 进程属于非正常关闭，启动之前要登录集群 3 个节点，查看启动位置 sequence number,找出具有最完整数据的节点，作为集群第一个节点优先启动，然后再启动另外两个节点同步数据。

但是，在尝试执行 mysqld_safe 命令查看启动位置的时候失败了，继续排查，最后找到故障原因：数据库主机挂载块存储的目录变成只读状态，重新挂载后命令可以正常执行，然后将数据库陆续启动。

这次故障原因，在后面回溯的时候发现是因为数据库主机存储网卡与上层网络设备互联信息过程中发生了闪断，导致存储目录变为只读状态，最后引发故障。

小结

以上内容是在我们日常工作经验的基础之上，在平时维护 MySQL 的过程中，觉得需要引起注意或需要弄清楚的一些知识点，结合一些工作中的实际案例分享出来，希望能够对大家有所帮助。

作者介绍：

刘书浩，“移动云”DBA，负责“移动云”业务系统的数据库运维、标准化等工作；擅长 MySQL 技术领域，熟悉 MySQL 复制结构、Cluster 架构及运维优化；具有自动化运维经验，负责“移动云”数据库管理平台的搭建。

原文链接：

https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650781897&idx=1&sn=b5253cd2da3251c986b87a801fbb942e&chksm=f3f90d5cc48e844a806fac2ee626544ad01336d6119cc20203e9a958ec08f1fb6e05962e9d2e&scene=27#wechat_redirect