分享一个 mysql 主从数据重建脚本
注意:
1. 这个脚本需要对服务器进行 ssh 密钥认证
2. 多台 slave 需要用 "," 连接
3.DBUSER 需要有 master 数据库导出和 slave 数据库导入以及 stop/start slave 的权限
使用方法:
mysql-replicate-rebuild.sh [master-ip/master-host] [slave-ip/slave-host,slave-ip/slave-host,...] [dbuser] [dbpassword]
- #!/bin/bash
- MASTER=$1
- SLAVE=$(echo $2|sed 's:,: :g')
- DBUSER=$3
- DBPASSWORD=$4
- DATABASES=$(ssh ${MASTER} "mysql -u${DBUSER} -p${DBPASSWORD} -e 'show master status \G;'|grep -oP '(?<=Binlog_Do_DB:).+'|sed 's:,: :'")
- ssh ${MASTER} "mysqldump -u${DBUSER} -p${DBPASSWORD} --master-data=1 --databases ${DATABASES} >/tmp/replicate.data"
- rsync -avc ${MASTER}:/tmp/replicate.data /tmp/
- ssh ${MASTER} "rm -rf /tmp/replicate.data"
- sed -i '1i stop slave;' /tmp/replicate.data
- echo "start slave;" >>/tmp/replicate.data
- for i in $SLAVE
- do
- (
- rsync -avc /tmp/replicate.data ${i}:/tmp/replicate.data
- ssh ${i} "mysql -u${DBUSER} -p${DBPASSWORD} </tmp/replicate.data && rm -rf /tmp/replicate.data"
- echo "${i} replicate finish!"
- )&
- done
- wait
- rm -rf /tmp/replicate.data
来源: