MySQL read and write separation of amoeba

I. Amoeba Introduction

Amoeba (amoeba) project, the Open source framework launched a amoeba for MySQL software in 2008. This software is dedicated to MySQL's distributed database front-end agent layer, which mainly serves as the SQL routing function when the application layer accesses MySQL, focusing on the development of the distributed database Proxy layer. is located between the client and DB Server (s) and is transparent to clients. With load balancing, high availability, SQL filtering, read and write separation, routable related to the target database, multiple database merge results can be requested concurrently. With amoeba you can perform high-availability, load-balancing, and data slicing functions for multiple data sources, currently amoeba is used on many enterprise production lines.

In the MySQL proxy 6.0 version above if you want to read and write separation and 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.

Currently Insufficient:

1. Transactions are not currently supported
   2. Stored procedures are not supported for the time being (recently supported)
   3. Not suitable for scenarios from amoeba or queries for large data volumes (such as when a request returns more than 10w or more)
   4. Temporarily does not support the sub-database sub-table, amoeba currently only do the sub-db instance, each segmented node needs to keep the library table structure consistent.

2. Principle Flowchart

Two mounting amoeba

wget http://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.x/amoeba-mysql-binary-2.1.0-RC5.tar.gz

mkdir-pv/usr/loca/amoeba/

TAR-ZXVF amoeba-mysql-binary-2.1.0-rc5.tar.gz-c/usr/loca/amoeba/

Then go to the bin directory to run Amoeba start or amoeba start & background, but let's look at the configuration file first;

Three configuration Amoeba

1. Imagine amoeba as the 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 use the user name and password, and so on. This information is stored in the $amoeba_home/conf/dbservers.xml.
2.Amoeba to complete data segmentation provides a perfect segmentation rule configuration, in order to understand how to shard the data, how to integrate the data returned by the database, it must know the segmentation rules. The information related to the Shard rule is stored in the $amoeba_home/conf/rule.xml.
3. When we write SQL to manipulate the database, we often use a lot of 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. When configuring $amoeba_home/conf/rule.xml, we will use some of our own defined functions, such as we need to hash the user ID value to slice the data, these functions in $amoeba_home/conf/ Defined in Rulefunctionmap.xml.
5.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.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.

OK to get ""? The most popular Java background framework

Configuration before we talk about our architecture, very simple, three hosts

A amoeba 192.168.127.140来 forwarding request;

A master 192.168.127.141 can read and write operations;

A slave 192.168.127 can only be read operation;

This scenario is a large amount of read data, so master can read and write, slave can only read, the final effect reading scale is master:slave=1:3.

7. Configure the Amoeba node

7.1 Red font is where the main profile needs to be noticed and modified

<?xml version= "1.0" encoding= "GBK"?>

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

