Centos8.3、proxysql2.0讀寫分離實戰記錄

時光城主發表於2021-06-22

    接著主從複製繼續往下講,這個專案中我是使用proxysql做讀寫分離的中介軟體,之前是使用mycat.老實說mycat屬於比較重量級的中介軟體,1.0還好到了2.0配置變得很複雜而且文件不是很齊全,我看著比較吃力.所以我就選擇了proxysql作為讀寫分離的中介軟體,相比mycat 它更加輕量級、配置簡單、更改配置的時候不用重啟就能生效。

快捷安裝命令

    還是是喜歡一鍵指令碼安裝比較省力氣。其他安裝方法請看 https://gitee.com/mirrors/proxysql#red-hat--centos

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo[proxysql_repo]name= ProxySQL YUM repositorybaseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasevergpgcheck=1gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key EOF 
 

    然後執行

yum install proxysql                #安裝預設
yum install proxysql-version        #安裝指定版本  二選一
systemctl enable  proxysql          #新增到開機啟動
systemctl start  proxysql           #啟動服務

開始配置

      proxysql客戶端:6033代理mysql服務的埠 也就是應用連線使用的

      proxysql管理端:6032 管理proxysql配置的埠  只能本地登入

      登入管理端 

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

     proxysql的管理端和mysql的客戶類似 可以使用 show tables;查詢表如下圖:

     主要用到

    mysql_servers        表是用來儲存資料庫例項資訊 

    mysql_user            表是用來儲存資料庫例項賬號資訊

    mysql_query_rules  路由規則表

    新增資料庫例項

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'172.16.102.7',3306,1,'master');#新增資料庫例項master

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'172.16.102.8',3306,1,'slave1');#新增資料庫例項slave1

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'172.16.102.9',3306,1,'slave2');#新增資料庫例項slave2
hostgroup_id  mysql_servers表的主鍵 很重要路由規則和賬號表都有用到
hostname      mysql資料庫例項的ip

   port mysql資料庫例項的埠

 weight 負載權重 數字越大承受的請求越多
comment       備註

  查詢新增結果 

select * from mysql_servers; #和sql語句語法一致

 如下圖:

 

 新增客戶端連線賬號

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','jishuzhai',1,1);
username  mysql        例項的賬號
password  mysql        例項的密碼 意味著所有mysql例項需要建立相同的賬號密碼。
default_hostgroup      和mysql_servers表的hostgroup_id進行關聯 這裡設定為1 意味著所有請求預設到hostgroup_id為1的這組例項然後在根據路由規則進行分發
transaction_persistent 開啟事物支援 預設 0 關閉 這裡設定為1開啟。

   查詢新增結果 

select * from mysql_users;

  如下圖:

  

  新增路由規則

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)

   rule_id                          路由編號

   active                           是否啟用路由 

   match_digest                路由匹配規則 支援正則

   destination_hostgroup    路由目標 也就資料庫例項組

   apply                           1 匹配目標後直接轉發 

   路由策略  所有請求到 default_hostgroup 設定的預設資料庫例項組 然後根據路由規則把查詢請求分發到hostgroup_id為2的資料庫例項組

   查詢新增結果

select rule_id,active,match_digest,destination_hostgroup,log,apply  from mysql_query_rules;

 如下圖:

  

      規則1:查詢更新結果的語句匹配搭配到hostgrouo_id 為1的例項組 也就是寫的例項組 

      規則2: 匹配所有查詢路由到hostgroup_id 為 2的例項組  也就是讀的例項組

      注意 proxysql的匹配規則是從路由編號 1開始的 所以這兩條規則順序不能顛倒 否則查詢更新結果的語句永遠不會路由到寫的例項 因為查詢包含查詢更新結果。 

新增監控賬號 

     所謂監控賬號就是用來監控資料庫例項狀態的賬號,這裡我和上面使用了同一個賬號,可以分開 但是要在所有例項上都要建立。

set mysql-monitor_username='proxysql'; 
set mysql-monitor_password='jishuzhai'; 

 檢視監控情況

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

結果如下圖:

  

  如果 connect_error 不為null 說明配置 監控賬號失敗 請檢查監控賬號是否正確

設定資料庫版本和語法相容

set mysql-server_version='8.0.21';        #設定資料庫版本 如果不設定使用druid連線的時候會出現問題
set mysql-set_query_lock_on_hostgroup=0;  # 設定語法相容 如果不配置使用資料庫連線工具比如說navicat 連線執行一些特殊查詢比如說select @@version 就會提示語法不相容

 

載入到配置到記憶體

load mysql users to runtime;          
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

寫入配置到硬碟

save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;

建議每次修改完配置都執行一遍 

接下來關閉從資料庫的資料複製 在所有從資料庫 執行

stop slave  # 接著上篇 關閉主從複製

登入到proxysql 客戶端 可以使用資料庫連線工具登入也可以使用mysql客戶端登入

mysql -uproxysql -pjishuzhai -h127.0.0.1 -P6033 --default-auth=mysql_native_password 

show databaases; #登入後執行 顯示所有資料

出現如下圖:

  

  出現這個結果說明配置成功了。

測試讀寫分離路由規則

  1.新增一條資料 然後查詢看結果

INSERT INTO `test` VALUES (4, 'master4');

 

如下圖:

我新增了一條資料成功了 但是查詢看不到結果 然後我們單獨登入master資料 也就是hostgroup_id 為 1的資料例項檢視 結果如下圖:

  結果有五條資料 說明讀寫分離是配置成功了

 現在我們在做一個實驗 更換主資料庫 把從資料庫 slave1 設定為主資料庫

登入proxysql 管理端

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

1.更新mysql_servers 表 master 的 hostgroup_id 更新為 2, 將slave1 hostgroup_id 更新為3

2.更新mysql_users 表 將 default_hostgroup 更新為3

3.更新mysql_query_rules 表 將 destination_hostgroup 更新為 3

4.然後載入到記憶體中

update mysql_servers set  hostgroup_id = 2 where comment = 'master';

update mysql_servers set  hostgroup_id = 3  where comment = 'slave1';

update mysql_users set default_hostgroup = 3;

update mysql_query_rules set destination_hostgroup = 3 where rule_id =1;
load mysql users to runtime; load mysql servers to runtime; load mysql query rules to runtime; save mysql users to disk; save mysql servers to disk; save mysql query rules to disk;

登入客戶端測試 結果如下圖:


     插入 slave6 插入成功了 但是查詢的結果一個是5條記錄 一個是4條記錄 分別對應之前master資料 和 slave2 資料庫 

    登入到我們剛才設定為主庫的slave1資料庫例項插入 結果如下圖:

     Slave6 這條記錄在slave1的例項上 說明我們切換主資料庫成功了

     做這個實驗是想說明 proxysql的主資料庫 是通過 mysql_user 使用者表 和mysql_query_rules 路由規則表結合配置的。

 

開發中注意的事項

proxysql 關於一主多從資料庫的配置實驗結果

1.transaction_persistent必須設定為1

2.必須開啟事物

3.更新或修改方法在前 查詢會路由到主庫

4.如果查詢方法在前 更新方法在後那麼查詢將路由到從庫 出現髒讀。

我針對查詢方法在前 更新方法在後這種情況單獨寫一個更新操作的方法並且封裝為靜態方法  在查詢之前先呼叫一次更新操作 然後在查詢 在更新 如下圖:

 

相關文章