概述
下载地址:https://gitee.com/zhjh256/mysqlawr/releases
mysql awr提供的监控信息等价于Oracle AWR/Statspack,几乎比现有所有开源、商业mysql监控工具更为实用和强大。
![647a1128f022e8cff76dc4ff40b30268.png](https://i-blog.csdnimg.cn/blog_migrate/953a40a0358711cff982fb983dad3de1.jpeg)
继续前面部署mysql awr 监控的部署。
一、部署mysqlawr war包
1、部署
将mysqlawr上传到/opt目录下,解压到webapps/ROOT目录,不然css/js引用不到
#unzip mysqlawr-1.0.6.war -d mysqlawr#cp -rf mysqlawr/* /home/tomcat80/webapps/ROOT/
![7027305e90c204ac310a66d883e139d4.png](https://i-blog.csdnimg.cn/blog_migrate/c4ae3f29b51d8ecda818d2c8a1bdd032.jpeg)
2、修改配置文件
$TOMCAT_HOME/webapps/ROOT/WEB-INF/conf/jdbc-mysql.properties
![8d457de81b69126bd3ed5f2463a3b943.png](https://i-blog.csdnimg.cn/blog_migrate/854cdc8994b2fbfba237a066fb4537db.jpeg)
3、启动tomcat
![ca924a960c28a78f941699b59fb2c173.png](https://i-blog.csdnimg.cn/blog_migrate/008fee6f0010912a5372b868e8c0259e.jpeg)
4、测试访问
http://ip:80即可打开主页面
![7e88358832c5cf0223423e0c9a99172b.png](https://i-blog.csdnimg.cn/blog_migrate/b3d5490cc235cc4bab3e80640bd3b334.jpeg)
![ffeae7830f4d035cee71dc51f32a6dac.png](https://i-blog.csdnimg.cn/blog_migrate/0afec8502220858ed778d2f251cd9ff9.jpeg)
二、配置被监控数据库项
• 建议percona server 5.6/mariadb+,支持mysql,percona,mariadb 5.6,5.7。
• 源系统OS版本为redhat/centos,6.3/6.5经过测试,windows不支持;
• 安装lsof,yum install lsof;
• 开放mysql/ssh相关端口;
• 启用performance_schema,userstat参数;
• 创建perf_stat用户,授予select performance_schema select/truncate权限;
1、被监控mysql数据库授权
mysql客户端登录数据库,执行war中包含的initsqlsrc-db-createuser.sql
set global validate_password_policy=0;CREATE USER perf_stat@'%' IDENTIFIED BY 'perf_stat@123';GRANT SELECT ON performance_schema.* TO perf_stat@'%' ;
![79e83cc02f302145e87117ffa6f19c46.png](https://i-blog.csdnimg.cn/blog_migrate/2372e9f491941da198017952b6a2ab2c.jpeg)
2、图形界面增加被监控数据库
![de1829e66185519406e43ef41215877f.png](https://i-blog.csdnimg.cn/blog_migrate/77dbe285756e5e95fd7a8e7946866a19.jpeg)
![fda8f7e3eb80bf635966307c17fa3231.png](https://i-blog.csdnimg.cn/blog_migrate/d551bd4e8f61c268e5ab0d275c8cc49e.jpeg)
FAQ
1、注意每个实例只要配置一条记录即可,使用perf_stat或者root用户都可以,如果重复配置了,目前需要登录mysqlawr仓库数据库,执行下列操作进行删除:
use perf_stat;
delete from apps where app_name='perf_stat@XX.XX.X.X';
2、top N SQL内容为空。确保源系统启用了performance_schema,并设置了相关参数,可以参考如下:
performance_schema=onperformance-schema-instrument='wait/io/file/%=on'performance-schema-instrument='wait/io/table/%=on'performance-schema-instrument='statement/com/%=on'performance-schema-instrument='statement/sql/%=on'performance-schema-instrument='stage/sql/%=on'# 注:只有current=on,performance_schema才会检查是否需要填充到history和long history中,后两者没有关系,均依赖于current,因此current必须无条件启用performance-schema-consumer-events_stages_current=on# performance-schema-consumer-events_stages_history=on# performance_schema_events_stages_history_size=30performance-schema-consumer-events_stages_history_long=onperformance_schema_events_statements_history_long_size=10000performance-schema-consumer-events_statements_current=on# performance-schema-consumer-events_statements_history=on# performance_schema_events_statements_history_size=30performance-schema-consumer-events_statements_history_long=onperformance_schema_events_statements_history_long_size=10000performance-schema-consumer-events_waits_current=on# performance-schema-consumer-events_waits_history=on# performance_schema_events_statements_history_size=30performance-schema-consumer-events_waits_history_long=onperformance_schema_events_waits_history_long_size=10000performance-schema-consumer-statements_digest=onperformance_schema_digests_size=10000
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
![8e4cb2b88c7090db477e0f99788f0e70.gif](https://i-blog.csdnimg.cn/blog_migrate/4e08237edcc750e84ba9286b21a68b3f.gif)