搭建mysql主从并编写监控主从状态脚本

yum  -y install mysql mysql-server
复制

service  iptables  stop
setenforce 0
复制

vi  etc/my.cnf
#添加
server-id=1
log-bin=mysqlbin
relay-log=relays
复制

service mysqld  restart
复制

mysql
grant all  on *.* to pdh@'%' identified by '123';
flush privileges;
复制

show  master  status;
复制

vi  etc/my.cnf
#添加
server-id=2
log-bin=mysqlbin
relay-log=relays
复制

service mysqld  restart
复制

mysql


MariaDB [(none)]> change master to 
    -> master_host='主mysql的IP地址',
    -> master_user='pdh',
    -> master_password='123',
    -> master_log_file='主上面的二进制名称',
    -> master_log_pos=主上二进制位置信息;
复制

start slave;
复制

show  slave status \G;
复制

#!/bin/bash
IO=$(mysql -e "show slave status \G;" | grep 'Slave_IO_Running'| awk '{print $2}')
SQL=$(mysql -e "show slave status \G;" | grep 'Slave_SQL_Running'| awk '{print $2}')


if [ $IO = $SQL -a $IO = 'Yes' ];then
    echo "同步正常"
else
    echo "同步异常,尝试修复同步！"
    mysql -e 'stop slave;set global sql_slave_skip_counter=1;start slave;'
    IO=$(mysql -e "show slave status \G;" | grep 'Slave_IO_Running'| awk '{print $2}')
    SQL=$(mysql -e "show slave status \G;" | grep 'Slave_SQL_Running'| awk '{print $2}')
    if [ $IO = $SQL -a $IO = 'Yes' ];then
       echo "恢复正常!"
    else
       echo "修复失败!"
    fi
fi
复制