运维监控系统之Prometheus-MySQLl监控

简介

mysqld_exporter是用于获取mysql实例的指标服务。

安装

mysql授权

CREATEUSERexporter@localhostidentifiedby"mima";
GRANTPROCESS,REPLICATIONCLIENT,SELECTON*.*TO'exporter'@'localhost'WITHMAX_USER_CONNECTIONS3;
flushprivileges;

直接通过命令下载就可以了。

wget-t1000-chttps://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
tarzxvfmysqld_exporter-0.12.1.linux-amd64.tar.gz-C/opt/
cd/opt/mysqld_exporter-0.12.1.linux-amd64
exportDATA_SOURCE_NAME='exporter:mima@(localhost:3306)/'
./mysqld_exporter

这里设置用户名密码是通过环境变量的形式，也可以用配置文件，默认找的配置文件是~/.my.cnf，也可以使用参数–config.my-cnf="/opt/mysqld_exporter/.my.cnf" 进行设置。

./mysqld_exporter--config.my-cnf="/opt/mysqld_exporter/.my.cnf"

其他参数

usage:mysqld_exporter[<flags>]

Flags:
-h,--helpShowcontext-sensitivehelp(alsotry--help-longand--help-man).
--exporter.lock_wait_timeout=2
Setalock_wait_timeoutontheconnectiontoavoidlongmetadatalocking.
--exporter.log_slow_filter
Addalog_slow_filtertoavoidslowqueryloggingofscrapes.NOTE:NotsupportedbyOracleMySQL.
--collect.heartbeat.database="heartbeat"
Databasefromwheretocollectheartbeatdata
--collect.heartbeat.table="heartbeat"
Tablefromwheretocollectheartbeatdata
--collect.info_schema.processlist.min_time=0
Minimumtimeathreadmustbeineachstatetobecounted
--collect.info_schema.processlist.processes_by_user
Enablecollectingthenumberofprocessesbyuser
--collect.info_schema.processlist.processes_by_host
Enablecollectingthenumberofprocessesbyhost
--collect.info_schema.tables.databases="*"
Thelistofdatabasestocollecttablestatsfor,or'*'forall
--collect.mysql.user.privileges
Enablecollectinguserprivilegesfrommysql.user
--collect.perf_schema.eventsstatements.limit=250
Limitthenumberofeventsstatementsdigestsbyresponsetime
--collect.perf_schema.eventsstatements.timelimit=86400
Limithowoldthe'last_seen'eventsstatementscanbe,inseconds
--collect.perf_schema.eventsstatements.digest_text_limit=120
Maximumlengthofthenormalizedstatementtext
--collect.perf_schema.file_instances.filter=".*"
RegExfile_namefilterforperformance_schema.file_summary_by_instance
--collect.perf_schema.file_instances.remove_prefix="/var/lib/mysql/"
Removepathprefixinperformance_schema.file_summary_by_instance
--web.listen-address=":9104"
Addresstolistenonforwebinterfaceandtelemetry.
--web.telemetry-path="/metrics"
Pathunderwhichtoexposemetrics.
--timeout-offset=0.25Offsettosubtractfromtimeoutinseconds.
--config.my-cnf="/home/jalright/.my.cnf"
Pathto.my.cnffiletoreadMySQLcredentialsfrom.
--collect.mysql.userCollectdatafrommysql.user
--collect.info_schema.tables
Collectmetricsfrominformation_schema.tables
--collect.info_schema.innodb_tablespaces
Collectmetricsfrominformation_schema.innodb_sys_tablespaces
--collect.info_schema.innodb_metrics
Collectmetricsfrominformation_schema.innodb_metrics
--collect.global_statusCollectfromSHOWGLOBALSTATUS
--collect.global_variables
CollectfromSHOWGLOBALVARIABLES
--collect.slave_statusCollectfromSHOWSLAVESTATUS
--collect.info_schema.processlist
Collectcurrentthreadstatecountsfromtheinformation_schema.processlist
--collect.perf_schema.tablelocks
Collectmetricsfromperformance_schema.table_lock_waits_summary_by_table
--collect.perf_schema.eventsstatements
Collectmetricsfromperformance_schema.events_statements_summary_by_digest
--collect.perf_schema.eventsstatementssum
Collectmetricsofgrandsumsfromperformance_schema.events_statements_summary_by_digest
--collect.perf_schema.eventswaits
Collectmetricsfromperformance_schema.events_waits_summary_global_by_event_name
--collect.auto_increment.columns
Collectauto_incrementcolumnsandmaxvaluesfrominformation_schema
--collect.binlog_sizeCollectthecurrentsizeofallregisteredbinlogfiles
--collect.perf_schema.tableiowaits
Collectmetricsfromperformance_schema.table_io_waits_summary_by_table
--collect.perf_schema.indexiowaits
Collectmetricsfromperformance_schema.table_io_waits_summary_by_index_usage
--collect.info_schema.userstats
Ifrunningwithuserstat=1,settotruetocollectuserstatistics
--collect.info_schema.clientstats
Ifrunningwithuserstat=1,settotruetocollectclientstatistics
--collect.info_schema.tablestats
Ifrunningwithuserstat=1,settotruetocollecttablestatistics
--collect.info_schema.schemastats
Ifrunningwithuserstat=1,settotruetocollectschemastatistics
--collect.perf_schema.file_events
Collectmetricsfromperformance_schema.file_summary_by_event_name
--collect.perf_schema.file_instances
Collectmetricsfromperformance_schema.file_summary_by_instance
--collect.perf_schema.replication_group_member_stats
Collectmetricsfromperformance_schema.replication_group_member_stats
--collect.perf_schema.replication_applier_status_by_worker
Collectmetricsfromperformance_schema.replication_applier_status_by_worker
--collect.engine_innodb_status
CollectfromSHOWENGINEINNODBSTATUS
--collect.heartbeatCollectfromheartbeat
--collect.slave_hostsScrapeinformationfrom'SHOWSLAVEHOSTS'
--collect.info_schema.innodb_cmp
Collectmetricsfrominformation_schema.innodb_cmp
--collect.info_schema.innodb_cmpmem
Collectmetricsfrominformation_schema.innodb_cmpmem
--collect.info_schema.query_response_time
Collectqueryresponsetimedistributionifquery_response_time_statsisON.
--collect.engine_tokudb_status
CollectfromSHOWENGINETOKUDBSTATUS
--log.level="info"Onlylogmessageswiththegivenseverityorabove.Validlevels:[debug,info,warn,error,fatal]
--log.format="logger:stderr"
Setthelogtargetandformat.Example:"logger:syslog?appname=bob&local=7"or"logger:stdout?json=true"
--versionShowapplicationversion.

