本文共 8953 字，大约阅读时间需要 29 分钟。
4.1 下载所需要的版本，这里的系统平台为rhel6.4 32位系统，因此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz为例。 # tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local # cd /usr/local # ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy 添加代理用户 # useradd mysql-proxy 4.2 为mysql-proxy提供SysV服务脚本，内容如下所示 #!/bin/bash # # mysql-proxy This script starts and stops the mysql-proxy daemon # # chkconfig: - 78 30 # processname: mysql-proxy # description: mysql-proxy is a proxy daemon for mysql # Source function library. . /etc/rc.d/init.d/functions prog="/usr/local/mysql-proxy/bin/mysql-proxy" # Source networking configuration. if [ -f /etc/sysconfig/network ]; then . /etc/sysconfig/network fi # Check that networking is up. [ ${NETWORKING} = "no" ] && exit 0 # Set default mysql-proxy configuration. ADMIN_USER="admin" ADMIN_PASSWD="admin" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_OPTIONS="--daemon" PROXY_PID=/var/run/mysql-proxy.pid PROXY_USER="mysql-proxy" # Source mysql-proxy configuration. if [ -f /etc/sysconfig/mysql-proxy ]; then . /etc/sysconfig/mysql-proxy fi RETVAL=0 start() { echo -n $"Starting $prog: " daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD" RETVAL=$? echo if [ $RETVAL -eq 0 ]; then touch /var/lock/subsys/mysql-proxy fi } stop() { echo -n $"Stopping $prog: " killproc -p $PROXY_PID -d 3 $prog RETVAL=$? echo if [ $RETVAL -eq 0 ]; then rm -f /var/lock/subsys/mysql-proxy rm -f $PROXY_PID fi } # See how we were called. case "$1" in start) start ;; stop) stop ;; restart) stop start ;; condrestart|try-restart) if status -p $PROXY_PIDFILE $prog >&/dev/null; then stop start fi ;; status) status -p $PROXY_PID $prog ;; *) echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}" RETVAL=1 ;; esac exit $RETVAL 将上述内容保存为/etc/rc.d/init.d/mysql-proxy，给予执行权限，而后加入到服务列表。 # chmod +x /etc/rc.d/init.d/mysql-proxy # chkconfig --add mysql-proxy mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.0.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" tail /var/log/mysql-proxy.log netstat -tlnp 4040 主 grant all on *.* to root@'%' identified by 'nihao201314' flush privileges mysql -uroot -p -h 172.16.100.201 --port=4040 201 proxy show global variables like '%dns%' show global variables like '%hostname%' show global variables like '%name%' skip_name_resolve off set @@global.skip_name_resolve=ON killall mysql-proxy mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.0.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" 4.3 为服务脚本提供配置文件/etc/sysconfig/mysql-proxy，内容如下所示： # Options for mysql-proxy ADMIN_USER="admin" ADMIN_PASSWORD="admin" ADMIN_ADDRESS="" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS="" PROXY_USER="mysql-proxy" PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog" 其中最后一行，需要按实际场景进行修改，例如： PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16.100.6:3306 --proxy-read-only-backend-addresses=172.16.100.7:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" 其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。 service mysql-proxy mysql -uadmin -padmin -h172.16.100.107 --port=4040 -e '' 4.4 mysql-proxy的配置选项 mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。 --help --help-admin --help-proxy --help-all ———— 以上四个选项均用于获取帮助信息； --proxy-address=host:port ———— 代理服务监听的地址和端口； --admin-address=host:port ———— 管理模块监听的地址和端口； --proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口； --proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口； --proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本； --daemon ———— 以守护进程模式启动mysql-proxy； --keepalive ———— 在mysql-proxy崩溃时尝试重启之； --log-file=/path/to/log_file_name ———— 日志文件名称； --log-level=level ———— 日志级别； --log-use-syslog ———— 基于syslog记录日志； --plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件； --user=user_name ———— 运行mysql-proxy进程的用户； --defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识； --proxy-skip-profiling ———— 禁用profile； --pid-file=/path/to/pid_file_name ———— 进程文件名； 5、复制如下内容建立admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中。 --[[ $%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%$ --]] function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = errmsg or "error" } end function read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx", type = proxy.MYSQL_TYPE_LONG }, { name = "address", type = proxy.MYSQL_TYPE_STRING }, { name = "state", type = proxy.MYSQL_TYPE_STRING }, { name = "type", type = proxy.MYSQL_TYPE_STRING }, { name = "uuid", type = proxy.MYSQL_TYPE_STRING }, { name = "connected_clients", type = proxy.MYSQL_TYPE_LONG }, } for i = 1, #proxy.global.backends do local states = { "unknown", "up", "down" } local types = { "unknown", "rw", "ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name, -- configured backend address states[b.state + 1], -- the C-id is pushed down starting at 0 types[b.type + 1], -- the C-id is pushed down starting at 0 b.uuid, -- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command", type = proxy.MYSQL_TYPE_STRING }, { name = "description", type = proxy.MYSQL_TYPE_STRING }, } rows[#rows + 1] = { "SELECT * FROM help", "shows this help" } rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT end proxy.response = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT end mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.0.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" /usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.23.208.39:3306" --proxy-read-only-backend-addresses="172.23.208.40:3306" --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" tail /var/log/mysql-proxxy.log 4040 4041管理接口 6、测试 6.1 管理功能测试 # mysql -uadmin -padmin -h172.16.100.107 --port=4041 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT * FROM backends; +-------------+-------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+-------------------+-------+------+------+-------------------+ | 1 | 172.16.100.6:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.7:3306 | up | ro | NULL | 0 | +-------------+-------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec) 6.2 读写分离测试 # mysql -uroot -pmagedu.com -h172.16.100.107 --port=4040 mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"
本文转自 远永201314 51CTO博客，原文链接:http://blog.51cto.com/7336056/1857667
转载地址：http://jvkfl.baihongyu.com/