前言 ProxySQL ProxySQL 是基於 MySQL 的一款開源的中間件的產品,是一個靈活的 MySQL 代理層,可以實現讀寫分離,支持 Query 路由功能,支持動態指定某個 SQL 進行緩存,支持動態載入(無需重啟 ProxySQL 服務),故障切換和一些 SQL 的過濾功能。 Grea ...
前言
ProxySQL
ProxySQL 是基於 MySQL 的一款開源的中間件的產品,是一個靈活的 MySQL 代理層,可以實現讀寫分離,支持 Query 路由功能,支持動態指定某個 SQL 進行緩存,支持動態載入(無需重啟 ProxySQL 服務),故障切換和一些 SQL 的過濾功能。
GreatSQL MGR
GreatSQL
是適用於金融級應用的國內自主開源資料庫,具備高性能、高可靠、高易用性、高安全等多個核心特性,可以作為MySQL或Percona Server的可選替換,用於線上生產環境,且完全免費並相容MySQL或Percona Server。
GreatSQL
在高可靠方面的主要提升是針對MGR做了大量的改進和提升工作,進一步提升MGR的高可靠等級。包括但不限於以下提升:
地理標簽
,提升多機房架構數據可靠性。讀寫節點動態VIP
,高可用切換更便捷。仲裁節點
,用更低的伺服器成本實現更高可用。快速單主模式
,在單主模式下更快,性能更高。智能選主
,高可用切換選主機制更合理。全新流控演算法
,使得事務更平穩,避免劇烈抖動。- 優化了節點加入、退出時可能導致性能劇烈抖動的問題。
- 解決磁碟空間爆滿時導致MGR集群阻塞的問題。
- 解決了長事務造成無法選主的問題。
- 優化事務認證隊列清理演算法,規避每60s抖動問題。
- 修複了recover過程中長時間等待的問題。
瞭解更多詳細信息可以前往➥https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/5-enhance/5-2-ha.md
部署環境介紹
部署架構圖
GreatSQL MGR集群實現資料庫複製功能及高可用。Proxysql對應用程式提供訪問,對MGR集群進行讀寫分離,集群狀態檢測,實現故障切換。
部署環境配置
部署軟體詳情
軟體名 | 版本號 |
---|---|
GreatSQL | 8.0.32-24 |
ProxySQL | 2.5.4-58 |
部署環境準備
本次採用的是單機多實例的部署方式,如何部署單機多實例可以前往➥https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-6-multi-instances.md
IP | 埠 | 角色 |
---|---|---|
172.17.139.77 | 3306 | MGR01 |
172.17.139.77 | 3307 | MGR02 |
172.17.139.77 | 6032、6033 | ProxySQL |
$ cat /etc/system-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
$ uname -a
Linux gip 3.10.0-1160.el7.x86_64 #1 SMP Tue Aug 18 14:50:17 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux
GreatSQL配置
#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
loose-group_replication_group_seeds = '172.17.139.77:33061,172.17.139.77:33071'
loose-group_replication_start_on_boot = ON
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
loose-group_replication_communication_max_message_size = 10M
loose-group_replication_transaction_size_limit = 3G
loose-group_replication_arbitrator = 0
loose-group_replication_single_primary_fast_mode = 0
loose-group_replication_request_time_threshold = 20000
report_host = "172.17.139.77"
MGR01節點配置如下
[mysqld@mgr01]
datadir=/data/GreatSQL/mgr01
socket=/data/GreatSQL/mgr01/mysql.sock
port=3306
server_id=103306
log-error=/data/GreatSQL/mgr01/error.log
loose-group_replication_local_address= "172.17.139.77:33061"
MGR02節點配置如下
[mysqld@mgr02]
datadir=/data/GreatSQL/mgr02
socket=/data/GreatSQL/mgr02/mysql.sock
port=3307
server_id=103317
log-error=/data/GreatSQL/mgr02/error.log
loose-group_replication_local_address= "172.17.139.77:33071"
搭建MGR集群及ProxySQL
搭建GreatSQL MGR 集群
MGR01實例操作
greatsql> set session sql_log_bin=0;
greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
接下來即可啟動MGR集群
greatsql> set global group_replication_bootstrap_group=ON;
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
MGR02實例操作
greatsql> set session sql_log_bin=0;
greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
greatsql> start group_replication;
Query OK, 0 rows affected (5.39 sec)
MGR集群搭建成功
greatsql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 8f20696c-3b46-11ee-b59e-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 8ff26a55-3b46-11ee-bd70-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
在MGR集群上創建ProxySQL所需的賬號
# proxysql的監控賬戶
greatsql> create user 'monitor'@'%' identified with mysql_native_password by 'Monitor@666';
greatsql> grant all privileges on *.* to 'monitor'@'%' with grant option;
# proxysql的對外訪問賬戶
greatsql> create user 'proxysql'@'%' identified with mysql_native_password by 'Proxysql@666';
greatsql> grant all privileges on *.* to 'proxysql'@'%' with grant option;
用戶認證的方式需要修改為
mysql_native_password
看看有沒有創建成功
greatsql> select User,Host from mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| monitor | % |
| proxysql | % |
| repl | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
安裝ProxySQL
ProxySQL文檔中有詳細的安裝教程可以瀏覽➥https://github.com/sysown/proxysql
RPM方式和yum方式都可以安裝的,本文采用RPM方式安裝,如果要用yum安裝需要更換yum源
$ cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
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
Red Hat 系統要把
\$releasever
改為7
接著直接安裝即可
$ yum install proxysql
這裡要註意一下,如果GreatSQL使用RPM方式安裝的,會和ProxySQL需要的依賴衝突!
RPM包下載地址:https://github.com/sysown/proxysql/releases/tag/v2.5.4
所以採用RPM的
--nodeps
選項強制安裝rpm -ivh proxysql-2.5.4-1-centos7.x86_64.rpm --nodeps
但是啟動
systemctl start proxysql.service
的時候會報錯,需要libgnutls.so.28
這時候再安裝
yum install -y gnutls
,再次systemctl start proxysql.service
即可啟動
啟動ProxySQL
$ systemctl start proxysql
# 檢查是否啟動成功
$ systemctl status proxysql
查看下埠是否開放
$ netstat -anlp | grep proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1009915/proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1009915/proxysql
- 6032 是ProxySQL的管理埠號
- 6033 是對外服務的埠號
ProxySQL的用戶名和密碼都是預設的admin
配置ProxySQL
管理員登錄
$ mysql -uadmin -padmin -h 127.0.0.1 -P 6032
可以看到有一些資料庫可用, ProxySQL將SHOW DATABASE
命令轉換為SQLite3的等效命令。
greatsql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
這些資料庫作用如下:
-
main
:記憶體配置資料庫
使用此資料庫,可以輕鬆地以自動方式查詢和更新ProxySQL的配置。使用LOAD MYSQL USERS FROM MEMORY和類似命令,存儲在此處的配置可以在運行時傳播到ProxySQL使用的記憶體數據結構。 -
disk
:基於磁碟的"main"鏡像。
在重新啟動時,"main"不會持久存在,並且可以從“磁碟”資料庫或配置文件中載入,具體取決於啟動標誌和磁碟資料庫的存在。 -
stats
:包含從代理的內部功能收集的運行時指標。
示例度量標準包括每個查詢規則匹配的次數,當前運行的查詢等。 -
monitor
:包含與ProxySQL連接的後端伺服器相關的監控指標。
示例度量標準包括連接到後端伺服器或對其進行ping操作的最短和最長時間。 -
myhgm
:僅在調試版本中啟用
此外,使用這兩種類型的用戶使用這些預設憑據訪問管理資料庫:
-
user:admin / password:admin - 具有對所有表的讀寫訪問許可權
-
user:stats / password:stats - 具有對統計表的只讀訪問許可權。 這用於從ProxySQL中提取指標,而不會暴露太多的資料庫
上述的訪問憑據,可通過變數admin-admin_credentials
和admin-stats_credentials
進行配置。
更多詳細的介紹可以前往”老葉茶館MySQL-ProxySQL中間件(一)【微信添加】“”老葉茶館MySQL-ProxySQL中間件(二)【微信添加】“
為配置監控賬號
greatsql> set mysql-monitor_username='monitor';
greatsql> set mysql-monitor_password='Monitor@666';
上面這兩句是修改變數的方式還可以在main庫下麵用sql語句方式修改
greatsql>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
greatsql>UPDATE global_variables SET variable_value='Monitor@666' WHERE variable_name='mysql-monitor_password';
配置預設組信息
greatsql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader)values(10,20,30,40,1,1);
這段SQL語句是用來配置MGR集群的主備和讀寫分離的,向mysql_group_replication_hostgroups
表插入配置
writer_hostgroup
:寫入主節點的主機組(必須大於0),這裡設置為10backup_writer_hostgroup
:備份寫入主節點的主機組,這裡是20。reader_hostgroup
:只讀節點的主機組,這裡是30。offline_hostgroup
:離線節點的主機組,這裡是40。active
:是否激活該配置,1表示激活。writer_is_also_reader
:寫入主節點是否也可以作為讀節點,1表示可以。
配置對外訪問用戶到寫組10內
greatsql> insert into mysql_users(username,password,default_hostgroup)values('proxysql','Proxysql@666',10);
這個 SQL 代碼的作用是將一個 MySQL 伺服器節點添加到 ProxySQL 的管理中,以便 ProxySQL 可以根據定義的規則和策略來分發連接請求,從而實現負載均衡和高可用性。
配置主節點定義為寫組10,從節點定義為只讀組30
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (10,'172.17.139.77',3306,1,3000,10,'mgr01');
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'172.17.139.77',3306,2,3000,10,'mgr01');
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'172.17.139.77',3307,2,3000,10,'mgr02');
hostgroup_id
:指定所屬的主機組(Hostgroup),這是 ProxySQL 中用於分組管理的一個概念。在這裡,它被設置為 10。hostname
:指定 MySQL 伺服器的主機名或 IP 地址,這裡是 '172.17.139.77'。port
:指定 MySQL 伺服器的埠號,這裡是 3306。weight
:指定該節點在負載均衡中的權重。權重越高,代表更多的請求會被分配到這個節點。這裡設置為 1。max_connections
:指定該節點允許的最大連接數。max_replication_lag
:指定最大的複製延遲(以秒為單位),這是一個連接到主從複製的節點時的配置。comment
:一個可選的註釋或描述信息,這裡設置為 'mgr01'。
這個 SQL 代碼的作用是將一個 MySQL 用戶添加到 ProxySQL 的管理中,以便 ProxySQL 可以根據定義的用戶訪問規則和策略來控制用戶對資料庫的訪問,包括路由、負載均衡和故障轉移等。
配置讀寫分離參數,與之相關的有兩個表mysql_query_rules
和mysql_query_rules_fast_routing
這裡大家可以自行配置
其中表mysql_query_rules_fast_routing
是mysql_query_rules
的擴展,併在以後評估快速路由策略和屬性(僅在ProxySQL 1.4.7+中可用)。
greatsql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,^SELECT.*FORUPDATE$,10,1);
greatsql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',30,1);
active
:是否啟用這個規則,1表示啟用,0表示禁用match_pattern
欄位就是代表設置規則destination_hostgroup
欄位代表預設指定的分組,apply
代表真正執行應用規則
在 ProxySQL 中,
rule_id
的排序作用是控制規則的匹配順序。ProxySQL 在處理查詢請求時,會按照rule_id
的升序順序逐一匹配規則,直到找到第一個匹配的規則為止。一旦找到匹配的規則,ProxySQL 將根據該規則的定義來處理查詢請求。這種排序的作用是確保規則按照預期的順序進行匹配和應用,以實現精確的查詢路由、分流和負載均衡。在上述例子中,
select ... for update
規則,確保其rule_id
小於普通的select
規則的rule_id
是為了確保在匹配時先匹配到select ... for update
規則,而不是普通的select
規則。因為
select ... for update
是一種特殊的查詢,它在執行時會涉及到鎖定操作,可能會影響其他查詢的執行。通過讓select ... for update
的rule_id
更小,可以確保 ProxySQL 在匹配查詢規則時優先考慮匹配這個特殊的規則,從而在處理select ... for update
時能夠更精確地應用相應的路由和處理邏輯。
save使記憶體數據永久存儲到磁碟,load使記憶體數據載入到runtime生效:
greatsql> save mysql users to disk;
greatsql> save mysql servers to disk;
greatsql> save mysql query rules to disk;
greatsql> save mysql variables to disk;
greatsql> save admin variables to disk;
greatsql> load mysql users to runtime;
greatsql> load mysql servers to runtime;
greatsql> load mysql query rules to runtime;
greatsql> load mysql variables to runtime;
greatsql> load admin variables to runtime;
載入完成後,可以使用select * 查詢下設置的各表的信息是否有誤
驗證監控信息 ProxySQL 監控模塊的指標都保存在monitor庫的log表中
,以下是連接是否正常的監控,對connect指標的監控 ,在前面可能會有很多connect_error,這是因為沒有配置監控信息時的錯誤,配置後如果connect_error的結果為NULL則表示正常
greatsql> select * from monitor.mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 172.17.139.77 | 3307 | 1692152694528579 | 2347 | NULL |
| 172.17.139.77 | 3306 | 1692152695231434 | 3131 | NULL |
+---------------+------+------------------+-------------------------+---------------+
#只截取部分
對心跳信息的監控(對ping 指標的監控)
greatsql> select * from mysql_server_ping_log limit 10;
+---------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 172.17.139.77 | 3306 | 1692152694511424 | 132 | NULL |
| 172.17.139.77 | 3307 | 1692152694511498 | 125 | NULL |
+---------------+------+------------------+----------------------+------------+
#只截取部分
測試讀寫分離
通過proxysql 連接看看讀操作,是否路由給了讀組
$ mysql -uproxysql -pProxysql@666 -P 6033 -h 172.17.139.77 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
| 103317 |
+-------------+
#可以看到已經把路由分配給了讀組
測試下寫操作
greatsql> begin;
Query OK, 0 rows affected (0.00 sec)
greatsql> select User from mysql.user for update;
+------------------+
| User |
+------------------+
| monitor |
| proxysql |
| repl |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
7 rows in set (0.00 sec)
greatsql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 103306 |
+-------------+
1 row in set (0.00 sec)
#可以看到已經把路由分配給了寫組
greatsql> commit;
Query OK, 0 rows affected (0.00 sec)
如果想在 ProxySQL 中查看SQL請求路由信息stats_mysql_query_digest
greatsql> select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------------------------------+------------+
| hostgroup | schemaname | username | digest_text | count_star |
+-----------+--------------------+----------+----------------------------------------+------------+
| 10 | information_schema | proxysql | commit | 1 |
| 10 | information_schema | proxysql | select User from mysql.user for update | 1 |
| 10 | information_schema | proxysql | select * from beta1 for update | 2 |
| 10 | information_schema | proxysql | begin | 1 |
| 30 | information_schema | proxysql | select @@server_id | 2 |
+-----------+--------------------+----------+----------------------------------------+------------+
#只截取部分
count_start
統計 SQL 語句次數,可以分析哪些 SQL ,頻繁執行
至此一個GreatSQL MGR + ProxySQL集群搭建方案到此部署完成
結尾
雖然ProxySQL的功能強大,但是ProxySQL畢竟不是官方原生的,在和MGR的配合上不如GreatSQL-MySQL-Router更順滑,例如還需要額外創建存儲過程以監控MGR的變化。此外就是ProxySQL的BUG其實也挺多的,當然了,如果是業務量不大,或者出於學習、實驗用途,選用ProxySQL也是可以的。
推薦使用GreatSQL-MySQL-Router
,對GreatSQL MGR的配合更加絲滑,相容度更高。
使用GreatSQL-MySQL-Router
構建MGR集群構建讀寫分離方案:https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-3-oper-rw-splitting.md
Enjoy GreatSQL