MySQL read-write separation

The structure diagram is as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/88/5C/wKiom1fyDEeQulXRAAA8LOGwCDk547.png "title=" 123. PNG "alt=" Wkiom1fydeequlxraaa8logwcdk547.png "/>

Ideas:

1 Let Mysql-proxy service monitor 3306 ports

2 user authorization on backend database Server 1.1 and 1.2

3 to ensure that users can see the data written, the database server responsible for the read and write operations into a master-slave synchronization structure

1 , database server read-write separation configuration

1) Open Database service

Service mysqld Start

2) All database creation libraries, table structure consistent, set table record initial value is not the same (verify whether the agent implements read-write separation)

Entering IP 1.1 is consistent with the 1.2 database server creation Library, table structure, and table records.

[Email protected] ~]mysql-uroot-p

mysql> CREATE DATABASE test300; Create the same library on IP 1.1 and 1.2

Mysql> CREATE TABLE test300.a (id int); Create the same table structure as ditto

Mysql>insert into test300.a values (1); IP 1.1 Insert Record is 1

Mysql>insert into test300.a values (2); IP 1.2 Insert record is 2

3 ) to allow the proxy server to connect to itself on two database servers, respectively

On IP 1.1 and 1.2 do the same authorization to the same user, @ address is match all

mysql>grant All on * * to [email protected] '% ' identifiedby ' 123 ';   

4) test whether authorized users can access 2 database servers on the backend from the MySQL proxy server

Log on to the proxy server 192.168.1.254

[Email protected] ~]mysql-h192.168.1.1-uproxyuser-p123

[Email protected] ~]mysql-h192.168.1.2-uproxyuser-p123

2 , install Mysql-proxy, and configure

1) mysql-proxy (Agent software)

The client sends the request to the agent, which is distinguished by the read and write operations by the agent, and the reads are sent to the primary database server from the database server.

Mysql–hmysql-proxy ip–u User name –p password//client Direct Access IP 1.254

2) Stop the proxy server's MySQL service, release 3306 ports, and give mysql-proxy agent software

#service MySQL stop #chkconfig--level MySQL off

Package Required for installation

**linux6 in Lua,lua-devel already included in Mysql-proxy

lua-5.0.2-1.el5.rf.i386.rpm

lua-devel-5.0.2-1.el5.rf.i386.rpm

Mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

Mysql-proxy Default Listener 3306 Port

3) Install the software that provides agent service Mysql-proxy

Mysql-proxy The software is written in Lua, so the LUA package must be installed on the system

[Email protected] ~]# Rpm-qa | grep LUA

Lua-devel-5.0.2-1.el5.rf

Lua-5.0.2-1.el5.rf

Unzip the Mysql-proxy package to the specified location

[Email protected]~]# tar-zxvf mysql-proxy-0.8.3 ... 64bit.tar.gz -c/usr/local/

[[Email protected]~]# cd/usr/local

[Email protected]]# mv Mysql-proxy ... mysql-proxy//Renaming a folder for easy access

/usr/local/mysql-proxy the folder under the

Bin Start Service Script

Include header file

Share feature Scripts

LIB Library file

Libexec executable Commands

#ls mysql-proxy/share/doc/mysql-proxy/

Rw-splitting.lua//script to implement read and write separation function

#ll/usr/local/proxy/share/doc/mysql-proxy/rw-splitting.lua //view permission limit

#chmod +x/usr/local/proxy/share/doc/mysql-proxy/rw-splitting.lua//Add Execute Permissions

4 ) Start the agent service

Format:./mysql-proxy–p Proxy IP: Specify Port-r Read Server IP: Specify Port-B write Server IP: Port-S (drop with execute script). /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

Description:& indicates background run

[Email protected]]#/usr/local/mysql-proxy/bin/mysql-proxy \

-P 192.168.1.254:3306-r 192.168.1.2:3306-b 192.168.1.1:3306 \

-s/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

2014-02-18 14:26:51: (critical) Plugin proxy 0.8.3started//out this sentence stands for success

Stop: can only kill process #pkill-9 Mysql-proxy

Kill process number #kill 9%1

5 ) to see if the MySQL Proxy service is in a running state

[[Email protected]~]# jobs//See if the background is running

[1]+ running/usr/local/mysql-proxy/bin/mysql-proxy-p 192.168.1.254:3306-r 192.168.1.23:3306-b 192.168. 1.100:3306-s/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

6 ) test the Read and write separation function of MySQL proxy server

**proxy the server also needs to create a library table that is consistent with the 1.1, 1.2 database server libraries, and the table structure.

The client connects the proxy server with an authorized account, and the proxy server automatically connects to the database server test read and write separation

# mysql–h192.168.1.254-uproxyuser-p123

Mysql>insert into test300.a vales (100);//Because there are no concurrency numbers, you can see the 100 record on both the client and 1.1.

To open 5 or 6 terminals on the client computer, proceed as follows:

# mysql–h192.168.1.3-uproxyuser-p123

Mysql> select * from test300.a;

When the 5th or 6th terminal, because the concurrency number is reached, then execute the select * from TEST300.A; statement, only see: 1.2 of the record, and then insert the record, the record will be written to the responsible server 1.1, the client will not see this record.

7) If you want to see the client immediately, you also need to read the server to make a master-slave server

Write Server 192.168.1.1:→ master server

Read server 192.168.1.2:→ from server

To authorize the slave server on the primary server:

#mysql-uroot-p123456

Mysql>grant all on * * to [E- Mail protected] identified by ' 123 ';

Log on from the server:

#mysql-h192.168.1.1-uroot-p123

Mysql> select * from test300.a;

At this point, a record is inserted on the client, and you can see it immediately on 1.2.

8) Modify master configuration file-concurrency number

** The concurrency number cannot be set to 0**

#vim /usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua

Min_idle_connections = 4

Max_idle_connections = 8

Question:

1 concurrent connection of MySQL proxy server reaches (4 or 4+1) (distinguishes user's read and write operation)

2 How the MySQL proxy server distributes user read and write requests (master) when the number of concurrent connections for a user does not exceed the specified minimum concurrent connection threshold

3 If the user's number of concurrent connections exceeds the minimum concurrent connection threshold, can the user's read-write request be distributed according to the settings specified when the MySQL agent service is started (CAN)

4 when the number of concurrent connections of a user is greater than 4 o'clock, the user's read and write operations are differentiated, and when the number of concurrent connections of a user recovers less than the minimum number of concurrent connections, how the MySQL proxy service distributes the user's read and write requests ( once differentiated, will remain differentiated)

This article is from the "Dave-Technology blog" blog, please be sure to keep this source http://davewang.blog.51cto.com/6974997/1858580

MySQL read-write separation