Installing MySQL Proxy on rhel5

时间：2008-06-24 来源：jacky.lee

     这篇文章主要说明如何在rhel5中安装mysql-proxy.mysql-proxy是一个简单的程序用来在客户端和服务器之间进行通信、监控和分析。它的灵活性被应用到无限的用途; 共同点一个包括：负载平衡、故障转移、查询分析、查询过滤和修改等等。      首先安装系统所必须的软件：      # yum -y install libevent libevent-devel readline readline-devel ncurses ncurses-devel glib2 glib2-devel      # cd /tmp      # wget http://www.lua.org/ftp/lua-5.1.3.tar.gz      # 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，加入以下内容：(为什么这样，请参考http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=3)      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
http://forge.mysql.com/wiki/MySQL_Proxy
http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=1