Amoeba for MySQL read-write separation configuration

Experimental environment

Hostname IP

Master-db 192.168.0.1

SLAVE-DB1 192.168.0.2

SLAVE-DB2 192.168.0.3

Amoeba 192.168.0.123

I. Introduction of AMOEBA

Amoeba is an open source project initiated by Ali technology Chen Siju, which is a distributed database proxy solution. The amoeba framework was developed based on Java SE 1.5, and the Java environment needs to be installed before installing amoeba. The amoeba is verified to work correctly on Java SE 1.5 and Java SE 1.6.

Amoeba's Products:

Amoeba for MySQL

Amoeba for Aladdin

Amoeba for MongoDB

The following main introduction to amoeba for MySQL:

Amoeba for MySQL is committed to MySQL's distributed database front-end agent layer, which is mainly used in the application layer to access MySQL when the query routing function, with the note distributed database Proxy development. is located between the client and DB Server (s). Transparent to the client. With load balancing, high availability, query filtering, read/write separation, routing-related query to the target database, multiple database merge results can be requested concurrently. On amoeba you can perform high-availability, load-balancing, and data slicing functions for multiple data sources. Currently in the production line of many enterprises to use.

So what is amoeba for MySQL to the client program? Let's just think of it as MySQL, it's a virtual MySQL, the MySQL protocol is provided externally. The client connects amoeba as if it were a MySQL connection. The relevant authentication properties need to be configured within the amoeba.

Comparison of Amoeba for MySQL vs MySQL Proxy:

In the MySQL proxy 6.0 version above if you want to read and write separate and hideaway read the cluster, write the cluster machine more cases, with MySQL proxy requires a considerable amount of work, currently MySQL proxy does not have a ready-made Lua script. MySQL Proxy does not have a configuration file at all, and the Lua script is its entirety, of course Lua is quite handy. So the same thing requires writing a lot of scripts to complete a complex configuration. Amoeba for MySQL only needs to be configured to meet the requirements.

Advantages of amoeba for MySQL:

1. Reduce the complex multi-database structure caused by data segmentation

2. Provide segmentation rules and reduce the impact of data segmentation rules on applications

3. Reduce the number of DB non-client connections

4. Read and write separation

Amoeba for MySQL Disadvantages:

1. Amoeba for MySQL does not support transactions: In fact, the problem of distributed transactions is the common problem of all the database middle tier, not amoeba unique problem; The usual solution is to split the transaction into small transactions within a single database, which is controlled by the application.

2. Amoeba for MySQL does not support cross-node joins: Again, this problem is not amoeba exclusive, and the usual solution is for the application to fetch the data from one node and then the application to join the other node.

3. Amoeba for MySQL does not support cross-node ordering: This problem is not ameoba unique, and the usual workaround is for the application to fetch the data from multiple nodes and then sort it.

4. Amoeba for MySQL is not suitable for queries that return a large number (more than 100,000) of data.

5. Amoeba for MySQL does not support the sub-database sub-table, can only be divided into DB instances.

Why use Amoeba:

With the maturing of traditional database technology, the rapid development of computer network technology and the expansion of application scope, database application has been widely established on computer network. At this point, the centralized database system shows its insufficiency: centralized processing, it will cause performance bottleneck; The application is centrally run on one computer, and if the computer fails, the whole system is affected, the reliability is not high, the scale and configuration of the system are not flexible and the expansibility of the system is poor. In this situation, the centralized database will be developed to the distributed database. Amoeba's transparent, simple configuration and multiple advantages make it an excellent choice in distributed Database Agent products.

Second, installation and operation of Amoeba

1. Amoeba for MySQL Architecture:

2. Verifying the installation of Java

The amoeba framework was developed based on Java SE1.5, and it is recommended that you use Java SE1.5 above.

# java-version

Java Version "1.6.0_35"

Java (TM) SE Runtime Environment (build 1.6.0_35-B10)

Java HotSpot (TM) 64-bit Server VM (build 20.10-b01, Mixed mode)

Note: JDK installation method no longer elaborated, will not be able to refer to:

http://304076020.blog.51cto.com/7503470/1642482 #安装JDK部分.

3. Install MySQL database and configure the master-slave copy section is no longer described here, will not be consulted:

http://304076020.blog.51cto.com/7503470/1674911

4. Download Amoeba

http://down.51cto.com/data/720175

5. Installing Amoeba

# Mkdir/usr/local/amoeba

# Unzip Amoeba-mysql-3.0.4-beta-distribution.zip

#/BIN/CP–RF amoeba-mysql-3.0.4-beta/*/usr/local/amoeba

Third, configure Amoeba for MySQL:

Amoeba for MySQL is very simple to use, mainly through the XML file to achieve.

1. Description of the configuration file:

(1.) Dbservers.xml Imagine Amoeba as a database agent layer, it must be in communication with many databases, so it has to know how it proxies the database to connect, such as the most basic: Host IP, port, amoeba user name and password used and so on. This information is stored in the $amoeba_home/conf/dbservers.xml.

