mysql主从的搭建+shell实现检测并邮件报警

做mysql主从复制
安装mysql
[root@localhost ~]# yum -y install mariadb mariadb-server
修改配置文件开启二进制日志（添加在mysqld模块内）
[root@localhost ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-master-bin
关闭防火墙、sulinux。
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
重启mysql
[root@localhost ~]# systemctl restart mariadb
设置mysql密码
[root@localhost ~]# mysqladmin -uroot password 123456
登录数据库
[root@localhost ~]# mysql -uroot -p123456

MariaDB [(none)]> grant all on . to tom@’%’ identified by ‘tom’;
MariaDB [(none)]> show master status \G;
*************************** 1. row ***************************
File: mysql-master-bin.000003
Position: 505
从安装
[root@localhost ~]# yum -y install mariadb mariadb-server
修改配置文件
[root@localhost ~]# vim /etc/my.cnf
server-id=2
log-bin=mysql-server-bin
relay-log=relay-server-bin

关闭防火墙、sulinux
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0

重启mysql
[root@localhost ~]# systemctl restart mariadb

设置mysql密码
[root@localhost ~]# mysqladmin -uroot password 123456

登录数据库
[root@localhost ~]# mysql -uroot -p123456
MariaDB [(none)]> change master to master_host=‘192.168.33.136’,
-> master_user=‘tom’,
-> master_password=‘tom’,
-> master_log_file=‘mysql-master-bin.000003’,
-> master_log_pos=505;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show slave status \G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.33.136
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000003
Read_Master_Log_Pos: 505
Relay_Log_File: relay-server-bin.000002
Relay_Log_Pos: 536
Relay_Master_Log_File: mysql-master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

编写shell脚本实现邮件报警
安装maile
[root@localhost ~]# yum -y install mailx

编辑maile配置文件
[root@localhost ~]# vim /etc/mail.rc
#########################
set from=823916920@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=823916920@qq.com
set smtp-auth-password=ncmekcuebqdcbdic
set smtp-auth=login
########################

编写脚本
[root@localhost ~]# vim /shell/check_mysql.sh

#!/bin/bash
num=mysql -e "show slave status \G" | grep Yes |awk -F ':' '{print $2}' | wc -l
if [ $num -eq 2 ];then
echo “mysql 主从正常！”
else
echo “mysql 主从异常，请及时修正！” | mail -s “chenk mail” 823916920@qq.com
fi
执行脚本测试
[root@localhost ~]# sh /shell/check_mysql.sh
mysql 主从正常！

关闭slave并再次测试
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# sh /shell/check_mysql.sh