Mysql proxy读写分离

 

准备工作:

两个mysql 5.6 数据库(版本其实无所谓)

mysql_installer_community_V5.6.21.1_setup.1418020972

一个proxy

mysql-proxy-0.8.5-windows-x86-32bit

当前的最新版:

http://download.csdn.net/detail/dingsai88/8426355

 

Proxy 192.168.1.33:3305

Master 192.168.1.33:3306

Slave: 192.168.1.22:3306

 

Master-Slave 配置方法:

http://blog.csdn.net/dingsai88/article/details/43451099

 

数据库准备:

主从数据库都执行：

create databaseproxy1;

use proxy1

create tableuser(id int,name varchar(20),age int);

grant all onproxy1.* to dingsai88@"%" Identified by "密码";

 

两台数据库都授权dingsai88用户远程权限等

 

（根据自己机器情况修改）

 

 

 

 

改变CMD窗口大小: mode con cols=30 lines=20

 

 

 

启动proxy:

在Proxy安装包bin目录下执行:

mysql-proxy--proxy-address=192.168.1.33:3305 --proxy-backend-addresses=192.168.1.33:3306--proxy-read-only-backend-addresses=192.168.1.22:3306  --proxy-lua-script=C:\rw-splitting.lua > C:\mysql-proxy.log

 结果：

C:\Program Files\MySQL\mysql-proxy-0.8.5-windows-x86-32bit\bin>mysql-proxy --proxy-address=192.168.1.33:3305 --proxy-backend-addresses=192.168.1.33:33
2015-02-05 17:35:52: (critical) plugin proxy 0.8.5 started

说明:

rw-splitting.lua :在\mysql-proxy-0.8.5-windows-x86-32bit\share\doc\mysql-proxy\rw-splitting.lua

mysql-proxy.log：随意配置

 

参数说明：
--proxy-read-only-backend-addresses -- remote slave-server（只读服务器）
--proxy-backend-addresses -- master-server（写服务器）
--proxy-lua-script -- lua script（读写分离脚本）
--keepalive -- restart the proxy if it crashed（mysql-proxy宕掉重启）

 参数全:

Microsoft Windows [版本 6.1.7600]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\hpo>cd C:\Program Files\MySQL\mysql-proxy-0.8.5-windows-x86-32bit\bin

C:\Program Files\MySQL\mysql-proxy-0.8.5-windows-x86-32bit\bin>mysql-proxy --help-all
Usage:
  mysql-proxy.exe [OPTION...] - MySQL Proxy

Help Options:
  -?, --help                                              Show help options
  --help-all                                              Show all help options
  --help-proxy                                            Show options for the proxy-module

proxy-module
  -P, --proxy-address=<host:port>                         listening address:port of the proxy-server (default: :4040)
  -r, --proxy-read-only-backend-addresses=<host:port>     address:port of the remote slave-server (default: not set)
  -b, --proxy-backend-addresses=<host:port>               address:port of the remote backend-servers (default: 127.0.0.1:3306)
  --proxy-skip-profiling                                  disables profiling of queries (default: enabled)
  --proxy-fix-bug-25371                                   fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
  -s, --proxy-lua-script=<file>                           filename of the lua script (default: not set)
  --no-proxy                                              don't start the proxy-module (default: enabled)
  --proxy-pool-no-change-user                             don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
  --proxy-connect-timeout                                 connect timeout in seconds (default: 2.0 seconds)
  --proxy-read-timeout                                    read timeout in seconds (default: 8 hours)
  --proxy-write-timeout                                   write timeout in seconds (default: 8 hours)

Application Options:
  -V, --version                                           Show version
  --defaults-file=<file>                                  configuration file
  --verbose-shutdown                                      Always log the exit code when shutting down
  --daemon                                                Start in daemon-mode
  --basedir=<absolute path>                               Base directory to prepend to relative paths in the config
  --pid-file=<file>                                       PID file in case we are started as daemon
  --plugin-dir=<path>                                     path to the plugins
  --plugins=<name>                                        plugins to load
  --log-level=(error|warning|info|message|debug)          log all messages of level ... or higher
  --log-file=<file>                                       log all messages in a file
  --log-use-syslog                                        log all messages to syslog
  --log-backtrace-on-crash                                try to invoke debugger on crash
  --max-open-files                                        maximum number of open files (ulimit -n)
  --event-threads                                         number of event-handling threads (default: 1)
  --lua-path=<...>                                        set the LUA_PATH
  --lua-cpath=<...>                                       set the LUA_CPATH


C:\Program Files\MySQL\mysql-proxy-0.8.5-windows-x86-32bit\bin>

 

 

测试修改：默认rw-splitting.lua连接数4个以上连接才能读写分离，不然读写都在Master。

 

修改rw-splitting.lua

-- connection pool

if notproxy.global.config.rwsplit then

       proxy.global.config.rwsplit = {

              min_idle_connections= 1,

              max_idle_connections= 1,

 

              is_debug = false

       }

end

 

 

测试:

开启客户端连接proxy:插入一条数据

bin>mysql-h192.168.1.33 -P3305 -udingsai88 -p

mysql> useproxy1;

mysql>  insert into uservalues(99,"ding99",999);

mysql> select *from user;

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

| id   | name  | age  |

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

|   99 | ding99 |  999 |

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

1 row in set (0.00sec)

 

登录Salve 192.168.1.22数据库

mysql> select *from user;

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

| id   | name  | age  |

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

|   99 | ding99 |  999 |

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

1 row in set (0.00sec)

--数据已经同步,修改从数据

mysql> update user set name='dingsai99' where id=99;

Query OK, 1 rowaffected (0.05 sec)

 

 

此时开启一个新客户端连接到proxy发现数据已经是从slave读取：master数据为ding99,slave为dingsai99 

mysql> select *from user;

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

| id   | name  | age  |

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

|   99 | dingsai99 |  999 |

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

1 row in set (0.00sec)

 

 

已经实现读写分离。