PMM2 MySQL监控管理工具

目录

1. PMM介绍

2. 安装PMM服务端

2.1 安装docker

2.1.1 下载docker

2.1.2 上传docker包

2.1.3 启动守护进程

2.1.4 查看docker状态

2.2 安装PMM

2.2.1 下载镜像

2.2.2 load镜像

2.2.3 查看镜像

2.2.4 创建容器

2.2.5 运行镜像

2.2.6 验证PMM服务器

2.2.7 删除PMM服务

3. 安装PMM客户端 

3.1 下载客户端

 3.2 上传解压

3.3 配置环境变量

3.4 安装客户端

3.5 配置客户端

4. 添加mysql监控服务

4.1 创建用户

4.2 选择和配置数据源

4.2.1 Slow query log数据源配置(预备工作)

4.2.2 Performance Schema数据源配置(预备工作)

4.2.3 Query response time配置

4.2.4 User statistics配置

4.3 添加MySQL Service

4.3.1 添加Slow query log

4.3.2 添加Performance Schema

4.3.3 图形界面添加服务

 4.3.4 查看服务

4.3.5 删除服务

4.3.6 从PMM服务注销PMM客户端

5. 官网命令手册

6. 界面清单

7. 界面仪表盘管理

8. 告警管理

9. PMM架构 

---

1. PMM介绍

Percona Monitoring and Management (PMM)是一个针对MySQL、PostgreSQL和MongoDB的开源数据库监控、管理和可视化解决方案的工具，分为服务端和客户端。

• PMM有多种安装方式，我以docker方式安装
• PMM客户端安装在你需要监控的主机里

2. 安装PMM服务端

官方文档安装

Docker - Percona Monitoring and Management

2.1 安装docker

2.1.1 下载docker

https://download.docker.com/linux/static/stable/x86_64/

2.1.2 上传docker包

手动下载tar包后上传到Linux主机， 并将解压出来的docker文件内容移动到 /usr/bin/ 目录下

[root@localhost soft]# tar -zxvf docker-20.10.6.tgz
[root@localhost soft]# cp docker/* /usr/bin

2.1.3 启动守护进程

[root@localhost soft]# dockerd &

2.1.4 查看docker状态

查看是否成功，看到下面结果说明docker启动成功

[root@localhost soft]# docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

 

2.2 安装PMM

2.2.1 下载镜像

PMM服务端docker镜像下载地址

https://www.percona.com/downloads

2.2.2 load镜像

也可以不下载镜像，直接pull镜像，会从网上下载（docker pull percona/pmm-server:2.41.1）

[root@localhost soft]# docker load < pmm-server-2.41.1.docker
c83f386e1dda: Loading layer [==================================================>]  114.2MB/114.2MB
c3536f8fe5be: Loading layer [==================================================>]  2.254GB/2.254GB
Loaded image: percona/pmm-server:2.41.1

2.2.3 查看镜像

[root@localhost soft]# docker images
REPOSITORY           TAG       IMAGE ID       CREATED        SIZE
percona/pmm-server   2.41.1    a71c917d72f2   2 months ago   2.34GB

2.2.4 创建容器

[root@localhost bin]# docker create --volume /srv \
> --name pmm-data percona/pmm-server:2.41.1 /bin/true

2.2.5 运行镜像

[root@localhost bin]# docker run --detach --restart always \
> --publish 443:443 \
> --volumes-from pmm-data --name pmm-server \
> percona/pmm-server:2.41.1

2.2.6 验证PMM服务器

运行PMM Server之后，可以使用运行容器的主机的IP地址访问PMM Web界面，服务器ip:443 即可打开服务端webui，访问默认用户密码：admin / admin，第一次进去提示修改密码

2.2.7 删除PMM服务

#停止pmm-server容器
docker stop pmm-server
#删除容器
docker rm pmm-server pmm-data
#删除镜像
docker rmi $(docker images | grep "percona/pmm-server" | awk {'print $3'})

3. 安装PMM客户端 

官方文档安装客户端

Set up PMM Client - Percona Monitoring and Management 

3.1 下载客户端

