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;