1、自定义MySQL主从监控和声音报警

1.1 部署MySQL主从

数据库主服务器

[root@master ~]# curl -o /etc/yum.repos.d/CentOS-base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
[root@sql-master ~]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-base.repo
[root@master ~]# yum -y install mariadb mariadb-server
[root@master ~]# systemctl start mariadb.service 
[root@master ~]# systemctl enable mariadb.service
[root@master ~]# mysql_secure_installation
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

数据库从服务器

[root@slave ~]# curl -o /etc/yum.repos.d/CentOS-base.repo https://mirrors.aliyun.com/repo/Centos-8.repo
[root@slave ~]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-base.repo
[root@slave ~]# yum -y install mariadb mariadb-server
[root@slave ~]# systemctl start mariadb.service 
[root@slave ~]# systemctl enable mariadb.service
[root@slave ~]# mysql_secure_installation
[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl disable firewalld.service 
[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

修改配置文件

数据库主服务器

[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf

// 添加内容
[mysqld]
log_bin=mysql-bin     //开启二进制日志
server_id=1           //服务id号，不可从复，值为0时则表示拒绝服务器连接

[root@master ~]# systemctl restart mariadb.service

数据库从服务器：

[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf

// 添加内容
[mysqld]
server_id=2			//服务id号，不可重复,为0则拒绝从服务器连接
relay_log=myrelay  //开启中继日志

[root@slave ~]# systemctl restart mariadb.service

创建用户并授权，让从服务可以登陆主服务器
数据库主服务器

[root@master ~]# mysql -uroot -p123456
// 创建用户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'yibie'@'%' IDENTIFIED BY 'yibie123!';
Query OK, 0 rows affected (0.000 sec)

// 刷新权限
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      639 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

登陆到从服务器的数据库，在数据库中配置主服务器的信息

[root@slave ~]# mysql -uroot -p123456

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.23.117',MASTER_PORT=3306,MASTER_USER='yibie',MASTER_PASSWORD='yibie123';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW SLAVE STATUS G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.23.117
                   Master_User: yibie
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 759
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 1058
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

从服务器安装zabbix

下载zabbix软件包、解压、创建zabbix用户、安装依赖包、编译安装

[root@slave ~]# cd /usr/src/
[root@slave src]# wget https://cdn.zabbix.com/zabbix/sources/stable/5.4/zabbix-5.4.4.tar.gz
[root@slave src]# ll
total 23700
drwxr-xr-x. 2 root root        6 Aug 12  2018 debug
drwxr-xr-x. 2 root root        6 Aug 12  2018 kernels
-rw-r--r--. 1 root root 24266079 Aug 30 16:23 zabbix-5.4.4.tar.gz
[root@slave src]# tar xf zabbix-5.4.4.tar.gz 
[root@slave src]# useradd -r -M -s /sbin/nologin zabbix
[root@slave src]# yum -y install net-snmp-devel libevent-devel make gcc gcc-c++

// 编译安装
[root@slave src]# cd zabbix-5.4.4/
[root@slave zabbix-5.4.4]# ./configure --enable-agent
[root@slave zabbix-5.4.4]# make install

// 更改zabbix配置文件
[root@slave ~]# vim /etc/zabbix_agentd.conf
Server=192.168.23.116
ServerActive=192.168.23.116
Hostname=192.168.23.118

1.2 编写脚本文件

指定key和脚本位置

//修改配置文件，让key可以执行脚本识别MySQL状态
[root@slave scripts]# vim /etc/zabbix_agentd.conf
...
UnsafeUserParameters=1
...
//在文件末尾添加
UserParameter=check_replication,/scripts/check_replication.sh

// 杀死进程重新启动
[root@slave scripts]# pkill zabbix_agentd 
[root@slave scripts]# zabbix_agentd 
[root@slave ~]# mysql -uroot -p123456
//创建一个zabbix的用户，并设置权限只读和密码
MariaDB [(none)]> grant select on *.* to 'zabbix'@'localhost' identified by 'zabbix';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant SUPER, REPLICATION CLIENT on *.* to 'zabbix'@'localhost' identified by 'zabbix';
Query OK, 0 rows affected (0.001 sec)

//刷新权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

[root@slave ~]#  mkdir /scripts
[root@slave ~]#  cd /scripts
[root@slave scripts]# vim check_replication.sh
#!/bin/bash

count=$(mysql -uzabbix -pzabbix -e 'show slave statusG' | grep '_Running' | grep -c 'Yes')

if [ $count -ne 2 ];then
        echo '1'
else
        echo '0'
fi

[root@slave scripts]# chmod +x check_replication.sh 
// 测试脚本
[root@slave scripts]# ./check_replication.sh 
0
// 在到zabbix服务端测试
[root@localhost ~]# zabbix_get -s 192.168.23.118 -k check_replication
0

1.3 web界面配置监控和触发器

添加监控主机

添加监控项

添加触发器

手动触发报警

// 从服务器上关闭主从复制
[root@slave ~]# mysql -uroot -p123456
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW SLAVE STATUS G
*************************** 1. row ***************************
                Slave_IO_State: 
                   Master_Host: 192.168.23.117
                   Master_User: yibie
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 759
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 1058
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: No
             Slave_SQL_Running: No

声音报警
这里不同的报警等级声音也是不一样的可以根据自己的喜好来更改声音和报警次数

2、自定义MySQL主从延迟

编写脚本

[root@slave scripts]# vim check_replication_delay.sh 
#!/bin/bash

delay=$( mysql -uzabbix -pzabbix -e 'show slave statusG' | grep 'Behind'| awk '{print $2}' )
if [ $delay != NULL ];then

        echo '$delay'
else    
        echo '0'
fi
[root@slave scripts]# chmod +x check_replication_delay.sh 
[root@slave scripts]# ./check_replication_delay.sh 
$delay

修改配置文件

[root@slave scripts]# vim /etc/zabbix_agentd.conf

UserParameter=check_replication_delay,/scripts/check_replication_delay.sh

[root@slave scripts]# pkill zabbix_agentd      
[root@slave scripts]# zabbix_agentd 

[root@localhost ~]# zabbix_get -s 192.168.23.118 -k check_replication_delay
$delay

web界面配置监控项和触发器

3、用户和组权限

这里所显示的用户都是来自zabbix数据库中的表，如果你想添加新的用户有两种方法一种是直接在web界面添加新的用户，或者到数据库中插入数据，然后就可以在web界面中看到所添加的用户

添加用户到管理员组

此时可以去数据库中查看是否添加用户成功

[root@localhost ~]# mysql -uroot -pabc123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_zabbix           |
+----------------------------+
| users                      |
+----------------------------+
166 rows in set (0.00 sec)
mysql> select * from usersG;

*************************** 3. row ***************************
        userid: 3
      username: yibie
          name: 
       surname: 
        passwd: $2y$10$IM1y4KP2RBzvWMekXYq.7.oT7KAQ/D6qWQsJxZwGr3Z0F9CC0Qip2
           url: 
     autologin: 0
    autologout: 0
          lang: default
       refresh: 30s
         theme: default
attempt_failed: 0
    attempt_ip: 
 attempt_clock: 0
 rows_per_page: 50
      timezone: default
        roleid: 2
3 rows in set (0.00 sec)

修改用户密码
如果你已经登陆到了web界面，却忘记了用户密码可以直接点击用户名到里面修改密码。

而如果忘记密码而登陆不上zabbix web页面时，可以到zabbix服务器系统上进行更改密码
zabbix用户的密码是使用MD5加密方式，所有只需要生成一个MD5加密的密码替换即可

//生成一个新的MD5密码，密码是yibie 加密后是下面的字符串
//进入到数据库中更新密码
[root@localhost ~]# echo -n yibie |openssl md5
(stdin)= 4a9c940e9f4128afeafefc6b75ab68a6
[root@localhost ~]# mysql -uroot -pabc123
mysql> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update users set passwd='4a9c940e9f4128afeafefc6b75ab68a6' where userid = '3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Zabbix自定义监控(主从延迟)

1、自定义MySQL主从监控和声音报警

Linux相关栏目本月热门文章