MySQL Slave 自动化部署

手动部署Slave 多套，发现这个玩意还是有点累，随后怒写shell 脚本，希望留给需要的朋友。

说明：1.主库用户 platform 手动建立，进行备库连接及备份使用
2.主库用户repl 作为同步用户

准备：1.主从库需要安装部署完成
2.准备IP，root密码，端口，在脚本中进行编辑
3.因为在Slave 进行备份，需要确认好可用空间容量，保证备份文件能在从库完整备份

开始：根据准备的信息进行编辑，在备库进行执行

完整脚本如下：

#!/bin/bash
# create USER 'platform'@'%' IDENTIFIED BY 'xsw2CDE#' WITH MAX_USER_CONNECTIONS 10;
# GRANT ALL ON *.* TO 'platform'@'%' WITH GRANT OPTION;

# create USER 'rep'@'%' IDENTIFIED BY 'xsw2CDE#';
# GRANT RELOAD, LOCK TABLES, PROCESS,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'%';
# 需要在master 先建立好 platform 用户
# 注意备份文件大小

master_ip=10.84.116.1
master_port=3305
master_user=platform
master_user_pwd='password'
rep_user=rep
rep_user_pwd='password'

slave_ip=10.84.116.8
slave_port=3309
slave_user=root
slave_user_pwd='password'
slave_server_id=`date "+%Y%m%d%H%M%S"`

#backup_time=`date "+%Y-%m-%d_%H:%M"`

check_ok()
{
if [ $? -ne 0 ]
then
echo "$1 出错了。"
exit 1
fi
}

###主库配置
mysql -u${master_user} -h ${master_ip} -p${master_user_pwd} -P${master_port} << EOF
create USER 'rep'@'%' IDENTIFIED BY 'xsw2CDE#';
GRANT RELOAD, LOCK TABLES, PROCESS,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'%';
EOF

#check_ok

mysql -u${master_user} -h ${master_ip} -p${master_user_pwd} -P${master_port} -e "show master status" > /tmp/master.log
check_ok

file=`tail -1 /tmp/master.log|awk '{print $1}'`
pos=`tail -1 /tmp/master.log|awk '{print $2}'`

##配置部署

sed -i 's/^\(server-id\).*/\server-id='${slave_server_id}'/' /data/mysql_data/my_${slave_port}.cnf
cd /data/mysql/mysql5.7
./bin/mysqladmin -u root -p${slave_user_pwd} shutdown -S /data/mysql_data/data_${slave_port}/mysql.sock
check_ok
sleep 10
./bin/mysqld_safe --defaults-file=/data/mysql_data/my_${slave_port}.cnf &
check_ok
sleep 10
mysqldump -h ${master_ip} -P${master_port} -u${master_user} -p${master_user_pwd} -A -R --triggers -E --master-data=2 --single-transaction >mbackup_backup_time.sql
check_ok

mysql -u${slave_user} -h localhost --socket=/data/mysql_data/data_${slave_port}/mysql.sock -p${slave_user_pwd} << EOF
reset master;
reset slave all;
EOF


mysql -u${slave_user} -h localhost --socket=/data/mysql_data/data_${slave_port}/mysql.sock -p${slave_user_pwd}<mbackup_backup_time.sql

check_ok
mysql -u${slave_user} -h localhost --socket=/data/mysql_data/data_${slave_port}/mysql.sock -p${slave_user_pwd} << EOF
stop slave;
change master to master_host="$master_ip",master_port=${master_port}, master_user="$rep_user", master_password="$rep_user_pwd", master_log_file="$file", master_log_pos=$pos;
start slave;
show slave status \G;
EOF
check_ok