目录
4.2.1 Slow query log数据源配置(预备工作)
4.2.2 Performance Schema数据源配置(预备工作)
1. PMM介绍
Percona Monitoring and Management (PMM)是一个针对MySQL、PostgreSQL和MongoDB的开源数据库监控、管理和可视化解决方案的工具，分为服务端和客户端。
- PMM有多种安装方式，我以docker方式安装
- PMM客户端安装在你需要监控的主机里
2. 安装PMM服务端
官方文档安装
2.1 安装docker
2.1.1 下载docker
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/bin2.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镜像下载地址
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.12.2.3 查看镜像
[root@localhost soft]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
percona/pmm-server 2.41.1 a71c917d72f2 2 months ago 2.34GB2.2.4 创建容器
[root@localhost bin]# docker create --volume /srv \
> --name pmm-data percona/pmm-server:2.41.1 /bin/true2.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.12.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客户端
官方文档安装客户端
3.1 下载客户端
我下载的是二进制文件，下面是下载地址，选择版本号和linux generic
3.2 上传解压
[root@localhost soft]# tar -zxvf pmm2-client-2.41.1.tar.gz3.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_profile3.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 420014. 添加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=33064.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-511407ea544d4.3.5 删除服务
pmm-admin remove <service-type> <service-name>
[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