我下载的是二进制文件，下面是下载地址，选择版本号和linux generic

https://www.percona.com/downloads

 3.2 上传解压

[root@localhost soft]# tar -zxvf pmm2-client-2.41.1.tar.gz

3.3 配置环境变量

[root@localhost local]# mkdir -p /usr/local/percona/pmm2
 
#添加$PMM_DIR/bin至环境变量配置文件
[root@localhost local]# vi ~/.bash_profile
export PMM_DIR=/usr/local/percona/pmm2
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/mysql/percona-toolkit-3.3.1/bin:$PMM_DIR/bin
[root@localhost local]# source ~/.bash_profile

3.4 安装客户端

[root@localhost local]# cd /soft/pmm2-client-2.41.1
[root@localhost pmm2-client-2.41.1]# ./install_tarball
Installing into /usr/local/percona/pmm2...

3.5 配置客户端

[root@localhost config]# pmm-agent setup --config-file=/usr/local/percona/pmm2/config/pmm-agent.yaml --server-address=192.168.26.101 --server-insecure-tls --server-username=admin --server-password=123456
[root@localhost config]# pmm-agent --config-file=${PMM_DIR}/config/pmm-agent.yaml

注册

pmm-admin config --server-insecure-tls --server-url=https://admin:123456@192.168.26.101:443

查看状态

[root@localhost ~]# pmm-admin status
Agent ID : /agent_id/cd9ebf97-a1c0-4386-8f97-1adb346efba5
Node ID  : /node_id/0309f2f0-d1e7-451e-9798-31964bfb9126
Node name: localhost.localdomain
 
PMM Server:
	URL    : https://192.168.26.101:443/
	Version: 2.41.1
 
PMM Client:
	Connected        : true
	Time drift       : 78.014µs
	Latency          : 426.998µs
	Connection uptime: 100
	pmm-admin version: 2.41.1
	pmm-agent version: 2.41.1
Agents:
	/agent_id/c61a3f9a-dab0-4806-af39-68403b73d25b node_exporter Running 42000
	/agent_id/d8e6f253-dd81-40c6-8ae3-91fd55f85a40 vmagent Running 42001

4. 添加mysql监控服务

4.1 创建用户

(root@localhost) [(none)]> CREATE USER 'pmm'@'%' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
(root@localhost) [(none)]> GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

4.2 选择和配置数据源

4.2.1 Slow query log数据源配置(预备工作)

/etc/my.cnf添加如下

[mysqld]
slow_query_log=1
log_output=FILE
long_query_time=2
log_slow_admin_statements=ON
log_slow_slave_statements=ON

或者session配置

SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_slow_admin_statements = 1;
SET GLOBAL log_slow_slave_statements = 1;

 percona mysql额外添加的配置参数

log_slow_rate_limit=100
log_slow_rate_type='query'
slow_query_log_always_write_time=1
log_slow_verbosity='full'
slow_query_log_use_global_control='all'

或者session配置

SET GLOBAL log_slow_rate_limit = 100;
SET GLOBAL log_slow_rate_type = 'query';
SET GLOBAL slow_query_log_always_write_time = 1;
SET GLOBAL log_slow_verbosity = 'full';
SET GLOBAL slow_query_log_use_global_control = 'all';

4.2.2 Performance Schema数据源配置(预备工作)

/etc/my.cnf添加如下参数

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all

或者session配置

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';
SET GLOBAL innodb_monitor_enable = all;

4.2.3 Query response time配置

/etc/my.cnf添加如下

[mysqld]
query_response_time_stats=ON

安装插件

INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';
SET GLOBAL query_response_time_stats = ON;

4.2.4 User statistics配置

/etc/my.cnf添加如下

[mysqld]
userstat=ON

或者session配置

SET GLOBAL userstat = ON;

4.3 添加MySQL Service

4.3.1 添加Slow query log

MYSQL_SERVICE是自己取的服务名字

[root@localhost ~]# pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GiB --username=pmm --password=pass MYSQL_SERVICE 192.168.26.101:3306
MySQL Service added.
Service ID  : /service_id/31afdb31-42ff-4b81-85f5-97d6ae222e86
Service name: MYSQL_SERVICE
 
