• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

91.mysql主从配置自动部署

mysql技术 Margotchen 37次浏览 已收录 0个评论
#!/bin/bash
#The script is used for master-slave configuration.
#Date 2021-02-21
master_ip=192.168.32.1
slave_ip=192.168.32.2
mysql="/usr/local/mysql/bin/mysql -uroot -ppassword"
my_cnf=/etc/my.cnf
master_dir=/tmp/mysql_slave
pass="hSlneoT03j"
#检测命令是否执行成功
check_ok() {
if [ $? -ne 0 ];then
        echo "$1 command is error."
        exit 1
fi
}
#检测文件是否存在
file_exist() {
    d=`date +%F-%T`
    if [ -f $1 ];then
        mv $1 $1_$d
    fi
}
#判断是否含有serve-id配置项,无则添加
if ! grep '^server-id' $my_cnf;then
        sed -i '/^\[mysqld\]$/a\server-id = 1001' $my_cnf
fi
#判断是否含有log-bin配置项,无则添加
if ! grep '^log-bin.*=.*' $my_cnf;then
    sed -i '/^\[mysqld\]$/a\log-bin = maria_bin' $my_cnf
fi
#判断是否含有binlog-ignore-db配置项,无则添加
if ! grep '^binlog-ignore-db'  $my_cnf;then
sed -i '/^log-bin.*/a\binlog-ignore-db = mysql' $my_cnf
fi
#重启mysql服务
systemctl restart mysql
#检测是否重启成功
check_ok "restart mysql"
#EOF赋值mysql从库账号密码
$mysql << EOF
    grant replication slave on *.* to 'repl'@$slave_ip identified by '$pass';
    flush tables with read lock;
EOF
#判断是否有该存放目录
[ -d $master_dir ] || mkdir -p $master_dir
#将master两个参数File和Position存放至master.log
$mysql -e "show master status" > $master_dir/master.log
file=`tail -1 $master_dir/master.log |awk '{ print $1}'`
pos=`tail -1 $master_dir/master.log |awk '{print $2}'`
#判断文件是否存在
file_exist $master_dir/slave.sh
a='EOF'#定义变量
#从库shell脚本
cat > $master_dir/slave.sh <<EOF
#!/bin/bash
check_ok() {
if [ $? -ne 0 ];then
        echo "$1 command is error."
        exit 1
fi
}
#判断my.cnf文件是否包含server-id配置项,无则添加
if ! grep '^server-id' /etc/my.cnf;then
    sed -i '/\[mysqld\]$/a\server-id = 1002' /etc/my.cnf
fi
#重启服务
systemctl restart mysql
check_ok "slave mysql restart"
$mysql <<EOF
        stop slave;
        change master to master_host="$master_ip",master_user='repl',master_password="$pass",
        master_log_file="$file",master_log_pos=$pos;
        start slave;
$a#内嵌不能双EOF,用变量代替
EOF
#检测文件是否存在
file_exist $master_dir/rs_slave.expect
#内嵌expect交互脚本将slave.sh传输至从库
cat > $master_dir/rs_slave.expect <<EOF
#!/usr/bin/expect
set passwd "password"
spawn rsync -a $master_dir/slave.sh root@$slave_ip:/tmp/slave.sh
expect {
        "yes/no" { send "yes\r"}
        "password:"{ send "\$passwd\r"}
}
expect eof
EOF
#rs_slave.expect文件给予执行权限
chmod +x $master_dir/rs_slave.expect
$master_dir/rs_slave.expect
check_ok "rysnc"
#检测文件是否存在
file_exist $master_dir/exe.expect
#内嵌expect交互脚本执行命令
cat > $master_dir/exe.expect <<EOF
#!/usr/bin/expect
set password "password"
spawn ssh root@$slave_ip
expect {
        "yes/no"{ send "yes\r"}
        "password:"{ send "\$password\r" }
}
expect "]*"
send "/bin/bash /tmp/slave.sh\r"
expect "]*"
send "exit\r"
EOF
#exe.expect文件给予执行权限
chmod +x $master_dir/exe.expect
$master_dir/exe.expect
check_ok "exe.expect excute“
#主库解除锁表
$mysql -e "unlock tables"

VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明91.mysql主从配置自动部署
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址