(2.) Rule.xml Amoeba provides a complete segmentation rule configuration for Data segmentation, and it must know the segmentation rules in order to understand how to shard the data and how to consolidate the data returned by the database. Information about non-sharding rules is stored in $amoeba_home/conf/rule.xml.

(3.) Functionmap.xml when we write SQL to manipulate the database, we often use many different database functions, such as Unix_timestamp (), Sysdate (), and so on. How are these functions parsed by amoeba? $AMOEBA _home/conf/functionmap.xml describes the relationship between function names and functional processing.

(4.) rulefunctionmap.xml the configuration of $amoeba_home/conf/rule.xml, we use some of our own defined functions, such as we need to hash the user ID value to slice the data, these functions in the $amoeba_ Defined in Home/conf/rulefunctionmap.xml.

(5.) access_list.conf Amoeba can develop some host IP addresses that can be accessed and denied access, which is configured in $amoeba_home/conf/access_list.conf.

(6.) Log4j.xml Amoeba allows the user to configure the output log level and mode, the configuration method uses the log4j file format, the file is $amoeba_home/conf/log4j.xml. Among them, we mainly use Dbserver.xml and amoeba.xml.

2. Configuration of Dbserver.xml:

<dbserver name= "Abstractserver" abstractive= "true" >

<factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" >

<property name= "ConnectionManager" >${defaultManager}</property>

<property name= "Sendbuffersize" >64</property>

<property name= "Receivebuffersize" >128</property>

<!-mysql Port--><!-The port number used by the proxy connection database--

<property name= "Port" >3306</property>

<!-mysql schema--><!-The database used by the proxy connection database--

<property name= "schema" >test</property>

<!-mysql user--><!-Proxy connection database used by username--

<property name= "User" >root</property>

<!-Proxy Connection database using the password--

<property name= "Password" >1234</property>

</factoryConfig>

<poolconfig class= "Com.meidusa.toolkit.common.poolable.PoolableObjectPool" >

<property name= "Maxactive" >500</property>

<property name= "Maxidle" >500</property>

<property name= "Minidle" >1</property>

<property name= "Minevictableidletimemillis" >600000</property>

<property name= "Timebetweenevictionrunsmillis" >600000</property>

<property name= "Testonborrow" >true</property>

<property name= "Testonreturn" >true</property>

<property name= "Testwhileidle" >true</property>

</poolConfig>

</dbServer>

<!---Configure the address of the real database---

<!---Configure master-slave server and server connection pool--

<dbserver name= "Server1" parent= "Abstractserver" >

<factoryConfig>

<!--mysql IP--

<property name= "IpAddress" >192.168.0.1</property>

</factoryConfig>

</dbServer>

<dbserver name= "Server2" parent= "Abstractserver" >

<factoryConfig>

<!-mysql IP--

<property name= "IpAddress" >192.168.0.2</property>

</factoryConfig>

</dbServer>

<dbserver name= "Server2" parent= "Abstractserver" >

<factoryConfig>

<!-mysql IP--

<property name= "IpAddress" >192.168.0.3</property>

</factoryConfig>

</dbServer>

<dbserver name= "Multipool" virtual= "true" >

<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >

<!-load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha-->

<property name= "LoadBalance" >1</property>

<!-separated by Commas,such As:server1,server2,server1--

<property name= "Poolnames" >server2,server3</property>

</poolConfig>

</dbServer>

3. Amoeba.xml Configuration:

The IP address, port, user name, and password that are bound when the client connects to amoeba. and IP access restrictions.

<service name= "Amoeba for Mysql" class= "Com.meidusa.amoeba.mysql.server.MySQLService" >

<!--Port--

<property name= "Port" >8066</property>

<!--bind IpAddress--

<!--

<property name= "IpAddress" >127.0.0.1</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= "Authenticateprovider" >

<bean class= "Com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator" >

<property name= "User" >root</property>

<property name= "Password" >123</property>

<property name= "Filter" >

<bean class= "Com.meidusa.toolkit.net.authenticate.server.IPAccessController" >

<property name= "Ipfile" >${amoeba.home}/conf/access_list.conf</property>

</bean>

</property>

</bean>

</property>

</service>

The following is a definition of read-write separation:

<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" >multiPool</property>

<property name= "Writepool" >server1</property>

<property name= "Readpool" >multiPool</property>

<property name= "Needparse" >true</property>

</queryRouter>

Through the above simple configuration, the database can already be read and written separated.

Iv. Testing

#/usr/local/amoeba/bin/launcher & (can also be started by nohup background, the abstinence person writes a script to start.) )

The amoeba default port for startup is 8066

Connect the amoeba test on the client:

# mysql-uroot–p123-h 192.168.0.123--port 8066

mysql> show databases;

mysql> CREATE DATABASE test1; \ \ Create the database, then in the master-slave library to see through amoeba login, data query and evaluates into the update and other operations, and view the Mysql-log log, you can find the execution of INSERT, UPDATE, delete and other operations on the main library server1 operation, Select query statements are executed from the library Server2 and Server3.

This article is from the "Linux art (q Group: 1991706)" blog, please be sure to keep this source http://304076020.blog.51cto.com/7503470/1674915

Amoeba for MySQL read-write separation configuration