Amobea read/write splitting and amobea read/write

Amobea read/write splitting and amobea read/write

The Chinese meaning of Amoeba is a variant insect. 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 );

Amoeba is equivalent to a router for SQL requests. It aims to provide mechanisms for Server Load balancer, read/write splitting, and high availability, rather than fully implementing them. You need to use MySQL Replication and other mechanisms to implement replica synchronization and other functions. Amoeba connection management and routing for underlying databasesThe implementation also adopts a pluggable mechanism. A third party can develop more advanced strategies to replace the implementation of the author. In general, this program is equivalent to KISS..

Main solution:
• Reduce the complex multi-database structure caused by data splitting
• Provides sharding rules and reduces the impact of data sharding rules on Applications
• Reduce the number of connections between the database and the client
• Read/write splitting

 

Disadvantages:
A) transactions are not currently supported.
B) stored procedures are not currently supported (will be supported in the near future)
C) It is not suitable for exporting data from amoeba or querying large data volumes (for example, when a request returns more than million data records)
D. Currently, database/table sharding is not supported. Currently, amoeba only supports database sharding instances. Each split node must maintain the same database/table structure:
Amoeba: Amoeba

1Environment Introduction

Master -- IP: 192.168.100.161
Slave --- IP: 192.168.100.160
Amoeba -- IP: 192.168.100.214
2Install JDK

# Mkdir/Amoeba
# Tar-xvf jdk-7u40-linux-x64.tar.gz-C/Amoeba
# Vim/etc/profile
JAVA_HOME =/Amoeba/jdk1.7.0 _ 40
Export JAVA_HOME
PATH = $ JAVA_HOME/bin: $ PATH
Export PATH
CLASSPATH =: $ JAVA_HOME/lib/tools. jar: $ JAVA_HOME/lib/dt. jar: $ CLASSPATH
Export CLASSPATH

3Install Amoeba#: Mkdir/usr/local/amoeba

#: Unzip amoeba-mysql-1.3.1-BETA.zip-d/usr/local/amoeba
#: Chmod-R + x/usr/local/amoeba/bin/
# Java-version
Java version "1.7.0 _ 40"
Java (TM) SE Runtime Environment (build 1.7.0 _ 40-b43)
Java HotSpot (TM) 64-Bit Server VM (build 24.0-b56, mixed mode)

4Master-slave authorization to add users

Mysql --> grant all privileges on *. * to amoeba @ '%' identified by 'amoeba123 ';
Mysql --> flush privileges;

5Amoeba Configuration　　

Amoeba For MySQL is very simple to use. All configuration files are standard XML files, and there are four configuration files in total. They are: amoeba. xml: The Master configuration file, which configures all data sources and parameter settings of Amoeba. If the master and slave nodes are enabled, you can configure this file;

Rule. xml: configure information about all Query routing rules;
FunctionMap. xml: configure the Java Implementation class for parsing the functions in the Query;
RullFunctionMap. xml: configure the implementation class of the specific function to be used in the routing rule;
6Configure the Amoeba IP address and user

# Vim/usr/local/amoeba/conf/amoeba. xml

7Amoeba: Configure basic Master information

8Amoeba Basic Slave configuration information

9Master load configuration

10Slave load configuration

11Finally, configure read/write splitting

Start Amoeba
# Cd/usr/local/amoeba/bin
This kind of startup makes it easy to see the nohup. log to prevent overflow prompts.
#: Nohup bash-x amoeba &

Common Errors: 1
　　　　The stack size specified is too small, Specify at least 228 k
　　　　Error: cocould not create the Java Virtual Machine.
　　　　Error: A fatal exception has occurred. Program will exit.
Solution:
# Vim/usr/local/amoeba/bin/amoeba
# DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss128k" comment
DEFAULT_OPTS = "-server-Xms256m-Xmx256m-Xss256k" added

Common Error 2:
　　Org. xml. sax. SAXParseException; lineNumber: 406; columnNumber: 6; the string "--" is not allowed in comments "--".
Solution:
Later, I changed the course and tried it for one afternoon. There must be spaces on both sides of the comment.

Example <! -- Object Class path and name -->

12Test read/write splitting

1> Create an alvin table in the test database on the master (master ).
Mysql-> use test;
Mysql-> create table alvin (id int (10), name char (10 ));

