Prometheus客户端mysql监控mysqld_exporter

淡淡的烟草味﹌ 2022-09-12 04:56 333阅读 0赞

上一篇文章讲了监控docker，这一篇讲监控mysql

用到的exporter是官方的：[GitHub - prometheus/mysqld\_exporter: Exporter for MySQL server metrics][GitHub - prometheus_mysqld_exporter_ Exporter for MySQL server metrics]

默认端口9104，监控通过添加用户查询sql实现，无需重启mysql服务端

1.下载安装mysqld\_exporter

下载安装mysql\_exporter

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
    tar -C /usr/local -zxvf  mysqld_exporter-0.13.0.linux-amd64.tar.gz

建立软链接

ln -sv /usr/local/mysqld_exporter-0.13.0.linux-amd64/ /usr/local/mysqld_exporter

执行sql

#exporter'@'localhost'用户
    CREATE USER 'exporter'@'localhost' IDENTIFIED BY '000000' WITH MAX_USER_CONNECTIONS 3;
    GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
    GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';  
    FLUSH PRIVILEGES;
    
    #'exporter'@'%'用户
    CREATE USER 'exporter'@'%' IDENTIFIED BY '000000' WITH MAX_USER_CONNECTIONS 3;
    GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
    GRANT SELECT ON performance_schema.* TO 'exporter'@'%';  
    FLUSH PRIVILEGES;

这里有两个用户的命令，默认exporter'@'localhost'看有无结果，若没有用exporter'@'%'

在mysqld\_exporter创建mysql配置文件，运行时读配置文件登录mysql

vi /usr/local/mysqld_exporter/.my.cnf

内容如下

[client]
    user=exporter
    password=000000

设置开机自启服务

vi /usr/lib/systemd/system/mysqld_exporter.service

内容如下

[Unit]
    Description=https://prometheus.io
    
    [Service]
    Restart=on-failure
    ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf
    
    [Install]
    WantedBy=multi-user.target

启动和开机自启

systemctl daemon-reload
    systemctl start mysqld_exporter.service & systemctl enable mysqld_exporter

防火墙放行9104端口

firewall-cmd --zone=public  --add-port=9104/tcp  --permanent
    firewall-cmd --reload

metrics访问地址  
[http://10.24.11.202:9104/metrics][http_10.24.11.202_9104_metrics]  
ip改成自己的ip  
![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16][]

2.服务端prometheus.yml添加节点信息

编辑

vi /usr/local/prometheus/prometheus.yml

添加

- job_name: "202mysql"
        static_configs:
          - targets: ["10.24.11.202:9104"]
            labels:
              service: mysql-service

名称自己起，targets需要配置客户端的ip端口号

重启服务端

systemctl restart prometheus

查看target

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

3.Grafana添加仪表

然后导入仪表盘插件，先从官网找模板

这里推荐复制id 7362(MySQL Overview)

导入过程跟上一篇一样，仪表如图

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

[GitHub - prometheus_mysqld_exporter_ Exporter for MySQL server metrics]: https://github.com/prometheus/mysqld_exporter/
[http_10.24.11.202_9104_metrics]: http://10.24.11.202:9104/metrics
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/a10fe8f214644986b17772e9b6564915.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/41bc9a60d8aa45d2a926bab526df0fa9.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAZ3NsczIwMDgwOA_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/8b169df65cff405bbeaa7cf7c1853ffa.png