主库授权
mysql> grant replication slave on *.* to 'csl'@'192.168.153.137' identified by '123' ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit //从库上测试 [root@localhost ~]# mysql -ucsl -p123 -h192.168.153.138 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 3 Server version: 5.7.35 MySQL Community Server (GPL) Copyright (c) 2000, 2021, 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> exit Bye
修改配置文件
//修改主库配置文件 [root@localhost ~]# vim /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 //添加此行 log-bin = mysql_bin //添加此行 //重启服务 [root@localhost ~]# systemctl restart mysqld.service //修改从库配置文件 [root@localhost ~]# vim /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 = 20 relay-log = my_relay //重启服务 [root@localhost ~]# systemctl restart mysqld.service
开启主从服务
//在主库上查看主库信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//在从库上配置主库信息
mysql> change master to
-> master_host='192.168.153.138' ,
-> master_user='csl' ,
-> master_password='123' ,
-> master_log_file=' mysql_bin.000003' ,
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.153.138
Master_User: csl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: my_relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000003
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
检查主从
//主库创建数据库 mysql> create database csl; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | csl | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) //从库查看 mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | csl | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
在zabbix agent端编写脚本
[root@localhost ~]# mkdir /scripts
[root@localhost ~]# cd /scripts/
[root@localhost scripts]# touch check_replication.sh
[root@localhost scripts]# chmod +x check_replication.sh
[root@localhost scripts]# vim check_replication.sh
#!/bin/bash
count=$(mysql --defaults-file=/scripts/.password -e 'show slave statusG' | grep '_Running:' |grep -c 'Yes' )
if [ $count -ne 2 ];then
echo '1'
else
echo '0'
fi
[root@localhost ~]# vim /scripts/.password
[client]
user=zabbix
password=123
[root@localhost scripts]# ./check_replication.sh
0
//授权登录的用户
[root@localhost scripts]# mysql -uroot -p123
mysql> grant select on *.* to 'zabbix'@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant SUPER, REPLICATION CLIENT on *.* to 'zabbix'@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改agent的配置文件
[root@localhost ~]# cd /usr/local/etc/ [root@localhost etc]# vim zabbix_agentd.conf # Mandatory: no # Range: 0-1 # Default: UnsafeUserParameters=1 //取消注释，并改为1 UserParameter=check_replication,/scripts/check_replication.sh //在文件末尾添加此行 //重启 [root@localhost ~]# zabbix_agentd //在server端检查key是否可用 [root@localhost ~]# zabbix_get -s 192.168.153.137 -k check_replication 0
创建监控项
添加触发器
触发报警
//从库触发报警 [root@localhost ~]# mysql -uroot -p123 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 53 Server version: 5.7.35 MySQL Community Server (GPL) Copyright (c) 2000, 2021, 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> //zabbix server端 查看键值 [root@localhost ~]# zabbix_get -s 192.168.153.137 -k check_replication 1
查看邮件
agent端写脚本
//写主从延迟脚本
[root@localhost scripts]# vim check_replication_delay.sh
#!/bin/bash
delay_count=$(mysql --defaults-file=/scripts/.password -e 'show slave statusG'| grep 'Behind' | awk '{print $2}')
if [ "$delay_count" != NULL ];then
echo $delay_count
else
echo 0
fi
[root@localhost scripts]# chmod +x check_replication_delay.sh
[root@localhost scripts]# ./check_replication_delay.sh
0
agent端修改配置文件
// [root@localhost scripts]# vim /usr/local/etc/zabbix_agentd.conf UserParameter=check_replication_delay,/scripts/check_replication_delay.sh //添加此行 [root@localhost scripts]# pkill zabbix [root@localhost scripts]# zabbix_agentd
服务端检查键值
[root@localhost ~]# zabbix_get -s 192.168.153.137 -k check_replication_delay 0
创建监控项
添加触发器
效果
