一、背景
MySQL 8.0是当前Oracle公司一直在大力宣传的新版本，从架构到性能均有显著变化，同时，随着kubernetes的普及，为更好的提升资源利用率，可以进行MySQL上云的探索。MySQL上云如何进行运行状态的监控呢?MySQL运行状态监控需要满足：监控数据实时准备，报警机制响应迅速，支持异地集中监控。本文将探索云上MySQL的监控方案。
二、方案对比
方案一：
Zabbix监控系统，基于c+php开发的开源监控系统，支持多种监控采集方式，应用广泛，支持比较成熟，社区活跃，缺点是对容器支持度比较差。
方案二：
Prometheus监控系统，基于go开发的开源监控系统，支持pull和push两种采集模式，有完整的监控、报警、展示、数据异地传输能力，配置简单，对容器支持良好。
由于目前使用的MySQL在云上部署，而且公司现有对PaaS云监控基于Promehteus，因此，方案二是更佳选项，既能满足MySQL监控，又能充分利用现有资源。
三、Prometheus监控系统概述
Prometheus是由SoundeCloud公司基于go语言开发的一款开源的监控报警解决方案，基于时间序列监控数据。
1、组件及架构
组件：
- promethues server：主要获取和存储时间序列数据
- Exporters(导出器)：主要是作为agent收集数据发送到prometheus server，不同的数据收集由不同的exporters实
- pushgateway：允许短暂和批处理的jobs推送它们的数据到prometheus;然后由prometheus拉取数据。
- alertmanager：实现prometheus的告警功能。
组件间关系如下：
图1 组件架构图
2、Prometheus特点
- 指标收集：prometheus服务器定义了名为目标(target)的配置，执行抓取所需要的信息。
- 服务发现：可以通过通过多种方式来处理要监控的资源。包括：静态资源列表、基于文件发现、自动发现。
- 聚合和报警：在服务器上可以查询和聚合时间序列数据。通过规则记录常用的查询并做聚合。可以设置报警规则，满足报警条件时会触发报警，把报警信息推送的alertmanager。
- 自治：不依赖分布式存储，单个服务器节点是自主的。
- 冗余和高可用性：可部署多台prmehteus服务器，实现监控系统的高可用性。
- 查询语言：prometheus服务器提供了查询语言PromQL，用于对时序数据进行筛选和运算。
- 可视化：prometheus内置表达式浏览器可提供可视化，可与grafana配合实现监控数据可视化展示。
四、MySQL数据库监控
1、监控方案
Prometheus官方提供了mysqld_exporter导出器，可实现对MySQL监控。该导出器通过MySQL用户连接数据库，查询相关数据库表、状态信息，通过http服务的方式暴露监控数据。
方案不足：导出器可实现单节点和主从复制相关监控项，但对于MGR模式相关监控目前还不能很好地支持。
方案改进：prometheus提供了client libraries，可实现对监控指标进行定制化采集。故可用python语言定制脚本的方式采集MGR相关数据。mysqld_exporter与python脚本能够满足全部监控信息的导出。
2、部署方案
关于Paas云上MySQL监控部署，有两种方案：
方案一：
MySQL、mysqld_exporter、my_exporter_python监控脚本三部分同在一个镜像中，运行该容器可实现对MySQL的监控。
方案二：
MySQL、mysqld_exporter、my_exporter_python监控脚本分别属于不同的镜像，MySQL主容器与监控容器按顺序运行。监控容器以sidecar的方式访问MySQL。
方案对比：
MySQL数据库服务对于应用是非常重要的一环，要确保MySQL安全可靠。方案一，如果MySQL异常或出现错误，对问题诊断与排错方面，监控导出器可能会干扰项，不利于后期MySQL运维管理。方案二，由于三部分在不同的容器中运行，不会产生互相干扰的可能性，因此方案二为最佳。
五、监控具体实现
1、创建MySQL监控用户并授权
2、my_exporter_python脚本说明
9000端口提供http提供服务
start_http_server(9000)
设置Gauge对象
连接接MySQL查询数据
设置MGR相关的metrics
3、镜像拉取与定制
mysqld_exporter镜像pull：
docker pull prom/mysqld_exporter
my_exporter_python镜像制作
Dockerfile内容
FROM centos7_python36:v1
RUN pip install prometheus_client pymysql
RUN pip install requests
COPY ./my_exporter_python_v2.py /my_exporter_python_v2.py
WORKDIR /
EXPOSE 9000
CMD ["python","my_exporter_python_v2.py"]
4、镜像部署yaml文件部分内容：
- apiVersion: apps/v1
- kind: StatefulSet
- metadata:
- ......
- containers:
- - env:
- - name: TZ
- value: Asia/Shanghai
- - name: DATA_SOURCE_NAME
- value: 'exporter:userpassword@(localhost:3306)/'
- - name: TARGET
- value: 'http://localhost:9104/metrics'
- image: 'registry.paas.test.abc/library/mysqld-exporter-python:v5'
- imagePullPolicy: Always
- name: mysqld-python
- ports:
- - containerPort: 9000
- name: mysqld-python
- protocol: TCP
- resources:
- limits:
- cpu: '2'
- memory: 4Gi
- terminationMessagePath: /dev/termination-log
- terminationMessagePolicy: File
- - env:
- - name: TZ
- value: Asia/Shanghai
- - name: DATA_SOURCE_NAME
- value: 'testuser:userpassword@(localhost:3306)/'
- image: 'registry.paas.test.abc/library/mysqld-exporter:latest'
- imagePullPolicy: Always
- name: mysqld-exporter
- ports:
- - containerPort: 9104
- name: mysqld-exporter
- protocol: TCP
- resources:
- limits:
- cpu: '2'
- memory: 4Gi
- terminationMessagePath: /dev/termination-log
- terminationMessagePolicy: File
- ......
5、Prometehus server设置target
- job_name: kubernetes-pods
- scrape_interval: 30s
- scrape_timeout: 10s
- metrics_path: /metrics
- scheme: http
- kubernetes_sd_configs:
- - api_server: null
- role: pod
- namespaces:
- names: []
- relabel_configs:
- - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_scrape]
- separator: ;
- regex: "true"
- replacement: $1
- action: keep
- - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_path]
- separator: ;
- regex: (.+)
- target_label: __metrics_path__
- replacement: $1
- action: replace
- - source_labels: [__address__, __meta_kubernetes_pod_annotation_prometheus_io_port]
- separator: ;
- regex: ([^:]+)(?::\d+)?;(\d+)
- target_label: __address__
- replacement: $1:$2
- action: replace
- - separator: ;
- regex: __meta_kubernetes_pod_label_(.+)
- replacement: $1
- action: labelmap
- - source_labels: [__meta_kubernetes_namespace]
- separator: ;
- regex: (.*)
- target_label: kubernetes_namespace
- replacement: $1
- action: replace
- - source_labels: [__meta_kubernetes_pod_name]
- separator: ;
- regex: (.*)
- target_label: kubernetes_pod_name
- replacement: $1
- action: replace
六、采集指标解释
- 查询mysql上线时间
- mysql> show status like '%uptime%';
- +---------------------------+---------+
- | Variable_name | Value |
- +---------------------------+---------+
- | Uptime | 1284686 |
- | Uptime_since_flush_status | 1284686 |
- +---------------------------+---------+
Uptime即为mysql上线时间，单位为秒，对应输出的监控指标为：Mysql_uptime。可以对监控指标运算得到相应时间单位，例如转为天数，mysql_uptime/60/60/24。
- 查询mysql服务端口mysql
- mysql> show variables like 'port';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | port | 3306 |
- +---------------+-------+
对应输出监控指标为：mysql_global_variables_port
- 查看mysql服务器是否在线
如果mysqld_exporter连接mysql服务器成功，表示服务器在线，否则表示离线状态，对应输出的监指标：mysql_up。数值为1表示在线，数值0表示离线。
查看数据库连接数
- mysql> show status like 'Threads%';
- +-------------------+-------+
- | Variable_name | Value |
- +-------------------+-------+
- | Threads_cached | 2 |
- | Threads_connected | 1 |
- | Threads_created | 3 |
- | Threads_running | 2 |
- +-------------------+-------+
- mysql> show variables like '%max_connection%';
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | max_connections | 151 |
- | mysqlx_max_connections | 100 |
- +------------------------+-------+
- mysql> show global status like 'max_used_connections';
- +----------------------+-------+
- | Variable_name | Value |
- +----------------------+-------+
- | Max_used_connections | 3 |
- +----------------------+-------+
Thread_connected：表示打开的链接数，对应输出的监控指标为：mysql_global_status_threads_connected。
Threads_running：表示激活的连接数，并发数，对应输出的监控指标为：mysql_global_status_threads_running。
max_used_connections：表示当前使用过的最大连接数，对应输出的监控指标为：mysql_global_status_max_used_connections。
max_connections：表示并发执行的最大连接数，对应输出的监控指标为：mysql_global_variables_max_connections。
- 查看慢查询数量
- mysql> show global status like '%Slow_queries%';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Slow_queries | 0 |
- +---------------+-------+
对应输出监控指标为：mysql_global_status_slow_queries
此指标为当前慢查询的总数，如果想要更精确的显示慢查询额状态，可以使用promQL，将监控指标显示为每秒慢查询的数量，可以如下所示：irate(mysql_global_status_slow_queries[5m])，显示5分钟内，每秒慢查询的数量。
- 查询QPS
- mysql> show global status like 'questions';
- +---------------+--------+
- | Variable_name | Value |
- +---------------+--------+
- | Questions | 407158 |
- +---------------+--------+
Questions：表示为收到的总请求的次数，对应输出的监控指标为：mysql_global_status_questions。如果想要得到没秒请求的数量，可以如下方法所示：
irate(mysql_global_status_questions[5m])，显示5分钟内每秒请求的数量，即QPS。
- 查询innodb_buffer_pool命中率
- mysql> show global status like 'innodb_buffer_pool_read%';
- +---------------------------------------+-------+
- | Variable_name | Value |
- +---------------------------------------+-------+
- | Innodb_buffer_pool_read_ahead_rnd | 0 |
- | Innodb_buffer_pool_read_ahead | 0 |
- | Innodb_buffer_pool_read_ahead_evicted | 0 |
- | Innodb_buffer_pool_read_requests | 19268 |
- | Innodb_buffer_pool_reads | 887 |
- +---------------------------------------+-------+
Innodb_buffer_pool_reads：表示直接从磁盘读的次数，对应输出的监控指标为：
mysql_global_status_innodb_buffer_pool_reads。
Innodb_buffer_pool_read_requests：表示逻辑读的次数，
对应输出的监控指标为：
mysql_global_status_innodb_buffer_pool_read_requests。
计算逻辑读的命中率，公式为：100 - 100 * (mysql_global_status_innodb_buffer_pool_reads/
mysql_global_status_innodb_buffer_pool_read_requests)。
计算逻辑读的命中率，公式为：100 - 100 * (mysql_global_status_innodb_buffer_pool_reads/
mysql_global_status_innodb_buffer_pool_read_requests)。
- 查询打开表的数量
- mysql> show global status like 'open_tables';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Open_tables | 371 |
- +---------------+-------+
对应输出的监控指标为：mysql_global_status_open_tables
- 查询表缓存命中率
- mysql> show global status like 'threads_created';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | Threads_created | 3 |
- +-----------------+-------+
- mysql> show global status like 'connections';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Connections | 33479 |
- +---------------+-------+
Threads_created：表示创建过的线程数，对应输出的监控指标为：mysql_global_status_threads_created。
Connections：表示试图链接mysql服务器的次数，对应输出的监控指标为：mysql_global_status_connections。
表缓存命中率为：(1-mysql_global_status_threads_created/mysql_global_status_connections)*100 。
- 查询锁状态
- mysql> show global status like 'table_locks%';
- +-----------------------+--------+
- | Variable_name | Value |
- +-----------------------+--------+
- | Table_locks_immediate | 156335 |
- | Table_locks_waited | 0 |
- +-----------------------+--------+
Table_locks_immediate：表示行锁总数量，对应输出监控指标为：mysql_global_status_table_locks_immediate，可以计算每秒行锁数量，如：
irate(mysql_global_status_table_locks_immediate[5m])。
Table_locks_waited 表示为表锁数量，对应输出监控指标为：mysql_global_status_table_locks_waited。
- 查询临时表状态
- mysql> show global status like '%tmp%';
- +-------------------------+--------+
- | Variable_name | Value |
- +-------------------------+--------+
- | Created_tmp_disk_tables | 0 |
- | Created_tmp_files | 6 |
- | Created_tmp_tables | 111563 |
- +-------------------------+--------+
Created_tmp_disk_tables：表示为创建磁盘临时表数量，对应输出监控指标为：mysql_global_status_created_tmp_disk_tables。
Created_tmp_tables：表示服务器内部创建临时表的数量，对应输出指标为：mysql_global_status_created_tmp_tables。
临时表比例计算为：
mysql_global_status_created_tmp_disk_tables/mysql_global_status_created_tmp_tables。
python脚本实现监控指标及sql语句
mysql组复制相关信息记录在performance_schema库中的replicaion_conection_status、replication_group_member_stats、replication_group_members表中，通过关联查询能够得到组复制相关的监控项
- 查询当前mysql待应用的事务数
- SELECT
- @@GLOBAL .server_uuid,
- GTID_SUBTRACT(RECEIVED_TRANSACTION_SET,
- @@GLOBAL .GTID_EXECUTED)
- FROM
- performance_schema.replication_connection_status
- WHERE
- channel_name = 'group_replication_applier'
对应输出的监控指标为：mysql_mgr_apply_queue
- 查询当前mysql待认证的事务数
- SELECT
- MEMBER_ID, Count_Transactions_in_queue
- FROM
- performance_schema.replication_group_member_stats
- WHERE
- member_id = @@GLOBAL .server_uuid
对应输出的监控指标为：mysql_mgr_cert_queue
- 查寻当前mysql的节点状态，如果是online返回1，如果是offline返回2，如果是error返回3，如果是recovering返回4.
- SELECT
- member_id,
- CASE
- WHEN MEMBER_STATE = 'ONLINE' THEN 1
- WHEN MEMBER_STATE = 'OFFLINE' THEN 2
- WHEN MEMBER_STATE = 'ERROR' THEN 3
- WHEN MEMBER_STATE = 'RECOVERING' THEN 4
- ELSE 0
- END AS MEMBER_STATE
- FROM
- performance_schema.replication_group_members
- WHERE
- MEMBER_ID = @@GLOBAL .server_uuid
- OR MEMBER_ID = ''
对应输出的监控指标为：mysql_mgr_node_status
- 查询当前mysql节点的健康状态情况，如果online返回1，如果offline返回0。
- SELECT
- member_id,
- IF(MEMBER_STATE = 'ONLINE'
- AND ((SELECT
- COUNT(*)
- FROM
- performance_schema.replication_group_members
- WHERE
- MEMBER_STATE != 'ONLINE') >= ((SELECT
- COUNT(*)
- FROM
- performance_schema.replication_group_members) / 2) = 0),
- '1',
- '0')
- FROM
- performance_schema.replication_group_members
- JOIN
- performance_schema.replication_group_member_stats USING (member_id)
- WHERE
- member_id = @@GLOBAL .server_uuid
对应输出的监控指标为：mysql_mgr_node_health
- 查询当前mysql节点角色情况，如果是主库返回1，如果是非主库返回0。
- SELECT
- @@server_uuid,
- IF(@@GLOBAL .group_replication_single_primary_mode,
- (SELECT
- COUNT(1)
- FROM
- performance_schema.global_status
- WHERE
- variable_value = @@server_uuid),
- (SELECT
- COUNT(1)
- FROM
- performance_schema.replication_group_members
- WHERE
- member_id = @@server_uuid
- AND member_state = 'ONLINE')) AS isPrimary
对应输出的监控指标为：mysql_mgr_role
- 大事务查询
- SELECT
- @@GLOBAL .server_uuid, COUNT(trx_id)
- FROM
- information_schema.INNODB_TRX,
- sys.session AS se
- WHERE
- trx_mysql_thread_id = conn_id
对应输出的监控指标为：mysql_big_trx，由于此查询需要调用MySQL系统sys相关视图，所以需要为exporter额外授权。
- grant usage on *.* to exporter@'%';
- grant select,execute on sys.* to exporter@'%';
七、grafana面板
grafana是一款带面板展示效果的开源应用。通过配置拉取prometheus服务器的指标数据，支持时序数据的查询及展示。拥有查询编译器功能，能够对时序数据运算后进行可视化展示。只需把prometheus提供的http服务配置即可。以下为MySQL监控指标图像化展示效果。
