Using amoeba to implement MySQL read-write separation mechanism

Amoeba's Practical Guide http://docs.hexnova.com/amoeba/how to implement MySQL read-write separation:                                                                                                            Typically there are two ways: 1, the application layer implementation 2, middleware layer implementation of application layer implementation is the implementation of the application layer in the applications and connectors to achieve read and write separation. For example: Com.mysql.jdbc.replicationdrivermysqlnd_ms advantages: The implementation of read-write separation within the program, installation can be used to reduce the difficulty of deployment. The access pressure is below a certain level, the performance is very good. Cons: Once the architecture is adjusted, the code needs to be more this, not flexible enough. It is difficult to implement advanced applications, such as automatic sub-Libraries, and the sub-table cannot be applied to larger scenarios. Transactional middleware layer implementations are not supported at this time: middleware layer implementation refers to the external middleware program to achieve read and write separation mysql-proxyamoebatddl (TAObao) CObar (Alibaba) Atlas (qihoo360) Advantages: More flexible architecture design, Read Library load balancer can implement some advanced control on the program, failover, flow control relies on some technology to achieve MySQL performance improvement, If the connection pool is less intrusive to the business code, there is a need for some development strength and the support of the OPS team Wkiom1zuezigsaqvaaghwb6qewy870.png if you want to support transactions, want to read and write, and want to load balance and high availability, Please go to understand Lou Fangxin Teacher's oneproxy what is amoeba? Amoeba 2008 released the first open source framework amoeba for MySQL, dedicated to MySQL distributed database proxy layer It is mainly in the application layer when accessing MySQL, the SQL routing function, focus on the Distributed Database Agent layer, located in the client, DBServer, the client transparent with load balancing, high availability, SQL filtering, read and write separation, can be routed to the target database, can concurrently request multiple database merge results through amoeba you can complete the data source of high availability, load balancing, data switching function, Currently amoeba does not support transactions the following is the architecture diagram for implementing this scenario: Wkiom1yt14_i8sslaaejlswgjgu617.jpgmysqlMaster-slave replication configuration because of the read and write separation, so one is responsible for the MySQL write operation, the other is responsible for the MySQL read operation, so we use MySQL's master-slave copy is suitable. For this configuration, please visit: http://douya.blog.51cto.com/6173221/1596113 Amoeba for read-write separation: The 2,java Environment Configuration Amoeba Framework is based on Java SE1.5, and it is recommended to use the Java SE 1.5 version. Currently, amoeba is proven to work correctly in JavaTM se 1.5 and Java SE 1.6 (which may include other unverified versions). Java SE 1.6:http://www.oracle.com/technetwork/java/javase/downloads/ jdk-6u32-downloads-1594644.html I download jdk-6u32-linux-i586-rpm.bin here. After the download is complete, the SH jdk-6u32-linux-i586-rpm.bin begins installation and will be installed into the/USR/JAVA/JDK1.6.0_32 directory. My own choice of JDK is based on the unified environment used by the 1.73,amoeba installation amoeba:http://sourceforge.net/projects/amoeba/The following is the installation steps: cd/rootmkdir/usr/local/ amoebawgethttp://softlayer.dl.sourceforge.net/project/amoeba/amoeba%20for%20mysql/2.x/ Amoeba-mysql-binary-2.1.0-rc5.tar.gztar xzf amoeba-mysql-binary-2.1.0-rc5.tar.gz-c/usr/local/amoeba Configuring User Environment variables Vim/ Etc/profile is set to: Export Java_home=/root/jdk1.7.0_79export classpath= $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar : $JAVA _home/jre/lib/rt.jarexport path= $PATH: $JAVA _home/biNexport java_homeexport Path takes effect immediately: Source/etc/profile4,amoeba for MySQL configuration configuration amoeba for MySQL the read-write separation mainly involves two files: 1,/usr/local/ Amoeba/conf/amoeba.xml This file defines the relevant configuration for the Amoeba agent. 2./usr/local/amoeba/conf/dbservers.xml This file defines how the amoeba proxy database is connected, such as the most basic: Host IP, port, user name and password used by amoeba, and so on. Amoeba.xml Configuration Amoeba Local configuration or external configuration: <!--service class must implements Com.meidusa.amoeba.service.Service-< Service Name= "Amoeba for Mysql" class= "Com.meidusa.amoeba.net.ServerableConnectionManager" ><!--Port-- <property name= "Port" >3306</property>-Change to 3306 better, develop direct and practical default 3306 port <!--bind IpAddress-->< Property Name= "IpAddress" >192.168.0.48</property> external provider Access IP address, that is, Amoeba machine external access IP address <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>amoeba External Connection Verification configuration: <property name= "Authenticator" > <bean class= " Com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator "> <property name=" User ">amoeba</property  > * * * * <property name= "password" >Centos</property> Password for external access <property name= "filter" > <bean class= "Com.meidusa.amoeba.server.IPAccessController" > &lt ;p roperty name= "ipfile" >${amoeba.home}/conf/access_list.conf</property> </bean> </property> & Lt;/bean> </property> This defines the user and password used to authenticate when connecting to amoeba. Amoeba.xml read-Write separation configuration: <property name= "Defaultpool" >master</property><property name= "WritePool" > Master</property><property name= "Readpool" >virtualSlave</property> Node-related defaultpool configured in Virtualslave and Dbserver.xml: The default database node is configured, and some statements except Select\update\insert\delete are executed in Defaultpool. Writepool: The database Write library is configured, usually with master, as it is configured as a previously defined master database. Readpool : A database Read library is configured, usually with a database pool consisting of slave or slave, as in this case, the Virtualslave database pool before it is configured. (1) Dbservers.xml file configuration: Local configuration or external access configuration *************** indicates the need to modify part <dbserver name= "Abstractserver" abstractive= "true" &G T <factoryconfig class= "Com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory" > <property name= "manag Er ">${defaultManager}</property> <property name=" sendbuffersize ">64</property> &lt ;p roperty name= "receivebuffersize" >128</property> <!--MySQL Port-to-<property name = "Port" >3306</property> <!--mysql schema--<property name= "schema" >roudy&lt ;/property> must be an existing database <!--mysql user--<property name= "user" >amoeba</prope Rty> ******** <!--mysql password--<property name= "password" &GT;CENTOS&LT;/PROPERTY&G T Default is commented out, need to open </factoryConfig> <poolconfig class= "com.Meidusa.amoeba.net.poolable.PoolableObjectPool "> <property name=" maxactive ">500</property>            <property name= "Maxidle" >500</property> <property name= "Minidle" >10</property> <property name= "Minevictableidletimemillis" >600000</property> <property name= "Timebetwe            Enevictionrunsmillis ">600000</property> <property name=" Testonborrow ">true</property> <property name= "Testwhileidle" >true</property> </poolConfig> </dbServer> This section defines the port, database name, MySQL user and password to log in to the MySQL server. Next Configure the master-slave definition: *************** indicates the need to modify part <dbserver name= "master" parent= "Abstractserver" > <factoryConfig> &L t;! --MySQL IP--<property name= "ipAddress" >192.168.0.1</property> * * Master-ip </factoryconfig&     Gt </dbserver><dbserver name= "Slave1" parent= "Abstractserver" > <factoryConfig>    <!--mysql IP--<property name= "ipAddress" >192.168.0.2</property> ****slave1 IP &l t;/factoryconfig> </dbserver><dbserver name= "Slave2" parent= "Abstractserver" > &LT;FACTORYCONFIG&G     T <!--mysql IP--<property name= "ipAddress" >192.168.0.3</property> ***slave2 IP </factoryc onfig> </dbServer> <dbserver name= "Virtualslave" virtual= "true" > * * * Polling policy <poolconfig CL ass= "Com.meidusa.amoeba.server.MultipleServerPool" > <!--Load balancing Strategy:1=roundrobin, 2=weight BASED, 3=ha--> <property name= "LoadBalance" >1</property> <!--separated by Commas,such As:serv     Er1,server2,server1--<property name= "Poolnames" >Slave1,Slave2</property> </poolConfig> </dbServer> This section defines the primary server, from the server, and from the server connection pool. Only the database addresses are defined here, and their users and passwords are the settings in the abstractserver above. Note that the user who connects to the real MySQL server must have remote connection permissions, which is installed first, and the authorized user master server MYsql> Grant Create,select,insert,update,delete on * * to [email protected] ' 192.168.0.% ' identified by ' Centos '; mysql> flush privileges; Now that MySQL is configured for master-slave synchronization, you need to stop the permissions first. Here the amoeba, is Amoeba.xml, Dbserver.xml configured in the amoeba user from the server stop slave; Grant SELECT On * * to [email protected] ' 192.168.0.% ' identified by ' Centos '; Amoeba Start command: [[email protected] amobea]# bin/amoeba startthe stack size specified is too small, specify at least 228k Error:could not create the Java Virtual machine.error:a fatal exception have occurred. Program will exit. This command starts at the foreground, outputs information at startup, checks for no error messages, interrupts, and runs in the background: Start error, modify startup script [[email protected] amobea]# vim bin/ Amoeba change to default_opts= "-server-xms256m-xmx256m-xss256k" background start Amoeba/usr/local/amoeba/bin/amoeba & Verify that the boot is successful (port 3306): [[email protected] amoeba]# PS aux | grep amoebaroot 24580 0.2 19.2 408912 49264 pts/1 Sl 12:52 0:11/USR/JAVA/JDK1.6/BIN/JAVA-SERVER-XMS256M-XM X256m-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[[email protected] amoeba]# NETSTAT-LNP | grep java Final Test: Link Test login on any client amoeba[[email protected] ~]# mysql-u amoeba-p-h192.168.2.48enter password:welcome to T  He MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 1982632907Server version:5.1.45-mysql-amoeba-proxy-2.1.0-rc5 mysql Community Server (GP L) Copyright (c) and/or, Oracle, its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | Amoeba_test | | MySQL | | Performance_schema | | Repl | | Roudy | | Test |+--------------------+7 rows in Set (0.02 sec) mysql> above indicates that the amoeba configuration is completely correct. Test read and write is correct: at this time the library is not turned on the replication function, (stop slave) on the master server operation, add a new data insert into Luodi values (' 1 ', ' luodi ', ' master1 '); Immediately after the master query mysql> select * FROM luodi;+------+-------+---------+| ID | name |    Address |+------+-------+---------+| 1 | Luodi |    Master | | 1 | Luodi | Master1 | operation on the Salve server, add a new data insert into Luodi values (' 1 ', ' luodi ', ' slave1 '), followed by Salve query:mysql> select * FROM luodi;+ ------+-------+---------+| ID | name |    Address |+------+-------+---------+| 1 | Luodi |    Slave | | 1 | Luodi | slave1 | Now in Ameoba server query:mysql> SELECT * FROM luodi;+------+-------+---------+| ID | name |    Address |+------+-------+---------+| 1 | Luodi |    Slave | | 1 | Luodi | slave1 | Without the data from the master server, insert a piece of data on the Amoeba server, verify insert INTO Luodi values (' 1 ', ' luodi ', ' slave2 ') again, and log in to master, Salve View master:mysql> SELECT * FROM luodi;+------+-------+---------+| ID | name |    Address |+------+-------+---------+| 1 | Luodi |    Master | | 1 | Luodi |    Master1 | | 1 | Luodi | Master2salve: At this point slave still does not open copy mysql> select * from luodi;+------+-------+---------+| ID | name |    Address |+------+-------+---------+| 1 | Luodi |    Slave | | 1 | Luodi | slave1 | So far amoeba based on-m-s structure of static and dynamic separation is completely successful, and then start slave can,

Using amoeba to implement the MySQL read-write separation mechanism