一、MySQL读写分离原理
● 读写分离就是只在主服务器上写,只在从服务器上读
●主数据库处理事务性查询,而从数据库处理select查询
●数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
二、案例概述
在企业应用中,在大量的数据请求下,单台数据库将无法承担所有的读写操作
● 配置多台数据库服务器以实现读写分离
● 本案例在主从复制的基础上实现读写分离
三、案例环境
2-1 系统参数
主机 | 操作系统 | IP地址 | 主要软件 |
---|---|---|---|
amoeba | centos-7.6-X86_64 | 192.168.100.41 | amoeba-mysql-3.0.5-RC-distribution.zip; jdk-8u144-linux-x64.tar.gz |
Master | centos-7.6-X86_64 | 192.168.100.42 | mysql-boost-5.7.20.tar.gz |
Slave1 | centos-7.6-X86_64 | 192.168.100.43 | mysql-boost-5.7.20.tar.gz |
Slave2 | centos-7.6-X86_64 | 192.168.100.44 | mysql-boost-5.7.20.tar.gz |
客户机 | centos-7.6-X86_64 | 192.168.100.45 | mysql-boost-5.7.20.tar.gz |
2-1 实验拓扑图
四、案例实施
4-1 配置MySQL读写分离
如果命令补不全的、ifconfig命令不能用的、解决方案如下
yum -y install bash-completion ###tab补全功能
yum -y install net-tools ###ifconfig 命令
4-1-1卸载原有的java环境
[root@localhost ~]# java -version ##确定java版本 1.8.0_181的 有点高
openjdk version “1.8.0_181”
OpenJDK Runtime Environment (build 1.8.0_181-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
[root@localhost ~]# rpm -qa |grep java
java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64 ####卸载
tzdata-java-2018e-3.el7.noarch
python-javapackages-3.4.1-11.el7.noarch
java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64 ####卸载
javapackages-tools-3.4.1-11.el7.noarch
java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64 ####卸载
java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_6 ####卸载
查出openjdk相关的文件并且删除它
[root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_64
4-1-2安装JDK##
上传JDK到opt目录
[root@localhost ~]# cd /opt
[root@localhost opt]# tar xzvf jdk-8u144-linux-x64.tar.gz
[root@localhost opt]# cp -rv jdk1.8.0_144/ /usr/local/java
[root@localhost opt]# vi /etc/profile
export JAVA_HOME=/usr/local/java
export JRE_HOME=/usr/local/java/jre
export PATH=$PATH:/usr/local/java/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib
[root@localhost opt]# source /etc/profile
[root@localhost opt]# java -version ###java环境变成1.8.0_144的##
java version "1.8.0_144"
Java(TM) SE Runtime Environment (build 1.8.0_144-b01)
Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)
4-1-3安装
amoebahttps://sourceforge.net/projects/amoeba/files/#####
[root@localhost opt]# yum -y install unzip ###如果unzip命令没有,必须装下,如果装了,此步骤跳过##
[root@localhost opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@localhost opt]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/
[root@localhost opt]#vi /usr/local/amoeba/jvm.properties
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" //32行修改成如下
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
4-1-4制作amoeba管理脚本
[root@localhost opt]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62
#
export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba
case "$1" in
start)
echo -n "Starting $NAME... "
$AMOEBA_BIN
echo " done"
;;
stop)
echo -n "Stoping $NAME... "
$SHUTDOWN_BIN
echo " done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac
[root@localhost opt]# chmod +x /etc/init.d/amoeba
[root@localhost opt]# chkconfig --add amoeba
service amoeba start
ctrl + c
netstat -anpt | grep 8066 //默认监听在8066端口
4-1-5在三台mysql数据库中为amoeba授权
##登录192.168.100.42终端###
[root@localhost ~]# mysql -u root -p ####输入密码abc123
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit
##登录192.168.100.43终端###
[root@localhost ~]# mysql -u root -p ####输入密码abc123
mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> qui
##登录192.168.100.44终端###
root@localhost ~]# mysql -u root -p ####输入密码abc123
mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;
mysql> quit
#######在前端服务器上 192.168.100.41 你可以把它理解成调度器 ######
[root@localhost opt]# cd /usr/local/amoeba
[root@localhost amoeba]# vi conf/amoeba.xml
—28行-----设置客户端连接amoeba前端服务器时使用的用户名和密码----
<property name="user">amoeba</property>
----30行---------
<property name="password">123456</property>
------------------------------------以上配置用于客户端连接用户名密码-------------
—83-去掉注释-同时把默认的server1改成master,把默认的servers改成 slaves
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
######上面修改后如下#### 这种注释一定要去掉
82 <property name="LRUMapSize">1500</property>
83 <property name="defaultPool">master</property>
84 <property name="writePool">master</property>
85 <property name="readPool">slaves</property>
86 <property name="needParse">true</property>
[root@localhost amoeba]# vi conf/dbServers.xml
–26-29–去掉注释–
<property name="user">test</property>
<property name="password">abc123</property>
------主服务器地址—
43 <dbServer name="master" parent="abstractServer">
46 <property name="ipAddress">192.168.100.42</property>
–50-从服务器主机名-
<dbServer name="slave1" parent="abstractServer">
–53-从服务器地址-
<property name="ipAddress">192.168.100.43</property>
-----------------省略------------------------
<!-- mysql port -->
<property name="port">3306</property>
<!-- mysql schema -->
<property name="schema">test</property>
<!-- mysql user -->
<property name="user">test</property> #####user 后面改成test 这边是mysql数据库授权账户
<property name="password">abc123</property> #####password 后面改成abc123 这是mysql数据库授权密码
</factoryConfig>
--------------省略-------------------------
<!-- mysql schema -->
<property name="schema">test</property> #####数据库中要有此处定义的数据库,否则客户端连接后会报错,默认我用test数据库,创建是test数据库不要弄错了
<dbServer name="master" parent="abstractServer"> #####name后面改成 master
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.100.42</property> ####ipAddress加上主mysql的IP地址
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer"> #####name后面改成 slave1
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.100.43</property> ###ipAddress加上从1mysql的IP地址
</factoryConfig>
</dbServer>
###注意啦!在脚本中slave2是没有的,需要复制下#####
<dbServer name="slave2" parent="abstractServer"> #####name后面改成 slave2
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.100.44</property> ###ipAddress加上从2mysql的IP地址
</factoryConfig>
</dbServer>
<dbServer name="slaves" virtual="true"> #####name后面改成 slaves
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property> #####poolNames后面改成 slave1,slave2
</poolConfig>
</dbServer>
---------------------以上脚本做参考—注意----------------------
<!-- mysql schema -->
<property name="schema">test</property> //数据库中要有此处定义的数据库,否则客户端连接后会报如下错误:
ERROR 1044 (42000): Could not create a validated object, cause: ValidateObject failed
[root@localhost amoeba]# service amoeba restart
[root@localhost amoeba]# netstat -anpt | grep java
4-1-6测试用客户端 192.168.32.15
service firewalld stop
setenforce 0
yum install -y mysql
mysql -u amoeba -p123456 -h 192.168.100.41 -P8066 ##### 登录192.168.100.45 amoeba服务端
4-1-7在主mysql上创建数据库 192.168.100.42
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> use test;
Database changed
mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.05 sec)
4-1-8在从1 mysql关闭同步 192.168.100.43
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> insert into zhang1 values('2','zhang','this_is_slave1');
Query OK, 1 row affected (0.00 sec)
4-1-9在从2 mysql关闭同步 192.168.32.13
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> insert into zhang1 values('3','zhang','this_is_slave2');
Query OK, 1 row affected (0.00 sec)
4-1-1-在主mysql上插入数据 192.168.100.42
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123
mysql> use test;
Database changed
mysql> insert into zhang1 values('1','zhang','this_is_master');
Query OK, 0 rows affected (0.05 sec)
4-1-11登录客户端 192.168.32.15
[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.32.14 -P8066
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bdqn |
| db_test |
| mysql |
| performance_schema |
| sys |
| test | #####发现数据库test
+--------------------+
7 rows in set (0.01 sec)
MySQL [(none)]> use test; ####进入数据库test
Database changed
MySQL [test]> select * from zhang1; ###查看zhang信息 发现进入slave1 数据中的数据
| id | name | address |
+------+-------+----------------+
| 2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)
MySQL [test]> select * from zhang; ###查看zhang信息 发现进入slave2 数据中的数据
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.01 sec)
#########上述实验,实现数据读取负载均衡#######
4-2验证
在客户机上 192.168.100.45 继续写输数据,按照正常的规则应该是slave1 和slave2是看不见的
MySQL [(none)]> use test;
Database changed
MySQL [test]> insert into zhang1 values('4','zhang','write_test');
Query OK, 1 row affected (0.01 sec)
4-2-1登录mastar 192.168.100.42
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
+------+-------+----------------+
2 rows in set (0.00 sec)
4-2-2登录slave1 192.168.100.43
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)
4-2-3登录slave2 192.168.100.44
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 3 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.00 sec)
上述验证结果如下
在客户端(192.168.100.45)上操作写入数据,数据是往master上写的,在salve1和slave2上看不到写的数据