Centos7下Mysql8配置主主同步

  baikapala

主机信息

主A:

172.16.109.226

mysql版本:8.0.11

端口 3306

主B:

172.16.109.227

mysql版本:8.0.11

端口3306

两主机配置、目录结构,mysql配置均相同。

相关教程参考

https://yq.aliyun.com/articles/580959?utm_content=m_1000011089

https://blog.csdn.net/zyhlwzy/article/details/80569422

主从同步原理:

http://www.360doc.com/content/16/0624/16/34500721_570422538.shtml

操作步骤

1、A节点配置

vi /etc/my.cnf

# 增加
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=2
sync_binlog=1
auto-increment-increment=2
auto-increment-offset=1 
binlog_format=mixed
relay-log=mysql-relay-bin
log-slave-updates
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

# 重启mysql
/etc/init.d/mysqld restart

配置解析:

log-bin binlog日志文件名(可以任意命名)
server-id 设置主服务器的ID,可以任意配置但是多个主从之间不能重复
innodb_flush_log_at_trx_commit 参考https://blog.csdn.net/codepen/article/details/52160715
sync_binlog 参考https://www.cnblogs.com/lehao/p/3915824.html
auto-increment-increment 自增长字段从那个数开始
auto-increment-offset 自增长字段每次递增的量
binlog_format binlog日志格式,mysql默认采用statement,建议使用mixed 参考https://www.cnblogs.com/langtianya/p/5504774.html
log-slave-updates 参考http://www.bubuko.com/infodetail-518747.html
replicate-wild-ignore-table 复制时排除指定的库

其他配置

# 这个表示只同步某个库 (如果没有此项,表示同步所有的库)
binlog-do-db=leisureCar
# 复制时指定某个库,只复制该库
replicate_wild_do_table=leisureCar.%

# binlog过期清理时间
expire_logs_days=7   
# binlog每个日志文件大小                        
max_binlog_size=100m    
# binlog缓存大小                   
binlog_cache_size=4m   
# 最大binlog缓存大小                     
max_binlog_cache_size=512m 

2、B节点配置

vi /etc/my.cnf

# 增加
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=2
sync_binlog=1
auto-increment-increment=2
auto-increment-offset=1 
binlog_format=mixed
relay-log=mysql-relay-bin
log-slave-updates
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


# 重启mysql
/etc/init.d/mysqld restart

如果两主机上的mysql是复制来的,需要修改uuid:

vi $MYSQL_HOME/data/auto.cnf

修改uuid即可,保证不一样

3、配置A->B同步

以下在A节点上操作:

A节点创建用户rep1,用于B节点链接A节点时使用。

CREATE USER 'repl'@'172.16.109.227' IDENTIFIED WITH mysql_native_password BY '111111';
GRANT REPLICATION SLAVE ON,replication client *.* TO 'repl'@'172.16.109.227';
flush privileges;

获取A节点当前binary log文件名和位置(position)

flush logs; 
SHOW MASTER STATUS;

以下在B节点上操作:

从B上设置A节点参数:

CHANGE MASTER TO MASTER_HOST='172.16.109.226',
MASTER_USER='repl',
MASTER_PASSWORD='111111',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=155;

开启同步:

start slave;

查看同步状态:

show slave status\G;

4、配置B->A同步

以下在B节点上操作:
B节点创建用户rep1,用于A节点链接B节点时使用。

CREATE USER 'repl'@'172.16.109.226' IDENTIFIED WITH mysql_native_password BY '111111';

GRANT REPLICATION SLAVE,replication client ON *.* TO 'repl'@'172.16.109.226';

flush privileges;

获取B节点当前binary log文件名和位置(position)

flush logs; 
SHOW MASTER STATUS;

以下在A节点上操作:
A节点上设置B节点参数:

CHANGE MASTER TO MASTER_HOST='172.16.109.227',

MASTER_USER='repl',

MASTER_PASSWORD='111111',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=155;

开启同步:

start slave;

查看同步状态:

show slave status\G;

测试

分别在主从两个数据库创建创建不同的表aa和bb

再手动插入几天数据,

刷新查看两个数据库的内容是不是一致

问题

重启后需要重新开启同步 start slave;
如果状态有误,需要重新SHOW MASTER STATUS查看,在执行CHANGE MASTER TO语句

监控

参考脚本:

#!/bin/bash
#Mysql sync
#chenglee
#master机器ip
MasterIP="172.16.109.226"
#slave机器ip
SlaveIp="172.16.109.227"
#主从同步账号密码
MASTER_USER="rep1"
MASTER_PASSWORD="111111"
#master机mysql登录账号密码
Muser="root"
Mpass="111111"
#slave机mysql登录账号密码
Suser="root"
Spass="111111"
#slave机ssh的登录账号密码
SSHuser="root"
SSHpass="DCtest@0123"
 