2> check whether the alvin table is synchronized from the test database on the (slave ).
Mysql-> use test;
Mysql-> show tables;
3> operate on the master node. Insert a data entry.
Mysql --> insert into alvin values (1, 'master ');
4> insert a data entry in the slave (slave) operation.
Mysql --> insert into alvin values (2, 'slave ');
Note that the data from the master is synchronized. So here there are two pieces of data,

5> operate on Amoeba
In the test above 192.168.100.214, this password is the authentication password in the configuration file, root and password 123456.

# Mysql-u root-p 123456-h 192.168.100.214-P 9066
(1) Verify read first: the configuration file uses DR round-robin. Note that this IP address should be written: The amoeba IP address, not the master or slave. It is used to log on to 192.168.100.214 through the amoeba proxy.
Mysql-> use test;
The test round-robin is successful. The master node and slave node are queried once.
(2) Verify the write: normally. If only all data is written to the master, the data on both sides will be generated. If one of them is inconsistent. The description is written from it. Because the master does not synchronize the Slave Data. All are unidirectional data transmission.

13.Common configuration method:

　　(1) If multiple databases are loaded, the software determines which databases have permissions based on your user authorization.:
　　　　Grant all privileges on *. * to amoeba @ '%' identified by 'amoeba123 '; if all is written here, all databases are authorized (all databases are automatically loaded ).
　　　　The database name test in the configuration file above is determined by this statement. If it is all, write test directly for testing. This is more convenient.

　　(2) Load writing for one master and one slave:
　　　　Write only. <property name = "poolNames"> server1 </property>
　　　　From read-only. <property name = "poolNames"> server2 </property>

　　(3) Load writing for one master and one slave:
　　　　Primary negative write: <property name = "poolNames"> server1 </property>
　　　　The master is also responsible for reading and reading. ratio: first read. from. the second read. if you write data cyclically, you can only write data to the master node. <property name = "poolNames"> server1, server2 </property>

　　(4) Load writing for one master and multiple slaves:
　　　　For example, one master node and three slave nodes. The. server1 (master) server2 (slave1) server3 (slave2) server4 (slave3) are defined at the top)
　　　　1 primary write only: <property name = "poolNames"> server1 </property>
　　　　3 read <property name = "poolNames"> server2, server3, server4 </property> from the load
　　　　Weight: 1: 1, 3 round robin each time. Continuous Loop

　　(5) For example, if I want to set a higher slave1 weight, the other two will be read once. slave1 will be read twice.
　　　　<Property name = "poolNames"> server2, server2, server3, server4 </property>

========================================================== ======================================


Amoeba. xml configuration file
<? Xml version = "1.0" encoding = "gbk"?>

<! DOCTYPE amoeba: configuration SYSTEM "amoeba. dtd">
<Amoeba: configuration xmlns: amoeba = "http://amoeba.meidusa.com/">

<Server>
<! -- Port bound to the proxy server -->
<Property name = "port"> 6006 </property>

<! -- Proxy server bound IP -->

<Property name = "ipAddress"> 10.0.2.160 </property>

<! -- Proxy server net IO Read thread size -->
<Property name = "readThreadPoolSize"> 20 </property>

<! -- Proxy server client process thread size -->
<Property name = "clientSideThreadPoolSize"> 30 </property>

<! -- Mysql server data packet process thread size -->
<Property name = "serverSideThreadPoolSize"> 30 </property>

<! -- Socket Send and receive BufferSize (unit: K) -->
<Property name = "netBufferSize"> 128 </property>

<! -- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->
<Property name = "tcpNoDelay"> true </property>

<! -- Username for external verification -->
<Property name = "user"> root </property>

<! -- Password for external verification -->

<Property name = "password"> 123456 </property>


<! -- Query timeout (default: 60 second, TimeUnit: second) -->
<Property name = "queryTimeout"> 60 </property>
</Server>

<! --
Each ConnectionManager is started as a thread.
Manager is responsible for Connection IO read/write/death detection
-->
<ConnectionManagerList>
<ConnectionManager name = "defaultManager" class = "com.meidusa.amoeba.net. MultiConnectionManagerWrapper">
<Property name = "subManagerClassName"> com.meidusa.amoeba.net. AuthingableConnectionManager </property>

<! --
Default value is avaliable Processors
<Property name = "processors"> 5 </property>
-->
</ConnectionManager>
</ConnectionManagerList>

