MySQL Proxy for MySQL read-write separation improves concurrency load

MySQL Proxy has a powerful function of "read and write Separation", the basic principle is to let the primary database processing the write aspect transaction, let the library process select query.

• Operating system: centos6.5_x64
• Primary server master:192.168.1.102
• From server slave:192.168.1.103
• Dispatch Server mysql-proxy:192.168.1.102

First, MySQL master-slave replication

Please refer to MySQL master-slave synchronization

Second, mysql-proxy to achieve read and write separation

1, installation Mysql-proxy

The implementation of read-write separation is implemented with LUA scripting, now mysql-proxy inside is integrated, no need to install

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

Tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz

MV Mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit/usr/local/mysql-proxy

2. Configure Mysql-proxy to create the master configuration file

Cd/usr/local/mysql-proxy

mkdir Lua #创建脚本存放目录

mkdir logs #创建日志目录

CP Share/doc/mysql-proxy/rw-splitting.lua./lua #复制读写分离配置文件

CP Share/doc/mysql-proxy/admin-sql.lua./lua #复制管理脚本

VI/ETC/MYSQL-PROXY.CNF #创建配置文件

[Mysql-proxy]

User=root #运行mysql-proxy User

Admin-username=proxy #主从mysql共有的用户

Admin-password=123.com #用户的密码

proxy-address=192.168.1.102:4000 #mysql-proxy Run IP and port, no port, default 4040

proxy-read-only-backend-addresses=192.168.1.103 #指定后端从slave读取数据

proxy-backend-addresses=192.168.1.102 #指定后端主master写入数据

Proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置

Admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本

Log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置

Log-level=info #定义log日志级别, from high to Low (Error|warning|info|message|debug)

Daemon=true #以守护进程方式运行

Keepalive=true #mysql-proxy crashes, try restarting save to exit!

chmod 660/etc/mysql-proxy.cnf

3, modify read-Write separation configuration file

Vi/usr/local/mysql-proxy/lua/rw-splitting.lua

If not Proxy.global.config.rwsplit then

Proxy.global.config.rwsplit = {

Min_idle_connections = 1, #默认超过4个连接数时 before starting read-write separation, changed to 1

Max_idle_connections = 1, #默认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 0 192.168.1.102:4000 0.0.0.0:* LISTEN 1264/mysql-proxy off mysql-proxy use: killall-9 mysql-proxy
5. Test read/write separation

1>. Create a proxy user in the master server for Mysql-proxy use, and the operation will be synchronized from the server

Mysql> Grant All on * * to ' proxies ' @ ' 192.168.1.102 ' identified by ' 123.com ';

2> Using Client connection Mysql-proxy

Mysql-u proxy-h 192.168.1.102-p 4000-p123.com

Create a database and table, then the data is only written to the master MySQL, and then sync from slave, you can first turn off the slave, see if you can write, here I do not test, the following test read the data!

Mysql> CREATE TABLE User (number INT (ten), name VARCHAR (255));mysql> INSERT INTO test values ("Zhangsan"); MySQL > INSERT INTO user values ("Lisi");

3>. Login master-Slave mysq View the newly written data as follows,

mysql> Use Test;database changedmysql> select * from user;+--------+----------+| number | Name |+--------+----------+| 1 | Zhangsan | | 2 | Lisi |+--------+----------+

4>: Then log in to Mysql-proxy, query the data, see the normal query

Mysql-u proxy-h 192.168.1.102-p 4000-p123.commysql> use test;mysql> select * from user;+--------+----------+| number | Name |+--------+----------+| 1 | Zhangsan | | 2 | Lisi |+--------+----------+

5>. Login from the server to close the MySQL synchronization process, then login Mysql-proxy will certainly not query data

Stop slave;

6>. Login mysql-proxy query data, the following appears, can see the table, the query does not have data

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 is successful! Really realize the effect of read and write separation!

Original address: http://www.open-open.com/lib/view/open1447823312181.html

MySQL Proxy for MySQL read-write separation improves concurrency load