function Master(){
    #MASTER
    function create(){
        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "flush PRIVILEGES;" >/dev/null 2>&1
        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "CREATE USER '${MASTER_USER}'@'${SlaveIp}' IDENTIFIED BY '${MASTER_PASSWORD}';" >/dev/null 2>&1
        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "grant replication slave on *.* to '${MASTER_USER}'@'${SlaveIp}' identified by '${MASTER_PASSWORD}';" >/dev/null 2>&1
        mysql --protocol=TCP -u ${Muser} -p''${Mpass}'' --connect-expired-password -e "flush PRIVILEGES;" >/dev/null 2>&1
    }
    #my.cnf
    function addbin(){
        number=`cat -n /etc/my.cnf | grep -F "[mysqld]" | awk '{print$1}'`
        serverID=`echo ${MasterIP} | awk -F'.' '{print$NF}'`
        text1="server-id=${serverID}"
        text2="log-bin=mysql-bin"
        text3="lower_case_table_names=1"
        sed -i "${number}a\\${text1}\n${text2}\n${text3}" /etc/my.cnf
    }
    #restart
    function Mrestart(){
        service mysql restart
    }
    #ssh
    function mstscSSH(){
        echo -e "\033[43;35m 开始配置双机交互,请按回车后输入slave机登录密码 \033[0m"
        ssh-keygen -N ''
        ssh-copy-id ${SSHuser}@${SlaveIp}
    }
echo "创建同步账号"
create
echo "添加master端id到my.cnf文件"
addbin
echo "重启master端数据库"
Mrestart
echo "开始账号交互"
mstscSSH
}
#SLAVE
function Slave(){
    #my.cnf
    function addbin2(){
        Snumber2=`ssh ${SSHuser}@${SlaveIp} "cat -n /etc/my.cnf | grep -F "[mysqld]" | awk '{print$1}'" | awk '{print$1}'`
        SserverID2=`echo ${SlaveIp} | awk -F'.' '{print$NF}'`
        Stext1="server-id=${SserverID2}"
        Stext3="lower_case_table_names=1"
        ssh ${SSHuser}@${SlaveIp} "sed -i '${Snumber2}a\\${Stext1}\n${Stext3}' /etc/my.cnf"
    }
    #restart
    function Srestart(){
        ssh ${SSHuser}@${SlaveIp} "service mysql restart"
    }
    function Ssync(){
        mkdir logs
        mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e "show master status;" > logs/chenglee.logs
        MASTER_HOST=${MasterIP}
        MASTER_LOG_FILE=`cat logs/chenglee.logs | tail -n 1 | awk '{print$1}'`
        MASTER_LOG_POS=`cat logs/chenglee.logs | tail -n 1 | awk '{print$2}'`
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\"" >/dev/null 2>&1
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\"" >/dev/null 2>&1
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave all;\"" >/dev/null 2>&1
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}, MASTER_CONNECT_RETRY=10;\"" >/dev/null 2>&1
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\"" >/dev/null 2>&1
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"show slave status \G;\"" > logs/slave_sync.logs
}
echo "添加master端id到my.cnf文件"
addbin2
echo "重启slave端数据库"
Srestart
echo "开始同步配置"
Ssync
}
#校准同步
#:提示
#当发现主从同步失败的时候, 开启此选项则自动检测并重新配置同步
function calibrate(){
    mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e "show master status;" > logs/chenglee.logs
    ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"show slave status \G;\"" > logs/slave_sync.logs
    MASTER_HOST=${MasterIP}
    MASTER_LOG_FILE=`cat logs/chenglee.logs | tail -n 1 | awk '{print$1}'`
    MASTER_LOG_POS=`cat logs/chenglee.logs | tail -n 1 | awk '{print$2}'`
    string1=`cat logs/slave_sync.logs | sed -n '12,13p' | awk -F ':' '{print$2}' | head -n 1`
    string2=`cat logs/slave_sync.logs | sed -n '12,13p' | awk -F ':' '{print$2}' | head -n 2 | tail -n 1`
    if [ ${string1} = ${string2} ];then
        if ( echo ${string1} |grep -q 'Yes' && echo ${string2} |grep -q 'Yes' );then
            echo "主从已同步,无需校准"
        else
            echo "1主从已断开,准备校准"
            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\""
            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\""
            ssh ${SSHuser}@${SlaveIp} "service mysql restart"
            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}\""
            ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\""
            echo "1校准成功"
        fi
        exit
    else
        echo "2主从已断开,准备校准"
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"stop slave;\""
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"reset slave;\""
        ssh ${SSHuser}@${SlaveIp} "service mysql restart"
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"change master to master_host='${MasterIP}', master_user='${MASTER_USER}', master_password='${MASTER_PASSWORD}', master_log_file='${MASTER_LOG_FILE}', master_log_pos=${MASTER_LOG_POS}\""
        ssh ${SSHuser}@${SlaveIp} "mysql --protocol=TCP -u ${Suser} -p''${Spass} --connect-expired-password -e \"start slave;\""
        echo "2校准成功"
    fi
}
 
function main(){
    #Master
    #Slave
    calibrate
}
main

以前,我总以为自己是菜鸟,也总想着早起的鸟儿有虫吃。直到有一天我想飞,才愕然的发现自己没有翅膀和羽毛,我竟然是菜虫!早起的虫儿被鸟吃……原来,百足应该厚积薄发!