天天看點
傳回

基于ProxySQL讀寫分離實作

基于ProxySQL讀寫分離實作

前段時間與一個同僚聊天，過程中卻被吐槽。

因為自己的部落格中空空如也，一直是技術類文章忠實的讀者和使用者，自己卻不曾有什麼分享

有點慚愧…

最近剛好完成了一些項目，特此做些總結和分享

基于ProxySQL讀寫分離實作

（附上Larry Ellison的照片，吐槽下MySQL Proxy就别自己寫了，這麼多年還沒有GA版本，還是買吧，畢竟有錢~）

1、讀寫分離概述

讀寫分離是資料庫架構部署裡一個老生常談的話題，提高MySQL主備庫資源使用率的同時實作讀寫壓力均勻的分擔。在講究彈性縮容和資源最大化的今天，無比是一個非常務實的行為。

讀寫分離的實作方式：

  • 業務代碼實作
  • 中間件實作

今天主要介紹下基于ProxySQL讀寫分離的實作，也是第二種，基于中間件的實作

2、環境場景描述

相信大家都有使用阿裡雲ECS的經曆，辛苦部署了資料庫，主備庫也有了，卻發現不支援VIP，高可用一下子

成為難題要使應用可以伴随資料庫的高可用做到瞬時切換，vip是一個非常可靠的選擇。為了滿足高可用的同時，

還要實作讀寫分離，于是開始思考基于中間件實作

（使用阿裡雲RDS的就不要吐槽了，蘿蔔青菜各有所愛，不要問那麼多為什麼）

MySQL
192.168.1.1 master
192.168.1.2 slave
192.168.1.3 slave

ProxySQL
192.168.1.4 
192.168.1.5

LVS
192.168.1.6
基于ProxySQL讀寫分離實作

3、ProxySQL介紹

有朋友或許會疑惑，中間件那麼多，知名的MaxScale、mycat、HAproxy…為什麼非要使用這個産品

先引入對比圖，proxysql官方的産品特點對比

ProxySQL官方的産品特點對比圖

基于ProxySQL讀寫分離實作

ProxySQL由René Cannaò開發，作者是一個擁有十多年MySQL資料庫維護和開發的經驗的DBA。

目前Proxysql與AWS和Percona等很多公司合作，該産品也被大量部署使用

ProxySQL産品優勢:

支援查詢緩存

ProxySQL可以對某些路由規則設定查詢緩存，在緩存時間内多次比對這條查詢規則，則通過query cache來擷取資料

支援查詢路由

具有靈活的查詢路由規則配置，可以通過來源位址、sql語句正則、使用者名進行路由規則的自定義

支援故障轉移

ProxySQL會自動監視後端mysql的狀态，根據後端mysql狀态自動做對應的調整

支援進階配置，動态配置，0停機

ProxySQL支援動态加載配置，包括查詢規則、後端server、新增使用者等等

跨平台

ProxySQL支援多種平台的Linux

應用代理

進階拓撲支援

ProxySQL支援複雜的mysql複制拓撲架構，包括級聯複制

防火牆

支援MGR和PXC

4、ProxySQL安裝 

安裝部分不想過多描述，建議參照下面的位址，直接使用rpm包的方式比較友善
https://github.com/sysown/proxysql/wiki

1、啟動proxysql
rpm包安裝完成後，即可直接啟動proxysql
/etc/init.d/proxysql start

2、登陸proxysql
mysql -uadmin -padmin -h127.0.0.1 -P6032
(使用admin使用者登陸到proxysql管理端口)

5、ProxySQL配置

5.1 proxysql_cluster

用于配置proxysql的叢集，其中包括用于部署proxysql機器的ip位址配置，proxysql_cluster能實作proxysql節點之間的配置自動同步,多台proxysql節點還能起到負載均衡和高可用的作用

配置proxysql叢集的節點資訊

insert into proxysql_servers (hostname, port, `comment`)values ("192.168.1.4", 6032, "ProxySQL1");
insert into proxysql_servers (hostname, port, `comment`)values ("192.168.1.5", 6032, "ProxySQL2");
Hostname:proxysql節點位址
Port:proxysql端口，6032預設管理端口，6033預設資料源端口
Comment:簡單的節點備注

設定proxysql叢集的使用者

update global_variables set variable_value='proxysql' where variable_name='admin-cluster_username';
update global_variables set variable_value='proxysql' where variable_name='admin-cluster_password';
update global_variables set variable_value='admin:admin;proxysql:proxysql' where variable_name='admin-admin_credentials';
--設定管理使用者，預設admin
--設定proxysql的叢集使用者，預設proxysql，用于proxysql節點之間的配置同步

加載配置到runtime中生效，并儲存配置到磁盤上

load proxysql servers to runtime;
save proxysql servers to disk;

load admin variables to runtime;
save admin variables to disk;

注意：

（這邊補充proxysql加載配置時的原理

基于ProxySQL讀寫分離實作

預設修改在memory層，既修改隻在記憶體中，不會生效，下次重新開機後，配置自動消失

需要加載到runtime層，配置才能生效

為了讓配置永久生效，需要儲存到disk中，使用save指令）

5.2 mysql_replication_hostgroups

配置mysql主備角色對應組号，這是比較關鍵的配置，ProxySQL識别資料庫主備角色就依賴對應的組号

Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: 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),
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');

這邊我的定義
1--代表writer_hostgroup
2--代表reader_hostgroup
Cluster1-代表組的備注，可以是名稱

If they have read_only=0 , they will be moved to hostgroup 1
If they have read_only=1 , they will be moved to hostgroup 2

5.3 mysql_servers

配置mysql的主備資訊，其中包括ip、端口、角色

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.1.2',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.1.3',3306);
load mysql serves to runtime;
save mysql serves to disk;
--hostgroup_id 1對應writehost,也就是主庫
--hostgroup_id 2對應readhost,也就是備庫
--port就是端口，預設3306

5.4 mysql_users

配置mysql執行個體上的使用者，登陸proxysql進行的權限驗證，實則還是通過後端mysql的權限驗證來實作的

INSERT INTO mysql_users(username,password,default_hostgroup,default_schema) VALUES ('user2','password2',1,'dbtest1');
load mysql users to runtime;
save mysql users to disk;
--預設組是1，也就是說預設讀寫都在主庫
（如果通過資料源使用者來分離讀寫，這邊可以将隻讀使用者對應的default_hostgroup設定為2）

5.5 mysql_query_rules

讀寫分離配置的核心表，用于配置mysql讀寫分離的路由規則，可以是sql digest值、sql正則、來源ip、來源使用者名…

(這邊暫不描述，由下面的章節單獨描述)

5.6 proxysql參數配置 

--配置monitor
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
load mysql variables to runtime;
save mysql variables to disk;
--proxysql需要不斷對後端mysql進行監控
--monitor使用者需要先建立在mysql執行個體上，需要REPLICATION CLIENT權限


--配置variables

	--sql文本的長度
	set mysql-query_digests_max_digest_length=20480;
	
	--事務的最大執行時間，8小時，超過8小時自動終止
	set mysql-max_transaction_time=28800000;

	--最大資料傳輸包設定為100M
	set mysql-max_allowed_packet = 104857600;
	
	--單個package傳輸的最大大小
	set mysql-max_allowed_packet=100000000;
	
	--proxysql支援的最大連接配接數
	set mysql-max_connections=4000;
	
	set mysql-verbose_query_error = 1;
	
	--proxysql内部處理的線程數，不超過CPU的核數
	set mysql-threads=8
	
	--設定為true時，set autocommit=0視為一個開啟一個事務
	set mysql-autocommit_false_is_transaction = 'true';
	set mysql-forward_autocommit = 'true';
	set mysql-enforce_autocommit_on_reads = 'true';
	set mysql-autocommit_false_not_reusable = 'true'; 
	
load mysql variables to runtime;
save mysql variables to disk;

5.7 ProxySQL讀寫分離配置方式

這邊開始是本章讀寫分離的重要内容了，三種讀寫分離的配置方式

一、基于使用不同端口的讀寫分離實作 
SET mysql-interfaces='192.168.1.4:6401;192.168.1.5:6402';
## save it on disk and restart proxysql
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,1,1), (2,1,6402,2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

--基于端口的方式實作讀寫分離，有比較明确的分離，需要業務代碼拆分成兩套，通路不同的端口
--6401的請求轉發到主庫
--6402的請求轉發到從庫
**二、基于正規表達式的讀寫分離實作 
UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; 

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',1,1),(2,1,'^SELECT',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

--基于正規表達式的比對方式來實作sql的路由，需要提醒大家一點，文中的正則案例是有bug的，作者明确說明不建議在生産環境中使用上述配置，隻是用于在測試環境中測試。還是見到很多人運用到生産中去
三、基于SQL digest讀寫分離實作 
基于SQL digest值實作的路由規則，業務切換到ProxySQL上，stats_mysql_query_digest會記錄SQL的各種執行資訊，包括執行時間、執行次數、以及生成去參後的digest值(digest用于唯一的定位一條sql)

TOP-sql

(依稀看到了oracle AWR報告的感覺)

Find the top 5 queries based on total execution time:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 |
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  |
| 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.01 sec)

Find the top 5 queries based on count:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 |
| 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  |
| 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.00 sec)

Find the top 5 queries based on maximum execution time:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 |
| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 |
| 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
5 rows in set (0.01 sec)

配置mysql_query_rules表

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

實作讀寫分離的方式很多，基于使用者、來源位址、端口、sql digest、正則等等

可根據業務類型，選擇合适的

讀寫分離路由規則定義需要考慮下面的兩點：

一、SQL執行是否能接受讀取到延遲的資料？？

	mysql主備延遲的問題，似乎一直存在，sql釋出，高并發通路，在MySQL 5.7 無損複制下依然會有延遲，需要評估讀取到延遲的資料影響

二、SQL是否是屬于執行效率低的？？

	通過proxysql實作讀寫分離後，性能上收益有多少？

6、總結 

本章描述了ProxySQL一些基礎配置和實作原理

内容還是蠻多，先到這兒

關于proxysql監控、優化以及高可用切換，放到後面章節再補充

目前ProxySQL已經出了2.0版本，增加了基于GTID一緻性讀取，對它的未來比較看好

有興趣大家可以一起測試下，遇到問題一起交流~
mysql 讀寫分離  proxysql  基于SQL讀寫分離 SQL digest
上一篇: 讀書筆記 - 《聯盟：網際網路時代的人才變革》
下一篇: 聯盟----網際網路時代的人才變革

繼續閱讀