一、准备

OS: CentOS6.5

Mysql：mysql5.7.15

关闭SELINUX

关闭iptables

a) 主库 master 10.1.34.64

b) 从库 slave 10.1.34.62

 

二、步骤

a) 主库 master 10.1.34.64

1.配置my.cnf

[mysqld]

binlog_format = row   #日志格式有statement/row/mixed3种，一般来说row最好！

server-id = 643306    #主从服务器的server-id不一致就可以，一般的习惯是改为IP末尾数字加端口号

log-bin = /data/mysql/logs/mysql-bin    #日志文件路径及名称，可以只写mysql-bin也可以，这样默认会在data目录下。

 

#其他:

binlog_cache_size = 4M

max_binlog_cache_size = 1M

#具体见 http://blog.csdn.net/lxpbs8851/article/details/38455223

max_binlog_size = 256M    #binlog文件轮循大小，默认是1G

sync_binlog = 0    #1是最安全，保证数据一致性。0最效率，性能损失最小

expire_logs_days = 10    #单位是天，自动清理过期binlog

 

2、建立用于同步的账号rep

在主数据库中创建如下账号：

 

grant replication slave on *.* to 'rep'@'%' identified by 'awkxy';
flush privileges;

b) 从库 slave 10.1.34.62

配置my.cnf

 

[mysqld]
binlog_format = row
server-id = 623306
log-bin = /data/mysql/logs/mysql-bin

3. 主库

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      588 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

上面的file和position要记下，从库设置master_log_file和master_log_pos的配置要一致

4.从库

CHANGE MASTER TO  
MASTER_HOST='10.1.34.64',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='awkxy',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=588;


start slave;
show slave status\G;

5.测试，主库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.05 sec)

mysql> CREATE DATABASE IF NOT EXISTS awkxy DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| awkxy              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| awkxy              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

 改gtid模式

gtid-mode=on

enforce-gtid-consistency=1    #强制gtid一致性

 

change master to master_host=’10.1.34.64′,

master_port=3306,master_user=’rep’,master_password=’awkxy’,master_auto_position=1;

(因为之前已经change 过master，其实只需要

change master to master_auto_position=1;)

 

Gtid切换主从的方法

https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_gtid.html

 

最后附上监控主从复制的脚本（邮件报警需要安装下sendmail）：

#!/bin/bash
#check mysql_slave status
#auther:[email protected]

MYSQLPORT=`netstat -na | grep "LISTEN"|grep "3306" |awk -F[:" "]+ '{print $5}'`
MYSQLIP=$(ifconfig |grep inet| grep -v "127.0.0.1" |awk -F[:" "]+ '{print $4}')
STATUS=$(/usr/local/mysql/bin/mysql -u wupeng2 -pXXXX -e "show slave status\G" \
    |grep -i "running")
IO_env=`echo $STATUS |grep IO |awk '{print $2}'`
SQL_env=`echo $STATUS |grep SQL |awk '{print $2}'`
DATE=`date +%Y-%m-%d:%H-%M`
LOGDIR=/root/log
LOGNAME=check_mysql_slave.log
EMAIL="[email protected]"

[ -d ${LOGDIR} ] || mkdir -p /root/log
[ ! -f ${LOGDIR}/${LOGNAME} ] &&  touch ${LOGDIR}/${LOGNAME}

if [ "$MYSQLPORT" == "3306" ]
then
	echo "mysql is running!"
else
	mail -s "warning ! Mysql server:$MYSQLIP  is down" ${EMAIL}
fi

if [ "$IO_env" == "Yes" -a "$SQL_env" == "Yes" ]
then
	echo "slave is OK!"
else
	cat <<EOF>> ${LOGDIR}/${LOGNAME}
"------${DATE}------"
"slave is not running!" 
"--------------------"
EOF
	mail -s "Mysql slave is down" ${EMAIL} < ${LOGDIR}/${LOGNAME}
fi