我有若干台云主机， 但是只有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 Performance Advanced Proxy for MySQL.
配置ProxySQL
参考：
https://proxysql.com/documentation/proxysql-configuration/
查看版本
root@tf-vpc0-subnet0-main-server:/opt/proxySQL# proxysql --version
ProxySQL version 2.6.2-41-gb368fc9, codename Truls
登陆proxysql
root@tf-vpc0-subnet0-main-server:/opt/proxySQL# mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQLAdmin>
注意这里用mysql client 登陆到proxysql 的服务， 我们可以认为proxysql 在本机运行了1个轻量级的mysql
确认当前proxysql 的配置是空的
第一次配置时，可以用下面sql 来确认当前的配置是空的， 处女状态
ProxySQLAdmin> SELECT * FROM mysql_servers;
Empty set (0.000 sec)
ProxySQLAdmin> SELECT * from mysql_replication_hostgroups;
Empty set (0.000 sec)
ProxySQLAdmin> SELECT * from mysql_query_rules;
Empty set (0.000 sec)
把真正后台mysql server 的信息加入proxySQL
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.0.42',3306);
Query OK, 1 row affected (0.000 sec)
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.0.42',3306);
Query OK, 1 row affected (0.000 sec)
这里创建了两个组， 但是里面的server 是同1台， 读写都是它
ProxySQLAdmin> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.0.42 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.0.42 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
保存SERVER改动
ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.003 sec)
ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.046 sec)
配置mysql 版本 很重要
参考：https://www.tusacentral.net/joomla/index.php/mysql-blogs/198-proxysql-server-version-impersonation.html
否则， 加入proxy server 默认mysql 版本是5.x.x , 实际上背后的mysql 版本是8.x.x的话
即使你用mysql 8的jdbc driver 去连接proxysql ， 还是会有错误的
ProxySQLAdmin> update global_variables set variable_value="8.0.36" where variable_name='mysql-server_version';
Query OK, 1 row affected (0.001 sec)
ProxySQLAdmin> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)
ProxySQLAdmin> save mysql variables to disk;
Query OK, 162 rows affected (0.008 sec)
配置监控
创建monitor 用户
注意创建用户动作必须在mysql 那边执行
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'xxxxxxxx';
Query OK, 0 rows affected (0.01 sec)
分配权限给montior
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
USAGE 权限：这是一个通用权限，它允许用户登录到 MySQL 服务器，但没有任何其他特定权限。授予 USAGE 权限相当于允许用户建立连接并进行身份验证，但限制了其他操作的执行。
REPLICATION CLIENT 权限：这是一个针对复制功能的权限，允许用户作为复制客户端连接到服务器，并执行与复制相关的操作。复制客户端可以检索有关主服务器状态和二进制日志的信息，用于配置和管理 MySQL 复制拓扑。
@ 符号用于分隔用户名和主机部分。在授权语句中，‘monitor’@‘%’ 表示用户名为 ‘monitor’，主机部分为 ‘%’。
% 符号在主机部分表示通配符，表示任何主机。当使用 ‘monitor’@‘%’ 这样的格式时，表示用户 ‘monitor’ 可以从任意主机连接到 MySQL 服务器。
把上面monitor 账号的信息更新入proxysql
注意这里的动作在proxy sql 这边执行
ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.001 sec)
ProxySQLAdmin> UPDATE global_variables SET variable_value='xxxxxxxx' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.001 sec)
配置监控间隔
5秒来一次
ProxySQLAdmin> UPDATE global_variables SET variable_value='5000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.001 sec)
ProxySQLAdmin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_aws_rds_topology_discovery_interval | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_group_by_host | false |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_local_dns_cache_ttl | 300000 |
| mysql-monitor_local_dns_cache_refresh_interval | 60000 |
| mysql-monitor_local_dns_resolver_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor |
| mysql-monitor_password | 32565624 |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 5000 |
| mysql-monitor_ping_interval | 5000 |
| mysql-monitor_read_only_interval | 5000 |
| mysql-monitor_read_only_timeout | 500 |
+----------------------------------------------------------------------+----------------+
保存上面的proxysql 改动
在执行"LOAD MYSQL VARIABLES TO RUNTIME"语句后，MySQL Monitor在"global_variables"表中的更改将生效。为了在重启后保持配置更改的持久性，还必须执行"SAVE MYSQL VARIABLES TO DISK"语句。
ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 162 rows affected (0.008 sec)
查看监控信息
ProxySQLAdmin> SHOW TABLES FROM monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.000 sec)
ProxySQLAdmin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+--------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 192.168.0.42 | 3306 | 1712520341352074 | 1263 | NULL |
| 192.168.0.42 | 3306 | 1712520336361230 | 1505 | NULL |
| 192.168.0.42 | 3306 | 1712520331427523 | 1251 | NULL |
+--------------+------+------------------+-------------------------+---------------+
3 rows in set (0.000 sec)
ProxySQLAdmin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+--------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+--------------+------+------------------+----------------------+------------+
| 192.168.0.42 | 3306 | 1712520351265194 | 407 | NULL |
| 192.168.0.42 | 3306 | 1712520346264936 | 334 | NULL |
| 192.168.0.42 | 3306 | 1712520341264709 | 351 | NULL |
+--------------+------+------------------+----------------------+------------+
3 rows in set (0.000 sec)
ProxySQLAdmin>
配置读写组
当后台的mysql 是1个集群时(主从库）, proxySQL 可以配置哪个库是读， 哪个是写.
但是本文只有1个mysql instance, 读写都是它了
ProxySQLAdmin> SHOW CREATE TABLE mysql_replication_hostgroups;
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | Create Table |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql_replication_hostgroups | CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) |
+------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
ProxySQLAdmin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.000 sec)
查看server 是不是readonly
ProxySQLAdmin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC
-> ;
+--------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+--------------+------+------------------+-----------------+-----------+-------+
| 192.168.0.42 | 3306 | 1712521296323678 | 543 | 0 | NULL |
| 192.168.0.42 | 3306 | 1712521291323603 | 572 | 0 | NULL |
| 192.168.0.42 | 3306 | 1712521286323547 | 514 | 0 | NULL |
| 192.168.0.42 | 3306 | 1712521281323471 | 593 | 0 | NULL |
| 192.168.0.42 | 3306 | 1712521276323355 | 422 | 0 | NULL |
| 192.168.0.42 | 3306 | 1712521271323248 | 584 | 0 | NULL |
我只有1台server, 它在writer的group (group id = 1) 所以read_only =0 (不是只读） 正常
配置mysql 用户
这里的账号nvd11 是mysql 里面已经存在的账号
ProxySQLAdmin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('nvd11','xxxxxxxx',1);
Query OK, 1 row affected (0.000 sec)
ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.000 sec)
ProxySQLAdmin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.011 sec)
到这里， 基本的配置已经完成
测试
在家里电脑 打开dbeaver ， 尝试用 main-server 的ip 和 6033 去连接mysql ， 成功！
