devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

使用 proxySQL 来代理 Mysql

作者：笔触狂放9 | 2024-04-14 22:53:37

赞

踩

使用 proxySQL 来代理 Mysql

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

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

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

解决方案两种

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

环境准备

serverip addressexternal ipcomment
tf-vpc0-subnet0-main-server192.168.0.3534.39.2.90对外主机
tf-vpc0-subnet0-mysql0192.168.0.42N/Amysql 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

  • 1

这里我使用的是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/"

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

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

  • 1




安装
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.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

顺利




启动
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.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25




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

  • 1
  • 2
登陆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> 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

注意这里用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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8




把真正后台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台， 读写都是它

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
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              |         |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8




保存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)

  • 1
  • 2
  • 3
  • 4
  • 5
配置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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8




配置监控
创建monitor 用户

注意创建用户动作必须在mysql 那边执行

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'xxxxxxxx';
Query OK, 0 rows affected (0.01 sec)

  • 1
  • 2




分配权限给montior
mysql> GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)

  • 1
  • 2

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)

  • 1
  • 2
  • 3
  • 4
  • 5




配置监控间隔

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45




保存上面的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)

  • 1
  • 2
  • 3
  • 4
  • 5




查看监控信息
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> 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37




配置读写组

当后台的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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14





查看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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

我只有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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

到这里， 基本的配置已经完成

测试

在家里电脑 打开dbeaver ， 尝试用 main-server 的ip 和 6033 去连接mysql ， 成功！
在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/424495
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号