Linux MySQL专栏-监控(PMM)

拓扑图

注意：本次实验mysql-server，pmm-client，pmm-server  跑在同一主机上！

创建监控用户，开启慢查询

注意：时间一定要正确！

mysql -uroot -proot

select now();
CREATE   USER    'pmm'@'%'      IDENTIFIED      BY  "Pass@123";
grant all privileges  on  *.*  to 'pmm'@'%';
set  global    slow_query_log=ON;
set  session long_query_time=2;
SHOW VARIABLES LIKE  '%long_query%';
flush privileges;
exit

监控端（docker）

docker pull percona/pmm-server:2.41.2

docker create --volume /srv --name pmm-data percona/pmm-server:2.41.2  /bin/true
docker run --detach --restart always --publish 443:443 --volumes-from pmm-data --name pmm-server percona/pmm-server:2.41.2

docker ps -a

https://hostname:443  
admin:admin

被控端（docker）

docker pull percona/pmm-client:2.41.2

docker create --volume /srv --name pmm-client-data percona/pmm-client:2.41.2  /bin/true

PMM_SERVER=192.168.150.134:443

docker run \
--restart=always \
--network host \
--name pmm-client -itd \
-e PMM_AGENT_SERVER_ADDRESS=${PMM_SERVER} \
-e PMM_AGENT_SERVER_USERNAME=admin \
-e PMM_AGENT_SERVER_PASSWORD=admin \
-e PMM_AGENT_SERVER_INSECURE_TLS=1 \
-e PMM_AGENT_SETUP=1 \
-e PMM_AGENT_CONFIG_FILE=config/pmm-agent.yaml \
--volumes-from pmm-client-data \
percona/pmm-client:2.41.2

docker ps -a

[root@mysql ~]# docker exec pmm-client pmm-admin status
Agent ID : /agent_id/2fc6a4d3-a659-4ccf-8ca2-3ee8e9a9bd68
Node ID  : /node_id/e88c1934-c2f7-4bfb-9665-5c083568090f
Node name: mysql

PMM Server:
        URL    : https://192.168.150.134:443/
        Version: 2.41.2

PMM Client:
        Connected        : true
        Time drift       : -28.08µs
        Latency          : 719.518µs
        Connection uptime: 100
        pmm-admin version: 2.41.2
        pmm-agent version: 2.41.2
Agents:
        /agent_id/2ed3aca8-18c9-4749-a324-4b9b19535f4d vmagent Running 42000
        /agent_id/bd34f8e2-ba0d-4818-bfef-2b3a51056731 node_exporter Running 42001

下面是被监控主机ip和port，注意下！
[root@mysql ~]# docker exec pmm-client pmm-admin add mysql --username=pmm --password=Pass@123 --service-name=mysgl-192.168.150.134 --host=192.168.150.134 --port=3306
Connection check failed: dial tcp 192.168.150.134:3306: connect: connection refused.

问题出在密码验证方式上！

解决：

[root@mysql ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=1
user=mysql
port=3306
mysql_native_password=ON

basedir = /var/lib/mysql
datadir = /var/lib/mysql/data

log-error = /var/lib/mysql/mysqld.log

systemctl  restart  mysqld

mysql -uroot -proot

ALTER   USER    'pmm'@'%'      IDENTIFIED   WITH mysql_native_password    BY  "Pass@123";

再次执行好了！

docker exec pmm-client pmm-admin add mysql --username=pmm --password=Pass@123 --service-name=mysgl-192.168.150.134 --host=192.168.150.134 --port=3306

[root@mysql ~]# docker ps -a
CONTAINER ID   IMAGE                       COMMAND                  CREATED          STATUS                    PORTS                                           NAMES
b5db48a22870   percona/pmm-client:2.41.2   "/usr/local/percona/…"   5 minutes ago    Up 5 minutes                                                              pmm-client
03f68232540f   percona/pmm-client:2.41.2   "/usr/local/percona/…"   14 minutes ago   Created                                                                   pmm-client-data
c39bd5376393   percona/pmm-server:2.41.2   "/opt/entrypoint.sh"     22 minutes ago   Up 22 minutes (healthy)   80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp   pmm-server
90cdafcf5685   percona/pmm-server:2.41.2   "/bin/true"              23 minutes ago   Created    

验证

慢查询

mysql> SELECT SLEEP (3);
+-----------+
| SLEEP (3) |
+-----------+
|         0 |
+-----------+
1 row in set (3.00 sec)

mysql> SELECT SLEEP (3);
+-----------+
| SLEEP (3) |
+-----------+
|         0 |
+-----------+
1 row in set (3.00 sec)

mysql> SELECT SLEEP (5);
+-----------+
| SLEEP (5) |
+-----------+
|         0 |
+-----------+
1 row in set (5.00 sec)

3个慢查询，如下图所示！

主机情况监控！