MySQL-Proxy implements MySQL read/write splitting to improve concurrent Load

MySQL-Proxy implements MySQL read/write splitting to improve concurrent Load

Working topology:
110018378. jpg
A powerful function of MySQL Proxy is to implement "read/write splitting". The basic principle is to let the primary database handle write transactions and let the slave database process SELECT queries.
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, you can test 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

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/

Mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz 12 tar zxvf
Music 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-proxy
Mkdir lua # create a script storage directory
Mkdir logs # 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 management script
Vi/etc/mysql-proxy.cnf # create a configuration file
[Mysql-proxy]
User = root # Run mysql-proxy user
Admin-username = proxy # users in Master/Slave mysql
Admin-password = 123.com # User password
Proxy-address = 192.168.0.204: 4000 # mysql-proxy runs the ip address and port without ports. The default value is 4040.
Proxy-read-only-backend-addresses = 192.168.0.203 # specify the backend to read data from slave
Proxy-backend-addresses = 192.168.0.202 # specify the backend master to write data.
Proxy-lua-script =/usr/local/mysql-proxy/lua/rw-splitting.lua # specify the location of the read/write splitting configuration file
Admin-lua-script =/usr/local/mysql-proxy/lua/admin-sql.lua # specify management scripts
Log-file =/usr/local/mysql-proxy/logs/mysql-proxy.log # log location
Log-level = info # defines the log level, from high to low (error | warning | info | message | debug)
Daemon = true # Run as a daemon
Keepalive = true # When mysql-proxy crashes, try to restart
Save and exit!
Chmod 660/etc/mysql-porxy.cnf

3. Modify the read/write splitting 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, # read/write splitting starts only when the default number of connections exceeds 4, and is changed to 1
Max_idle_connections = 1, # default value: 8, changed to 1
Is_debug = false
}
End

4. Start mysql-proxy

/Usr/local/mysql-proxy/bin/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf
Netstat-tupln | grep 4000 # started
Tcp 0 0 192.168.0.204: 4000 0.0.0.0: * LISTEN 1264/mysql-proxy
Disable mysql-proxy: 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 '192. 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 turn off the slave first to see if it can be written. I will not test it here, test the read data below!

Mysql & gt; create table user (number INT (10), name VARCHAR (255 ));
Mysql> insert into test values (01, 'hangsan ');
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 changed
Mysql> 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.com
Mysql> 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 changed
Mysql> 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!

MySQL Proxy read/write splitting practice

Install the latest MySQL Proxy in CentOS 5.2

Install MySQL Proxy in RHEL5.5

Mysql proxy and mysql-mmm achieve high availability of read/write splitting

MySQL Proxy details: click here
MySQL Proxy: click here

This article permanently updates the link address: