使用Prometheus和Grafana监控Mysql服务器性能

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《使用Prometheus和Grafana监控Mysql服务器性能》就很适合你！本篇内容主要包括使用Prometheus和Grafana监控Mysql服务器性能，希望对大家的知识积累有所帮助，助力实战开发！

这是一篇快速入门文章，介绍了如何使用

$ wget https://github.com/prometheus/prometheus/releases/download/v1.6.3/prometheus-1.6.3.linux-amd64.tar.gz -O prometheus-1.6.3.linux-amd64.tar.gz
$ mkdir /usr/local/services/prometheus
$ tar zxf prometheus-1.6.3.linux-amd64.tar.gz -C /usr/local/services/prometheus --strip-components=1

配置prometheus

然后在安装目下编辑配置文件

$ ./prometheus       
INFO[0000] Starting prometheus (version=1.6.3, branch=master, revision=c580b60c67f2c5f6b638c3322161bcdf6d68d7fc)  source=main.go:88
INFO[0000] Build context (go=go1.8.1, user=root@a6410e65f5c7, date=20170522-09:15:06)  source=main.go:89
INFO[0000] Loading configuration file prometheus.yml     source=main.go:251
INFO[0000] Loading series map and head chunks...         source=storage.go:421
INFO[0000] 0 series loaded.                              source=storage.go:432
INFO[0000] Listening on :9090                            source=web.go:259
INFO[0000] Starting target manager...                    source=targetmanager.go:61
INFO[0300] Checkpointing in-memory metrics and chunks...  source=persistence.go:633
INFO[0300] Done checkpointing in-memory metrics and chunks in 75.372924ms.  source=persistence.go:665

Prometheus内置了一个web界面，我们可通过

$ wget https://github.com/prometheus/node_exporter/releases/download/v0.14.0/node_exporter-0.14.0.linux-amd64.tar.gz -O node_exporter-0.14.0.linux-amd64.tar.gz
$ wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.10.0/mysqld_exporter-0.10.0.linux-amd64.tar.gz -O mysqld_exporter-0.10.0.linux-amd64.tar.gz
$ mkdir /usr/local/services/prometheus_exporters
$ tar zxf node_exporter-0.14.0.linux-amd64.tar.gz -C /usr/local/services/prometheus_exporters --strip-components=1
$ tar zxf mysqld_exporter-0.10.0.linux-amd64.tar.gz -C /usr/local/services/prometheus_exporters --strip-components=1

运行node_exporter ：

$ cd /usr/local/services/prometheus_exporters
$ ./node_exporter 
INFO[0000] Starting node_exporter (version=0.14.0, branch=master, revision=840ba5dcc71a084a3bc63cb6063003c1f94435a6)  source="node_exporter.go:140"
INFO[0000] Build context (go=go1.7.5, user=root@bb6d0678e7f3, date=20170321-12:12:54)  source="node_exporter.go:141"
INFO[0000] No directory specified, see --collector.textfile.directory  source="textfile.go:57"
INFO[0000] Enabled collectors:                           source="node_exporter.go:160"
INFO[0000]  - netdev                                     source="node_exporter.go:162"
INFO[0000]  - sockstat                                   source="node_exporter.go:162"
INFO[0000]  - stat                                       source="node_exporter.go:162"
INFO[0000]  - vmstat                                     source="node_exporter.go:162"
INFO[0000]  - filefd                                     source="node_exporter.go:162"
INFO[0000]  - loadavg                                    source="node_exporter.go:162"
INFO[0000]  - mdadm                                      source="node_exporter.go:162"
INFO[0000]  - meminfo                                    source="node_exporter.go:162"
INFO[0000]  - time                                       source="node_exporter.go:162"
INFO[0000]  - uname                                      source="node_exporter.go:162"
INFO[0000]  - edac                                       source="node_exporter.go:162"
INFO[0000]  - hwmon                                      source="node_exporter.go:162"
INFO[0000]  - diskstats                                  source="node_exporter.go:162"
INFO[0000]  - entropy                                    source="node_exporter.go:162"
INFO[0000]  - infiniband                                 source="node_exporter.go:162"
INFO[0000]  - netstat                                    source="node_exporter.go:162"
INFO[0000]  - textfile                                   source="node_exporter.go:162"
INFO[0000]  - wifi                                       source="node_exporter.go:162"
INFO[0000]  - zfs                                        source="node_exporter.go:162"
INFO[0000]  - conntrack                                  source="node_exporter.go:162"
INFO[0000]  - filesystem                                 source="node_exporter.go:162"
INFO[0000] Listening on :9100                            source="node_exporter.go:186"

