MySQL read-write separation

MySQL read-write separation
Static Detach: Writes the server address directly to the program
Dynamic separation: Through the proxy server to read and write data, the proxy server to determine the read and write operations, on the primary server writing data,
Read data from the server.

1, using Mysql-proxy to achieve read and write separation
#./mysql-proxy--proxy-backend-addresses=10.0.5.150:3306--proxy-read-only-backend-addresses=10.0.5.151:3306-- Proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua--daemon--user=mysql--log-level= Warning--log-file=/var/log/mysql-proxy.log--max-open-files=2048--event-threads=4--proxy-address= 10.0.5.152:3306

--proxy-backend-addresses specifying the primary server address (write data)
--proxy-read-only-backend-addresses specified from server address (read data)
--proxy-lua-script specifying scripts for read-write separation
--daemon Specifies to run as daemon
--USER=MYSQL Specifies the user to run
--log-level Specifying logging levels
--log-file specifying the log file location
--max-open-files Specifies the maximum number of files opened by the class, limited by the operating system
--event-threads set the number of worker threads
--proxy-address setting the native address and port of the listener


Get mysql-proxy Help
#./mysql-proxy--help \ \ Basic Options Help
#./mysql-proxy--help-proxy \ \ proxy Options Help


authorizing on all servers
mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.5.151 ' identified by ' aixocm ';
mysql> flush Privileges;
(2) using amoeba for read and write separation
Installing the JDK
# Tar XF jdk-7u51-linux-x64.tar.gz-c/usr/local/
# MV Jdk1.7.0_51 Java

Edit the/etc/profile file, set the Java environment variable
# Vim/etc/profile
Java_home= "/usr/local/java"
Class_path= "/usr/local/java/jre:/usr/local/java/lib"
Export Java_home Class_path
Path= "$PATH:/usr/local/mysql/bin:/usr/local/java/bin"
# Source/etc/profile

View and adjust the default version of Java
# java-version
# alternatives--install/usr/local/java/bin/java Java/usr/local/java/bin/java 100 \ \ Register the Java version and set the priority in the system
# alternatives--display java \ \ Display Java information
# alternatives--config java \ \ Set the default version of Java
installation and configuration amoeba for read-write separation
# Unzip amoeba-mysql-3.0.5-rc-distribution.zip-d/usr/local/
# MV AMOEBA-MYSQL-3.0.5-RC Amoeba

# vim dbservers.xml \ \ Define back-end server information
<property name= "sendbuffersize" >256</property> \ \ Set Send buffer size
<property name= "receivebuffersize" >256</property> \ \ Set the receive buffer size
<property name= "port" >3306</property> \ \ Set back-end server port
<property name= "schema" >sxjy</property> \ \ Set the proxy default connected database
<property name= "User" >root</property>
<property name= "password" >aixocm</property> \ \ Set the password to connect back-end server

<dbserver name= "Master1" parent= "abstractserver" > \ \ Define back-end server
<factoryConfig>
<property name= "IpAddress" >10.0.5.150</property>
</factoryConfig>
</dbServer>

<dbserver name= "slave1" parent= "abstractserver" > \ \ Define back-end server
<factoryConfig>
<property name= "IpAddress" >10.0.5.151</property>
</factoryConfig>
</dbServer>

<dbserver name= "Master" virtual= "true" > \ \ \ define Server pool
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >
<property name= "loadbalance" >1</property> \ \ Define load Balancing algorithm
<property name= "poolnames" >master1</property> \ \ Add server, multiple separated by commas
</poolConfig>
</dbServer>

<dbserver name= "Slave" virtual= "true" > \ \ \ define Server pool
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >
<property name= "LoadBalance" >1</property>
<property name= "Poolnames" >slave1</property>
</poolConfig>
</dbServer>
# vim amoeba.xml \ \ Define Proxy information
<property name= "port" >3306</property> \ \ Set the port on which the proxy server listens
<property name= "ipAddress" >10.0.5.152</property> \ \ Set the address of the agent listener
<property name= "user" >root</property> \ \ \ Set the username used by the proxy server
<property name= "password" >aixocm</property> \ \ Set the password used by the proxy server
<property name= "Defaultpool" >master</property> \ \ Set the default server pool, generally except
Other operations, such as Select,insert,update,delete, are performed on the default server.

<property name= "Writepool" >master</property> \ \ \ \ \ \ \ \ \ \ \ Perform a write operation server pool
<property name= "Readpool" >slave</property> \ \ \ \ \ \ \ \ server pool for read operations
<property name= "Needparse" >true</property>

# vim jvm.properties \ \ Set Java memory allocation information
Jvm_options= "-server-xms256m-xmx1024m-xss512k-xx:permsize=32m-xx:maxpermsize=128m"

# vim access_list.conf \ \ Define access Control List
10.0.5.100:no
10.0.*.*:yes

# vim dbservers.xml \ \ Define back-end server information
# vim amoeba.xml \ \ Define Proxy information
# vim jvm.properties \ \ Set Java memory allocation information
Jvm_options= "-server-xms256m-xmx1024m-xss512k-xx:permsize=32m-xx:maxpermsize=128m"

# Vim Access_list.conf
10.0.5.100:no
10.0.*.*:yes

(5) Achieve Data segmentation
# Vim Rule.xml
<tablerule name= "Stu" schema= "Sxjy" defaultpools= "Master1,master2" >
<rule name= "Rule1" >
<parameters>id</parameters>
<expression><! [cdata[id% 2 = = 0]]></expression>
<defaultPools>master1</defaultPools>
<readPools>slave1</readPools>
<writePools>master1</writePools>
</rule>

<rule name= "Rule2" >
<parameters>id</parameters>
<expression><! [cdata[id% 2 = = 1]]></expression>
<defaultPools>master2</defaultPools>
<writePools>master2</writePools>
<readPools>slave2</readPools>
</rule>

</tableRule>


Vertical Split
<tablerule name= "user_info" schema= "blog" defaultpools= "Master1"/>
<tablerule name= "User_info" schema= "webchat" defaultpools= "Master2"/>

This article is from the "Kenasel" blog, make sure to keep this source http://kenasel.blog.51cto.com/10620829/1839523

MySQL read-write separation