mysql 主从复制及监控

主从复制 

第一种: 

主库操作

1、开启binlog server-id

 

vim /etc/my.cnf    ,  mysqld下添加. server-id必须要不同

 

[mysqld]

log_bin = mysql-bin

server-id = 11

 

修改配置重启生效, 检查binlog是否开启生效

 

2、授权用户

grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '123123';

flush privileges;

 

3、锁表备份

mysql> flush table with read lock;

mysql> show master status;

 

 

 

 

重开窗口备份：

mysqldump -A -B --master-data=2 --single-transaction|gzip>/data/backup/mysql_$(date +%F).sql.gz

也有用这个?  mysqldump -A –B –events|gzip >/data/backup/mysql_$(date +%F).sql.gz

数据较少可不压缩   mysqldump -A –B –events –master-data=2 >/data/backup/mysql_$(date +%F).sql

 

解锁

unlock table;

 

从库操作

修改my.cnf  设置与主库不同的server-id  

4、把备份数据推到从库，还原到从库

scp -rp -P22 /data/backup/mysql_$(date +%F).sql.gz 10.0.0.53:/opt

cd /opt

gzip -d mysql_2017-08-15.sql.gz

mysql < mysql_2017-08-15.sql

 

5、mysql库中操作 CHANGE MASTER

[root@db03 ~]# sed -n 22p /opt/mysql_2017-08-23.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=313;

 

 

CHANGE MASTER TO 

MASTER_HOST='172.16.1.11',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123123',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=313;

 

6、开启复制开关，并检查

start slave;

 

[root@db03 data]# mysql -e "show slave status\G"|egrep "IO_Running:|SQL_Running|_Behind_Master"

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

 第二种:

 

初始化数据库

mysql_install_db --datadir=/mysqldata/3306 --basedir=/usr/local/mysql/ --user=mysql --insecure

 

for i in `mysql -P3308 -uXXX -pXXX  -S'/data/3308/mysql.sock' -e "show databases"  | sed 1d | grep -v mysql | grep -v test | grep -v information_schema  | grep -v performance_schema` ; do mysqldump -P3308 -uXXX -pXXX -S'/data/3308/mysql.sock' $i > $i.sql ;done

 

zabbix监控

写好获取监控值得脚本

#!/bin/bash
##############################################################
# File Name:  mysql-master-slave.sh
##############################################################
mysql_cmd="mysql -uroot -p123"

array=($($mysql_cmd  -e "show slave status\G" 2>/dev/null |egrep '_Running:|Behind_Master' |awk '{print $NF}'))


if [ "${array[0]}" == "Yes" -a "${array[1]}" == "Yes" -a "${array[2]}" == "0" ]
  then
    echo "1"
else
  echo '0'
fi

 

在 /etc/zabbix/zabbix_agentd.d/  添加key值

 重启zabbix-agent服务

 

添加监控项及触发器

 

 

 

 

 

 

 

 

mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G