Amoeba for MySQL database read/write splitting

Amoeba for MySQL database read/write splitting

I. Environment and software version:
System: CentOS Linux release 7.2.1511 (Core)
Mysql: Server version: 5.6.33
Amoeba: amoeba-mysql-3.0.5-RC-distribution
JDK: 1.7.0 _ 80-b15 (jdk is required for running Amoeba)

2. Install Amoeba:
1 download the required software:

    # lsamoeba-mysql-3.0.5-RC-distribution.zip    jdk-7u80-linux-x64.rpm

2. Install jdk

# yum -y install jdk-7u80-linux-x64.rpm#  java -version    java version "1.7.0_80"Java(TM) SE Runtime Environment (build 1.7.0_80-b15)Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11, mixed mode)

3. Decompress Amoeba

# Unzip amoeba-mysql-3.0.5-RC-distribution.zip # mv amoeba-mysql-3.0.5-RC/usr/local/amoeba # mv amoeba-mysql-3.0.5-RC/usr/local/amoeba # cd/usr/local/amoeba/conf/# vim amoeba. xml <property name = "port"> 3306 </property> # Set the amoeba listening port, the default value is 8066 <property name = "ipAddress"> 10.0.0.159 </property> # Set the IP address of the amoeba listener <property name = "user"> root </property> # Set the IP address used to connect to amoeba. user <property name = "password"> 123456 </property> # sets the password for connecting to amoeba # (the account password here is irrelevant to the password used by amoeba to connect to the backend database server) <property name = "defaultPool"> master </property> # sets the default pool of amoeba. Set it to matster # The two options are deregistered by default and need to be uncommented, it is used to specify the <property name = "writePool"> master </property> <property name = "readPool"> multiPool </property> # This is a group., if this group contains multiple dbServers, load balancing can be performed. This group is in dbServers. set in xml

Set the mysql database information in the dbServers. xml file in the current directory.

# Vim dbServers. xml <! -- Mysql port --> # Set amoeba to connect to the backend mysql port <property name = "port"> 3306 </property> <! -- Mysql schema --> # Set the default database. when connected to amoeba, the operation table must explicitly specify the database name, that is, dbname. tablename does not support using dbname to specify the default database, because the operation is scheduled to each backend dbserver <property name = "schema"> test </property> <! -- Mysql user --> # Set the username and password for connecting amoeba to the backend mysql <property name = "user"> amoeba </property> <property name = "password"> 123456 </property> <dbServer name = "master" parent = "abstractServer"> # Set a backend dbServer, set the connection writable master database. This name can be customized, but it must be in line with amoeba. in xml, the called names must be consistent. <FactoryConfig> <! -- Mysql ip --> # backend mysql ip address <property name = "ipAddress"> 10.0.0.176 </property> </factoryConfig> </dbServer> <dbServer name = "slave" parent = "abstractServer"> # Set a backend dbServer, set a readable salve for connection. This name can be customized, but it must be in line with amoeba. in xml, the called names must be consistent. <FactoryConfig> <! -- Mysql ip --> # backend mysql ip address <property name = "ipAddress"> 10.0.0.193 </property> </factoryConfig> </dbServer> <dbServer name = "multiPool" virtual = "true"> # define a dbserver group <poolConfig class = "com. meidusa. amoeba. server. multipleServerPool "> <! -- Load balancing strategy: 1 = ROUNDROBIN, 2 = WEIGHTBASED, 3 = HA --> # select a scheduling algorithm. 1 indicates round robin Load balancing, 2 indicates weight, and 3 indicates HA, select 1 <property name = "loadbalance"> 1 </property> <! -- Separated by commas, such as: server1, server2, server1 --> <property name = "poolNames"> slave </property> # group members, use commas to separate multiple </poolConfig> </dbServer>

3. Authorize the amoeba user to log on to mysql (here, the master and slave of my two mysql servers have completed master-slave synchronization and have been verified successfully. Please refer to the master-slave synchronization for details)
Master authorization:
Mysql> grant select, insert, update, delete on.To 'amoeba' @ '10. 0.0.% 'identified by '123 ';
Mysql> flush privileges;
Authorization on slave:
Mysql> grant select on.To 'amoeba' @ '10. 0.0.% 'identified by '123 ';
Mysql> flush privileges;
Note: The authorized user password must be consistent with that configured in dbServers. xml. If you enable master-slave synchronization for all databases in your mysql database, the user authorized on the master database is synchronized to the slave database. The solution is: 1. Permission downgrading on the slave database; 2. mysql Database Synchronization is not allowed during master-slave synchronization.

4. Start amoeba:

 #cd /usr/local/amoeba/bin

Vim launcher

Add JAVA_HOME:

JAVA_HOME =/usr/java/jdk1.7.0 _ 80

# Cd/usr/local/amoeba/# vim jvm. properties modify VM_OPTIONS: JVM_OPTIONS = "-server-Xms1024m-Xmx1024m-Xss256k-XX: PermSize = 16 m-XX: maxPermSize = 96 m "#/usr/local/amoeba/bin/launcher 23:34:21 [INFO] Project Name = Amoeba-MySQL, PID = 48981, starting... log4j: WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j. xml 23:34:22, 152 INFO context. mysqlRuntimeContext-Amoeba for Mysql current versoin = 5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j: WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2018-01-02 23:34:22, 520 INFO net. serverableConnectionManager-Server listening on/10.0.0.159: 3306. # startup successful # ss-tanlp LISTE 128: ffff: 10.0.0.159: 3306 :::*

Test 5: Use another mysql client to connect to amoeba. During the test, I paused the master-slave synchronization so that I could see the effect more directly.

#  mysql -uroot -p123456  -h10.0.0.159

Add a data entry to the master database.

Add a data entry to the database

Return to the mysql client for verification:

You can only view the data in the slave database. This indicates that the operation is successful. For further verification, you can insert a data entry into the client.

In addition, the client does not query the newly inserted data from the slave database and returns to the master database for confirmation:

After the verification is successful, you can enable master-slave synchronization to implement read/write splitting. (before synchronization, clear the data that has differences so that the master-slave instance can run properly)