說明

MySQL 常見代理有 MySQ Proxy、Atlas、MaxScale、ProxySQ L其中 MySQL Proxy 是 MySQL 原廠研發(fā)的，沒有發(fā)布過 GA 版，項(xiàng)目已經(jīng) 9 年沒有維護(hù)，官方不建議生產(chǎn)環(huán)境使用。另外三個(gè)項(xiàng)目都是第三方研發(fā)的開源代理，其中 ProxySQL 項(xiàng)目的 Star 最多，而且一直都在維護(hù)。本篇 SOP 主要介紹 ProxySQL 運(yùn)維管理。

ProxySQL 官網(wǎng)：https://www.proxysql.com/

安裝部署

1.1 yum 安裝

以 CentOS Linux release 7.8.2003 (Core) 系統(tǒng)版本為例：

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

使用 yum 安裝，可以指定版本：

yum install proxysql OR yum install proxysql-version

1.2 啟停管理

service proxysql start   # 啟動(dòng) proxysql
service proxysql stop    # 停止 proxysql
service proxysql status  # 查看 proxysql 狀態(tài)

1.3 查詢版本

proxysql --version

1.4 Admin 管理接口

當(dāng) ProxySQL 啟動(dòng)后，將會(huì)監(jiān)聽兩個(gè)端口：
**Admin 管理接口：**默認(rèn)為 6032 該端口用于管理配置 ProxySQL。
**接收業(yè)務(wù) SQL 接口：**默認(rèn)為 6033 用于服務(wù)業(yè)務(wù)端口，類似于 MySQL 的 3306 端口。

Admin 管理接口兼容 MySQL 客戶端協(xié)議，所以可以直接使用 MySQL 客戶端連接這個(gè)管理接口。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

一般來(lái)講 Admin 接口不需要額外配置，最有可能需要配置的是 Admin 用戶的密碼。

Admin> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+

修改 Admin 接口密碼：

set admin-admin_credentials='admin:YouPassword';load admin variables to runtime; -- 立即生效
save admin variables to disk; -- 持久化磁盤

入門體驗(yàn)

以下是測(cè)試使用的環(huán)境信息，操作系統(tǒng)均為 CentOS 7.9 版本。

ip 地址	角色	程序
172.16.104.56	主節(jié)點(diǎn)	MySQL 5.7.33
172.16.104.57	備節(jié)點(diǎn)	MySQL 5.7.33
172.16.104.55	代理節(jié)點(diǎn)	ProxySQL 2.4.3

接下來(lái)，一起簡(jiǎn)單的測(cè)試一下 ProxySQL，使用下方命令登入管理端。

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '-- 設(shè)置監(jiān)控后端使用的賬號(hào)信息
set mysql-monitor_username = 'monitor';
set mysql-monitor_password = 'monitor';-- 配置生效
load mysql variables to runtime;
save mysql variables to disk;

在后端創(chuàng)建 ProxySQL 的監(jiān)控賬戶：

create user 'monitor'@'%' identified with mysql_native_password by 'monitor';grant replication client on *.* to 'monitor'@'%';-- group replication
grant select on performance_schema.replication_group_member_stats to 'monitor'@'%';grant select on performance_schema.replication_group_members to 'monitor'@'%';

配置后端節(jié)點(diǎn)，寫入后端節(jié)點(diǎn)的 ip、端口、主機(jī)組。

insert into mysql_servers (hostgroup_id, hostname, port) values (1, '172.16.104.56', 3306);LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

配置訪問用戶，這里的用戶分為兩種含義：

• 前端用戶：客戶端用來(lái)訪問 ProxySQL 的用戶。
• 后端用戶：ProxySQL 用來(lái)訪問后端 MySQL 節(jié)點(diǎn)的用戶。

在此，案例中我們配置的前后端用戶一致。在 MySQL 中創(chuàng)建后端使用的賬號(hào)：

-- 創(chuàng)建后端用戶
create user 'op_user'@'%' identified by 'abc123';
grant all privileges on *.* to 'op_user'@'%';

將賬號(hào)信息錄入到 ProxySQL 中：

-- 寫入用戶信息
insert into mysql_users(username, password, default_hostgroup, comment) values ('op_user', 'abc123', 1, '后端用戶');-- 加載 & 持久化
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

