splunk监控mysql的性能:
mysql server端:
mysql 5.6:
mysql -u root -p'xxxx'
grant select,process,replication client on . to splunk@'10.0.0.3' identified by 'xxxx';
flush privileges;
exit
mysql 5.7:
mysql -u root -p'xxxx'
grant select,process,replication client on . to splunk@'10.0.0.3' identified by 'xxxx';
set global show_compatibility_56=on;
grant execute on sys.* to splunk@'10.0.0.3';
flush privileges;
exit
splunk服务器端:
安装Splunk DB Connect插件(可到splunk官网下载)
mysql的jdbc驱动使用8.0版本(mysql-connector-java-8.0.16.jar),splunk官网可下载,然后放到/opt/splunk/etc/apps/splunk_app_db_connect/drivers/目录,splunk页面点击重新加载(Splunk DB Connect——配置——Settings——Drivers)
splunk主页——Splunk DB Connect——配置——Identities(配下认证帐号)——Connections——New Connection(配置略,要勾选SSL)
splunk主页——Splunk DB Connect——Data Lab——Inputs——此处有3个input模板(processlist、statusvars、tablestats),每台mysql服务器复制3份
启用TCP 9936端口:
splunk主页——添加数据——监控——TCP/UDP——端口:9936——来源类型(随便选个),索引:mysql——检查——完成
到命令行:
netstat -nplt | grep 9936 (确认9936端口也起来)
cp -rv /opt/splunk/etc/apps/mysqlmonitor/legacy/hosts.yaml.sample /opt/splunk/etc/apps/mysqlmonitor/legacy/hosts.yaml
vi /opt/splunk/etc/apps/mysqlmonitor/legacy/hosts.yaml
Databases:
db01:
host: 10.0.0.5
username: splunk
password: xxxxx
:wq
vi /opt/splunk/etc/apps/mysqlmonitor/legacy/bin/database.rb
hosts_file = "/opt/splunk/etc/apps/mysqlmonitor/legacy/hosts.yaml" (确保这个路径是正确的)
:wq
vi /opt/splunk/etc/apps/mysqlmonitor/legacy/local/inputs.conf
connection_host = 10.0.0.3 (此处为splunk服务器ip)
检查下mysql-processlist.rb等的路径是否正确,不对的话调整下(默认应该是不对的)
:wq
cp -rv /opt/splunk/etc/apps/mysqlmonitor/legacy/bin/daemon/config.ini.sample /opt/splunk/etc/apps/mysqlmonitor/legacy/bin/daemon/config.ini
vi /opt/splunk/etc/apps/mysqlmonitor/legacy/bin/daemon/config.ini (添加多台只需要改这个文件和splunk主页添加Connection和3个inputs,上面的hosts.yaml、inputs.conf等可以不动)
mysql指被监控的mysql服务器
//监控多台服务器(在[mysql]下添加)
host=10.0.0.5
port=3306
username=splunk
password=xxxxx
splunk指splunk服务器
:wq
启动服务:
/opt/splunk/etc/apps/mysqlmonitor/legacy/bin/daemon/splunkmysqlmonitor.py start
确认:
splunk主页——MySQL——Host overiew——选定主机——时间——提交(确认有数据即是没问题)
转载于:https://blog.51cto.com/yangzhiming/2406775