部署服务

直接注册成systemd服务。

```bash
#!/bin/bash

VERSION="0.12.1"

wget-t100-chttps://github.com/prometheus/mysqld_exporter/releases/download/v${VERSION}/mysqld_exporter-${VERSION}.linux-amd64.tar.gz

if[!-emysqld_exporter-${VERSION}.linux-amd64.tar.gz]
then
echo"安装包下载失败"
exit1
fi
tarxvfzmysqld_exporter-${VERSION}.linux-amd64.tar.gz-C/opt/
cd/opt
ln-smysqld_exporter-${VERSION}.linux-amd64mysqld_exporter
cat>/etc/systemd/system/mysqld_exporter.service<<EOF

[Unit]
Description=mysqld_exporter
After=network.target

[Service]
Type=simple
WorkingDirectory=/opt/mysqld_exporter
ExecStart=/opt/mysqld_exporter/mysqld_exporter--config.my-cnf="/opt/mysqld_exporter/.my.cnf"
LimitNOFILE=65536
PrivateTmp=true
RestartSec=2
StartLimitInterval=0
Restart=always

[Install]
WantedBy=multi-user.target
EOF
​```
systemctldaemon-reload
systemctlenablemysqld_exporter
systemctlstartmysqld_exporter

配置prometheus Job

默认端口是9104

-job_name:'mysqld_exporter'

#metrics_pathdefaultsto'/metrics'
#schemedefaultsto'http'.

static_configs:
-targets:['localhost:9104']

指标展示

指标可以通过prometheus的WebUI进行查看

http://[prometheserverip]:9090

如果需要画图，可以直接使用grafana，有人已经配置好了图形可以通过grafana官方下的dashboard找到mysqld_exporter相关的dashboard就可以直接使用了，配置好prometheus数据源，直接导入grafana就可以直接展示了。