mysqld_exporter需要连接到Mysql，所以需要Mysql的权限，我们先为它创建用户并赋予所需的权限：

mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'prom'@'localhost' identified by 'abc123';
mysql> GRANT SELECT ON performance_schema.* TO 'prom'@'localhost';

创建

$ cd /usr/local/services/prometheus_exporters
$ cat  .my.cnf
[client]
user=prom
password=abc123
EOF
$ ./mysqld_exporter -config.my-cnf=".my.cnf"  
INFO[0000] Starting mysqld_exporter (version=0.10.0, branch=master, revision=80680068f15474f87847c8ee8f18a2939a26196a)  source="mysqld_exporter.go:460"
INFO[0000] Build context (go=go1.8.1, user=root@3b0154cd9e8e, date=20170425-11:24:12)  source="mysqld_exporter.go:461"
INFO[0000] Listening on :9104                            source="mysqld_exporter.go:479

我们再次回到

$ wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-4.3.1.linux-x64.tar.gz
$ mkdir /usr/local/services/grafana
$ tar zxvf grafana-4.3.1.linux-x64.tar.gz -C /usr/local/services/grafana  --strip-components=1

编辑配置文件

[dashboards.json]
enabled = true
path = /var/lib/grafana/dashboards

安装仪表盘:

$ git clone https://github.com/percona/grafana-dashboards.git
$ cp -r grafana-dashboards/dashboards /var/lib/grafana/

最后我们运行Grafana服务：

$ cd /usr/local/services/grafana/bin/
$ ./grafana-server
INFO[05-25|15:42:46] Starting Grafana                         logger=main version=4.3.1 commit=befc15c compiled=2017-05-23T21:50:22+0800
INFO[05-25|15:42:46] Config loaded from                       logger=settings file=/usr/local/services/grafana/conf/defaults.ini
INFO[05-25|15:42:46] Path Home                                logger=settings path=/usr/local/services/grafana
INFO[05-25|15:42:46] Path Data                                logger=settings path=/usr/local/services/grafana/data
INFO[05-25|15:42:46] Path Logs                                logger=settings path=/usr/local/services/grafana/data/log
INFO[05-25|15:42:46] Path Plugins                             logger=settings path=/usr/local/services/grafana/data/plugins
INFO[05-25|15:42:46] Initializing DB                          logger=sqlstore dbtype=sqlite3
INFO[05-25|15:42:46] Starting DB migration                    logger=migrator
INFO[05-25|15:42:46] Executing migration                      logger=migrator id="copy data account to org"
INFO[05-25|15:42:46] Skipping migration condition not fulfilled logger=migrator id="copy data account to org"
INFO[05-25|15:42:46] Executing migration                      logger=migrator id="copy data account_user to org_user"
INFO[05-25|15:42:46] Skipping migration condition not fulfilled logger=migrator id="copy data account_user to org_user"
INFO[05-25|15:42:46] Creating json dashboard index for path: /var/lib/grafana/dashboards 
INFO[05-25|15:42:46] Starting plugin search                   logger=plugins
INFO[05-25|15:42:46] Initializing CleanUpService              logger=cleanup
INFO[05-25|15:42:46] Initializing Alerting                    logger=alerting.engine
INFO[05-25|15:42:46] Initializing Stream Manager 
INFO[05-25|15:42:46] Initializing HTTP Server                 logger=http.server address=0.0.0.0:3000 protocol=http subUrl= socket=

我们可通过

http://monitor_host:3000

访问Grafana网页界面（缺省的帐号/密码为admin/admin）：

然后我们到

Data Sources

页面添加数据源：

最后我们就可以通过选择不同的仪表盘（左上角）和时间段（右上角）来呈现图表了：

遇到的问题

Prometheus和Grafana都正常，但是仪表盘不显示数据

Prometheus和Grafana都运行正常，exporter的状态也正常，Grafana上的添加的数据源也正常工作，但是仪表盘就是没有任何数据。唯一的线索是在Prometheus的管理后台执行查询时显示

No datapoints found.

的错误，根据这个错误在网上搜索到这个issue：https://github.com/prometheus...，里面有人提到是系统时间不正确的问题，发现系统的时间确实不对，同步时间后解决问题。

参考资料

https://prometheus.io/
http://grafana.org/
https://github.com/percona/gr...
https://www.percona.com/blog/...

更新记录

• 2017/05/25：软件更新到最新版本（示例图片未更新，因为基本没什么变化），添加一个遇到的问题。

今天关于《使用Prometheus和Grafana监控Mysql服务器性能》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！