使用 proxySQL 来代理 Mysql

nvd11

已于 2024-04-09 03:23:15 修改

阅读量2k

点赞数 24

于 2024-04-09 00:05:05 首次发布

本文链接：https://blog.csdn.net/nvd11/article/details/137486460

版权

文章详细描述了如何在GoogleCloudPlatform(GCP)环境中，通过为具有内网MySQL的云主机添加公网IP或使用ProxySQL作为代理，解决远程访问问题。作者一步步指导了ProxySQL的安装、配置、监控设置以及用户权限管理的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我有若干台云主机，但是只有1个台vm 具有外部ip

而在另1台vm上我安装了1个mysql instance, 正常来讲，我在家里的电脑是无法连接上这个mysql

尝试过用nginx 代理，但是nginx只能代理http协议的， mysql 3306 并不是http协议

解决方案两种

为mysql的vm也添加公网ip, 缺点就是贵
在有公网ip的主机上安装proxy sql ，代理内网的mysql, 就是本文的内容

环境准备

	server	ip address	external ip	comment
	tf-vpc0-subnet0-main-server	192.168.0.35	34.39.2.90	对外主机
	tf-vpc0-subnet0-mysql0	192.168.0.42	N/A	mysql installed port 3306

proxy的安装和配置是在 main-server 主机上的

备份 main-server 主机

gcloud compute images create tf-vpc0-subnet0-main-server  --source-disk=tf-vpc0-subnet0-main-server         --source-disk-zone=europe-west2-c

这里我使用的是gcp 的备份功能（创建磁盘快照）

其他云平台的自己参考

安装ProxySQL

参考：
https://proxysql.com/documentation/installing-proxysql/

先查看os 版本

gateman@tf-vpc0-subnet0-main-server:~$ sudo su -
root@tf-vpc0-subnet0-main-server:~# cat /etc/os-release 
PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
NAME="Debian GNU/Linux"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

Debian 11

下载proxySQL for debian 11

root@tf-vpc0-subnet0-main-server:/opt/proxySQL# wget https://github.com/sysown/proxysql/releases/download/v2.6.2/proxysql_2.6.2-debian11_amd64.deb

安装

root@tf-vpc0-subnet0-main-server:/opt/proxySQL# dpkg -i proxysql_2.6.2-debian11_amd64.deb
Selecting previously unselected package proxysql.
(Reading database ... 64699 files and directories currently installed.)
Preparing to unpack proxysql_2.6.2-debian11_amd64.deb ...
Unpacking proxysql (2.6.2) ...
Setting up proxysql (2.6.2) ...
Created symlink /etc/systemd/system/multi-user.target.wants/proxysql.service → /lib/systemd/system/proxysql.service.

顺利

启动

root@tf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
     Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead)
root@tf-vpc0-subnet0-main-server:/opt/proxySQL# 
root@tf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl start proxysql
root@tf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQL
     Loaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2024-04-07 19:34:17 UTC; 3s ago
    Process: 129157 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
   Main PID: 129159 (proxysql)
      Tasks: 25 (limit: 19184)
     Memory: 85.4M
        CPU: 258ms
     CGroup: /system.slice/proxysql.service
             ├─129159 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
             └─129160 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf

Apr 07 19:34:17 tf-vpc0-subnet0-main-server systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Apr 07 19:34:17 tf-vpc0-subnet0-main-server proxysql[129157]: 2024-04-07 19:34:17 [INFO] Using config file /etc/proxysql.cnf
Apr 07 19:34:17 tf-vpc0-subnet0-main-server proxysql[129157]: 2024-04-07 19:34:17 [INFO] Current RLIMIT_NOFILE: 102400
Apr 07 19:34:17 tf-vpc0-subnet0-main-server proxysql[129157]: 2024-04-07 19:34:17 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
Apr 07 19:34:17 tf-vpc0-subnet0-main-server proxysql[129157]: 2024-04-07 19:34:17 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
Apr 07 19:34:17 tf-vpc0-subnet0-main-server systemd[1]: Started High Perf