使用PMM图形化监控MySQL MGR动态

参考文档：https://mp.weixin.qq.com/s/5jQOgPKEvU0xFF9E59iz9w

PMM介绍

PMM 全称percona-monitoring-and-management，以下简称PMM PMM是Percona Monitoring and Management监控并支持MySQL多个分支版本，包括Percona Server for MySQL、Percona XtraDB Cluster、Oracle MySQL Community Edition、Oracle MySQL Enterprise Edition和MariaDB。
PMM其结构是C/S结构，由客户端pmm-agent和服务器端pmm-server组成

数据监控逻辑图

一、docker安装PMM

操作系统：Rocky Linux release 8.6 (Green Obsidian)

docker版本：Docker version 20.10.17

安装docker

yum-config-manager --add-repo  https://download.docker.com/linux/centos/docker-ce.repo
yum install -y yum-utils device-mapper-persistent-data lvm2 docker-ce
systemctl enable --now docker

在监控主机拉取pmm-server镜像

[root@docker-pmm ~]# docker pull percona/pmm-server:latest
latest: Pulling from percona/pmm-server
2d473b07cdd5: Pull complete 
bf7875dc8ab7: Pull complete 
Digest: sha256:975ad1b212771360298c1f9e0bd67ec36d1fcfd89d58134960696750d73f4576
Status: Downloaded newer image for percona/pmm-server:latest
docker.io/percona/pmm-server:latest
[root@docker-pmm ~]# docker images
REPOSITORY           TAG       IMAGE ID       CREATED       SIZE
percona/pmm-server   latest    3d3c8937808d   4 weeks ago   1.94GB
[root@docker-pmm ~]# docker ps -a
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
[root@docker-pmm ~]#

运行pmm-server容器

[root@docker-pmm ~]# docker ps -a
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
[root@docker-pmm ~]# 
[root@docker-pmm ~]# docker create -v /opt/prometheus/data \
> -v /opt/consul-data \
> -v /var/lib/mysql \
> -v /var/lib/grafana \
> --name pmm-data \
> percona/pmm-server:latest /bin/true
7a74887ddcc796a5a6fcf63a93f4362521e176bf5cb55d4b978e38179d3f9f41
[root@docker-pmm ~]# 
[root@docker-pmm ~]# docker run --detach --restart always \
> --publish 443:443 \
> --volumes-from pmm-data \
> --name pmm-server \
> percona/pmm-server:latest
786a09a68e4b1ebdd20637ce395db241dc3edd5f37fc1358ba38c3118fc401a8
[root@docker-pmm ~]# 
[root@docker-pmm ~]# docker ps -a
CONTAINER ID   IMAGE                       COMMAND                CREATED          STATUS                            PORTS                                           NAMES
786a09a68e4b   percona/pmm-server:latest   "/opt/entrypoint.sh"   6 seconds ago    Up 5 seconds (health: starting)   80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp   pmm-server
7a74887ddcc7   percona/pmm-server:latest   "/bin/true"            22 seconds ago   Created                                                                           pmm-data
[root@docker-pmm ~]#

通过浏览器访问pmm-server grafana网页 admin admin

二、在MGR集群节点安装

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum install -y pmm2-client

注册pmm-client客户端到pmm-server上即可

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.247.134:443
释义：
a)、192.168.247.134是PMM服务器的地址。
b)、443是默认端口号。
c)、admin/admin是默认的PMM用户名和密码。这是您用于登录PMM用户界面的同一个帐户
d)、server-insecure-tls客户必须使用安全通道向PMM服务器注册。如果使用http作为服务器URL，PMM将尝试通过https在端口443上进行连接。如果无法建立TLS连接，则会出现错误，您必须使用https以及相应的安全端口。

[root@mgr-node-01 ~]# pmm-admin config --server-insecure-tls --server-url=http://admin:admin@192.168.247.134:443
Warning: PMM Server requires TLS communications with client.
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.
[root@mgr-node-01 ~]# 

[root@mgr-node-02 ~]# pmm-admin config --server-insecure-tls --server-url=http://admin:admin@192.168.247.134:443
Warning: PMM Server requires TLS communications with client.
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.
[root@mgr-node-02 ~]# 

[root@mgr-node-03 ~]# pmm-admin config --server-insecure-tls --server-url=http://admin:admin@192.168.247.134:443
Warning: PMM Server requires TLS communications with client.
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.
[root@mgr-node-03 ~]#

连接mgr集群创建并授权pmm账号密码连接数据库

[root@mgr-node-01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 35377c98-2d8f-11ed-8232-000c29b917df | mgr-node-01 |        3306 | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | 81a478f2-2d87-11ed-bcc7-000c29549461 | mgr-node-02 |        3306 | ONLINE       | SECONDARY   | 8.0.26         |
| group_replication_applier | 8e04be2d-2d87-11ed-a9c4-000c290bfd7f | mgr-node-03 |        3306 | ONLINE       | SECONDARY   | 8.0.26         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

mysql> 
mysql> create user pmm@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> select * from performance_schema.setup_consumers  WHERE NAME LIKE '%statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
4 rows in set (0.00 sec)

mysql> SET persist innodb_monitor_enable = all;
Query OK, 0 rows affected (0.01 sec)

mysql>