mysql proxy能做什么_MySQL Proxy的使用（1）

今天下载了MySQL代理(MySQL

Proxy)试用了一下，感觉还有很有用的，类似于oracle中的cman，可以进行连接的转发，对于访问内网数据库终于有了一个好的解决方案(以前用

secuCRT的端口转发功能也可以实现，但是需要打开securtCRT的相应session，比较麻烦)；还可以用来进行负载均衡和failer

over，类似于lvs等负载均衡软件。目前该组件还是测试版。

1、下载软件包。

2、解压

[zzx@zzx ~]$ tar xzvf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz

mysql-proxy-0.6.0-linux-rhas4-x86/

mysql-proxy-0.6.0-linux-rhas4-x86/sbin/

mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy

mysql-proxy-0.6.0-linux-rhas4-x86/share/

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-basic.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-inject.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-rewrite.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-query-time.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-packets.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-warnings.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-constants.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/tutorial-states.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/auto-config.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/balance.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/commands.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/parser.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/proxy/tokenizer.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/active-queries.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/analyze-query.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/auditing.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/histogram.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/rw-splitting.lua

mysql-proxy-0.6.0-linux-rhas4-x86/share/mysql-proxy/xtab.lua

3、查看使用帮助：

[zzx ~]$ cd mysql-proxy-0.6.0-linux-rhas4-x86/sbin

[zzx ~/mysql-proxy-0.6.0-linux-rhas4-x86/sbin]$ ls

./  ../  mysql-proxy*

[zzx ~/mysql-proxy-0.6.0-linux-rhas4-x86/sbin]$ ./mysql-proxy --help-all

Usage:

mysql-proxy [OPTION...] - MySQL Proxy

Help Options:

-?, --help                                          Show help options

--help-all                                          Show all help options

--help-admin                                        Show options for the admin-module

--help-proxy                                        Show options for the proxy-module

admin module

--admin-address=                         listening address:port of internal admin-server (default: :4041)

proxy-module

--proxy-address=                         listening address:port of the proxy-server (default: :4040)

--proxy-read-only-backend-addresses=     address:port of the remote slave-server (default: not set)

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

--proxy-lua-script=                           filename of the lua script (default: not set)

--no-proxy                                          Don't start proxy-server

Application Options:

-V, --version                                       Show version

--daemon                                            Start in daemon-mode

--pid-file=                                   PID file in case we are started as daemon

4、启动代理服务，使得客户端可以访问内网192.168.161.30上3311端口的数据库服务：

./mysql-proxy --proxy-backend-addresses=192.168.161.30:3311 &

5、用netstat查看启动的服务：

[zzx ~/mysql-proxy-0.6.0-linux-rhas4-x86/sbin]$ netstat -nlp

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name

tcp        0      0 0.0.0.0:4040                0.0.0.0:*                   LISTEN      15690/mysql-proxy

tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      15690/mysql-proxy

。。。。。。

其中：4041是admin的默认端口，4040是代理服务的默认端口，和cman类似。

6、客户端进行连接测试：

C:\mysql-5.0.41-win32\bin>mysql -h202.108.15.169 -P4040 -uams_admin -p

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 14848

Server version: 4.1.13-standard-log

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

mysql> exit

Bye

连接成功，其中202.108.15.169 是中转服务器的IP。如果连接失败，请检查目标数据库里是否允许202.108.15.169的内网IP进行访问。

7、因为192.168.161.30上有多个数据库服务，如果想都访问这些服务怎么办呢？文档上说可以多写几个--proxy-backend-addresses参数，测试如下：

[zzx ~/mysql-proxy-0.6.0-linux-rhas4-x86/sbin]$./mysql-proxy

--proxy-backend-addresses=192.168.161.30:3311

--proxy-backend-addresses=192.168.161.30:3312 &

客户端对3311上的服务进行连接测试：

C:\mysql-5.0.41-win32\bin>mysql -h202.108.15.169 -P4040 -uams_admin -p

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 14848

Server version: 4.1.13-standard-log

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

mysql> exit

Bye

客户端对3312上的服务进行连接测试：

C:\mysql-5.0.41-win32\bin>mysql -h202.108.15.169 -P4040 -ucaiwu -p

ERROR 1045 (28000): Access denied for user 'caiwu'@'192.168.161.43' (using passw

ord: YES)

3312上的服务连接失败，原因是因为代理指定的多个端口是采用轮询的算法进行访问的，如果我们打开一个新的连接，3312就可以访问了。这样，也就实现了failer over和负载均衡。

更深入的功能，以后再慢慢测试。