2-6 监控MySQL
最后更新于：2022-04-02 07:40:54
#### 使用的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
```
';