連接代理節(jié)點(diǎn)，訪問數(shù)據(jù)庫(kù)：

mysql -u op_user -pabc123 -h 172.16.104.55 -P6033 -e'select @@hostname;'
+---------------+
| @@hostname    |
+---------------+
| 172-16-104-56 |
+---------------+

功能介紹

ProxySQL 默認(rèn)的配置文件位于/etc/proxysql.cnf，第一次啟動(dòng) ProxySQL 會(huì)初始化配置數(shù)據(jù)庫(kù)，往后的所有配置都可以在 ProxySQL 數(shù)據(jù)庫(kù)中修復(fù)，直接修改配置文件則不會(huì)生效，除非重新初始化。

3.1 多層次配置系統(tǒng)

ProxySQL 為了實(shí)現(xiàn)動(dòng)態(tài)修改大部分配置項(xiàng)，不需要重啟，設(shè)計(jì)了多級(jí)配置系統(tǒng)，將配置從運(yùn)行時(shí)環(huán)境移到內(nèi)存，并在有需求時(shí)持久化到磁盤上。

• 最底層的是 DISK 庫(kù)和 CONFIG FILE，負(fù)責(zé)持久化保存配置。這里的 CONFIG FILE 就是傳統(tǒng)的配置文件，ProxySQL 啟動(dòng)時(shí)，主要是從 DISK 庫(kù)中讀取配置加載到內(nèi)存并最終加載到 runtime 生效，只有極少的幾個(gè)特定配置內(nèi)容是從 CONFIG FILE 中加載的，除非是第一次初始化 ProxySQL 運(yùn)行環(huán)境或者 DISK 庫(kù)為空。
• 中間層的是 MEMORY，表示的是內(nèi)存數(shù)據(jù)庫(kù)，管理接口中的 main 庫(kù)。通過管理接口可以修改所有配置，都保存在內(nèi)存數(shù)據(jù)庫(kù)中，此時(shí)并沒有生效也沒有持久化，需要 load 到上層 RUNTIME 才能生效，save 到下層 DISK 才能持久化保存。
• 最上層的是 RUNTIME，它是 ProxySQL 相關(guān)線程運(yùn)行時(shí)讀取的數(shù)據(jù)結(jié)構(gòu)。該數(shù)據(jù)結(jié)構(gòu)中的配置都是已生效的配置。修改了 main 庫(kù)中的配置后，必須 load 到 runtime 數(shù)據(jù)結(jié)構(gòu)中才能使其生效。
  

在上面的多層配置系統(tǒng)圖中，標(biāo)注了** [1]、[2]、[3]、[4]、[5] ** 的序號(hào)。每個(gè)序號(hào)都有兩個(gè)操作方向 from/to b 以下是各序號(hào)對(duì)應(yīng)的操作：

• [1] LOAD FROM MEMORY / LOAD TO RUNTIME
• [2] SAVE FROM RUNTIME / SAVE TO MEMORY
• [3] LOAD FROM DISK / LOAD TO MEMORY
• [4] SAVE FROM MEMORY / SAVE TO DISK
• [5] LOAD FROM CONFIG

另外，上面的 是什么？這表示要 加載/保存 的是哪類配置。 詳細(xì)如下：

+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |  # (1)
| mysql_collations                   |  # (N)
| mysql_group_replication_hostgroups |  # (2)
| mysql_query_rules                  |  # (3)
| mysql_query_rules_fast_routing     |  # (4)
| mysql_replication_hostgroups       |  # (5)
| mysql_servers                      |  # (6)
| mysql_users                        |  # (7)
| proxysql_servers                   |  # (8)
| scheduler                          |  # (9)
+------------------------------------+

• (1)中包含兩類變量，以 amdin 為前綴的表示 admin variables，以 mysql 為前綴的表示 mysql variables。
• (2,5,6)對(duì)應(yīng)的都是 mysql servers。
• (3,4)對(duì)應(yīng)的是 mysql query rules。
• (7)對(duì)應(yīng)的 mysql users。
• (9)對(duì)應(yīng)的 scheduler。
• (N)只是一張表，保存的是 ProxySQL 支持的字符集和排序規(guī)則，不需要修改。
• (8)是 ProxySQL 的集群配置表，對(duì)應(yīng) proxysql_servers。

在 ProxySQL 客戶端使用 DML/SET 語(yǔ)句修改配置，是直接在內(nèi)存中修改的，所以需要使用命令持久化和激活，這也是官方推薦的管理方式。下面提供常見的管理語(yǔ)句：

1. 激活/持久化 MySQL 用戶配置：

# Active current in-memory MySQL User configuration
LOAD MYSQL USERS TO RUNTIME;# Save the current in-memory MySQL User configuration to disk
SAVE MYSQL USERS TO DISK;

2. 激活/持久化 MySQL 服務(wù)器配置和主機(jī)組配置：

# Active current in-memory MySQL Server and Replication Hostgroup configuration
LOAD MYSQL SERVERS TO RUNTIME;# Save the current in-memory MySQL Server and Replication Hostgroup configuration to disk
SAVE MYSQL SERVERS TO DISK;

3. 激活/持久化 MySQL 查詢規(guī)則：

# Active current in-memory MySQL Query Rule configuration
LOAD MYSQL QUERY RULES TO RUNTIME;# Save the current in-memory MySQL Query Rule configuration to disk
SAVE MYSQL QUERY RULES TO DISK;

4. 激活/持久化 MySQL 管理變量：

# Active current in-memory MySQL Variable configuration
LOAD MYSQL VARIABLES TO RUNTIME;# Save the current in-memory MySQL Variable configuration to disk
SAVE MYSQL VARIABLES TO DISK;

5. 激活/持久化 ProxySQL 管理變量：

# Active current in-memory ProxySQL Admin Variable configuration
LOAD ADMIN VARIABLES TO RUNTIME;# Save the current in-memory ProxySQL Admin Variable configuration to disk
SAVE ADMIN VARIABLES TO DISK;

讀寫分離

在入門體驗(yàn)中，已完成監(jiān)控賬號(hào)、業(yè)務(wù)賬號(hào)的創(chuàng)建和配置，并將主節(jié)點(diǎn)完成接入。本小節(jié)，將介紹一個(gè)高頻的使用場(chǎng)景，就是讀寫分離。

將實(shí)例接入到代理服務(wù)

下圖，是當(dāng)前的 mysql_servers 表的配置，只添加了主庫(kù)。

Admin> 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            | 172.16.104.56 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

現(xiàn)在將備庫(kù)也接入進(jìn)來(lái)：

insert into mysql_servers (hostgroup_id, hostname, port) values (2, '172.16.104.57', 3306);LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

定義主機(jī)組之間的復(fù)制關(guān)系

主機(jī)之間的復(fù)制關(guān)系是通過 hostgroup_id 來(lái)綁定的，關(guān)系定義使用的是 mysql_replication_hostgroups 表。

insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, check_type, comment) values(1, 2, 'read_only', 'op 集群');LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

ProxySQL 會(huì)基于后端節(jié)點(diǎn) check_type 的值，來(lái)動(dòng)態(tài)調(diào)整它所屬的主機(jī)組，所以必須保證從庫(kù)的 read_only 參數(shù)為 on 的狀態(tài)。

配置路由規(guī)則

路由的規(guī)則是在 mysql_query_rules 中配置：

insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) values (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) values (2, 1, '^SELECT', 2, 1);

• rule_id：是規(guī)則 ID。
• active：表示是否啟動(dòng)規(guī)則。
• match_pattern：表示匹配規(guī)則，支持正則匹配。
• apply：設(shè)置為 1，如果當(dāng)前的規(guī)則匹配，則不再進(jìn)行后續(xù)其他規(guī)則的匹配。
• destination_hostgroup：SQL轉(zhuǎn)發(fā)目的 hostgroup。

上方配置的路由規(guī)則效果如下：

• 所有的 SELECT FOR UPDATE 操作將發(fā)往主庫(kù)執(zhí)行。
• 其他所有的 SELECT 操作將發(fā)往備庫(kù)執(zhí)行。
• 除此之外的所有操作將默認(rèn)發(fā)往主庫(kù)處理。

配置完成后，驗(yàn)證一下：

mysql -u op_user -pabc123 -h 172.16.104.55 -P6033-- 測(cè)試執(zhí)行 SQL 語(yǔ)句
select * from test_semi;
select * from test_semi where a = 10 for update;

連接管理節(jié)點(diǎn)，查看日志：

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '-- 查詢 SQL 執(zhí)行情況
select hostgroup, digest_text from stats.stats_mysql_query_digest order by first_seen desc;
+-----------+------------------------------------------------+
| hostgroup | digest_text                                    |
+-----------+------------------------------------------------+
| 1         | select * from test_semi where a = ? for update |
| 2         | select * from test_semi                        |
+-----------+------------------------------------------------+

事務(wù)讀的配置

接下來(lái)我們驗(yàn)證另外一個(gè)操作，顯式開啟事務(wù)后執(zhí)行 SELECT 操作。

begin;
select * from test_semi;
update test_semi set c = 123 where a = 10;
select * from test_semi;
commit;

執(zhí)行結(jié)果可以通過 stats.stats_mysql_query_digest 表進(jìn)行觀測(cè)。這里直接說結(jié)論，顯式開啟事務(wù)后，會(huì)直接路由到主庫(kù)執(zhí)行，該行為由 mysql_users 表中的 transaction_persistent 參數(shù)有關(guān)。創(chuàng)建用戶時(shí)，如果不指定 transaction_persistent 參數(shù)，其默認(rèn)值為 1，表示事務(wù)開啟后，所有的操作都會(huì)在事務(wù)開啟的主機(jī)組中執(zhí)行，此時(shí)會(huì)忽略所有的路由規(guī)則。
我們將 transaction_persistent 設(shè)置為 0 測(cè)試一下。

update mysql_users set transaction_persistent = 0 where username = 'op_user';-- 配置生效
LOAD MYSQL USERS TO RUNTIME;

開啟事務(wù)后，執(zhí)行查詢可以看到是路由到了備庫(kù)，不過 transaction_persistent = 1 更符合我們對(duì)事務(wù)的認(rèn)知習(xí)慣。

Admin> select hostgroup, digest_text from stats.stats_mysql_query_digest order by first_seen desc;
+-----------+----------------------------------------+
| hostgroup | digest_text                            |
+-----------+----------------------------------------+
| 1         | commit                                 |
| 1         | update test_semi set c = ? where a = ? |
| 2         | select * from test_semi                |
| 1         | begin                                  |
+-----------+----------------------------------------+

延遲閾值和請(qǐng)求轉(zhuǎn)發(fā)

上方是一個(gè)比較簡(jiǎn)單的讀寫分離配置，實(shí)際上我們還會(huì)遇到如下問題：

• 如果備庫(kù)宕機(jī)了，所有轉(zhuǎn)發(fā)的查詢都會(huì)失敗。
• 如果備庫(kù)的延遲過高，查詢會(huì)讀取到舊數(shù)據(jù)。

接下來(lái)我們通過配置 延遲閾值 和 路由權(quán)重 來(lái)解決。

• 備庫(kù)宕機(jī)了，所有的請(qǐng)求都會(huì)轉(zhuǎn)發(fā)到主庫(kù)。
• 為備庫(kù)設(shè)置延遲閾值，如果延遲大于該值，請(qǐng)求會(huì)自動(dòng)轉(zhuǎn)發(fā)到主庫(kù)。

-- 寫入一個(gè)規(guī)則
insert into mysql_servers (hostgroup_id, hostname, port) values (2, '172.16.104.56', 3306);-- 修改備庫(kù)配置
update mysql_servers set weight = 100, max_replication_lag = 30 where hostname = '172.16.104.57';

第一條 SQL 將主庫(kù)加入到了 reader_hostgroup 中，這樣當(dāng)備庫(kù)出現(xiàn)故障的時(shí)候，查詢會(huì)自動(dòng)路由到主庫(kù)中。
第二條 SQL 調(diào)大了備庫(kù)的權(quán)重，這樣只有極少部分 SQL 會(huì)路由到主庫(kù)，同時(shí)將 max_replication_lag 調(diào)整到了 30，表示延遲如果大于 30 的時(shí)候，查詢會(huì)路由到主庫(kù)。

ProxySQL 核心表

本節(jié)會(huì)介紹 ProxySQL 中常用的表，熟悉這些表中字段的含義。

mysql_users

該表用來(lái)配置用戶信息。

CREATE TABLE mysql_users (username VARCHAR NOT NULL,password VARCHAR,active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,default_hostgroup INT NOT NULL DEFAULT 0,default_schema VARCHAR,schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',comment VARCHAR NOT NULL DEFAULT '',PRIMARY KEY (username, backend),UNIQUE (username, frontend)) 

• username & password：用戶名和密碼。
• active：是否激活賬號(hào)。
• use_ssl：設(shè)置為 1，則強(qiáng)制用戶使用 SSL 證書進(jìn)行身份驗(yàn)證。
• default_hostgroup：默認(rèn)的主機(jī)組。如果沒有匹配的路由規(guī)則或路由規(guī)則沒配置，請(qǐng)求會(huì)轉(zhuǎn)發(fā)到默認(rèn)主機(jī)組。
• default_schema：默認(rèn)的 schema。如果不設(shè)置，則由 mysql-default_schema 決定。
• schema_locked：目前未實(shí)現(xiàn)。
• transaction_persistent：設(shè)置為 1，則代表一個(gè)事務(wù)內(nèi)的所有請(qǐng)求都會(huì)轉(zhuǎn)發(fā)到第一個(gè)主機(jī)組內(nèi)執(zhí)行。
• fast_forward：設(shè)置為 1，則表示由該用戶發(fā)起的 SQL 會(huì)跳過重寫、緩存 等查詢處理層，執(zhí)行轉(zhuǎn)發(fā)到后端。
• backend & frontend：當(dāng)前沒有實(shí)現(xiàn)前后端分離，默認(rèn)為 1 即可。
• max_connections：賬號(hào)的最大連接數(shù)，默認(rèn)為 1。
• attributes：目前未實(shí)現(xiàn)。
• comment：注釋。

mysql_servers

用于配置后端的 MySQL 節(jié)點(diǎn)。

CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,hostname VARCHAR NOT NULL,port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,comment VARCHAR NOT NULL DEFAULT '',PRIMARY KEY (hostgroup_id, hostname, port) )

• hostgroup_id：主機(jī)組 ID。
• hostname：后端節(jié)點(diǎn)的主機(jī)名或 IP。
• port：后端節(jié)點(diǎn)的端口。
• gtid_port：不詳。
• status：節(jié)點(diǎn)的狀態(tài)，有 ONLINE、SHUNNED、OFFLINE_SOFT、OFFLINE_HARD 四個(gè)取值。
  • ONLINE：節(jié)點(diǎn)狀態(tài)正常，可對(duì)外服務(wù)。
  • SHUNNED：節(jié)點(diǎn)暫時(shí)離線，無(wú)法對(duì)外服務(wù)。
  • OFFLINE_SOFT：節(jié)點(diǎn)離線，在離線前會(huì)等待該節(jié)點(diǎn)的事務(wù)執(zhí)行完。
  • OFFLINE_HARD：節(jié)點(diǎn)離線，如果當(dāng)前節(jié)點(diǎn)有事務(wù)正在執(zhí)行會(huì) Kill 掉。
• weight：讀寫分離的權(quán)重，權(quán)重越高被分發(fā)的請(qǐng)求就越多。
• compression：是否開啟壓縮。
• max_connections：限制 ProxySQL 到后端節(jié)點(diǎn)的最大連接數(shù)。
• max_replication_lag：主備的延遲閾值。一旦超過該值，該節(jié)點(diǎn)的狀態(tài)會(huì)被設(shè)置為 SHUNNED，直到延遲恢復(fù)。
• use_ssl：是否開啟 SSL 連接，針對(duì)的是 ProxySQL 與后端之間的連接。
• max_latency_ms：ProxySQL 會(huì)定期對(duì)后端節(jié)點(diǎn)進(jìn)行 ping 操作，如果 ping 的響應(yīng)時(shí)間超過該參數(shù)，則會(huì)將該節(jié)點(diǎn)從連接池中剔除。
• comment：注釋。

mysql_replication_hostgroups

用于配置復(fù)制關(guān)系的表。

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

• writer_hostgroup：定義可寫的主機(jī)組。
• reader_hostgroup：定義只讀的主機(jī)組。
• check_type：檢測(cè)類型，提供了多種選擇，基本都是通過 read_only 的值來(lái)區(qū)分的。
• comment：注釋。

將主庫(kù)和備庫(kù)的 hostgroup 配置到 mysql_replication_hostgroups 表中后，ProxySQL 會(huì)檢測(cè)主備庫(kù)的 read_only 狀態(tài)。
當(dāng)檢測(cè)到 read_only 從 OFF 變成 ON 時(shí)，會(huì)將 writer_hostgroup 中的主機(jī)添加的 reader_hostgrup 中。
反之，當(dāng) read_only 從 ON 變成 OFF 時(shí)，會(huì)將其調(diào)整到 writer_hostgroup 組中。
由此可見，在 ProxySQL 中，一定要將備庫(kù)的 read_only 設(shè)置為 on，否則可能會(huì)出現(xiàn)雙寫。
當(dāng)一個(gè)后端 MySQL 實(shí)例的 read_only 狀態(tài)從 ON 變成 OFF 時(shí)，會(huì)將該實(shí)例加入到 writer_hostgroup 中，同時(shí)該實(shí)例依然位于 read_hostgroup 中。如果需要將該實(shí)例從 read_hostgroup 中移除，需要將參數(shù) mysql-monitor_writer_is_also_reader 設(shè)置為 false。

set mysql-monitor_writer_is_also_reader='false';
load mysql variables to runtime;

mysql_query_rules

用于配置路由關(guān)系的表。

CREATE TABLE mysql_query_rules (rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,username VARCHAR,schemaname VARCHAR,flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,client_addr VARCHAR,proxy_addr VARCHAR,proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),digest VARCHAR,match_digest VARCHAR,match_pattern VARCHAR,negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,re_modifiers VARCHAR DEFAULT 'CASELESS',flagOUT INT CHECK (flagOUT >= 0),replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),destination_hostgroup INT DEFAULT NULL,cache_ttl INT CHECK(cache_ttl > 0),cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,cache_timeout INT CHECK(cache_timeout >= 0),reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,timeout INT UNSIGNED CHECK (timeout >= 0),retries INT CHECK (retries>=0 AND retries <=1000),delay INT UNSIGNED CHECK (delay >=0),next_query_flagIN INT UNSIGNED,mirror_flagOUT INT UNSIGNED,mirror_hostgroup INT UNSIGNED,error_msg VARCHAR,OK_msg VARCHAR,sticky_conn INT CHECK (sticky_conn IN (0,1)),multiplex INT CHECK (multiplex IN (0,1,2)),gtid_from_hostgroup INT UNSIGNED,log INT CHECK (log IN (0,1)),apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',comment VARCHAR)

• rule_id：指定規(guī)則的 ID。該值越小，越先匹配。
• active：是否啟用規(guī)則。
• username：基于用戶名進(jìn)行匹配。
• schemaname：基于 schema 名進(jìn)行匹配。
• flagIN & flagOUT：定義規(guī)則的入口和出口，用于實(shí)現(xiàn)鏈?zhǔn)狡ヅ湟?guī)則。
• client_addr：基于客戶端地址進(jìn)行匹配。通過 client_addr 實(shí)現(xiàn)簡(jiǎn)單的白名單功能。
• proxy_addr & proxy_port：如果 ProxySQL 部署的服務(wù)器中有多個(gè) IP 地址，可匹配來(lái)自指定 IP 的流量。
• digest：基于 Query ID 進(jìn)行匹配。
• match_digest & match_pattern：基于正則表達(dá)式進(jìn)行匹配，match_digest 是匹配模版化后的 SQL，match_pattern 是匹配原 SQL。
• negate_match_pattern：設(shè)置為 1，表示沒匹配上 match_digest & match_pattern 的規(guī)則才為真。
• re_modifiers：設(shè)置正則引起的修飾符。
• replace_pattern：替換后的文本，用于查詢重寫。
• destination_hostgroup：規(guī)則目標(biāo)的主機(jī)組。
• cache_ttl：設(shè)置結(jié)果集的緩存時(shí)長(zhǎng)，單位是毫秒。
• cache_empty_result：是否緩存空的結(jié)果集。
• cache_timeout：暫未實(shí)現(xiàn)。
• reconnect：暫無(wú)實(shí)現(xiàn)。
• timeout：定義查詢的超時(shí)時(shí)長(zhǎng)，單位是毫秒。如果查詢?cè)谥付〞r(shí)間沒有完成，則會(huì)被 ProxySQL kill 掉，如果不指定該字段，則由全局參數(shù)控制該行為 mysql-default_query_timeout 默認(rèn)為 10 小時(shí)。
• retries：查詢失敗重試的次數(shù)。
• delay：定義查詢延遲執(zhí)行的時(shí)長(zhǎng)，單位是毫秒。
• mirror_flagOUT & mirror_hostgroup：用于景象功能。
• error_msg：匹配規(guī)則的操作將返回 error_msg，用于實(shí)現(xiàn)黑名單的功能。
• log：是否將匹配規(guī)則的查詢記錄到審計(jì)日志中。不指定的話，則由全局參數(shù) mysql-eventlog_defalut_log 決定，默認(rèn)為 0。
• apply：若設(shè)置為 1 的話，則操作匹配到該規(guī)則時(shí)，會(huì)直接被轉(zhuǎn)發(fā)給后端節(jié)點(diǎn)處理，不會(huì)再進(jìn)行其他規(guī)則的匹配。

高級(jí)特性

本小節(jié)，介紹一下 ProxySQL 實(shí)用的高級(jí)特性。

SQL 審計(jì)

ProxySQL 可將流經(jīng)它的 SQL 語(yǔ)句全部記錄下來(lái)，用于審計(jì)分析和問題定位。是一個(gè)非常實(shí)用的功能，不過數(shù)據(jù)庫(kù)流量如果比較大的話，會(huì)占用很大的存儲(chǔ)空間。

Admin> show variables like 'mysql-eventslog%';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| mysql-eventslog_filename    |           |
| mysql-eventslog_filesize    | 104857600 |
| mysql-eventslog_default_log | 0         |
| mysql-eventslog_format      | 1         |
+-----------------------------+-----------+

各參數(shù)含義如下：

• mysql-eventslog_filename：日志前綴名。默認(rèn)為空，代表 SQL 審計(jì)沒有開啟。
• mysql-eventslog_filesize：日志的最大大小。超過此限制，會(huì)對(duì)日志進(jìn)行切割，默認(rèn)為 100MB。
• mysql-eventslog_default_log：是否開啟操作審計(jì)，默認(rèn)為 0 不記錄。
• mysql-eventslog_format：日志的格式。1 表示二進(jìn)制格式 2 表示 JSON 格式。如果要查看二進(jìn)制格式的內(nèi)容，必須使用專用的解析工具 eventslog_reader_sample。

下面我們進(jìn)行一個(gè)測(cè)試：

set mysql-eventslog_filename = 'query';
set mysql-eventslog_format = 2;
set mysql-eventslog_default_log = 1;LOAD MYSQL VARIABLES TO RUNTIME;

{"client":"172.16.104.56:38474","digest":"0xDC257DF652F9B5E6","duration_us":1764,"endtime":"2024-06-26 14:43:39.203870","endtime_timestamp_us":1719384219203870,"event":"COM_QUERY","hostgroup_id":2,"query":"select * from fractest1","rows_affected":0,"rows_sent":1,"schemaname":"test","server":"172.16.104.57:3306","starttime":"2024-06-26 14:43:39.202106","starttime_timestamp_us":1719384219202106,"thread_id":2,"username":"op_user"}

SQL 黑名單

當(dāng)有一類 SQL 突然并發(fā)較高，打滿數(shù)據(jù)庫(kù)資源時(shí)，往往會(huì)導(dǎo)致數(shù)據(jù)庫(kù)整體變慢。此時(shí)處理的方法，往往是讓研發(fā)定位接口，然后停掉相關(guān)業(yè)務(wù)，及時(shí)止血，但也需要一定的時(shí)間，此時(shí)業(yè)務(wù)可能已經(jīng)受損。
如果使用 ProxySQL 可以通過 SQL 黑名單的方式解決。接下來(lái)我們介紹通過 SQL 指紋來(lái)屏蔽 SQL 語(yǔ)句。
狀態(tài)庫(kù) stats_mysql_query_digest 表中，記錄了一些 TOP SQL 的信息，可從該表中獲取 SQL 指紋。

select * from stats.stats_mysql_query_digest;-- digest 字段是 SQL 指紋
-- 0xDC257DF652F9B5E6 | select * from fractest1

獲取指紋后，需要寫入路由規(guī)則，需要注意的是，路由規(guī)則是按 rule_id 順序匹配的，所以屏蔽規(guī)則需要放在 rule_id 小的規(guī)則中，之前演示 rule_id = 1 和 2，所以限制改大。

Admin> update mysql_query_rules set rule_id = 11 where rule_id = 1;
Query OK, 1 row affected (0.00 sec)Admin> update mysql_query_rules set rule_id = 12 where rule_id = 2;
Query OK, 1 row affected (0.00 sec)

插入屏蔽規(guī)則：·

insert into mysql_query_rules (rule_id, destination_hostgroup, digest, error_msg, active, apply, comment) values (1, 1, '0xDC257DF652F9B5E6', 'request denied by rule', 1, 1, 'request denied by rule');load mysql query rules to runtime;
save mysql query rules to disk;

驗(yàn)證屏蔽效果：

op_user@mysql 15:15:  [test]>select * from fractest1;
ERROR 1148 (42000): request denied by rule

ProxySQL Cluster

ProxySQL 作為一個(gè)中間價(jià)，本身是無(wú)狀態(tài)的，如果代理節(jié)點(diǎn)出現(xiàn)故障，那就會(huì)導(dǎo)致業(yè)務(wù)無(wú)法訪問。所以線上一般會(huì)部署多個(gè)節(jié)點(diǎn)，使用 VIP 或者 LVS 來(lái)進(jìn)行故障切換。既然涉及到多個(gè)節(jié)點(diǎn)，ProxySQL 的配置調(diào)整需要實(shí)時(shí)同步，這樣將流量切換到任意一個(gè)代理都是正確可用的。這就是接下來(lái) ProxySQL Cluster 的作用。

搭建 ProxySQL Cluster

開啟 proxysql 的集群功能，需要：

• 配置 cluster 賬號(hào)，用于查詢對(duì)比集群內(nèi)各 proxysql 節(jié)點(diǎn)的配置信息
• 配置 proxysql_servers，將集群內(nèi)的 proxysql 節(jié)點(diǎn)信息添加到 proxysql_servers 表

以上操作需要到組成集群的每一個(gè) proxysql 節(jié)點(diǎn)上執(zhí)行。
通過參數(shù) admin-cluster_username 和 admin-cluster_password 設(shè)置 cluster 賬號(hào)。不能使用 admin 賬號(hào)作為cluster 賬號(hào)，因?yàn)?admin 賬號(hào)只能在本地(127.0.0.1)登陸。
同時(shí)還需要將 cluster 賬號(hào)添加到參數(shù) admin-admin_credentials 中。

set admin-admin_credentials = 'admin:admin;clusteradmin:passadmin';set admin-cluster_username='clusteradmin';
set admin-cluster_password='passadmin';load admin variables to runtime;
save admin variables to disk;

將組成 proxysql 集群的多個(gè)節(jié)點(diǎn)的信息添加到 proxysql_servers 表。

mysql> show create table proxysql_servers\G
*************************** 1. row ***************************table: proxysql_servers
Create Table: CREATE TABLE proxysql_servers (hostname VARCHAR NOT NULL,port INT NOT NULL DEFAULT 6032,weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,comment VARCHAR NOT NULL DEFAULT '',PRIMARY KEY (hostname, port) )
1 row in set (0.00 sec)insert into proxysql_servers values('172.16.104.55', 6032, 1, 'proxysql node 1');
insert into proxysql_servers values('172.16.104.56', 6032, 1, 'proxysql node 2');LOAD PROXYSQL SERVERS TO RUNTIME;
save PROXYSQL SERVERS TO disk;

參考文檔

【1】ProxySQL Multi layer configuration system