prometheus 监控之 mysql 篇（含mysql报警规则）

矫情吗；* 2022-05-13 05:06 2158阅读 0赞

架构（盗用一张图，嘿嘿）：  
![盗用一张图，嘿嘿][70]

mysql\_exporter的安装：

https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz
    tar -xvf mysqld_exporter-0.10.0.linux-amd64.tar.gz
    cd mysqld_exporter-0.10.0.linux-amd64
    cp -r mysqld_exporter /usr/local/bin/
    chmod +x /usr/local/bin/mysqld_exporter

mysqld\_exporter需要连接Mysql，首先为它创建用户并赋予所需要的权限：

GRANT REPLICATION CLIENT, PROCESS ON . TO 'exporter'@'localhost' identified by '123456';
    GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
    flush privileges;

创建.my.cnf文件：

cat .my.cnf
    [client]
    user=exporter
    password=123456

运行mysql\_exporter(使用supervisor 启动，[supervisor安装部署请参考][supervisor]):

cd /etc/supervisor.d/
    cat mysql_exporter.conf
    [program:mysql_exporter]
    command=mysqld_exporter -config.my-cnf="~/.my.cnf"
    stdout_logfile=/tmp/mysqld_exporter.log
    autostart=true
    autorestart=true
    startsecs=5
    priority=1
    user=root
    stopasgroup=true
    killasgroup=true
    
    启动supervisor：
    /etc/init.d/supervisord restart

Prometheus配置:  
修改prometheus.yml加入MySql节点：

- job_name: mysql     static_configs:
          - targets: ['192.168.8.121:9104']         labels:
              instance: pre-product_mysql_192.168.8.121

保存热加载prometheus：

curl  -XPOST localhost:9090/-/reload

查看targets：  
![这里写图片描述][70 1]

