Installing MySQL Proxy on rhel5

Installing MySQL Proxy on rhel5

     这篇文章主要说明如何在rhel5中安装mysql-proxy.mysql-proxy是一个简单的程序用来在客户端和服务器之间进行通信、监控和分析。它的灵活性被应用到无限的用途; 共同点一个包括： 负载平衡、故障转移、查询分析、查询过滤和修改等等。

     首先安装系统所必须的软件：

     # yum -y install libevent libevent-devel readline readline-devel ncurses ncurses-devel glib2 glib2-devel

     # cd /tmp

     # wget

     # tar -xvzf lua-5.1.3.tar.gz

     # cd lua-5.1.3

     # make linux
     # make install

     # wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-6.2/mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz

     # tar xzvf mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz

     # ln -s  mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23 mysql
     编辑.bash_profile文件,加入/usr/local/mysql/bin，如下所示：

     # vi .bash_profile

      # Get the aliases and functions
      if [ -f ~/.bashrc ]; then
        . ~/.bashrc
      fi
      # User specific environment and startup programs
      PATH=$PATH:/usr/local/mysql/bin:$HOME/bin
      export PATH
      unset USERNAME

     # wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.1.tar.gz

     # tar zxvf mysql-proxy-0.6.1.tar.gz

     # cd mysql-proxy-0.6.1
     下来是编译安装，注意一下在编译之前请确认系统已安装了mysql-devel软件包，否则编译时出错会提示找不到mysql.h文件。

     # ./configure  LDFLAGS="-lm -ldl" LUA_CFLAGS="-I/usr/local/include/" LUA_LIBS=/usr/local/lib/liblua.a

     # make;make install

     下来创建LUA的日志文件目录和脚本目录：

     # mkdir /var/log/mysql-proxy/;mkdir -p /usr/local/mysql/lua-scripts/

     # vi /usr/local/mysql/lua-scripts/simple-log.lua，加入以下内容：(为什么这样，请参考)

     local log_file = '/var/log/mysql-proxy/mysql.log'
     local fh = io.open(log_file, "a+")
     function read_query( packet )
     if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     fh:write( string.format("%s %6d -- %s :IP %s :USER: %s\n",
     os.date('%Y-%m-%d %H:%M:%S'),
     proxy.connection.server.thread_id,
     query,
     proxy.connection.client.address,
     proxy.connection.client.username))
     fh:flush()
     end
    end

    现在使用--proxy-backend-addresses参数启动你的mysql服务器：

    # /usr/local/sbin/mysql-proxy --proxy-lua-script=/usr/local/mysql/lua-scripts/simple-log.lua --proxy-backend-addresses=192.168.1.105:3306
--proxy-backend-addresses=192.168.1.107:3306 --daemon

   192.168.1.105和192.168.1.107是代理服务器连接的两个节点。

    如果开启的防火墙，则要做如下设置：

    # iptables -A INPUT -s 192.168.l.105 -d 192.168.l.107 -p tcp -m state --state NEW -m tcp --dport 4040 -j ACCEPT

    现在你就可以连接你的mysql服务器了：

    # mysql -u dba_admin -p -h 192.168.1.107 -P 4040

      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 16 to server version: 5.1.23-ndb-6.2.15

      Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

      mysql> show databases;

      +--------------------+
      | Database           |
      +--------------------+
      | information_schema |
      | Imap_Forms         |
      | mysql              |
      | test               |
      +--------------------+
      4 rows in set (0.01 sec)

      mysql> quit

      Bye

    代理服务器使用4040代替了mysql的3306端口；

    可以在代理服务器上测试，如下：

    # mysql -u root -p -h 127.0.0.1 -P 4041
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.1.20-agent MySQL Enterprise Agent

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> select * from proxy_connections;
    +------+--------+-------+------+
    | id   | type   | state | db   |
    +------+--------+-------+------+
    |    0 | server | 0     |      |
    |    1 | proxy  | 0     |      |
    |    2 | server | 10    |      |
    +------+--------+-------+------+
    3 rows in set (0.01 sec)

    mysql> quit
    Bye

 

参考：
http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html