mysql主从脚本_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 <

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 <

#!/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 <

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