Mysql read/write splitting improves concurrent Loads

Working topology:

650) this. length = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/201Z26336-0.jpg "title =" mysql-master-salve-proxy.jpg "width =" 400 "height =" 354 "border =" 0 "hspace =" 0 "vspace =" 0 "style =" width: 400px; height: 354px; "alt =" 110018378.jpg"/>

A powerful function of MySQL Proxy is to implement read/write splitting. The basic principle is to allow the primary database to process transactional queries and allow the slave database to process SELECT queries. Database Replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

Amoeba for MySQL is an excellent middleware software that can also implement read/write splitting, load balancing, and other functions, and has higher stability than MySQL Proxy. If you are interested, feel it.

Environment Description:

Operating System: CentOS6.3 _ x64

Master server: 192.168.0.202

Slave server slave: 192.168.0.203

Scheduling server MySQL-Proxy: 192.168.0.204

I. mysql master-slave Replication

This is omitted, please refer to the http://going.blog.51cto.com/7876557/1290431

Ii. mysql-proxy for read/write splitting

1. Install mysql-proxy

The lua script is used to implement read/write splitting. Now mysql-proxy has been integrated and no installation is required.

Download: http://dev.mysql.com/downloads/mysql-proxy/

tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gzmv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

2. Configure mysql-proxy and create the master configuration file

Cd/usr/local/mysql-proxymkdir lua # create a script to store the directory mkdir log # create a log directory cp share/doc/mysql-proxy/rw-splitting.lua. /lua # copy the read/write splitting configuration file cp share/doc/mysql-proxy/admin-sql.lua. /lua # copy the management script vi/etc/mysql-proxy.cnf [mysql-proxy] user = root # Run mysql-proxy user admin-username = proxy # Master-slave mysql user admin-password = 123.com # user's password proxy-address = 192.168.0.204: 4000 # mysql-proxy run ip and port proxy-read-only-backend-address = 192.168.0.203 # specify the backend Slave read data proxy-backend-address = 192.168.0.202 # specify back-end master write data proxy-lua-script =/usr/local/mysql-proxy/lua/rw-splitting.lua # specify read/write splitting Configuration file Location admin-lua-script =/usr/local/mysql-proxy/lua/admin-sql.lua # specify management script log-file =/usr/local/mysql-proxy/log/mysql-proxy.log # log location log-level = warning # define the log level, from high to low, there are (error | warning | info | message | debug) daemon = true # Run keepalive = true in daemon mode # mysql-proxy closes Automatic Restart due to exceptions, in highly concurrent or error connections, mysq L-proxy is easier to disable. It is better to enable this parameter. You can also consider installing a mysql-proxy on each server to provide redundancy. Save and exit! Chmod 660/etc/mysql-porxy.cnf

3. Modify the read/write splitting configuration file

If not proxy. global. config. rwsplit then proxy. global. config. rwsplit = {min_idle_connections = 1, # default 4, changed to 1 max_idle_connections = 1, # default 8, changed to 1 is_debug = false} end

4. Start mysql-proxy

/Usr/local/mysql-proxy/bin/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnfnetstat-tupln | grep 4000 # tcp 0 192.168.0.204: 4000 0.0.0.0: * LISTEN 1264/mysql-proxy close mysql-proxy use: killall-9 mysql-proxy

5. Test read/write splitting

1>. Create a proxy user on the master server for mysql-proxy use. The slave server also synchronizes this operation.

mysql> grant all on *.* to 'proxy'@'192.168.0.204' identified by '123.com';

2>. Use a client to connect to mysql-proxy

mysql -u proxy -h 192.168.0.204 -P 4000 -p123.com

Create a database and a table. At this time, the data is only written to the primary mysql, and then synchronized from the slave. You can first shut down the slave and check whether it can be written. Here we will not test it, next we will test the read data!

mysql> create table user;mysql> create table user (number INT(10),name VARCHAR(255));mysql> insert into test values(01,'zhangsan');mysql> insert into user values(02,'lisi');

3> log on to Master/Slave mysq to view the newly written data as follows,

mysql> use test;Database changedmysql> select * from user;+--------+----------+| number | name |+--------+----------+| 1 | zhangsan || 2 | lisi |+--------+----------+

4>. log on to mysql-proxy and query the data.

mysql -u proxy -h 192.168.0.204 -P 4000 -p123.commysql> use test;mysql> select * from user;+--------+----------+| number | name |+--------+----------+| 1 | zhangsan || 2 | lisi |+--------+----------+

5>. log on to the slave server to close the mysql synchronization process. Then, log on to mysql-proxy again, and no data can be found.

slave stop；

6>. log on to mysql-proxy to query the data. The following figure shows that the table cannot be queried.

mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| user |+----------------+mysql> select * from user;ERROR 1146 (42S02): Table 'test.user' doesn't exist

Configuration successful! Truly achieves read/write splitting!

This article is from the "Linux popular technology" blog, please be sure to keep this source http://going.blog.51cto.com/7876557/1305083