脚本内容

小白一枚，如有不足，还望指点

#!/bin/bash # # 执行后脚本后将生成本地生成slave1.sh和slave2.sh文件 # 脚本自动将slave1.sh和slave2.sh脚本文件分别拷贝到两台从服务器上 # 需要人为去两台服务器授予lave1.sh或slave2.sh脚本文件权限并执行 # ############# master服务器配置 ######################### read -p "请输入master（主）的IP:" P2 read -p "请输入slave1的IP:" P3 read -p "请输入slave2的IP:" P4 read -p "请输入虚拟机密码:" PWC ### 因本人设置的服务器登入密码相同，因此只用了PWC一个变量##### TMP=$(echo $P2 |awk -F'.' '{print $1,$2,$3}' |tr ' ' '.') P5="$TMP.%" sed -i '/server-id =/s/.*/server-id = 11/' /etc/my.cnf sed -i '/log-slave-updates =/d' /etc/my.cnf sed -i '/log-bin =/d' /etc/my.cnf sed -i '/server-id =/alog-bin = master-bin\nlog-slave-updates = true' /etc/my.cnf systemctl restart mysqld if [ -f /opt/AMO.info ] then echo "" else mkdir -p /opt/AMO.info fi while true do read -p "请输入mysql登入密码：" PW read -p "请再次确认：" PW2 if [[ $PW = $PW2 ]] then echo "密码正确" break else echo "密码错误，重新输入" fi done echo "mysql -uroot -p\"$PW\" -e \"grant replication slave on *.* to 'myslave'@'$P5' identified by '123456';\"" >tmp.sh echo "mysql -uroot -p\"$PW\" -e \"grant all on *.* to 'test'@'$P5' identified by '123456';\"" >>tmp.sh chmod +x tmp.sh ./tmp.sh echo " \"grant replication slave on *.* to 'myslave'@'$P5' identified by '123456';\" \"grant all on *.* to 'test'@'$P5' identified by '123456';\" " >/opt/AMO.info/info.txt /usr/bin/expect <<EOF spawn mysql -uroot -p -e "flush privileges;" expect { "password" {send "$PW\r"} } expect eof EOF mysql -uroot -p"$PW" mysql -e "show master status;" >/root/bin-log.info BLOG=$(cat /root/bin-log.info |awk 'NR==2 {print $1}') POS=$(cat /root/bin-log.info |awk 'NR==2 {print $2}') ############# 从服务器（slave1）配置 ################### echo -e "#!/bin/bash sed -i '/server-id =/s/.*/server-id = 22/' /etc/my.cnf sed -i '/relay-log =/d' /etc/my.cnf sed -i '/relay-log-index =/d' /etc/my.cnf sed -i '/server-id =/arelay-log = relay-log-bin' /etc/my.cnf sed -i '/server-id =/arelay-log-index = slave-relay-bin.index' /etc/my.cnf systemctl restart mysqld " >/root/slave1.sh echo " while true do read -p \"请输入mysql登入密码：\" PW read -p \"请再次确认：\" PW2 if [[ \$PW = \$PW2 ]] then echo \"密码正确\" break else echo \"密码错误，重新输入\" fi done" >>/root/slave1.sh echo " /usr/bin/expect <<EOF spawn mysql -uroot -p expect { \"password\" {send \"\$PW\r\";exp_continue} \".*\" {send \"change master to master_host='$P2',master_user='myslave',master_port=3306,master_password='123456',master_log_file='$BLOG',master_log_pos=$POS;\rflush privileges;\rstart slave;\rexit\r\"} } expect eof EOF " >>/root/slave1.sh /usr/bin/expect <<EOF spawn scp /root/slave1.sh $P4:/root/ expect { "(yes/no)" {send "yes\r";exp_continue} "password" {send "$PWC\r"} } expect eof EOF ############# 从服务器（slave2）配置 ################### echo -e "#!/bin/bash sed -i '/server-id =/s/.*/server-id = 33/' /etc/my.cnf sed -i '/relay-log =/d' /etc/my.cnf sed -i '/relay-log-index =/d' /etc/my.cnf sed -i '/server-id =/arelay-log = relay-log-bin' /etc/my.cnf sed -i '/server-id =/arelay-log-index = slave-relay-bin.index' /etc/my.cnf systemctl restart mysqld " >/root/slave2.sh echo " while true do read -p \"请输入mysql登入密码：\" PW read -p \"请再次确认：\" PW2 if [[ \$PW = \$PW2 ]] then echo \"密码正确\" break else echo \"密码错误，重新输入\" fi done" >>/root/slave2.sh echo " /usr/bin/expect <<EOF spawn mysql -uroot -p expect { \"password\" {send \"\$PW\r\";exp_continue} \".*\" {send \"change master to master_host='$P2',master_user='myslave',master_port=3306,master_password='123456',master_log_file='$BLOG',master_log_pos=$POS;\rflush privileges;\rstart slave;\rexit\r\"} } expect eof EOF " >>/root/slave2.sh /usr/bin/expect <<EOF spawn scp /root/slave2.sh $P4:/root/ expect { "(yes/no)" {send "yes\r";exp_continue} "password" {send "$PWC\r"} } expect eof EOF

