mysql主从自动搭建

1.主服务器 ​​master.sh​​

#!/bin/bash
#====================================================
# Author: Mr.Song
# Create Date: 2019-02-21
# Description: auto config MySQL master&slave
#====================================================
MYSQL_PASSWORD="123456"
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

2.从服务器 ​​slave.sh​​

#!/bin/bash

SLAVE_LOG_FILE=`mysql -uroot -p111111 -h'$MASTER_HOST' -e "show master status;" |grep mysql |awk '{print $1}'`
SLAVE_LOG_POS=`mysql -uroot -p111111 -h'$MASTER_HOST' -e "show master status;" |grep mysql |awk '{print $2}'`
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

3.复制从库脚本到从库执行

scp slave.sh $SLAVE_HOST:/root/
ssh $SLAVE_HOST  bash -x /root/slave.sh