MySQL Proxy读写分离

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/dsx001tz/article/details/44280973

MySQL Proxy简介

MySQL Proxy(MySQL代理)是一个通过MySQL网络协议,提供MySQL服务器与客户端之间连接的应用工具,在基本配置条件下,MySQL代理仅是传递客户端发出的查询请求到MySQL服务器端,而后返回MySQL服务器的响应到客户端。
由于MySQL代理使用的MySQL网络协议,因此所有MySQL兼容的客户端(包括mysql命令行、调用mysql命令行的类库、以及支持MySQL网络协议的应用)均可无需修改连接代理。
通过配置,MySQL代理同样能够监测及修改客户端与服务器端的通讯,这样DBA可以控制客户端提交的查询,比如调整查询的结果集,甚至可以跳过MySQL数据库,直接返回数据给客户端。

场景描述:
数据库Master主服务器:192.168.90.128
数据库Slave从服务器:192.168.90.129
数据库Slave从服务器:192.168.90.130
MySQL-Proxy调度服务器:192.168.90.130

以下操作,均是在192.168.90.130 即MySQL-Proxy调度服务器 上进行的。

MySQL的安装与配置

安装过程参见安装文档

检查系统所需软件包

shell> rpm -q libevent lua glib2 pkgconfig libtool ncurses-devel readline-devel 
libevent-1.4.13-1.el6.i686
lua-5.1.4-4.1.el6.i686
glib2-2.22.5-6.el6.i686
pkgconfig-0.23-9.1.el6.i686
libtool-2.2.6-15.5.el6.i686
ncurses-devel-5.7-3.20090208.el6.i686
readline-devel-6.0-3.el6.i686

安装MySQL-Proxy

源码安装
shell> tar zxvf mysql-proxy-0.8.5.tar.gz -C /db/tool/
shell> cd /db/tool/mysql-proxy-0.8.5
shell> ./configure
checking pkg-config is at least version 0.9.0... yes
checking for LUA... no
... checked for Lua via pkg-config: No package 'lua' found. retrying with lua5.1
checking for LUA... no
configure: error: checked for Lua via pkg-config: No package 'lua5.1' found. Make sure lua and its devel-package, 
which includes the lua5.1.pc (debian and friends) or lua.pc (all others) file, is installed

shell> export LUA_CFLAGS="-I/usr/local/include"
shell> export LUA_LIBS="-L/usr/local/lib -llua -ldl"

shell> ./configure
checking for LUA... yes
checking for GLIB... yes
checking for GMODULE... yes
checking for GTHREAD... yes
checking for inet_ntoa... yes
checking for inet_ntop... yes
checking for strerror... yes
checking for getcwd... yes
checking for chdir... yes
checking for writev... yes
checking for gmtime_r... yes
checking for sigaction... yes
checking for getaddrinfo... yes
checking size of rlim_t... 0
checking for event_init in -levent... no
configure: error: libevent is required

shell> tar xvfz libevent-1.4.14b-stable.tar.gz 
shell> cd libevent-1.4.14b-stable
shell> ./configure --prefix=/usr/local/libevent-1.4
shell> make && make install
shell> export LDFLAGS="-L/usr/local/libevent-1.4/lib -lm"  
shell> ./configure --prefix=/usr/local/mysql-proxy  
shell> make && make install                

编译好的二进制安装(推荐)
shell> tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz -C /usr/local/
shell> mv /usr/local/mysql-proxy-0.8.1-linux-rhel5-x86-32bit /usr/local/mysql-proxy
shell> export PATH=$PATH:/usr/local/mysql-proxy/bin

验证安装后,验证安装结果
shell> mysql-proxy -V
mysql-proxy 0.8.5
  chassis: 0.8.5
  glib2: 2.16.6
  libevent: 2.0.21-stable
  LUA: Lua 5.1.4
    package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua;
    package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so;
-- modules
  proxy: 0.8.5

MySQL Proxy配置选项

shell> mysql-proxy --help-all
Usage:
  mysql-proxy [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
  --user=<user>                                           Run mysql-proxy as user
  --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
  --keepalive                                             try to restart the proxy if it crashed
  --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

配置MySQL Proxy


修改读写分离脚本rw-splitting.lua
修改默认连接,进行快速测试,不修改的话要达到连接数为4时才启用读写分离
shell> vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
===========================================
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,   #默认为4
                max_idle_connections = 1,   #默认为8

                is_debug = false
        }
end
===========================================

shell> vim /etc/mysql-proxy.cnf
===========================================
[mysql-proxy]
#管理员用户
admin-username = root
#管理员密码
admin-password = mypass
#管理模块脚本
admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
#读写分离脚本
proxy-lua-script = /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
#守护进程模式运行
daemon = true
#mysql proxy的监听端口
proxy-address=0.0.0.0:3333 
#指定MySQL主库
proxy-backend-addresses = 192.168.90.128:3306
#指定MySQL备库
proxy-read-only-backend-addresses = 192.168.90.129:3306
proxy-read-only-backend-addresses = 192.168.90.130:3306
pid-file=/usr/local/mysql-proxy/mysql-proxy.pid
user=root
log-level=info
log-file=/tmp/mysql-proxy.log
===========================================

shell> chomd 660 /etc/mysql-proxy.cnf

shell> mysql-proxy --defaults-file=/etc/mysql-proxy.cnf 
shell> netstat -lnt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State      
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      
tcp        0      0 0.0.0.0:39481               0.0.0.0:*                   LISTEN      
tcp        0      0 0.0.0.0:3333                0.0.0.0:*                   LISTEN      
tcp        0      0 0.0.0.0:5672                0.0.0.0:*                   LISTEN      
tcp        0      0 :::111                      :::*                        LISTEN      
tcp        0      0 :::22                       :::*                        LISTEN      
tcp        0      0 ::1:631                     :::*                        LISTEN      
tcp        0      0 ::1:25                      :::*                        LISTEN      
tcp        0      0 :::60455                    :::*                        LISTEN      
tcp        0      0 :::3306                     :::*                        LISTEN

测试读写分离效果

创建用于读写分离的数据库连接用户,登陆主库服务器192.168.90.128
shell> mysql -uroot -p 
mysql> GRANT ALL ON *.* TO 'proxy_test'@'%' IDENTIFIED BY 'proxy';

[192.168.90.130]
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

连接MySQL-Proxy
shell> mysql -u proxy_test -h 192.168.90.130 -P3333 -pproxy

mysql> insert into  t values (2),(3),(4);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看主库192.168.90.128
mysql> select * from uuu.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

查看备库192.168.90.129
mysql> select * from uuu.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

查看备库192.168.90.130
mysql> select * from uuu.t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from uuu.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

代理端口
mysql> select * from uuu.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

由此验证,我们已经实现了MySQL读写分离,目前所有的写操作都全部在Master主服务器上,用来避免数据的不同步;
另外,所有的读操作都分摊到Slave从服务器上,用来分担数据库压力。

整理自网络

Svoid
2015-01-13
阅读更多
换一批

没有更多推荐了,返回首页