MySQL主从自动配置脚本

通过yum安装mariadb，并配置MySQL主从服务器
主服务器：192.168.10.11
从服务器：192.168.10.12

#!/bin/bash
#====================================================
# Author: Mr.Song
# Create Date: 2019-02-21
# Description: auto config MySQL master&slave
#====================================================
MYSQL_PASSWORD="123456"
MASTER_HOST="192.168.10.11"
SLAVE_HOST="192.168.10.12"
SYNC_USER="MySQL_SYNC_USER"
SYNC_USER_PASS="123456"

yum install -y mariadb mariadb-server mariadb-devel

if [ $? -eq 0 ];then
    sed -i '/socket/a\server-id=1\nlog-bin=mysql-bin' /etc/my.cnf
    systemctl start mariadb
else
    exit 1
fi
cat > master.sql <<- EOF
UPDATE mysql.user SET Password=PASSWORD('$MYSQL_PASSWORD') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\\_%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
grant replication slave on *.* to '$SYNC_USER'@'%' identified by '$SYNC_USER_PASS';
FLUSH PRIVILEGES;
EOF
mysql -uroot  < master.sql

SLAVE_LOG_FILE=`mysql -uroot -p111111 -e "show master status;" |grep mysql |awk '{print $1}'`
SLAVE_LOG_POS=`mysql -uroot -p111111 -e "show master status;" |grep mysql |awk '{print $2}'`
cat > slave.sh <<- END
#!/bin/bash
MYSQL_PASSWORD="123456"
MASTER_HOST="192.168.10.11"
SLAVE_HOST="192.168.10.12"
SYNC_USER="MySQL_SYNC_USER"
SYNC_USER_PASS="123456"

yum install -y mariadb mariadb-server mariadb-devel

if [ $? -eq 0 ];then
    sed -i '/socket/a\server-id=2\nlog-bin=mysql-bin' /etc/my.cnf
    systemctl start mariadb
else
    exit 1
fi
cat > slave.sql <<- EOF
UPDATE mysql.user SET Password=PASSWORD('$MYSQL_PASSWORD') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\\\_%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
change master to master_host='$MASTER_HOST',master_user='$SYNC_USER',master_password='$SYNC_USER_PASS',master_log_file='$SLAVE_LOG_FILE',master_log_pos=$SLAVE_LOG_POS;
slave start;
FLUSH PRIVILEGES;
EOF
mysql -uroot  < slave.sql
mysql -uroot -p111111 -e 'show slave status\G'|grep Slave
END
scp slave.sh $SLAVE_HOST:/root/
ssh $SLAVE_HOST  bash -x /root/slave.sh

转载于:https://blog.51cto.com/8460152/2353170