shell脚本实现MySQL主从复制（一主一从）

 本脚本为实现一主一从的主从复制的脚本，前提条件为主库和从库的数据库密码都为123，此脚本只需要在主库执行即可，无需在从库执行。仅供大家参考和讨论

#!/bin/bash
#主数据库脚本
systemctl stop firewalld
setenforce 0

echo '正在安装必备组件'
yum -y install expect &> /dev/null
UIP=`ip a | grep ens33 | awk 'NR==2' | awk '{print $2}' | awk -F "/" '{print $1}'`

#免密登录
miyao(){
/usr/bin/expect << EOF
spawn ssh-keygen
expect "(/root/.ssh/id_rsa)" {send "\r"}
expect "(empty for no passphrase)" {send "\r"}
expect "again" {send "\r"}
expect eof
EOF
}

fasong(){
/usr/bin/expect << EOF
spawn ssh-copy-id root@$IP
expect "(yes/no)" {send "yes\r"}
expect "password:" {send "1\r"}
expect eof
EOF
}

panduan(){
if [ $? -eq 0 ]
   then
	$1
   else
	$2
	exit
fi
}

echo '准备和从库建立免密连接...'
sleep 2
miyao &> /dev/null 
panduan "echo '密钥已经创建完成'" "echo '密钥创建失败'"
read -p '请输入从服务器IP：' IP
fasong



grep 'server-id=1' /etc/my.cnf &> /dev/null
if [ $? -eq 0 ];
    then
        break
    else
        echo 'log-bin=/var/log/mysql/mysql-bin' >> /etc/my.cnf
        echo 'server-id=1' >> /etc/my.cnf
fi

#创建日志目录并该权限
mkdir /var/log/mysql &> /dev/null
chown mysql.mysql /var/log/mysql -R /dev/null
systemctl restart mysqld
echo '日志目录创建成功！'
echo '修改权限成功！'

#数据库内创建主从同步用户
mysql -uroot -p123 -e "grant all on *.* to 'repl'@'%' identified by '123';" 
mysql -uroot -p123 -e "flush privileges;" 
master_file=`mysql -uroot -p123 -e "show master status\G" | awk 'NR==2' | awk -F ":" '{print $2}'` 
master_pos=`mysql -uroot -p123 -e "show master status\G" | awk 'NR==3' | awk -F ":" '{print $2}'` 
echo '主从同步用户创建成功！密码为123'

#从库脚本
cat > /root/congku.sh << EOF
#!/bin/bash
systemctl stop firewalld
setenforce 0

grep 'validate_password=off' /etc/my.cnf &> /dev/null
if [ \$? -eq 0 ];
    then
        break
    else
        echo 'validate_password=off' >> /etc/my.cnf
        systemctl restart mysqld
fi


grep 'server-id' /etc/my.cnf &> /dev/null
if [ \$? -eq 0 ];
    then
        systemctl restart mysqld
    else
        echo 'server-id=2' >> /etc/my.cnf
	systemctl restart mysqld
fi

mysql -uroot -p'123' -e "CHANGE MASTER TO
MASTER_HOST='$UIP',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='$master_file',
MASTER_LOG_POS=$master_pos;"

mysql -uroot -p'123' -e "start slave;"
mysql -uroot -p'123' -e "stop slave;"
mysql -uroot -p'123' -e "reset slave;"
mysql -uroot -p'123' -e "start slave;"
EOF

sleep 2
echo '从库脚本创建成功，正在发送至从服务器并执行...'
#在从库里执行脚本
scp /root/congku.sh $IP:/opt
ssh root@$IP "sh /opt/congku.sh"
echo '脚本执行完成'