Mysql-proxy代理内网数据库

Mysql-proxy

参考：https://segmentfault.com/q/1010000000394160

情景分析：首先您需要正在使用UCloud云主机（uhoust）以及云数据库（udb）。

鉴于udb无外网ip不能直接访问，可利用mysql-proxy将udb跳转到云主机的某一端口上进行访问，方便客户操作。

1. 安装mysql-proxy

[root@localhost ~]# yum install mysql-proxy

查看版本：

[root@localhost ~]# mysql-proxy -V
mysql-proxy 0.8.5
  chassis: 0.8.5
  glib2: 2.28.8
  libevent: 1.4.13-stable
  LUA: Lua 5.1.4
    package.path: /usr/lib64/mysql-proxy/lua/?.lua
    package.cpath: /usr/lib64/mysql-proxy/lua/?.so
-- modules
  proxy: 0.8.5

2. 配置mysql-proxy

配置mysql-proxy

大家也可以mysql-proxy -help-all查看它的帮助选项

此服务默认缺省端口为4040，也就是通过访问4040就能访问目标3306

但是新版mysql-proxy已经将4040改为了3307，默认3307。

可以直接命令开启服务，不过推荐使用配置文件，步骤如下：

[root@localhost ~]# vim /etc/mysql-proxy.cnf
[mysql-proxy]
daemon = true
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
max-open-files = 1024
plugins = admin,proxy
user = mysql-proxy
#
#Proxy Configuration
proxy-address = 0.0.0.0:3307 # 这里我们注释这一行，代理端口就默认改为了4040，也可以不改，后面连接端口就改为3307就可以了。
proxy-backend-addresses = 10.41.1.2  # 内网的数据库地址，默认3306端口
#proxy-read-only-backend-addresses =
#proxy-lua-script =
#proxy-skip-profiling = true
#
# Admin Configuration
# admin-address = 0.0.0.0:4041 # 监听端口是4041
admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua
admin-username = root  # 数据库用户名
admin-password = rootdbpassword  # 数据库密码

写好配置文件保存后需要改变权限，以上配置是由mysql-proxy自动生成的，所以权限已经是660了，可以查看一下：

[root@localhost ~]# ll /etc/mysql-proxy.cnf 
-rw-rw---- 1 root root 519 Aug 10  2016 /etc/mysql-proxy.cnf

如果不是，用以下命令修改。

[root@localhost ~]# chmod 0660 /etc/mysql-proxy.cnf

3. 启动进程

[root@localhost ~]# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

查看是否启动成功：

[root@localhost ~]# netstat -anplut|grep mysql-proxy
tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      28110/mysql-proxy   
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      28110/mysql-proxy 

若关闭kill命令把程序终止

4. 打开防火墙端口

需要在UCloud管理控制台中 打开云主机3307端口。

5. 测试

在外网环境，另一台主机：

[root@localhost ~]# mysql -u root -p -P3307 -h host

host为uhoust外网ip

6、错误分析

错误1、

[root@localhost ~]# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
2018-12-28 20:49:35: (critical) Key file contains key 'daemon' which has value that cannot be interpreted.
2018-12-28 20:49:35: (message) Initiating shutdown, requested from mysql-proxy-cli.c:367
2018-12-28 20:49:35: (message) shutting down normally, exit code is: 1

配置不对，可能用了网上的配置比如：

[mysql-proxy]
admin-username=root    #admin用户名
admin-password=123fsck    #admin密码
admin-lua-script=/usr/lib64/mysql-proxy/lua/admin.lua    #lua位置，参见上面的版本信息
daemon=true        # mysql-proxy以守护进程方式运行
keepalive=true        #保持连接启动进程会有2个, 一号进程用来监视二号进程
proxy-backend-addresses=10.6.X.XX  #目标地址，udb内网地址，默认端口3306
log-file=/var/log/mysql-proxy.log
log-level=debug

用这段配置就会报上述错误，版本不一样。新版mysql-proxy会自动生成/etc/mysql-proxy.cnf配置，我们修改相应字段就好了。如上述步骤2。

错误2、

[root@localhost ~]# mysql -hxx.xx.xx.xx -P4041 -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11690111
Server version: 5.6.20-ucloudrel1-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1105 (07000): use 'SELECT * FROM help' to see the supported commands
mysql> use mysql;
ERROR 1105 (07000): [admin] we only handle text-based queries (COM_QUERY)
mysql> select * from mysql.user;
ERROR 1105 (07000): use 'SELECT * FROM help' to see the supported commands

mysql> select * from help
    -> ;
+------------------------+------------------------------------+
| command                | description                        |
+------------------------+------------------------------------+
| SELECT * FROM help     | shows this help                    |
| SELECT * FROM backends | lists the backends and their state |
+------------------------+------------------------------------+
2 rows in set (0.14 sec)
mysql> select * from backends
    -> ;
+-------------+-------------------+---------+------+------+-------------------+
| backend_ndx | address           | state   | type | uuid | connected_clients |
+-------------+-------------------+---------+------+------+-------------------+
|           1 | 10.41.1.2:3306 | unknown | rw   | NULL |                 0 |
+-------------+-------------------+---------+------+------+-------------------+
1 row in set (0.18 sec)

这是因为一开始我们用了这样的配置：

[root@localhost ~]# vim /etc/mysql-proxy.cnf
[mysql-proxy]
daemon = true
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
max-open-files = 1024
plugins = admin,proxy
user = mysql-proxy
#
#Proxy Configuration
proxy-address = 0.0.0.0:3307
proxy-backend-addresses = 10.41.1.2
#proxy-read-only-backend-addresses =
#proxy-lua-script =
#proxy-skip-profiling = true
#
# Admin Configuration
# admin-address = 0.0.0.0:4041
admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua
admin-username = root
admin-password = rootdbpassword

以上两点不一样，此时我们查看mysql-proxy进程，会发现

[root@localhost ~]# netstat -anplut|grep mysql-proxy
tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      28110/mysql-proxy   
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      28110/mysql-proxy 

这里，这个配置是新版的mysql-proxy配置，如上述所示，此时mysql-proxy的代理端口是3307，监听端口是4041，但是你的进程连接的却是这个监听端口，所以会报上述错误。此时，我们只需要将连接端口改为3307就可以了。（老版mysql-proxy的端口是4040.）

[root@localhost ~]# mysql -hxx.xx.xx.xx -P4041 -uroot -p

改为===》

[root@localhost ~]# mysql -hxx.xx.xx.xx -P3307 -uroot -p

错误就解决了。