https://github.com/prometheus/mysqld_exporter/releases

grafana图表
https://grafana.com/grafana/dashboards/7362-mysql-overview/


吞吐量监控
MySQL内部通过一个名为Questions的计数器，当客户端发送一个查询语句后，其值就会+1

SHOW GLOBAL STATUS LIKE "Questions";


mysql_global_status_questions 999

rate(mysql_global_status_questions[2m])



insert语句执行次数
SHOW GLOBAL STATUS LIKE "Com_insert";

# HELP mysql_global_status_commands_total Total number of executed MySQL commands.
# TYPE mysql_global_status_commands_total counter
mysql_global_status_commands_total{command="admin_commands"} 0
mysql_global_status_commands_total{command="alter_db"} 0
mysql_global_status_commands_total{command="alter_db_upgrade"} 0
mysql_global_status_commands_total{command="select"} 10
mysql_global_status_commands_total{command="insert"} 2
mysql_global_status_commands_total{command="update"} 2
mysql_global_status_commands_total{command="delete"} 1

sum(rate(mysql_global_status_commands_total{command=~"insert|update|delete"}[2m])) without (command)


SHOW VARIABLES LIKE 'max_connections';

MySQL默认最大链接数为151 ,临时调整最大连接数 
SET GLOBAL max_connections = 200;


my.cnf 永久配置
max_connections

当前连接数
SHOW GLOBAL STATUS LIKE "Threads_connected";

当所有可用连接都被占用时，如果一个客户端尝试连接至MySQL，
会出现“Too many connections(连接数过多)”错误，同时Connection_errors_max_connections的值也会增加。
监控可用连接数量，确保其值保持在max_connections限制以内。
如果Aborted_connects的数量不断增加时，说明客户端尝试连接到MySQL都失败了。
可以通过Connection_errors_max_connections以及Connection_errors_internal分析连接失败的问题原因。


连接相关的监控指标

mysql_global_variables_max_connections： 允许的最大连接数；
mysql_global_status_threads_connected： 当前开放的连接；
mysql_global_status_threads_running：当前开放的连接；
mysql_global_status_aborted_connects：当前开放的连接；
mysql_global_status_connection_errors_total{error="max_connections"}：由于超出最大连接数导致的错误；
mysql_global_status_connection_errors_total{error="internal"}：由于系统内部导致的错误；


当前剩余可用连接数
mysql_global_variables_max_connections - mysql_global_status_threads_connected




监控缓冲池使用情况

MySQL默认的存储引擎InnoDB使用了一片称为缓冲池的内存区域，用于缓存数据表以及索引的数据。 
当缓冲池的资源使用超出限制后，可能会导致数据库性能的下降，同时很多查询命令会直接在磁盘中执行，导致磁盘I/O不断攀升。 

SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_pages_total";

缓冲池中各类内存页的数量
# HELP mysql_global_status_buffer_pool_pages Innodb buffer pool pages by state.
# TYPE mysql_global_status_buffer_pool_pages gauge
mysql_global_status_buffer_pool_pages{state="data"} 516
mysql_global_status_buffer_pool_pages{state="dirty"} 0
mysql_global_status_buffer_pool_pages{state="free"} 7675
mysql_global_status_buffer_pool_pages{state="misc"} 0

Innodb_buffer_pool_read_requests记录了正常从缓冲池读取数据的请求数量
SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_read_requests";

当缓冲池无法满足时，MySQL只能从磁盘中读取数据
Innodb_buffer_pool_reads


Innodb_buffer_pool_reads和Innodb_buffer_pool_read_requests

Innodb_buffer_pool_reads
The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

Innodb_buffer_pool_read_requests    缓冲池读取请求
The number of logical read requests.


Innodb_buffer_pool_reads 需要从磁盘读取数据到缓冲池的次数
Innodb_buffer_pool_read_requests  数据已经在缓冲池中而无需从磁盘读取数据的次数 


SHOW  STATUS LIKE "Innodb_buffer_pool_read%"

Innodb_buffer_pool_read_requests  13103941036
Innodb_buffer_pool_reads  4555



查询性能

Slow_queries计数器，当查询的执行时间超过long_query_time的值后，计数器就会+1，其默认值为10秒

SHOW VARIABLES LIKE 'long_query_time';

SHOW GLOBAL STATUS LIKE "Slow_queries";

# HELP mysql_global_status_slow_queries Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_slow_queries untyped
mysql_global_status_slow_queries 9

Slow_queries的增长情况
rate(mysql_global_status_slow_queries[2m])


密码包含 # ，配置文件配置会报错，可使用环境变量

[client]
user=test
# password=root#123!
host=192.168.1.9
port=7777

start.sh 

export MYSQLD_EXPORTER_PASSWORD="hello#123!"
nohup ./mysqld_exporter --config.my-cnf mysqld_exporter.cnf 2>&1 >> mysqld_exporter.log &

http://127.0.0.1:9104/metrics




https://www.prometheus.wang/exporter/use-promethues-monitor-mysql.html