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 status\G' | 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 status\G' | 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 users\G;
*************************** 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)