Build MySQL-based read-write separation tool amoeba

Build MySQL-based read-write separation tool Amoeba:
The Amoeba tool is a tool to realize the MySQL database read/write separation, if it is based on MySQL master-slave replication;

Lab environment (Virtual machine):
Host role
10.10.10.2 Multi-instance plus master-slave replication
10.10.10.30 Amoeba Server
10.10.10.40 client (last Test used)

1, first build MySQL master-slave replication (not to mention);
Note that: in the master and slave libraries need to create a user, the user created on the main library is amoeba, the permissions are create,update,insert,delete;
The user created from the library is also amoeba, and the permission is select;

2, because Amoeba is based on the Java environment to implement, so install the JDK plugin: (first download via Windows host)
http://pan.baidu.com/share/link?shareid=2793927523&uk=1678158691&fid=117337971851932
After the download is complete, it is uploaded to the server via Xshell's FTP software and then installed:

[Email protected] tools]# Tar XF jdk-7u79-linux-x64.tar.gz

[Email protected] tools]# vim/etc/profile      # #编辑jdk需要的环境变量; export Java_home=/root/tools/jdk1.7.0_79export jre_ Home=/root/tools/jdk1.7.0_79/jreexport Path=/root/tools/jdk1.7.0_79/bin: $PATHexport classapth=./:/root/tools/ Jdk1.7.0_79/lib:/root/tools/jdk1.7.0_79/lib

[[email protected] tools]# java-version        # #检测jdk是否安装成功, the following information appears to calculate the installation success; Java version "1.7.0_79" Java (TM) SE Runtime Environment (build 1.7.0_79-b15) Java HotSpot (TM) 64-bit Server VM (build 24.79-b02, Mixed mode)

3. Download and install the Amoeba software:

[[email protected] tools]# Mkdir/usr/local/amoeba # #创建一个目录用来放amoeba的软件; https://sourceforge.net/projects/ amoeba/# #windows主机登录网址到网页中部的地方下载 Released/oldfiles/amoeba-mysql-0.19.zip, put it on the server by Xshell with the FTP tool;[[email  Protected] amoeba]# cd/usr/local/amoeba/[[email protected] amoeba]# unzip amoeba-mysql-0.19.zip[[email  Protected] amoeba]# lsLICENSE.txt readme.html amoeba-mysql-0.19.zip bin Build.xml conf doc Lib logs src # #主要用到的目录是conf这个配置 [[email protected] conf]# lsaccess_list.conf amoeba.dtd amoeba.xml function.dtd functionMap.xml log4j.dtd Log4j.xml rule.dtd rule.xml rulefunctionmap.xml[[email protected] conf]# vim amoeba.xml<?xml version= "1.0" Encoding= "GBK"? ><! DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd" ><amoeba:configuration xmlns:amoeba= "/http/ amoeba.meidusa.com/"><server><!--proxy server°??? --><property name= "Port" >3306</property> # #配置的是Amoeba的服务端口, here with 3306 can be, the default is 8806;<!--proxy server° ép--><property name= "ipAddress" >10.10.10.30</property> # #配置的是Amoeba服务器的IP地址, The default is 127.0.0.1, which changes to the IP address on the physical network card;<!--proxy server Net IO Read thread size--><property name= "Readthreadpoolsize" >100</property> # #下面的四个数字可以调大一点;<!--proxy Server client process thread size--><property name= " Clientsidethreadpoolsize ">100</property><!--MySQL server data packet process thread size-->< Property Name= "Serversidethreadpoolsize" >200</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><!--???? Μ?? §? --><property name= "User" >root</property> # #配置客户端通过Amoeba连接数据库的用户, the default is Root;<property name= " Password ">xyp123123</property> # #配置的是客户端通过Amoeba连接数据库的密码, which is commented out by default; </server><dbserver Name=" sErver1 "> # #定义mysql池的名字;<!--poolableobjectfactory?ж '--><factoryconfig><classname> Com.meidusa.amoeba.mysql.net.mysqlserverconnectionfactory</classname><property name= "Manager" > defaultmanager</property><!--?? Mysql?????? --><property name= "Port" >3306</property> # #主库的端口号;<!--?? Mysql???? --><property name= "ipAddress" >10.10.10.20</property> # #主库的IP地址; <property name= "schema" >xyp </property># #需要进行操作的库;<!--?? Μ?? Mysqlμ?? §? --><property name= "User" >amoeba</property> # #用于Amoeba连接数据库的用户;<!--?? Μ?? Mysqlμ?? Whip-><property name= "password" >123123</property> # #用于Amoeba连接数据库的密码;</factoryconfig><!-- Objectpool?ж '--><poolconfig><classname>com.meidusa.amoeba.net.poolable.poolableobjectpool</ classname> # #这里的不用动; <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 name= "Server2" > # #定义从库的池;<!--poolableobjectfactory?ж '--><factoryconfig><classname> Com.meidusa.amoeba.mysql.net.mysqlserverconnectionfactory</classname><property name= "Manager" > defaultmanager</property><!--?? Mysql?????? --><property name= "Port" >3307</property> # #从库端口;<!--?? Mysql???? --><property name= "ipAddress" >10.10.10.20</property> # #从库ip地址; <property name= "schema" >xyp </property> # #从库上需要进行操作的库;<!--?? Μ?? Mysqlμ?? §? --><property name= "User" >amoeba</property> # #amoeba连接从库的用户;<!--?? Μ?? Mysqlμ?? Whip-><property name= "password" >123123</property> # #amoeba连接从库的密码;</factoryconfig><!--objectpool?ж '--><poolconfig><classname> com.meidusa.amoeba.net.poolable.poolableobjectpool</classname> # #下面的不用动; <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" > # #定义操作的池, master Main Library <poolConfig><className> com.meidusa.amoeba.server.multipleserverpool</classname><!--? o???? 1=roundrobin, 2=weightbased--><property name= "LoadBalance" >1</property><!--2?? Pool?o??? Poolname±?oo??? -><property name= "Poolnames" >server1</property> # #调用上面主库的池;Lt;/poolconfig></dbserver><dbserver name= "Slave" virtual= "true" > # #定义从库的池, named Slave;<poolconfig ><className>com.meidusa.amoeba.server.MultipleServerPool</className><!--? o???? 1=roundrobin, 2=weightbased--><property name= "LoadBalance" >1</property><!--2?? Pool?o??? Poolname±?oo??? -><property name= "Poolnames" >server2</property> # #调用上面从库的池;</poolconfig></dbserver> </dbserverlist><queryrouter><classname>com.meidusa.amoeba.mysql.parser.mysqlqueryrouter</ Classname><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>

