MySQL数据库读写分离

读写分离

为了确保数据库产品的稳定性，很多数据库拥有双机热备功能。也就是，第一台数据库服务器，是对外提供增删改业务的生产服务器；第二台数据库服务器，主要进行读的操作。·

在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作
配置多台数据库服务器以实现读写分离
读写分离建立在主从复制的基础上
MySQL 主从复制

来吧！展示！！

虚拟机四台
master：20.0.0.4
slave01：20.0.0.5
slave02：20.0.0.6
amoeba：20.0.0.140（也当客户机用）

读写分离基于主从复制

主从复制参考我上一篇博客

安装JAK

amoeba是Java写的，所以我们需要安装jdk

[root@localhost bao]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin
[root@localhost bao]# 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 bao]# mv jdk1.6.0_14/ /usr/local/jdk1.6
##添加环境变量
[root@localhost bao]# 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 bao]# source /etc/profile

设置三台mysql服务器

三台mysql服务器需授权给amoeba用户访问

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

配置amoeba

##为amoeba 创建文件夹，并将文件解压进去
[root@localhost bao]# mkdir /usr/local/amoeba
[root@localhost bao]# chmod -R 755 /usr/local/amoeba/
[root@localhost bao]# 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  172 2月  29 2012 access_list.conf
-rwxr-xr-x. 1 root root 1332 2月  29 2012 amoeba.dtd
-rwxr-xr-x. 1 root root 4484 2月  29 2012 amoeba.xml  ##amoeba主配置文件
-rwxr-xr-x. 1 root root  839 2月  29 2012 dbserver.dtd
-rwxr-xr-x. 1 root root 2458 6月  16 2012 dbServers.xml  ##数据库配置文件
-rwxr-xr-x. 1 root root  498 2月  29 2012 function.dtd
-rwxr-xr-x. 1 root root 4525 2月  29 2012 functionMap.xml
-rwxr-xr-x. 1 root root 5079 2月  29 2012 log4j.dtd
-rwxr-xr-x. 1 root root 5766 2月  29 2012 log4j.xml
-rwxr-xr-x. 1 root root 1080 2月  29 2012 rule.dtd
-rwxr-xr-x. 1 root root  869 2月  29 2012 ruleFunctionMap.xml
-rwxr-xr-x. 1 root root 2608 6月  16 2012 rule.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">20.0.0.4</property>
 49                 </factoryConfig>
 ##复制上面的，黏贴成下面，修改一下
 58         <dbServer name="slave02"  parent="abstractServer">
 59                 <factoryConfig>
 60                         <!-- mysql ip -->
 61                         <property name="ipAddress">20.0.0.6</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-27 09:37:02,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-27 09:37:22,324 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-08-27 09:37:22,327 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:9552.

使用客户端访问amoeba

[root@localhost ~]# mysql -u amoeba -p456456 -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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

进行验证

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

#在master上可以查看到
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LIU                |
| happy              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

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

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

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

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

#master查看，从服务器没同步，也没写入，所有看不到
mysql> use LIU;
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 JUEJUE;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)


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

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

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

实验结论

得出结论，读写分离已完成，且数据库读取是一个轮询的过程（相当于听歌的列表循环）