Centos7下Mysql8配置主主同步
主机信息
主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
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
正文到此结束
