MySQL-Proxy 实现mysql读写分离试验

1、mysql安装和配置，配置主从（具体步骤省去）
场景描述：
主数据库服务器：192.168.18.157，MySQL已经安装。
从数据库服务器：192.168.18.156，MySQL已经安装。
2、 mysql读写分离
场景描述：
数据库Master主服务器：192.168.18.157
数据库Slave从服务器：192.168.18.156
MySQL-Proxy服务器：192.168.18.157
2.1 安装lua
    MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。
         [root@bbsdb lua-5.3.0]# yum -y install lua*
         Loaded plugins: fastestmirror, presto
         Determining fastest mirrors
         * base: mirrors.yun-idc.com
         * extras: mirrors.yun-idc.com
         * updates: mirrors.yun-idc.com
2.2 MySQL-Proxy安装与配置
        [root@bbsdb src]# ll
        drwxr-xr-x. 8 7161 wheel     4096 Aug 19 16:26 mysql-proxy-0.8.5-linux-el6-x86-64bit
        -rw-r--r--. 1 root root  12067298 Jan 14 13:48 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
        解压缩
        [root@bbsdb src]# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
        修改读写分离脚本rw-splitting.lua
        [root@bbsdb mysql-proxy]# vi rw-splitting.lua

        --[[ $%BEGINLICENSE%$
         Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.

         This program is free software; you can redistribute it and/or
         modify it under the terms of the GNU General Public License as
         published by the Free Software Foundation; version 2 of the
         License.

         This program is distributed in the hope that it will be useful,
         but WITHOUT ANY WARRANTY; without even the implied warranty of
         MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
         GNU General Public License for more details.

         You should have received a copy of the GNU General Public License
         along with this program; if not, write to the Free Software
         Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
         02110-1301  USA

         $%ENDLICENSE%$ --]]

        ---
        -- a flexible statement based load balancer with connection pooling
        --
        -- * build a connection pool of min_idle_connections for each backend and maintain
        --   its size
        -- *
        --
        --

        local commands    = require("proxy.commands")
        local tokenizer   = require("proxy.tokenizer")
        local lb          = require("proxy.balance")
        local auto_config = require("proxy.auto-config")

        --- config
        --
        -- 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

2.3 启动 MySQL-Proxy
    [root@bbsdb bin]# ./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=                         listening address:port of the proxy-server (default: :4040)
      -r, --proxy-read-only-backend-addresses=     address:port of the remote slave-server (default: not set)
      -b, --proxy-backend-addresses=               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=                           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=                                  configuration file
      --verbose-shutdown                                      Always log the exit code when shutting down
      --daemon                                                Start in daemon-mode
      --user=                                           Run mysql-proxy as user
      --basedir=                               Base directory to prepend to relative paths in the config
      --pid-file=                                       PID file in case we are started as daemon
      --plugin-dir=                                     path to the plugins
      --plugins=                                        plugins to load
      --log-level=(error|warning|info|message|debug)          log all messages of level ... or higher
      --log-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

[root@bbsdb bin]# ./mysql-proxy --proxy-address=192.168.18.157:4040  --proxy-backend-addresses=192.168.18.157:3306 --proxy-read-only-backend-addresses=192.168.18.156:3306 --proxy-lua-script=/usr/local               /src/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua --daemon

2.4 读写分离测试
        此处省略

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28881244/viewspace-1402421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28881244/viewspace-1402421/