<DbServerList>
<! --
You need to configure a pool for a mysqlServer instance,
If multiple equal mysql databases require loadBalance,
The platform has provided an objectPool with load balancing capabilities: com. meidusa. amoeba. mysql. server. MultipleServerPool
A simple configuration is to add virtual = "true" to the property. factoryConfig cannot be configured for this Pool.
Or write an ObjectPool by yourself.
-->
<DbServer name = "server1">

<! -- PoolableObjectFactory implementation class -->
<FactoryConfig class = "com.meidusa.amoeba.mysql.net. MysqlServerConnectionFactory">
<Property name = "manager"> defaultManager </property>

<! -- Real mysql database port -->
<Property name = "port"> 3306 </property>

<! -- Real mysql database IP -->
<Property name = "ipAddress"> 10.0.2.159 </property>
<Property name = "schema"> test </property>

<! -- Username used to log on to mysql -->
<Property name = "user"> amoeba </property>

<! -- Password used to log on to mysql -->


<Property name = "password"> amoeba123 </property>


</FactoryConfig>

<! -- ObjectPool implementation class -->
<PoolConfig class = "com.meidusa.amoeba.net. poolable. PoolableObjectPool">
<Property name = "maxActive"> 200 </property>
<Property name = "maxIdle"> 200 </property>
<Property name = "minIdle"> 10 </property>
<Property name = "minEvictableIdleTimeMillis"> 600000 </property>
<Property name = "timeBetweenEvictionRunsMillis"> 600000 </property>
<Property name = "testOnBorrow"> true </property>
<Property name = "testWhileIdle"> true </property>
</PoolConfig>
</DbServer>


<DbServer name = "server2">

<! -- PoolableObjectFactory implementation class -->
<FactoryConfig class = "com.meidusa.amoeba.mysql.net. MysqlServerConnectionFactory">
<Property name = "manager"> defaultManager </property>

<! -- Real mysql database port -->
<Property name = "port"> 3306 </property>

<! -- Real mysql database IP -->
<Property name = "ipAddress"> 10.0.2.158 </property>
<Property name = "schema"> test </property>

<! -- Username used to log on to mysql -->
<Property name = "user"> amoeba </property>

<! -- Password used to log on to mysql -->


<Property name = "password"> amoeba123 </property>


</FactoryConfig>

<! -- ObjectPool implementation class -->
<PoolConfig class = "com.meidusa.amoeba.net. poolable. PoolableObjectPool">
<Property name = "maxActive"> 200 </property>
<Property name = "maxIdle"> 200 </property>
<Property name = "minIdle"> 10 </property>
<Property name = "minEvictableIdleTimeMillis"> 600000 </property>
<Property name = "timeBetweenEvictionRunsMillis"> 600000 </property>
<Property name = "testOnBorrow"> true </property>
<Property name = "testWhileIdle"> true </property>
</PoolConfig>
</DbServer>



<DbServer name = "master" virtual = "true">
<PoolConfig class = "com. meidusa. amoeba. server. MultipleServerPool">
<! -- Server Load balancer parameter 1 = ROUNDROBIN, 2 = WEIGHTBASED, 3 = HA -->
<Property name = "loadbalance"> 1 </property>

<! -- The poolName list participating in the pool Server Load balancer is separated by commas -->
<Property name = "poolNames"> server1 </property>
</PoolConfig>
</DbServer>


<DbServer name = "slave" virtual = "true">
<PoolConfig class = "com. meidusa. amoeba. server. MultipleServerPool">
<! -- Server Load balancer parameter 1 = ROUNDROBIN, 2 = WEIGHTBASED, 3 = HA -->
<Property name = "loadbalance"> 1 </property>

<! -- The poolName list participating in the pool Server Load balancer is separated by commas -->
<Property name = "poolNames"> server1, server2 </property>
</PoolConfig>
</DbServer>



</DbServerList>

<QueryRouter class = "com. meidusa. amoeba. mysql. parser. MysqlQueryRouter">
<Property name = "ruleConfig" >$ {amoeba. home}/conf/rule. xml </property>
<Property name = "functionConfig" >$ {amoeba. home}/conf/functionMap. xml </property>
<Property name = "ruleFunctionConfig" >$ {amoeba. home}/conf/ruleFunctionMap. xml </property>
<Property name = "LRUMapSize"> 1500 </property>
<Property name = "defaultPool"> master </property>


<Property name = "writePool"> master </property>
<Property name = "readPool"> slave </property>

<Property name = "needParse"> true </property>
</QueryRouter>
</Amoeba: configuration>