<proxy>  <!-- service class must implements  Com.meidusa.amoeba.service.service --> <service name= "Amoeba for mysql"   class= "Com.meidusa.amoeba.net.ServerableConnectionManager" > #1. Define this as a mysql proxy service. The corresponding will also have Mongodb proxy service and Aladdin proxy service.  <!-- port --> <property name= "Port" >8066</property>  #2. Port used by database clients to log on to Amoeba;  <!-- bind ipAddress --> <!--  < Property name= "IpAddress" >192.168.127.140</property>  #3. The IP that the database client uses to log amoeba, usually proxy  The host address of the service services does not need to be defined, and if the amoeba server is located within multiple network environments you can define one of the IP of the machine to specify the network environment that the amoeba serves. If set to 127.0.0.1 will cause other machines to be unable to access the Amoeba service.  -->  <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>  #6. The user name used by the database client to log in to amoeba;   <property name= "Password" >password</ Property> #7. The password used by the database client to log on 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>  <!-- server class must implements  com.meidusa.amoeba.service.Service --> <service name= "Amoeba monitor server"  class= " Com.meidusa.amoeba.monitor.MonitorServer "> <!-- port --> <!-- default  value: random number <property name= "Port" >9066</property> -->  <!-- bind ipaddress --> <property name= "IpAddress" >127.0.0.1</ Property> <property name= "Daemon" >true</property> <property name= " Manager ">${clientconnectionemanager}</property> <property name=" ConnectionFactory ">  <bean class= "Com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory" ></bean>  </property>  </service>  <runtime class= " Com.meidusa.amoeba.mysql.context.MysqlRuntimeContext "> #8. Runtime elements define some proxy-related runtime configurations, such as the number of threads on the client and database servers, and the SQL time-out setting.  <!-- 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>  <!-- per connection cache  Prepared statement size --> <property name= "Statementcachesize" >500</ property>  <!-- query timeout ( default: 60 second ,  Timeunit:second)  --> <property name= "QueryTimeout" >60</property> </ runtime>  </proxy>  <!--  each connectionmanager will  start as thread manager responsible for the connection io read , death detection -- > <connectionManagerList>  #9. Connectionmanagerlist defines a series of connection managers that can be referenced elsewhere. For example, Clientconnectionemanager is referenced as a client Connection manager for Mysql proxy service in Amoeba.xml Defaultmanager is referenced as a database server-side Connection manager for DBServer in Dbservers.xml. The Connection manager primarily configures the number of CPU cores used for network processing, with the default processor attribute being the number of CPU cores for the host amoeba.  <connectionmanager name= "Clientconnectionemanager"  class= " Com.meidusa.amoeba.net.MultiConnectionManagerWrapper "> <property name=" Submanagerclassname " >com.meidusa.amoeba.net.ConnectionManager</property> <!--  default value  Is avaliable processors  <property name= "Processors" >5</property> -- > </connectionmanager> <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 >  <!-- default using file loader --> <dbserverloader  class= "Com.meidusa.amoeba.context.DBServerConfigFileLoader" > <property name= "ConfigFile" >${amoeba.home}/conf/dbservers.xml</property> </dbserverloader>  <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=" >1500< "LRUMapSize" /property> <property name= "Defaultpool" >master</property> #10. Indicates that the default configuration reads and writes to the master of the data pool, defined in Dbserver.xml;   <property name= "Writepool" >master</ Property> #11. Indicates that a write-only data pool specifies the role of the name master;  <property name= "Readpool" >virtualslave</property > #12. Indicates that the read-only data pool specifies the role of the name Virtualslave;  <property name= "Needparse" >true</property>  </queryRouter></amoeba:configuration>

Ok

7.2 Configuring Dbserver.xml

<?xml version= "1.0" encoding= "GBK"?>

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

<!--  each dbserver needs to be configured into a pool,  If you need to configure multiple dbServer with load  balancing that can be simplified by the following configuration:  add attribute with name virtual =  "true"  in dbServer, but  the configuration does not allow the element with name  factoryconfig such as  ' Multipool '  dbserver  --> <dbserver name= " Abstractserver " abstractive=" true ">  #1. In this dbservers configuration file, we define three dbserver elements, which is the definition of the first DBSERVER element. This dbserver named Abstractserver, whose Abstractive property is true, means that this is an abstract dbserver definition that can be extended by other dbserver definitions.  <factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" > < Property name= "Manager" >${defaultmanager}</property>  #2. Manager defines the DBServer selected Connection Manager (ConnectionManager), which references Amoeba.xml's configuration  <property name = "Sendbuffersize" >64</property> <property name= "Receivebuffersize" >128</property > <!-- mysql port --> <property name= "Port" >3306</property> #3. Database ports  <!-- mysql schema --> <property name= "schema" >dongdong</ property> #4. Target database  <!-- mysql user --> <property name= "user" >haha </property> #5. The user who is used to connect to the backend database, pay attention to giving him permission to manipulate the database above; <!-- mysql password -->  <property name= "Password" >haha123</property> #6. Haha user login Password  </factoryConfig>

