Amoeba for MySQL load balancing, read and write separation;

http://onlyoulinux.blog.51cto.com/7941460/1570472

The above blog has been realized the master and slave of MySQL, this article on the basis of the master and slave to achieve load balancing, read and write separation.

Amoeba: Using Java nto framework non-blocking mode;

Amoeba for MySQL is a sub-project of the Amoeba project;

1, amoeba to have Java environment support: Install JDK

[Email protected] src]# RPM-IVH jdk-7u67-linux-i586.rpm Preparing ... ##################################### ###### [100%] 1:jdk ########################################### [100%]unpacking JAR files ... rt.jar. . Jsse.jar ... charsets.jar ... tools.jar ... localedata.jar ... jfxrt.jar ... plugin.jar ... javaws.jar ..... deploy.jar. .... ...

[Email protected] src]# cat/etc/profile.d/jdk.sh export java_home=/usr/java/jdk1.7.0_67export path= $PATH: $JAVA _home /bin: $JAVA _home/jre[[email protected] src]# source/etc/profile

2. Download Amoeba Package:

[[email protected] src] #wget http://jaist.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/ Amoeba-mysql-1.3.1-beta.zip[[email protected] amoeba]# unzip amoeba-mysql-1.3.1-beta.zip [[email protected] amoeba]# Lsamoeba-mysql-1.3.1-beta.zip bin build.properties build.xml conf doc lib LICENSE.txt logs readme.html src

3. The following is my profile (red is the place to note or change)

[email protected] amoeba]# cat conf/amoeba.xml |grep-v "^$"
<?xml version= "1.0" encoding= "GBK"?>

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

<server>
<!--proxy server bound Port--
<property name= "Port" >8066</property>
<!--proxy server bound IP--
<property name= "IpAddress" >192.168.0.120</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>
<!--Externally authenticated user name--
<property name= "User" >Root</property>
<!--Externally authenticated passwords-
<property name= "Password" >Password</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>
<!--
a MySQLServer needs to be configured with a pool，
if more than one equal MySQL needs to be loadbalance,
The platform has already provided a objectpool with load balancing capability:Com.meidusa.amoeba.mysql.server.MultipleServerPool
The simple configuration is the attribute plus virtual= "true", which the pool does not allow to configure Factoryconfig
or write a objectpool 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" >192.168.0.117</property>
<property name= "schema" >test</property>
<!--user name and password for logging in to MySQL-
<property name= "User" >Proxy</property>
<!--password to log in to MySQL--
<property name= "Password" >Proxy</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" >192.168.0.118</property>
<property name= "schema" >test</property>
<!--user name to log in to MySQL--
<property name= "User" >Proxy</property>
<!--password to log in to MySQL--
<property name= "Password" >Proxy</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" >
<!--load Balancing parameters1=roundrobin, 2=weightbased, 3=ha-->
<property name= "LoadBalance" >1</property>
<!--the list of poolname participating in the pool 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" >
<!--load Balancing parameters 1=roundrobin , 2=weightbased, 3=ha-->
<property name= "LoadBalance" >1</property>
<!--the list of poolname participating in the pool 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>

4, the Amoeba agent on the main library authorization (only the main library can be, from the library will be automatically synchronized)

Mysql>grant all privileges on * * to ' proxies ' @ ' 192.168.0.120 ' identified by PASSWORD ' proxies ' with GRANT OPTION1;

Row in Set (0.00 sec)

Mysql>flush privileges;

5. Start Amoeba

[[email protected] amoeba]#/usr/local/amoeba/bin/amoeba & first give amoeba executable permission

[Email protected] amoeba]# Ps-ef |grep Amoeba
Root 7686 1604 0 06:26 pts/1 00:00:22/usr/java/jdk1.7.0_67/bin/java-server-xms256m-xmx256m-xss128k-damoeba . home=/usr/local/amoeba-dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds-classpath/usr/local/amoeba/ Lib/classworlds-1.0.jar Org.codehaus.classworlds.Launcher
Root 7977 7949 0 07:25 tty1 00:00:00 vim/usr/local/amoeba/conf/amoeba.xml
Root 8004 1456 0 07:38 pts/0 00:00:00 grep Amoeba
[Email protected] amoeba]# NETSTAT-TUNLP |grep java
TCP 0 0:: ffff:192.168.0.120:8066:::* LISTEN 7686/java

6. Testing

Testing method: Build a test library alone, build a table Jaychou in the library, add a row of data in the table, then Mysql>slave stop, let the data no longer synchronized, and then add a row of data in the main library, on the amoeba repeatedly query look different; Write the test as long as the amoeba to insert a row of data on it;

# mysql-uroot-ppassword-h192.168.0.120-p8066 attention to distinguish which user name which password;

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
| 2 |
+------+
2 rows in Set (0.00 sec)

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.01 sec)

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
| 2 |
+------+
2 rows in Set (0.01 sec)

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)

mysql> INSERT into Jaychou value (3);
Query OK, 1 row affected (0.01 sec)

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)

Mysql> select * from Jaychou;
+------+
| ID |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in Set (0.01 sec)

Mysql>

Seems to be able to achieve this effect,

Amoeba can also do load balancing, but for the master-slave two-node MySQL cluster, the use of roundrobin mechanism can be, for multi-node MySQL cluster can be used round, weight, and even can do ha, everyone feels amoeba for MySQL is a lot easier than mysql-proxy.

；

This article is from the "Linux_ ant" blog, please make sure to keep this source http://onlyoulinux.blog.51cto.com/7941460/1570816

Amoeba for MySQL load balancing, read and write separation;