MySQL——读写分离

MySQL读写分离

MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。

读写分离建立在主从复制的基础上

配置环境

四台服务器
master：192.168.110.10
slave01：192.168.110.15
slave02：192.168.110.20
amoeba：192.168.110.25（也可当客户机）

安装JAK

amoeba需要安装jdk

[root@localhost opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin
[root@localhost opt]# chmod +x jdk-6u14-linux-x64.bin
Do you agree to the above license terms? [yes or no]
# yes
Press Enter to continue.....

 
Done.
[root@localhost opt]# mv jdk1.6.0_14/ /usr/local/jdk1.6
#添加环境变量
[root@localhost opt]# vim /etc/profile
#在最后添加
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@localhost opt]# source /etc/profile

配置三台mysql服务器

授权给amoeba用户访问

mysql> grant all on *.* to 'root'@'192.168.110.%' identified by '11925';
Query OK, 0 rows affected, 1 warning (0.00 sec)

配置amoeba

#为amoeba 创建文件夹，并将文件解压进去
[root@localhost opt]# mkdir /usr/local/amoeba
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/
[root@localhost opt]# cd /usr/local/amoeba/bin/
[root@localhost bin]# amoeba
amoeba start|stop
[root@localhost bin]# cd /usr/local/amoeba/conf/
[root@localhost conf]# ll
总用量 64
......
-rwxr-xr-x. 1 root root 4484 2月  29 2012 amoeba.xml  ##amoeba主配置文件
-rwxr-xr-x. 1 root root 2458 6月  16 2012 dbServers.xml  ##数据库配置文件
-.....
[root@localhost conf]# vim amoeba.xml
 30     <property name="user">JUE</pro    perty>
 32     <property name="password">456456</property>
115     <property name="defaultPool">master</property>
117      <!---->
118      <property name="writePool">master</property>
119      <property name="readPool">slaves</property>
120      <!---->

[root@localhost conf]# vim dbServers.xml
#设置用户登录amoeba的用户和密码
 23      <property name="schema">mysql</property>
 25      <!-- mysql user -->
 26      <property name="user">JUE</property>
 28       <!--  mysql password-->
 29       <property name="password">456456</property>
 30       <!---->
 45         <dbServer name="master"  parent="abstractServer">
 46                 <factoryConfig>
 47                         <!-- mysql ip --> 48                         <property name="ipAddress">192.168.110.15</property>
 49                 </factoryConfig>
 ##复制上面的，粘贴到下面，修改一下
 58         <dbServer name="slave02"  parent="abstractServer">
 59                 <factoryConfig>
 60                         <!-- mysql ip -->
 61                         <property name="ipAddress">192.168.110.20</property>
 62                 </factoryConfig>
 65         <dbServer name="slaves" virtual="true">
 66                 <poolConfig class="com.meidusa.amoeba.server.MultipleS    erverPool">
 67                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2    =WEIGHTBASED , 3=HA--> 68                         <property name="loadbalance">1</property>
 69 
 70                         <!-- Separated by commas,such as: server1,serv    er2,server1 --> 71                         <property name="poolNames">slave01,slave02</pr    operty>
 72                 </poolConfig>
 73         </dbServer>
#启动amoeba软件，后台运行
[root@localhost conf]# /usr/local/amoeba/bin/amoeba start &
[2] 3337
[root@localhost conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-08-28 23:30:30,161 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-08-28 23:30:30,324 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-08-28 23:30:30,327 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:9552.

使用客户端访问amoeba

[root@localhost ~]# mysql -u amoeba -p11925 -h 20.0.0.140 -P 8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 110977125
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
......

验证

#客户端创建
MySQL [(none)]> create database LIU;
Query OK, 1 row affected (0.03 sec)

#在master上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| text              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

验证读写分离
此时三台mysql服务器上都有text这个数据库
断开 slave的同步

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec

#客户端创建表
MySQL [text]> create table aa(id int(1) primary key auto_increment);
Query OK, 0 rows affected (0.02 sec)
MySQL [text]> insert into aa values(1);
Query OK, 1 row affected (0.00 sec)

MySQL [LIU]> insert into aa values(2);
Query OK, 1 row affected (0.01 sec)

#master查看，从服务器没同步，也没写入，所有看不到
mysql> use text;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select * from aa;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)


#从服务器01 写入
mysql> insert into aa values(3);
Query OK, 1 row affected (0.00 sec)
#从服务器02 写入
mysql> insert into aa values(5);
Query OK, 1 row affected (0.00 sec)

#客户端读取
#第一次
MySQL [LIU]> select * from aa;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

#第二次
MySQL [LIU]> select * from aa;
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.01 sec)