[Email protected] conf]# chmod +x-r/usr/local/amoeba/bin/# #给启动命令amoeba授权; [[email protected] amoeba]#/usr/local/ Amoeba/bin/amoeba & # #后台启动; Log4j:warn log4j config load completed from File:/usr/local/amoeba/conf/log4j.xml # # This message is not an error, it is normal, [[email protected] amoeba]# jobs # #查看后台启动的服务; [1]-Running. /bin/amoeba & (WD:/usr/local/amoeba/conf)

4. Test:

[[email protected]~]# mysql-uroot-pxyp123123-h 10.10.10.30 # #通过客户端去连接amoeba服务器来登录到mysql;mysql> show Databas      Es # #查看数据库, there is already a library xyp;+--------------------that needs to be configured +| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test | | Xyp |+--------------------+5 rows in Set (0.02 sec) to stop Slave from the library, easy to test;mysql> use xypdatabase changedmysql> create tab      Le Student (id int (null,name) not null,age int (2) is not NULL);     # #在库里面创建一个表;mysql> INSERT INTO student values (1, ' X ', 23);     # #插入一条信息做测试;mysql> select * from Xyp.student; # #先去主库上去查, with data, +----+------+-----+| ID | name | Age |+----+------+-----+| 1 | x |     |+----+------+-----+1 row in Set (0.02 sec) mysql> select * from Xyp.student; # #在amoeba上通过select去查结果没东西 because the state from the library is stopped empty set (0.01 sec) mysql> start slave;     # #在从库上把状态打开; Query OK, 0 rows Affected (0.00 sec) mysql> select * from Xyp.student; # #在从amoeba上查看, the data has been synced over, +----+------+-----+| ID | name | Age |+----+------+-----+| 1 | x | |+----+------+-----+1 row in Set (0.02 sec)

Build MySQL-based read-write separation tool amoeba