执行演示

在master上执行脚本

[root@localhost ~]# ./master.sh 请输入master（主）的IP:192.168.10.10 请输入slave1的IP:192.168.10.20 请输入slave2的IP:192.168.10.30 请输入虚拟机密码: 请输入mysql登入密码： 请再次确认： 密码正确 mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. spawn mysql -uroot -p -e flush privileges; Enter password: mysql: [Warning] Using a password on the command line interface can be insecure. spawn scp /root/slave1.sh 192.168.10.20:/root/ root@192.168.10.20's password: Permission denied, please try again. root@192.168.10.20's password: Permission denied, please try again. root@192.168.10.20's password: Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password). lost connection yes expect: spawn id exp6 not open while executing "expect eof" spawn scp /root/slave1.sh 192.168.10.20:/root/ root@192.168.10.20's password: slave1.sh 100% 844 413.4KB/s 00:00 spawn scp /root/slave2.sh 192.168.10.30:/root/ The authenticity of host '192.168.10.30 (192.168.10.30)' can't be established. ECDSA key fingerprint is SHA256:rXRxYO+vtdxxbnLayaaDBs9nP/y3x5v98yi6wjvP1zE. ECDSA key fingerprint is MD5:25:34:10:b8:54:07:87:eb:0b:1f:c0:4f:27:f0:13:27. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.10.30' (ECDSA) to the list of known hosts. yes root@192.168.10.30's password: Permission denied, please try again. root@192.168.10.30's password: Permission denied, please try again. root@192.168.10.30's password: Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password). lost connection yes expect: spawn id exp6 not open while executing "expect eof" spawn scp /root/slave2.sh 192.168.10.30:/root/ root@192.168.10.30's password: slave2.sh 100% 844 449.7KB/s 00:00

切换到slave1从服务器

执行脚本slave1.sh

[root@localhost ~]# ls #在本地查看，生成了slave1.sh脚本文件 …… slave1.sh [root@localhost ~]# chmod +x slave1.sh #授予执行权限 [root@localhost ~]# ./slave1.sh ##执行脚本 请输入mysql登入密码： ****** 请再次确认： ****** 密码正确 spawn mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. change master to master_host='192.168.10.10',master_user='myslave',master_port=3306,master_password='123456',master_log_file='master-bin.000001',master_log_pos=894; flush privileges; start slave; exit Your MySQL connection id is 3 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> change master to master_host='192.168.10.10',master_user='myslave',master_port=3306,master_password='123456',master_log_file='master-bin.000001',master_log_pos=894; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye

查看验证slave1

[root@localhost ~]# mysql -u root -p ##登入数据库 mysql> show slave status\G; ##查看slave状态，I/O线程和SQL线程正常，执行成功 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.10 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 894 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0

切换到slave2从服务器

执行脚本slave2.sh

[root@localhost ~]# ls #在本地查看，生成了slave2.sh脚本文件 …… slave2.sh [root@localhost ~]# chmod +x slave2.sh #授予执行权限 [root@localhost ~]# ./slave2.sh ##执行脚本 请输入mysql登入密码： ****** 请再次确认： ****** 密码正确 spawn mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. change master to master_host='192.168.10.10',master_user='myslave',master_port=3306,master_password='123456',master_log_file='master-bin.000001',master_log_pos=894; flush privileges; start slave; exit Your MySQL connection id is 3 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> change master to master_host='192.168.10.10',master_user='myslave',master_port=3306,master_password='123456',master_log_file='master-bin.000001',master_log_pos=894; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye

查看验证slave2

[root@localhost ~]# mysql -u root -p ##登入数据库 mysql> show slave status\G' ## 查看状态，两个线程工作正常 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.10 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 894 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: