杭州建站模板系统,wordpress 远程管理,wordpress 标签 取消,邯郸论坛官网我有若干台云主机#xff0c; 但是只有1个台vm 具有外部ip
而在另1台vm上我安装了1个mysql instance, 正常来讲#xff0c; 我在家里的电脑是无法连接上这个mysql
尝试过用nginx 代理#xff0c; 但是nginx只能代理http协议的#xff0c; mysql 3306 并不是http协议
解决…我有若干台云主机 但是只有1个台vm 具有外部ip
而在另1台vm上我安装了1个mysql instance, 正常来讲 我在家里的电脑是无法连接上这个mysql
尝试过用nginx 代理 但是nginx只能代理http协议的 mysql 3306 并不是http协议
解决方案两种
为mysql的vm也添加公网ip, 缺点就是贵在有公网ip的主机上安装proxy sql 代理内网的mysql, 就是本文的内容
环境准备
serverip addressexternal ipcommenttf-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-disktf-vpc0-subnet0-main-server --source-disk-zoneeurope-west2-c这里我使用的是gcp 的备份功能 创建磁盘快照
其他云平台的自己参考 安装ProxySQL
参考 https://proxysql.com/documentation/installing-proxysql/ 先查看os 版本
gatemantf-vpc0-subnet0-main-server:~$ sudo su -
roottf-vpc0-subnet0-main-server:~# cat /etc/os-release
PRETTY_NAMEDebian GNU/Linux 11 (bullseye)
NAMEDebian GNU/Linux
VERSION_ID11
VERSION11 (bullseye)
VERSION_CODENAMEbullseye
IDdebian
HOME_URLhttps://www.debian.org/
SUPPORT_URLhttps://www.debian.org/support
BUG_REPORT_URLhttps://bugs.debian.org/Debian 11 下载proxySQL for debian 11
roottf-vpc0-subnet0-main-server:/opt/proxySQL# wget https://github.com/sysown/proxysql/releases/download/v2.6.2/proxysql_2.6.2-debian11_amd64.deb安装
roottf-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.顺利 启动
roottf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQLLoaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)Active: inactive (dead)
roottf-vpc0-subnet0-main-server:/opt/proxySQL#
roottf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl start proxysql
roottf-vpc0-subnet0-main-server:/opt/proxySQL# systemctl status proxysql
● proxysql.service - High Performance Advanced Proxy for MySQLLoaded: loaded (/lib/systemd/system/proxysql.service; enabled; vendor preset: enabled)Active: active (running) since Sun 2024-04-07 19:34:17 UTC; 3s agoProcess: 129157 ExecStart/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (codeexited, status0/SUCCESS)Main PID: 129159 (proxysql)Tasks: 25 (limit: 19184)Memory: 85.4MCPU: 258msCGroup: /system.slice/proxysql.service├─129159 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf└─129160 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnfApr 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/
查看版本
roottf-vpc0-subnet0-main-server:/opt/proxySQL# proxysql --version
ProxySQL version 2.6.2-41-gb368fc9, codename Truls登陆proxysql
roottf-vpc0-subnet0-main-server:/opt/proxySQL# mysql -u admin -padmin -h 127.0.0.1 -P 6032 --promptProxySQLAdmin
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_value8.0.36 where variable_namemysql-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_valuemonitor WHERE variable_namemysql-monitor_username;
Query OK, 1 row affected (0.001 sec)ProxySQLAdmin UPDATE global_variables SET variable_valuexxxxxxxx WHERE variable_namemysql-monitor_password;
Query OK, 1 row affected (0.001 sec)配置监控间隔
5秒来一次
ProxySQLAdmin UPDATE global_variables SET variable_value5000 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_hostgroup0) NOT NULL PRIMARY KEY,reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND reader_hostgroup0),check_type VARCHAR CHECK (LOWER(check_type) IN (read_only,innodb_read_only,super_read_only,read_only|innodb_read_only,read_onlyinnodb_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 成功
