坚持#第257天~读写分离router负载均衡脚本

# 读写分离router脚本,总共为3个脚本,需要4台虚拟机(主机、从机、克隆机做router、其它人)

# 做实验先关闭从机的slave服务,但实际工作中不要关闭从机的slave服务

# 执行顺序1,主机部分

/usr/local/mysql/support-files/mysql.serverstart

mysql -e"grant all on *.* to lbx@'%' identified by '123';"

mysql -e"flush privileges;"

mysql -e"create database routerTestDB;"

mysql -e"use routerTestDB;create table routerTestTB(id int);"

mysql -e"use routerTestDB;insert into routerTestTB values(1);"

mysql -e"delete from mysql.user where user='';"

mysql -e "flushprivileges;"

 

# 执行顺序2,从机部分

/usr/local/mysql/support-files/mysql.serverstart

mysql -e"grant all on *.* to lbx@'%' identified by '123';"

mysql -e"flush privileges;"

mysql -e"create database routerTestDB;"

mysql -e"use routerTestDB;create table routerTestTB(id int);"

mysql -e"use routerTestDB;insert into routerTestTB values(2);"

mysql -e"delete from mysql.user where user='';"

mysql -e"flush privileges;"

 

# 执行顺序3,克隆机,也叫router机(中央转接站)

# 装好数据库之后,把软件mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar,gz拷贝到router机上并解压

# 注意执行本脚本之前当前目录要有router解压后的包

read -p"请输入AB复制的主机ip:" zhuIP

read -p"请输入AB复制的从机ip:" congIP

mvmysql-router-2.1.4-linux-glibc2.12-x86-64bit /usr/local/mysql-router

\cp/usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /usr/local/mysql-router/mysqlrouter.conf

routerIP=`ifconfigens33 | grep netmask | tr -s " " | cut -d " " -f3 | cut -d"." -f1,2,3,4`

sed -i -re'24,25 s@#@@;27 s@#@@;29,31 s@#@@;31c\destinations = '$zhuIP'' -e '27a\bind_address= '$routerIP'' -e '32i\[routing:balancing]\nbind_address = '${routerIP}'\nbind_port= 7002\nmode = read-only\ndestinations = '$zhuIP','$congIP''/usr/local/mysql-router/mysqlrouter.conf

/usr/local/mysql-router/bin/mysqlrouter&

 

# 执行顺序4,别人机手动执行，不制作成脚本，-h后面是routerIP即可

/usr/local/mysql/support-files/mysql.serverstart

mysql -ulbx-p123 -h 192.168.56.58 -P 7002 -e "select * fromrouterTestDB.routerTestTB;"

mysql -ulbx-p123 -h 192.168.56.58 -P 7002 -e "select * fromrouterTestDB.routerTestTB;"

mysql -ulbx-p123 -h 192.168.56.58 -P 7002 -e "select * fromrouterTestDB.routerTestTB;"

mysql -ulbx-p123 -h 192.168.56.58 -P 7001 -e "select * fromrouterTestDB.routerTestTB;"

mysql -ulbx-p123 -h 192.168.56.58 -P 7001 -e "select * fromrouterTestDB.routerTestTB;"

mysql -ulbx-p123 -h 192.168.56.58 -P 7001 -e "select * fromrouterTestDB.routerTestTB;"