2-6 监控MySQL · 运维工作笔记

#### 使用的Fpmmm模板监控MySQL 手册地址：http://www.fromdual.com/fpmmm-installation-guide 下载地址：https://support.fromdual.com/admin/download/fpmmm-1.0.1.tar.gz ##### 配置 ```shell yum install php-cli php-process php-mysqli -y cat << _EOF >/etc/php.d/fpmmm.ini variables_order = "EGPCS" date.timezone = 'Asia/Shanghai' _EOF ``` ##### 安装zabbix客户端 ```shell #fpmmm依赖zabbix-sender yum install zabbix-agent zabbix-sender ``` ##### 下载并上传fpmmm文件到/opt目录下 ```shell cd /opt tar xzf /opt/fpmmm-1.0.1.tar.gz mv fpmmm-1.0.1 /app/ chown -R root.root /app/fpmmm-1.0.1 cd /app &&ln -s fpmmm-1.0.1 fpmmm ``` ##### 配置fpmmm ```shell mkdir /etc/fpmmm cp /app/fpmmm/tpl/fpmmm.conf.template /etc/fpmmm/fpmmm.conf chown -R zabbix: /etc/fpmmm ``` ##### 增加本地授权（收集数据） ```shell CREATE USER 'fpmmm_agent'@'127.0.0.1' IDENTIFIED BY 'secret'; GRANT SELECT, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'fpmmm_agent'@'127.0.0.1'; ``` **生产请使用强密码！** ##### fpmmm配置文件模板修改以下内容 ```shell [self] Interval = 60 PidFile = /var/run/fpmmm/fpmmm.pid [default] LogLevel = 2 LogFile = /var/log/fpmmm/fpmmm.log CacheFileBase = /var/cache/fpmmm/fpmmm AgentLockFile = /var/run/fpmmm/fpmmm.lock Username = fpmmm_agent Password = secret MysqlHost = 127.0.0.1 MysqlPort = 3306 ZabbixServer = 192.168.2.30 Disabled = false Modules = innodb master myisam mysql process security PidFile = /data/mysql3306/mon02-s01.prod.ding.pid [mdb01-s1.test.ding] # This MUST match Hostname in Zabbix! Type = host Modules = fpmmm server [mdb01-s1-3306] # This MUST match Hostname in Zabbix! Type = mysqld MysqlPort = 3306 Modules = process mysql myisam innodb master slave security PidFile = /data/mysql3306/mon02-s01.prod.ding.pid ``` 备注1：[mdb01-s1.test.ding]要和zabbix中的Host name中的定义要一致 [mdb01-s1-3306]是自定义的名字，在Zabbix的host中也要添加，绑定模板不同备注2：数据库监控不要添加fpmmm和server监控，如果添加，其他监控项将**不收集！！！** ##### 这里栽了很大的坑，没有认真的看官方文档，fpmmm server但单独收集的，我不需要，所以就注释了。 ##### 还是多说一句吧，官方文档说的是，Zabbix建立两个主机，一个服务器主机，绑定fpmmm和server监控性能和版本，另一个MySQL主机（虚拟的，和配置里一个名字），绑定其他MySQL模板。 #### 测试 ```shell /app/fpmmm/bin/fpmmm --config=/etc/fpmmm/fpmmm.conf 1 ``` 查看日志 ```shell tail -f /var/log/fpmmm/fpmmm.log ``` ```shell 31489:2017-07-21 11:50:43.857 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) (1.0.1) run started. 31489:2017-07-21 11:50:43.912 - WARN: Please install program /usr/bin/numactl. (rc=1534). 31489:2017-07-21 11:50:43.912 - WARN: sudo apt-get install numactl 31489:2017-07-21 11:50:43.912 - WARN: sudo yum install numactl 31489:2017-07-21 11:50:44.917 - WARN: Please install program /usr/bin/numactl. (rc=1534). 31489:2017-07-21 11:50:44.917 - WARN: sudo apt-get install numactl 31489:2017-07-21 11:50:44.917 - WARN: sudo yum install numactl 31489:2017-07-21 11:50:46.305 - WARN: Instance mdb01-s1-3306 seems not to be a Slave. (rc=1577). 31489:2017-07-21 11:50:46.405 - INFO: FromDual Performance Monitor for MySQL and MariaDB (fpmmm) run finished (rc=0). ``` ##### 导入所有模板 ##### Zabbix添加主机 ```shell [mdb01-s1.test.ding] 模板：fpmmm server [mdb01-s1-3306] 模板：innodb master myisam mysql process security ``` ##### 配置数据发送方式 ```shell REGULARLY RUNNING THE FPMMM AGENT There are 2 different possibilities to run fpmmm regularly. First by the Zabbix agent: Put the following line either into its own file (/etc/zabbix/conf.d/fpmmm.conf) or add it to the Zabbix agent configuartion file (/etc/zabbix/zabbix_agentd.conf). UserParameter=FromDual.MySQL.check,/app/fpmmm/bin/fpmmm --config=/etc/fpmmm/fpmmm.conf or second by the crontab: * * * * * /app/fpmmm/bin/fpmmm --config=/etc/fpmmm/fpmmm.conf >/dev/null ```