MySQL read-write separation--mysql-proxy and amoeba

MySQL read-write separation--mysql-proxy and amoeba

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.

Benefit: Read or write on only one server to reduce server workload, improve efficiency and server performance

When writing (manipulating statements on the proxy server, writing to master), slave synchronizes data with master-slave replication

Three servers: master server 10.0.10.1, 10.0.10.2 from server, proxy server 10.0.10.3

Two approaches: Implement using Mysql-proxy, implement with Amoeba

1, using Mysql-proxy to achieve read and write separation

#./mysql-proxy--proxy-backend-addresses=10.0.10.1:3306--proxy-read-only-backend-addresses=10.0.10.2: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.10.3: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 that can be opened and is 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

The proxy server is authorized on the master-slave server, so that the proxy server can use this user on the master-slave server to connect the master and slave.

Mysql> Grant All on * * to ' repuser ' @ ' 10.0.10.3 ' identified by ' 123123 ';

mysql> flush Privileges;

2, using amoeba to achieve read and write separation

(1) Installing the JDK

# Tar XF jdk-7u51-linux-x64.tar.gz-c/usr/local/

# MV Jdk1.7.0_51 Java

(2) Edit the/etc/profile file, set the Java environment variable

# Vim/etc/profile

Before the export PATH USER LOGNAME MAIL HOSTNAME histsize Histcontrol

Java_home= "/usr/local/java"

Class_path= "/usr/local/java/jre:/usr/local/java/lib"

Export Java_home Class_path

Path= "$PATH:/usr/local/amoeba/bin:/usr/local/java/bin"

# Source/etc/profile

(3) View and adjust the default version of Java

# java-version

[email protected] local]# which Java

/usr/bin/java \ \ itself has a Java path to fill/usr/bin/java

# 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

(4) Installation and configuration amoeba for read and 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 connected to the master-slave server

<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" >zz</property> \ \ Set the proxy default connected database

<property name= "User" >root</property>

<property name= "password" >123123</property> \ \ Set the password to connect back-end server

===

<dbserver name= "Master1" parent= "abstractserver" > \ \ Define back-end server (master server)

<factoryConfig>

<property name= "IpAddress" >10.0.10.1</property>

</factoryConfig>

</dbServer>

<dbserver name= "slave1" parent= "abstractserver" > \ \ Define back-end server (from server)

<factoryConfig>

<property name= "IpAddress" >10.0.10.2</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.10.3</property> \ \ Set the address of the agent listener

<property name= "user" >root</property> \ \ \ Set the username used by the proxy server

<property name= "password" >123123</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.10.100:no

10.0.*.*:yes

(5) Start amoeba

#cd/usr/local/amoeba/bin

#./launcher &

Close Amoeba

#./shutdowm

(6) Achieve data segmentation

Split horizontally: Store key fields on a specific algorithm to different servers

Vertical segmentation: Save data to different servers by business difference

Split horizontally

# Vim Rule.xml

<tablerule name= "Stu" schema= "zz" defaultpools= "Master1,master2" >

<rule name= "Rule1" >

<parameters>id</parameters>

<expression><! [cdata[id% 2 = = 0]]></expression>

<defaultPools>master1</defaultPools>

<readPools>master1</readPools>

<writePools>master1</writePools>

</rule>

<rule name= "Rule2" >

<parameters>id</parameters>

<expression><! [cdata[id% 2 = = 1]]></expression>

<defaultPools>master2</defaultPools>

<writePools>master2</writePools>

<readPools>master2</readPools>

</rule>

</tableRule>

====

Operating on the proxy server

[Email protected] ~]# mysql-u root-h 10.0.10.3-paixocm-e "insert into Zz.stu (id,name) VALUES (1, ' Zhang San ')"

[Email protected] ~]# mysql-u root-h 10.0.10.3-paixocm-e "insert into Zz.stu (Id,name) VALUES (2, ' John Doe ')"

[Email protected] ~]# mysql-u root-h 10.0.10.3-paixocm-e "insert into Zz.stu (Id,name) VALUES (3, ' Harry ')"

[Email protected] ~]# mysql-u root-h 10.0.10.3-paixocm-e "insert into Zz.stu (id,name) VALUES (4, ' Zhao Liu ')"

Mysql> select * from Stu;

+----+--------+

| ID | name |

+----+--------+

| 2 | John Doe |

| 4 | Zhao Liu |

+----+--------+

Mysql> select * from Stu;

+----+--------+

| ID | name |

+----+--------+

| 1 | Zhang San |

| 3 | Harry |

+----+--------+

=======

Vertical split

<tablerule name= "User_info" schema= "weblog" readpools= "slave1" writepools= "Master1"/>

<tablerule name= "User_info" schema= "webchat" readpools= "Slave2" writepools= "Master2"/>

This article is from "Rookie in Growth" blog, please be sure to keep this source http://shuaiz.blog.51cto.com/10626377/1697535

MySQL read-write separation--mysql-proxy and amoeba