grafana配置可视化：  
模板MySQL Overview：[https://pan.baidu.com/s/1t0x9W7UrlPcusdoVLF8P1w][https_pan.baidu.com_s_1t0x9W7UrlPcusdoVLF8P1w]  
![这里写图片描述][70 2]

配置alertmanager报警,添加prometheus配置：

alerting:
      alertmanagers:
      - scheme: http     static_configs:
        - targets:       - "10.100.110.171:9093" rule_files:
      - /opt/prometheus/rules/mysql*.rules

制定mysql报警规则，如有不恰当或不准确的报警规则，欢迎大家互相交流~.~（[部分指标请参考][Link 1]）:

groups:
    - name: MySQLStatsAlert
      rules:
      - alert: MySQL is down
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Instance {
       { $labels.instance }} MySQL is down"
          description: "MySQL database is down. This requires immediate action!"
      - alert: open files high
        expr: mysql_global_status_innodb_num_open_files > (mysql_global_variables_open_files_limit) * 0.75
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} open files high"
          description: "Open files is high. Please consider increasing open_files_limit."
      - alert: Read buffer size is bigger than max. allowed packet size
        expr: mysql_global_variables_read_buffer_size > mysql_global_variables_slave_max_allowed_packet 
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} Read buffer size is bigger than max. allowed packet size"
          description: "Read buffer size (read_buffer_size) is bigger than max. allowed packet size (max_allowed_packet).This can break your replication."
      - alert: Sort buffer possibly missconfigured
        expr: mysql_global_variables_innodb_sort_buffer_size <256*1024 or mysql_global_variables_read_buffer_size > 4*1024*1024 
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} Sort buffer possibly missconfigured"
          description: "Sort buffer size is either too big or too small. A good value for sort_buffer_size is between 256k and 4M."
      - alert: Thread stack size is too small
        expr: mysql_global_variables_thread_stack <196608
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} Thread stack size is too small"
          description: "Thread stack size is too small. This can cause problems when you use Stored Language constructs for example. A typical is 256k for thread_stack_size."
      - alert: Used more than 80% of max connections limited 
        expr: mysql_global_status_max_used_connections > mysql_global_variables_max_connections * 0.8
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} Used more than 80% of max connections limited"
          description: "Used more than 80% of max connections limited"
      - alert: InnoDB Force Recovery is enabled
        expr: mysql_global_variables_innodb_force_recovery != 0 
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} InnoDB Force Recovery is enabled"
          description: "InnoDB Force Recovery is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data."
      - alert: InnoDB Log File size is too small
        expr: mysql_global_variables_innodb_log_file_size < 16777216 
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} InnoDB Log File size is too small"
          description: "The InnoDB Log File size is possibly too small. Choosing a small InnoDB Log File size can have significant performance impacts."
      - alert: InnoDB Flush Log at Transaction Commit
        expr: mysql_global_variables_innodb_flush_log_at_trx_commit != 1
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} InnoDB Flush Log at Transaction Commit"
          description: "InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of commited transactions in case of a power failure."
      - alert: Table definition cache too small
        expr: mysql_global_status_open_table_definitions > mysql_global_variables_table_definition_cache
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Table definition cache too small"
          description: "Your Table Definition Cache is possibly too small. If it is much too small this can have significant performance impacts!"
      - alert: Table open cache too small
        expr: mysql_global_status_open_tables >mysql_global_variables_table_open_cache * 99/100
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Table open cache too small"
          description: "Your Table Open Cache is possibly too small (old name Table Cache). If it is much too small this can have significant performance impacts!"
      - alert: Thread stack size is possibly too small
        expr: mysql_global_variables_thread_stack < 262144
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Thread stack size is possibly too small"
          description: "Thread stack size is possibly too small. This can cause problems when you use Stored Language constructs for example. A typical is 256k for thread_stack_size."
      - alert: InnoDB Buffer Pool Instances is too small
        expr: mysql_global_variables_innodb_buffer_pool_instances == 1
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} InnoDB Buffer Pool Instances is too small"
          description: "If you are using MySQL 5.5 and higher you should use several InnoDB Buffer Pool Instances for performance reasons. Some rules are: InnoDB Buffer Pool Instance should be at least 1 Gbyte in size. InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine."
      - alert: InnoDB Plugin is enabled
        expr: mysql_global_variables_ignore_builtin_innodb == 1
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} InnoDB Plugin is enabled"
          description: "InnoDB Plugin is enabled"
      - alert: Binary Log is disabled
        expr: mysql_global_variables_log_bin != 1
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Instance {
       { $labels.instance }} Binary Log is disabled"
          description: "Binary Log is disabled. This prohibits you to do Point in Time Recovery (PiTR)."
      - alert: Binlog Cache size too small
        expr: mysql_global_variables_binlog_cache_size < 1048576
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Binlog Cache size too small"
          description: "Binlog Cache size is possibly to small. A value of 1 Mbyte or higher is OK."
      - alert: Binlog Statement Cache size too small
        expr: mysql_global_variables_binlog_stmt_cache_size <1048576 and mysql_global_variables_binlog_stmt_cache_size > 0
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Binlog Statement Cache size too small"
          description: "Binlog Statement Cache size is possibly to small. A value of 1 Mbyte or higher is typically OK."
      - alert: Binlog Transaction Cache size too small
        expr: mysql_global_variables_binlog_cache_size  <1048576
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Binlog Transaction Cache size too small"
          description: "Binlog Transaction Cache size is possibly to small. A value of 1 Mbyte or higher is typically OK."
      - alert: Sync Binlog is enabled
        expr: mysql_global_variables_sync_binlog == 1
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Sync Binlog is enabled"
          description: "Sync Binlog is enabled. This leads to higher data security but on the cost of write performance."
      - alert: IO thread stopped
        expr: mysql_slave_status_slave_io_running != 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Instance {
       { $labels.instance }} IO thread stopped"
          description: "IO thread has stopped. This is usually because it cannot connect to the Master any more."
      - alert: SQL thread stopped 
        expr: mysql_slave_status_slave_sql_running == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Instance {
       { $labels.instance }} SQL thread stopped"
          description: "SQL thread has stopped. This is usually because it cannot apply a SQL statement received from the master."
      - alert: SQL thread stopped
        expr: mysql_slave_status_slave_sql_running != 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Instance {
       { $labels.instance }} Sync Binlog is enabled"
          description: "SQL thread has stopped. This is usually because it cannot apply a SQL statement received from the master."
      - alert: Slave lagging behind Master
        expr: rate(mysql_slave_status_seconds_behind_master[1m]) >30 
        for: 1m
        labels:
          severity: warning 
        annotations:
          summary: "Instance {
       { $labels.instance }} Slave lagging behind Master"
          description: "Slave is lagging behind Master. Please check if Slave threads are running and if there are some performance issues!"
      - alert: Slave is NOT read only(Please ignore this warning indicator.)
        expr: mysql_global_variables_read_only != 0
        for: 1m
        labels:
          severity: page
        annotations:
          summary: "Instance {
       { $labels.instance }} Slave is NOT read only"
          description: "Slave is NOT set to read only. You can accidentally manipulate data on the slave and get inconsistencies..."

[70]: /images/20220513/bd302aa11b7d47bc8bfadf84d5e4912f.png
[supervisor]: https://blog.csdn.net/qq_25934401/article/details/82225289
[70 1]: /images/20220513/b4866784f9d84de7841682c320c3404b.png
[https_pan.baidu.com_s_1t0x9W7UrlPcusdoVLF8P1w]: https://pan.baidu.com/s/1t0x9W7UrlPcusdoVLF8P1w
[70 2]: /images/20220513/d14d020f95d14fab8a1e7c4e5e3c34d4.png
[Link 1]: https://blog.csdn.net/qq_25934401/article/details/82225420