Table statistics collection enabled (the limit is 1000, the actual table count is 338).
#或者
[root@localhost ~]# pmm-admin add mysql --query-source=slowlog --size-slow-logs=1GiB --username=pmm --password=pass --service-name=MYSQL_SERVICE --host=192.168.26.101 --port=3306

4.3.2 添加Performance Schema

MYSQL_PERFSCHEMA是自己取服务名字

[root@localhost ~]# pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass MYSQL_PERFSCHEMA 192.168.26.101:3306
MySQL Service added.
Service ID  : /service_id/cd3acc69-2372-4fa3-9cd4-455cb605344a
Service name: MYSQL_PERFSCHEMA
 
Table statistics collection enabled (the limit is 1000, the actual table count is 338).
#或者
[root@localhost ~]# pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_PERFSCHEMA  --host=192.168.26.101 --port=3306

4.3.3 图形界面添加服务

 4.3.4 查看服务

[root@localhost ~]# pmm-admin list
Service type        Service name            Address and port           Service ID
MySQL               MYSQL_PERFSCHEMA        192.168.26.101:3306        /service_id/cd3acc69-2372-4fa3-9cd4-455cb605344a
MySQL               MYSQL_SERVICE           192.168.26.101:3306        /service_id/e1b0dfc3-e1de-4108-94de-3e894f637199
 
Agent type                    Status           Metrics Mode        Agent ID                                              Service ID                                              Port
pmm_agent                     Connected                            /agent_id/cd9ebf97-a1c0-4386-8f97-1adb346efba5                                                                0 
node_exporter                 Running          push                /agent_id/c61a3f9a-dab0-4806-af39-68403b73d25b                                                                42000 
mysqld_exporter               Running          push                /agent_id/8506e3b2-a7bf-44fb-99ae-aebb85ab92a9        /service_id/cd3acc69-2372-4fa3-9cd4-455cb605344a        42011 
mysqld_exporter               Running          push                /agent_id/f118f570-e506-4568-a9fc-2ec886b2a3b9        /service_id/e1b0dfc3-e1de-4108-94de-3e894f637199        42010 
mysql_perfschema_agent        Running                              /agent_id/a5d784f0-cd1f-406a-ba39-9cb9082d756e        /service_id/cd3acc69-2372-4fa3-9cd4-455cb605344a        0 
mysql_slowlog_agent           Running                              /agent_id/83a30217-8175-4065-bf8f-d5c2ba58574e        /service_id/e1b0dfc3-e1de-4108-94de-3e894f637199        0 
vmagent                       Running          push                /agent_id/d8e6f253-dd81-40c6-8ae3-91fd55f85a40                                                                42001
 

或者

[root@localhost ~]# pmm-admin inventory list services
Services list.
 
Service type           Service name         Address and Port  Service ID
MySQL                  MYSQL_PERFSCHEMA     192.168.26.101:3306 /service_id/cd3acc69-2372-4fa3-9cd4-455cb605344a
MySQL                  MYSQL_SERVICE        192.168.26.101:3306 /service_id/e1b0dfc3-e1de-4108-94de-3e894f637199
PostgreSQL             pmm-server-postgresql 127.0.0.1:5432    /service_id/fcec30c3-7d0c-46b7-bd45-511407ea544d

4.3.5 删除服务

pmm-admin remove

[root@localhost ~]# pmm-admin remove mysql MYSQL_PERFSCHEMA
Service removed.
[root@localhost ~]# pmm-admin remove mysql MYSQL_SERVICE
Service removed.

4.3.6 从PMM服务注销PMM客户端

pmm-admin unregister --force

5. 官网命令手册

Commands - Percona Monitoring and Management

6. 界面清单

 PMM Inventory - Percona Monitoring and Management

7. 界面仪表盘管理

 Manage dashboards - Percona Monitoring and Management

8. 告警管理

 Percona Alerting - Percona Monitoring and Management

9. PMM架构 

Architecture - Percona Monitoring and Management