<poolconfig class= "Com.meidusa.amoeba.net.poolable.PoolableObjectPool" > #7. DBServer with poolconfig elements, The attributes of this element are primarily configured with connection pooling to the database, and the specific configuration associated with this is described in more detail later.  <property name= "maxactive" >500</property> <property name= "MaxIdle" >500 </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"  parent= "Abstractserver" > #8. This master is an extension of the Abstractserver, the parent property is configured with an expanded abstract dbserver, which expands the IPAddress attribute of Abstractserver to name the IP address of the database, while the port, username password, Properties such as connection pooling are inherited by the Abstractserver configuration.  <factoryConfig> <!-- mysql ip --> <property name= "IpaddreSS ">192.168.127.141</property> #9. Master's address; </factoryconfig> </dbserver>  <dbserver name= "Slave"  parent= "Abstractserver" > <factoryConfig> <!--  Mysql ip --> <property name= "IpAddress" >192.168.127.142</property> #9. Slave's address;  </factoryconfig> </dbserver> <dbserver name= "VirtualSlave"   Virtual= "true" > #10 .  This section does not actually need to be configured and does not affect the basic use. The following is an overview of what this configuration means: Multipool is a virtual database node that can be configured as a database pool of several databases. For example, only one server1 is configured in the above configuration, and the load Balancing policy is roundrobin (polling). Detailed tutorials related to virtual database nodes are described in a later section.  <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&Nbsp;name= "Poolnames" >master,slave,slave,slave</property> #11. Here the master and slave configurations appear here to assign them the requested scale, here is 1:3  </poolConfig> </dbServer></amoeba:dbServers>

Supplemental definition Abstractserver: When we have a database cluster that needs to be managed, most of the information in the node in this DB cluster may be the same, such as: Port number, username, password, and so on. Thus, the abstractserver defined by the generalization of these commonalities greatly simplifies the dbservers configuration file.

Four. Run Amoeba and test

1./usr/loca/amoeba/bin/amoeba Start &

Log display

Open socket channel to server[192.168.127.141:3306] success!

Open socket channel to server[192.168.127.142:3306] success!

If it is not shown to prove that there is no connection to the backend database, I have a previous connection, the result is that the comments inside the configuration file are not removed, O (╯-╰) o

2. Because the actual environment needs master and slave synchronization, not the focus of this article, you can see other articles;

Here is the premise is to close the slave synchronization, on the slave stop slave, but if a large number of test data, then you can let the slave the master database synchronization in the shutdown is OK;

Create a database on master and slave, respectively

Grant all privileges the hehe.* to [e-mail protected] '% ' indefied by ' hehe123 ';

Flush privileges;

Create database hehe;

Use hehe;

Create tables Heheta (ID int (ten), name varchar (20));

Insert into Heheta (id,name,address) value (' 9, ' master ', ' 100 '); Inserting data to Master

Insert into Heheta (id,name,address) value (' Ten ', ' slave ', ' 101 '); Insert data to Slave

Then log in and test on the amoeba host.

mysql-uroot-ppassword-h192.168.127.140-p8066

Use Heheta;

Mysql> SELECT * FROM heheta;+------+-------+---------+| ID | name | Address |+------+-------+---------+| 10 | Slave | 101 |+------+-------+---------+1 row in Set (0.01 sec)

Mysql> SELECT * FROM heheta;+------+-------+---------+| ID | name | Address |+------+-------+---------+| 10 | Slave | 101 |+------+-------+---------+1 row in Set (0.00 sec)

Mysql> SELECT * FROM heheta;+------+-------+---------+| ID | name | Address |+------+-------+---------+| 10 | Slave | 101 |+------+-------+---------+1 row in Set (0.01 sec)

Mysql> SELECT * FROM heheta;+------+--------+---------+| ID | name | Address |+------+--------+---------+| 9 | Master | |+------+--------+---------+1 row in Set (0.01 sec)

Read scale Master:slave=1:3

Inserting data tests

Insert into Heheta (id,name,address) value (' One ', ' adddata ', ' 102 ');

Queries can only have data on the master that is responsible for writing the data

Mysql> SELECT * FROM heheta;+------+-------+---------+| ID | name | Address |+------+-------+---------+| 10 | Slave | 101 |+------+-------+---------+1 row in Set (0.01 sec)

Mysql> SELECT * FROM heheta;+------+---------+---------+| ID | name | Address |+------+---------+---------+| 9 | Master | 100 | | 11 | AddData | 102 |+------+---------+---------+2 rows in set (0.02 SE

MySQL read and write separation of amoeba