自定义监控mysql主从

自定义监控mysql主从

主机名	IP	要安装的应用
lamp	192.168.200.5	lamp架构zabbix serverzabbix agent
slave	192.168.200.6	zabbix agent，mysql主从配置
master	192.168.200.7	mysql主从配置

//mysql主从配置可以查看http://t.csdn.cn/AWtga 这篇博客
在主数据库里创建一个同步账号授权给从数据库使用

mysql> create user 'repl'@'192.168.200.6' identified by 'repl123';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.200.6';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

配置主数据库my.cnf文件

[root@master ~]# ls /etc/my.cnf 
/etc/my.cnf
[root@master ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 5
log-bin = mysql_bin
[root@master ~]# systemctl restart mysqld.service
[root@master ~]# mysql -uroot -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从数据库my.cnf文件

[root@slave ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 10
relay-log = mysql-relay-bin

配置并启动主从复制

mysql> CHANGE MASTER TO
    -> MASTER_HOST= '192.168.200.7',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123', 
    -> MASTER_LOG_FILE='mysql_bin.000002',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 

自定义监控mysql主从状态

编写脚本

因为正常状态为双yes 所以过滤出双yes 然后做判断即可

[root@slave ~]# mkdir /scripts/
[root@slave ~]# cd /scripts/
[root@slave scripts]# ls
[root@slave scripts]# vi mysqlms.sh 
#!/bin/bash
count=$(mysql -uzabbix -pzabbix -e "show slave status\G" 2>/dev/null | grep -v grep | grep -c 'Yes')

if [ $count -ne 2 ];then
        echo '1'
else
        echo '0'
fi
//  2>dev/null 去掉告警信息
// grep -v grep 将grep本身的进程去掉
// grep -c 'Yes'  //将匹配yes的行数取出来
//到从服务端数据库创建一个zabbix的用户，因为系统是默认用zabbix用户登陆数据库的，如果用root用户安全就会受到威胁，所要在从数据库创建一个zabbix用户、并设置权限只读和密码
[root@slave scripts]# mysql -uroot -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select on *.* to 'zabbix'@'localhost' identified by 'zabbix';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> grant SUPER, REPLICATION CLIENT on *.* to 'zabbix'@'localhost' identified by 'zabbix';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

测试脚本

[root@slave scripts]# chmod +x mysqlms.sh 
[root@slave scripts]# ll
total 4
-rwxr-xr-x. 1 root root 173 Jul 11 21:58 mysqlms.sh
[root@slave scripts]# ./mysqlms.sh
0

配置文件zabbix_agentd.conf

[root@slave scripts]# vi /usr/local/etc/zabbix_agentd.conf
# ListenBacklog=
UserParameter=check_mysqlms,/bin/bash /scripts/mysqlms.sh //添加自定义监控
[root@slave scripts]# pkill zabbix_agentd 
[root@slave scripts]# zabbix_agentd
[root@lamp ~]# zabbix_get -s 192.168.200.6 -k check_mysqlms
0

配置zabbix网页

手动触发报警

[root@slave scripts]# mysql -uroot -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

自定义监控mysql主从延迟

查看mysql主从的延迟

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.7
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 693
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0    //取出这个值 这个是mysql主从延迟
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5
                  Master_UUID: 036222dc-0118-11ed-b12f-000c29e4ee5c
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

编写脚本

因为生产环境下 延迟是上下浮动的所以 只需要打印出浮动的数值 当此数值达到指定的值时再去报警

[root@slave scripts]# vi mysql_delay.sh 
#!/bin/bash
delay=$( mysql -uzabbix -pzabbix -e "show slave status\G" 2>/dev/null | grep 'Seconds_Behind_Master'| awk '{print $2}')

echo $delay
[root@slave scripts]# chmod +x mysql_delay.sh 
[root@slave scripts]# ll
total 8
-rwxr-xr-x. 1 root root 148 Jul 11 22:21 mysql_delay.sh
-rwxr-xr-x. 1 root root 185 Jul 11 22:20 mysqlms.sh
[root@slave scripts]# ./mysql_delay.sh  //因为是在虚拟机中所以延迟为0
0
//修改配置文件
[root@slave scripts]# vi /usr/local/etc/zabbix_agentd.conf
# ListenBacklog=
UserParameter=check_mysqlms,/bin/bash /scripts/mysqlms.sh
UserParameter=mysql_delay,/bin/bash /scripts/mysql_delay.sh
[root@slave scripts]# pkill zabbix
[root@slave scripts]# zabbix_agentd   //让配置文件生效

//服务端测试
[root@lamp ~]# zabbix_get -s 192.168.200.6 -k mysql_delay
0

因为虚拟机环境内延迟为0 所以直接报警了
触发器的{ITEM.VALUE}为显示延迟数