MySQL read-write separation

Amoeba realizing MySQL read-write separation

Amoeba is actually a proxy that receives all requests, classifies them, and, based on the rules we write in the XML file, put them out, such as read-write separation, put all the write operations to master, put all the read operations to the slave, and finally return them to the user.

Read/write separation can effectively utilize MySQL's resource of master-slave replication cluster and reduce the pressure of master.

Configuration:

Server4:amoeba Agent 172.25.254.125

Server5:master 172.25.254.225

Server6:slave 172.25.254.215

Installation package:

Amoeba-mysql-3.0.5-rc-distribution.zip

JDK-8U121-LINUX-X64.RPM (Amoeba installation requires JDK environment)

To configure the Java environment:

RPM-IVH jdk-8u121-linux-x64.rpm

RPM-QLP jdk-8u121-linux-x64.rpm | grep java # #查看 Where does Java fit in?

Vim/etc/profile #配置 path Add as downstream

---------------------------------------------

java_home=/usr/java/jdk1.8.0_121

Jre_home=/usr/java/jdk1.8.0_121/jre

Class_path=.: $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar: $JRE _home/lib path= $PATH: $JAVA _home/bin: $JRE _home/ Bin export Java_home jre_home class_path PATH

----------------------------------------------

Source/etc/profile #读取路径文件

Java-version #查看安装是否成功

-------------------Java Version "1.8.0_121"

Java (TM) SE Runtime Environment (build 1.8.0_121-b13)

Java HotSpot (TM) 64-bit Server VM (build 25.121-b13, Mixed mode)

-------------------

Install Amoeba:

Unzip Amoeba-mysql-3.0.5-rc-distribution.zip

CD AMOEBA-MYSQL-3.0.5-RC

Vim Dbservers.xml

.....

<property name= "user" >amoeba</property> #设置 amoeba the account password used to log in to the backend server

<dbserver name= "Writedb" parent= "Abstractserver" > #设置一个后端可写的 dbserver

<dbserver name= "Myslaveserver IP is placed in a group

"Virtual=" true "> #设置定义一个 dbserver Group, all readable

.....

Vim Amoeba.xml

.....

<property name= "user" >root</property> #客户端连接 Amoeba account password

<property name= "Defaultpool" >writedb</property> #设置 amoeba Default Pool

<property name= "Writepool" >writedb</property> #本来是注释掉的, defining the Read-write pool written in front

....

Start Semoeba

Vim Jvm.properties

Jvm_options= "-server-xms1024m-xmx1024m-xss254k-xx:permsize=16m-xx:maxpermsize=96m"

Start:

Bin/launcher & #将其打入后台, because this command will always occupy a bash view port:

1

2

3

4

Test:

Open the Master,slave database first

Mysql-h172.25.254.125-uroot-p-p8066

MySQL [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Test |

+--------------------+

Create and insert a table data for query operations

MySQL [(none)]> use test;

MySQL [test]> CREATE TABLE test (name varchar (15));

Query OK, 0 rows affected (1.01 sec)

MySQL [test]> INSERT INTO test.test values (' user1 ');

Query OK, 1 row affected (0.11 sec)

MySQL [test]> SELECT * from test;

+-------+

| name |

+-------+

| User1 |

See if the insert is successful on the master and slave side

Stop the master database for write operations

MySQL [test]> CREATE table yy (age int);

ERROR 1044 (42000): Amoeba could not connect to MySQL Server[172.25.254.225:3306],connection refused

But the read operation will work

MySQL [test]> SELECT * from test;

+-------+

| name |

+-------+

| User1 |

+-------+

Because the read operation is using the slave database

Turn on master, close slave

Can write operation, cannot read operation

MySQL [test]> INSERT INTO test.test values (' User2 ');

Query OK, 1 row affected (0.11 sec)

MySQL [test]> SELECT * from test;

ERROR 1044 (42000): Poolname=myslave, no valid pools

Open slave, log in to MySQL, the number of paths have been completed synchronously.

MySQL read-write separation