mysql主从(主备)同步一键配置,配自动检测功能
2024-10-14 09:23:06
主从一键shell配置
做个笔记。
#!/bin/bash
#Mysql sync
#chenglee
#master机器ip
MasterIP="192.168.137.174"
#slave机器ip
SlaveIp="192.168.137.170"
#主从同步账号密码
MASTER_USER="sync"
MASTER_PASSWORD="sync"
#master机mysql登录账号密码
Muser="root"
Mpass="123456"
#slave机mysql登录账号密码
Suser="root"
Spass="123456"
#slave机ssh的登录账号密码
SSHuser="root"
SSHpass="lizhenghua" 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
最新文章
- 斯坦福iOS7公开课11笔记及演示Demo&;访问HTTPS链接下载数据
- Ant build ${renderscript.opt.level}问题解决方案
- 【转】互联网全站HTTPS的时代已经到来
- springboot pom 引用集合
- 剑指OFFER之用两个栈实现队列(九度OJ1512)
- NagiosQL 跨站脚本漏洞
- MongoDB 操作
- TCP、UDP和HTTP
- GMSK调制仿真
- Tcl与Design Compiler (八)——DC的逻辑综合与优化
- 一、PTA实验作业
- Linux下创建和删除软、硬链接 可临时处理空间不足
- Ubuntu16.10上安装NodeJS6.9.2
- 外键的约束(Mysql、PostgreSQL)
- 解决EasyUi中的DataGrid删除一条记录后,被删除的数据仍处于被选中状态问题
- 学习笔记之YAML
- GIT的使用及心得
- Restful 接口传递参数
- 【BZOJ3240】【NOI2013】矩阵游戏(数论)
- Python+Selenium 自动化实现实例-Xpath捕捉元素的几种方法
热门文章
- 机器学习算法的调试---梯度检验(Gradient Checking)
- 编辑器source insight,sublime,编码 utf-8和 GB2312
- Xcode 运行 Signing for ";XXXXXX"; requires selecting either a development team or a provisioning profile. Select a development team or a provisioning profile in the project editor
- vue中使用echarts
- /*使用PHP创建一个数组,保存5個员工的信息(ename/sex/salary/birthday/pic)*/
- SVN windows内修改日志内容(错误解决)
- DataRow 点不出 Select
- yii2 modules模块配置指南
- java设计模式之动态代理的概述和实现
- 微信小程序制作家庭记账本之七