mysql的主从配置+shell脚本的检测和邮箱报警（版本centos7）

1.关闭防火墙

systemctl stop firewalld
setenforce 0

2.安装mysql

yum -y install mariadb mariadb-server

3.mysql的主从配置（主）

vim /etc/my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin

systemctl start mariadb

进入mysql中，授权一个从连接主的用户tom

grant all on *.* to tom@'%' identified by '123';

查看二进制日志

MariaDB [(none)]> show master status;

4.mysql的主从配置（从）

vim /etc/my.cnf

[mysqld]
server-id=2
relay-log=mysql-relay

systemctl start mariadb

停掉主从同步

MariaDB [(none)]> stop slave;

同步的主机IP，用户名，密码，binlog文件，binlog位置等信息

MariaDB [(none)]> change master to
    -> master_host='192.168.116.129',
    -> master_user='tom',
    -> master_password='123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected (0.03 sec)

开启主从同步

MariaDB [(none)]> start slave;

验证主从是否同步正常（双Yes）

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.116.129
                    Master_User: tom
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000001
            Read_Master_Log_Pos: 451
                 Relay_Log_File: mysql-relay.000002
                  Relay_Log_Pos: 735
          Relay_Master_Log_File: mysql-bin.000001
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

5.安装配置mail

yum -y install mailx

vim /etc/mail.rc

#########################
set from=xxxxxxxx@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=xxxxxxxx@qq.com
set smtp-auth-password=xxxxxxxxxxx（此处为邮箱授权码）
set smtp-auth=login
########################

6.编写脚本

 vim /shell/check_mysql.sh

#!/bin/bash
num=`mysql -e "show slave status \G" | grep Yes | wc -l`
if [ $num -eq 2 ];then
        echo "mysql 主从正常！"
else
        echo "mysql 主从异常，请及时修正！" | mail -s "check mail" xxxxxxxxx@qq.com
fi

7.进行脚本测试

[root@localhost ~]#chmod +x /shell/check_mysql.sh
[root@localhost shell]#cd /shell/
[root@localhost shell]#./check_mysql.sh

[root@localhost ~]# sh /shell/check_mysql.sh 
mysql 主从正常！