Mysql middleware amoeba for mysql read/write splitting

Amoeba is a proxy that uses MySQL as the underlying data storage and provides MySQL protocol interfaces for applications. It centrally responds to application requests and sends SQL requests to specific databases for execution based on user-defined rules. Based on this, Server Load balancer, read/write splitting, and high availability can be achieved. Compared with MySQL's official MySQL Proxy, the author emphasizes the convenience of amoeba configuration (XML-based configuration files, which are easier to write rules using SQLJEP syntax than MySQL Proxy Based on lua scripts ). This article will implement a simple amoeba Configuration

Lab environment:
192.168.1.121 is the amoeba server and provides read/write splitting.
Master server whose 192.168.1.141 is mysql
192.168.1.142 is the slave server of mysql.

1. Configure the mysql master server
Edit/etc/my. cnf and provide the following configuration
Log_bin = index
Server_id = 1
Authorize on the master server
Mysql> grant replication slave, replication client on user @ '192. 168.1.142 'identified by "192 ";
Mysql> flush privileges;
2. Configure mysql slave Service
Edit/etc/my. cnf and provide the following configuration
Server_id = 10
Relay_log = relay
Enter the mysql command line interface
Mysql> change master toMASTER_HOST = "192.168.1.141", MASTER_USER = "user", MASTER_PASSWORD = "123456", MASTER_LOG_FILE = "index.000004", MASTER_LOG_POS = 429;
Mysql> start slave;
If you can see Slave_IO_Running: Yes and Slave_ SQL _Running: Yes, the master-slave configuration is successful.
3. Install amoeba on 192.168.1.121
Amoeba is developed in java, so jdk must be installed first.
[Root @ localhost ~] # Chmod + x jdk-6u43-linux-x64.bin
[Root @ localhost ~] #./Jdk-6u43-linux-x64.bin
# Vi/etc/profile. d/java. sh
Export JAVA_HOME =/root/jdk1.6.0 _ 43/bin
Export PATH = $ JAVA_HOME/bin: $ PATH
# Source/etc/profile. d/java. sh
Install amoeba
# Wget http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/download
# Mkdir/usr/local/amoeba
# Tar xf amoeba-mysql-binary-2.2.0.tar.gz-C/usr/local/amoeba

Go to the amoeba directory and you will see several directories.
Bechmark: Stress Testing
Bin: script file
Conf: Configuration File
Lib: Library

There are many configuration files in the conf file. The read/write splitting effect is achieved here. You only need two files: amoeba. xml and dbserver. xml. The configuration to be modified in the amoeba. xml file is as follows:

<Service name = "Amoeba for Mysql" class = "com.meidusa.amoeba.net. ServerableConnectionManager">
<! -- Port -->
<Property name = "port"> 3306 </property> # port number used to connect to amoeba

<! -- Bind ipAddress -->
<! --
<Property name = "ipAddress"> 127.0.0.1 </property> #
-->
<Property name = "ipAddress"> 0.0.0.0 </property> # listener address
<Property name = "manager" >$ {clientConnectioneManager} </property>

<Property name = "connectionFactory">
<Bean class = "com.meidusa.amoeba.mysql.net. MysqlClientConnectionFactory">
<Property name = "sendBufferSize"> 128 </property>
<Property name = "receiveBufferSize"> 64 </property>
</Bean>
</Property>

<Property name = "authenticator">
<Bean class = "com. meidusa. amoeba. mysql. server. MysqlClientAuthenticator">

<Property name = "user"> root </property> # account used to connect to amoeba

<Property name = "password"> amoeba </property> # password used to connect to amoeba

<Property name = "filter">
<Bean class = "com. meidusa. amoeba. server. IPAccessController">
<Property name = "ipFile" >$ {amoeba. home}/conf/access_list.conf </property>
</Bean>
</Property>
</Bean>
</Property>

</Service>

<QueryRouter class = "com. meidusa. amoeba. mysql. parser. MysqlQueryRouter">
<Property name = "ruleLoader">
<Bean class = "com. meidusa. amoeba. route. TableRuleFileLoader">
<Property name = "ruleFile" >$ {amoeba. home}/conf/rule. xml </property>
<Property name = "functionFile" >$ {amoeba. home}/conf/ruleFunctionMap. xml </property>
</Bean>
</Property>
<Property name = "sqlFunctionFile" >$ {amoeba. home}/conf/functionMap. xml </property>
<Property name = "LRUMapSize"> 1500 </property>
<! -- <Property name = "defaultPool"> server1 </property> --> # No default route
<Property name = "writePool"> test1 </property> # Write the route to test1.
<Property name = "readPool"> test2 </property> # Read route to test2

<Property name = "needParse"> true </property>
</QueryRouter>

The configuration to be modified in the dbserver. xml file is as follows:
<FactoryConfig class = "com.meidusa.amoeba.mysql.net. MysqlServerConnectionFactory">
<Property name = "manager" >$ {defamanager} </property>
<Property name = "sendBufferSize"> 64 </property>
<Property name = "receiveBufferSize"> 128 </property>

<! -- Mysql port -->
<Property name = "port"> 3306 </property> # backend mysql port
<! -- Mysql schema -->
<Property name = "schema"> amoeba </property> # default database connection for backend mysql
<! -- Mysql user -->
<Property name = "user"> root </property> # connect to the backend mysql account
<! -- Mysql password
<Property name = "password"> password </property>
-->
<Property name = "password"> amoebapass </property> # password used to connect to the backend mysql instance
</FactoryConfig>

<DbServer name = "test1" parent = "abstractServer">
<FactoryConfig>
<! -- Mysql ip -->
<Property name = "ipAddress"> 192.168.1.141 </property> # ip address of the backend MySQL instance
</FactoryConfig>
</DbServer>

<DbServer name = "test2" parent = "abstractServer">
<FactoryConfig>
<! -- Mysql ip -->
<Property name = "ipAddress"> 192.168.1.142 </property>
</FactoryConfig>
</DbServer>

Grant permissions to amoeba on the backend proxy mysql and perform the following operations on 192.168.1.141:
Grant all on *. * to 'root' @ '192. 168.1.121 'identified by 'amoebapass)
After all the items are configured, you can start amoeba. Run:/usr/local/amoeba/bin/amoeba and the following error may occur:
The stack size specified is too small, Specify at least 160 k cocould not create the Java virtual machine.

Modify the amoeba file, vi/usr/local/amoeba/bin/amoeba, and find the following file:
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k"
Modify it:
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k

Run/usr/local/amoeba/bin/amoeba again. If amoeba start | stop appears, you can start amoeba.
#/Usr/local/amoeba/bin/amoeba start
After the startup is successful, install a mysql client on the server where amoeba is installed to test
# Yum install mysql
# Mysql-uroot-pamoeba-h192.168.1.121
If the connection is successful and the statement is successfully created, it can be displayed on the back-end proxy mysql to prove that the amoeba configuration is successful. Of course, if you want to see whether the master-slave splitting has been fully implemented, you can analyze and view the packet capture.