MySQL read-write separation

650) this.width=650; "src=" Http://note.youdao.com/yws/res/1967/C8CCB6CF224C4F19907222B52E4D0B6E "alt=" Screenshot-2.png "/>
MySQL Advanced cluster-read-write separation amoeba
The amoeba is equivalent to a SQL-requested router that provides mechanisms for load balancing, read-write separation, and high availability, rather than fully implementing them. Users need to use a combination of MySQL replication and other mechanisms to achieve the functions such as replica synchronization. Amoeba also uses pluggable mechanisms for the underlying database connection management and routing implementations, and third parties can develop more advanced policy classes to replace the authors ' implementations. This procedure is generally more in line with the idea of the kiss principle.

2Advantages

Amoeba mainly addresses the following issues:

a). Complex data source consolidation after data segmentation

b). Provide data segmentation rules and reduce the impact of data segmentation rules on the database

c). Reduce database and client connections

d). Read and write separate routes

3Insufficient

A), currently does not support transaction B), temporarily does not support stored procedures (recent support)

c), not suitable for amoeba data from the scene or query for large data queries is not appropriate (such as a request to return more than 10w or more data)

D), temporarily does not support the sub-database sub-table, amoeba currently only do sub-DB instances, each node is divided to keep the library table structure consistent:

Read and Write Data synchronization
Master-->slave

I. Introduction to the Environment
master-ip:10.0.0.201
slave-ip:10.0.0.202
amobea-ip:10.0.0.203

Ii. Installation of JDK
1) # Mkdir/amoeba
# TAR-XVF Jdk-7u40-linux-x64.tar.gz-c/amoeba/
# Vim/etc/profile
Java_home=/amoeba/jdk1.7.0_40
Export Java_home
Path= $JAVA _home/bin: $PATH
Export PATH
Classpath=.: $JAVA _home/lib/tools.jar: $JAVA _home/lib/dt.jar: $CLASSPATH
Export CLASSPATH

2)# Source/etc/profile
# java-version
Java Version "1.7.0_40"
Java (TM) SE Runtime Environment (build 1.7.0_40-b43)
Java HotSpot (TM) 64-bit Server VM (build 24.0-b56, Mixed mode)

Third, installation amoeba
# Unzip amoeba-mysql-1.3.1-beta.zip-d/usr/local/amoeba/
# Chmod-r +x/usr/local/amoeba/bin/


Iv. inhost and slaveTo authorize on
>grant all privileges on * * to ' amobea ' @ '% ' identified by ' amoeba123 ';
>flush privileges;


V. Configuring the Amoeba File
# cd/usr/local/amoeba/conf
fix config file garbled: iconv-f gbk-t UTF8 Amoeba.xml-o amoeba.xml.bak
MV Amoeba.xml.bak Amoeba.xml

# Vim Amoeba.xml
Change the first areamodify port and IP in section 8,12,30,34Yes
#========================================================
<!--proxy server bound Port--
Change Port:<property name= "Port" >9006</property>

<!--proxy server bound IP--

To undo the comment Change IP:<property name= "IpAddress" >10.0.0.203</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" >123456</property>

Change the second zone, "Configuring Master-side Login Information"
dbserverlist the username and password of the master port and IP and Amoeba in section 74,78,83Yes
#========================================================
<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" >10.0.0.201</property>
<property name= "schema" >test</property>

<!--user name to log in to MySQL--
<property name= "User" >Amoeba</property>

<!--password to log in to MySQL--


<property name= "Password" >amoeba123</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>

Change the third zone, "Configuring slave-side Login information"
Modify the IP of line 110th slave after copying 35 rows from line 64
#========================================================

<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" >10.0.0.202</property>
<property name= "schema" >test</property>

<!--user name to log in to MySQL--
<property name= "User" >Amoeba</property>

<!--password to log in to MySQL--


<property name= "Password" >amoeba123</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>

Fourth area changed "Configure Master and slave load balancer pool"
#=======================================================
<dbservername= "Master"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</property>
</poolConfig>
</dbServer>
Copy the above 9 lines.：

<dbservername= "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>

<!--Here you can make a master multi-change
</poolConfig>
</dbServer>

Changes to the fifth region, "Configuring Master and slave Read and write separation"
#=======================================================
<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>
<!--configuring master and slave read-write separation begin-->
<property name= "Defaultpool" >Master</property>
To undo a Comment:<property name= "Writepool" >Master</property>
<property name= "Readpool" >slave</property>
<!--configuring master and slave read-write separation end-->
<property name= "Needparse" >true</property>
</queryRouter>


Vi. Modifying the Amoeba startup script
# Vim/usr/local/amoeba/bin/amoeba
#DEFAULT_OPTS = "-server-xms256m-xmx256m-xss128k" to comment out this line, adding a downward
Default_opts= "-server-xms256m-xmx256m-xss256k" adds this line

Vii. Start Amoeba Script

Source/etc/profile
Cd/usr/local/amoeba/bin

# nohup Bash-x Amoeba &
# is the cat nohup.out log successful?
# Ps-ef | grep amoeba to see if the process started successfully
Root 1896 1637 1 03:28 pts/3 00:00:06/amoeba/jdk1.7.0_40/bin/java-server-xms256m-xmx256m-xss256k-damoeba.h ome=/usr/local/amoeba-dclassworlds.conf=/usr/local/amoeba/bin/amoeba.classworlds-classpath/usr/local/amoeba/ Lib/classworlds-1.0.jar Org.codehaus.classworlds.Launcher


# Mysql-uroot-p123456-h 10.0.0.203-p 9006

Re-experiment to kill-9 kill process Nohup bash-x Amoeba &