Ameoba for MySQL read-write separation and load balancing

Amoeba Introduction:
Amoeba is a proxy that uses MySQL as the underlying data store and provides the MySQL protocol interface to the application. It responds centrally to the application's requests, sending SQL requests to a specific database based on the rules that the user has set beforehand. This allows for load balancing, read-write separation, and high availability. Compared to MySQL's official MySQL proxy, the author emphasizes the convenience of the amoeba configuration (XML-based configuration file, which uses the SQLJEP syntax to write rules that are simpler than the Lua script-based MySQL proxy).

The amoeba is equivalent to a SQL-requested router that provides mechanisms for load balancing, read-write separation, and high availability, rather than fully implementing them. Users need to use a combination of MySQL replication and other mechanisms to achieve the functions such as replica synchronization. Amoeba also uses pluggable mechanisms for the underlying database connection management and routing implementations, and third parties can develop more advanced policy classes to replace the authors ' implementations. This program is generally more in line with the idea of kiss.

The above from Baidu Encyclopedia, the following we experiment to learn how to configure Ameoba

Experiment: Amoeba for read-write separation and load balancing

Note: read-write and AB replication are used in real-world environments, but in order to observe the effects of read-write separations, the three MySQL servers in our experiment did not configure AB replication

Agent: 10.10.10.16
Written by: master:10.10.10.13
READ: slave1:10.10.10.14
slave2:10.10.10.15
Note: Read and write separations are generally combined with AB replication, but in order to test the effect, this experiment does not configure AB replication

Software: amoeba-mysql-binary-2.2.0.tar.gz
jdk-7u67-linux-x64.tar.gz \ \

Installing the Software
# Tar XF jdk-7u67-linux-x64.tar.gz-c/usr/local/
# cd/usr/local/
# MV Jdk1.7.0_67/java
# Mkdir/usr/local/amoeba
# Tar XF amoeba-mysql-binary-2.2.0.tar.gz-c/usr/local/amoeba/
Configuring the Environment
# vim/etc/profile.d/ameoba.sh
Export Java_home=/usr/local/java
Export path= $JAVA _home/bin:/usr/local/amoeba/bin: $PATH
# source/etc/profile.d/ameoba.sh
# java-version
Java Version "1.7.0_67"
Java (TM) SE Runtime Environment (build 1.7.0_67-b01)
Java HotSpot (TM) 64-bit Server VM (build 24.65-b04, Mixed mode)

MySQL server

All servers create users that are connected by a proxy server
> Grant All on * * to [e-mail protected] '% ' identified by ' 123 ';

* To test the results, we built a table with different contents with the same name on three read-only servers:

Master

> select * from Db1.t1;

+------+

| ID |

+------+

| 13 |

+------+

SLAVE1:

> select * from Db1.t1;

+------+

| ID |

+------+

| 14 |

+------+

Slave2:

> select * from Db1.t1;

+------+

| ID |

+------+

| 15 |

+------+

Configure Ameoba
# cd/usr/local/amoeba/
1. Configure Ameoba to connect back-end servers
# Vim Conf/dbservers.xml
-----The port number to connect to MySQL, default library, username, password-----
<property name= "Port" >3306</property>\\MySQL Server port number
<!--MySQL Schema--
<property name= "Schema" >Test</property>\ \ Which library is the default after connecting to the server
<!--mysql User--
<property name= "User" >Mary</property>
<property name= "Password" >123</property>
-----Define the MySQL host-----
<dbserver name= "Master"Parent=" > "Abstractserver"
<factoryConfig>
<!--mysql IP--
<property name= "IpAddress" >10.10.10.13</property>
</factoryConfig>
Wuyi </dbServer>
<dbserver name= "slave1"Parent=" > "Abstractserver"
<factoryConfig>
<!--mysql IP--
<property name= "IpAddress" >10.10.10.14</property>
</factoryConfig>
</dbServer>
<dbserver name= "Slave2"Parent=" > "Abstractserver"
<factoryConfig>
<!--mysql IP--
<property name= "IpAddress" >10.10.10.15</property>
</factoryConfig>
</dbServer>
-----Define load Balancing policies and host group-----
<dbserver name= "Slavepool"Virtual=" true ">\ \ Load Balancer Group name
<!--Load balancing Strategy:1=roundrobin, 2 =weightbased, 3=ha-->
<property name= "LoadBalance" >1</property>\ \ Load Balancing strategy: round robin
<!--separated by Commas,such As:server1,server2,server1--
<property name= "Poolnames" >Slave1,slave2</property>\ \ Group member
</poolConfig>
</dbServer>
2. Configure Ameoba
# Vim Conf/amoeba.xml
-----Define the proxy server port number and user name password-----
<property name= "Port" >8066</property>\ \ Proxy Server port number
<property name= "Authenticator" >
<property name= "User" >Admin</property>
<property name= "Password" >Admin</property>
-----Define a read-write server, using a host (group) defined in Dbservers.xml-----
<property name= "Defaultpool" >Master</property>\ \ Default Server
<property name= "Writepool" >Master</property>\ \ Write Server
117 <property name= "Readpool" >Slavepool</property>\ \ Read Server
Start Amoeba
# Amoeba Start &
Test
# mysql-u Admin-padmin-h 10.10.10.16-p 8066

> INSERT into DB1.T1 set id=100; \ \ Inserts a record into the test table

> select * from Db1.t1;

+------+

| ID |

+------+

| 15 |

+------+

> select * from Db1.t1;

+------+

| ID |

+------+

| 14 |

+------+

Multiple select several times the record of each T1 test table is switched between 14 and 15, which shows that the read-only server implements the perfect round robin, and we write 100 of this data? We'll just log in to the master server and see:

> select * from Db1.t1;

+------+

| ID |

+------+

| 13 |

| 100 |

+------+

As you can see, the records written by our connection proxy server are written on the master server, while the read is the data read on two read-only servers, perfect for read/write separation + load balancing

------------------------------------------------------------
Another: If the start times wrong:
The stack size specified is too small, specify at least 228k
Error:could not create the Java Virtual machine.
ERROR:A Fatal exception has occurred. Program would exit.
Workaround: Modify the Ameoba command file
# Vim/usr/local/amoeba/bin/amoeba
default_opts= "-server-xms256m-xmx256m-xss k"

Ameoba for MySQL read-write separation and load balancing