mariadb叢集搭建---Galera Cluster+ProxySQL教學

2023-03-17 06:05:49

前言

本篇主要用於記錄mariaDb環境下Galera Cluster模式叢集環境的搭建過程，只做演示中間引數可能會有不當地方需自行調整。

案例所採用的的是最新10.5.8版本，Mariadb10.1以後的版本中MariaDB Galera Cluste不再單獨發行，而是以galera-25.3.12-2.el7.x86_64包的形式出現。如果是10.0以下版本需要另外去安裝下Galera Cluster環境。

詳情可以去到官網瞭解https://mariadb.com/kb/en/galera-cluster/

一、Galera Cluster

MariaDB Galera Cluster 是一套在 mysql innodb 儲存引擎上面實現multi-master及資料實時同步的系統架構，業務層面無需做讀寫分離工作，資料庫讀寫壓力都能按照既定的規則分發到各個節點上去。在資料方面完全相容 MariaDB 和 MySQL。

Galera Cluster 與傳統的複製方式不同，不通過I/O_thread和sql_thread進行同步，而是在更底層通過wsrep實現檔案系統級別的同步，可以做到幾乎實時同步。

特點：

功能特性
同步複製 Synchronous replication
Active-active multi-master 拓撲邏輯
可對叢集中任一節點進行資料讀寫
自動成員控制，故障節點自動從叢集中移除
自動節點加入
真正並行的複製，基於行級
直接使用者端連線，原生的 MySQL 介面
每個節點都包含完整的資料副本
多臺資料庫中資料同步由 wsrep 介面實現

侷限性：

目前的複製僅僅支援InnoDB儲存引擎,任何寫入其他引擎的表，包括mysql.*表將不會複製,但是DDL語句會被複制的,因此建立使用者將會被複制,但是insert into mysql.user…將不會被複制的.
DELETE操作不支援沒有主鍵的表,沒有主鍵的表在不同的節點順序將不同,如果執行SELECT…LIMIT… 將出現不同的結果集.
在多主環境下LOCK/UNLOCK TABLES不支援,以及鎖函數GET_LOCK(), RELEASE_LOCK()…
查詢紀錄檔不能儲存在表中。如果開啟查詢紀錄檔，只能儲存到檔案中。
允許最大的事務大小由wsrep_max_ws_rows和wsrep_max_ws_size定義。任何大型操作將被拒絕。如大型的LOAD DATA操作。
由於叢集是樂觀的並行控制，事務commit可能在該階段中止。如果有兩個事務向在叢集中不同的節點向同一行寫入並提交，失敗的節點將中止。對於叢集級別的中止，叢集返回死鎖錯誤程式碼(Error: 1213 SQLSTATE:
40001 (ER_LOCK_DEADLOCK)).
XA事務不支援，由於在提交上可能回滾。
整個叢集的寫入吞吐量是由最弱的節點限制，如果有一個節點變得緩慢，那麼整個叢集將是緩慢的。為了穩定的高效能要求，所有的節點應使用統一的硬體。
叢集節點建議最少3個。
如果DDL語句有問題將破壞叢集。

注：Galera叢集至少需要三個節點

二、基礎環境搭建

1.安裝docker與docker-compose

這裡預設都已經安裝好，下面簡單給出安裝需要的命令。

下面命令是基於centos8.0以上環境

sudo yum install -y yum-utils
sudo yum-config-manager  --add-repo  https://download.docker.com/linux/centos/docker-ce.repo
sudo yum install docker-ce docker-ce-cli containerd.io
sudo systemctl start docker
這裡建議大家在/etc/docker下增加國內映象源，可以自行去阿里註冊申請。目錄下新增daemon.json映象源設定後重啟docker
systemctl restart  docker

docker-compose：

這裡有一個比較低概率的小坑提醒大夥，如果你使用的是arm架構的系統就不能採取常規方式安裝。樓主之前買了華為雲鯤鵬務器因為是arm架構自己沒有注意在此採坑解決了很久，最後還是通過給華為兄弟提交工單才解決。

下面給出解決問題的地址。

如有相同情況請採用下面方式安裝https://support.huaweicloud.com/prtg-kunpengmm/dockercompose_01_0001.html

x86系統有很多方式通過curl weget下載安裝包都可以，不過github經常會丟包很慢建議使用國內映象。

下面給出centos8下常規pip3安裝方式，如果是centos7則用pip

pip3 install -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com --upgrade pip

pip3 install -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com docker-compose

方便快捷一步到位

起初打算採用docker的方式進行設定但是由於容器的很多侷限性普通資料庫放在docker並不合適會有很多弊端後面改變了主意，索性將這部分留下當做一個簡單的安裝教學。

2. 常規環境搭建

想要安裝最新版本，需要新增自己的repo倉庫設定。這裡使用國內清華提供的映象速度更快一點。/etc/yum.repos.d下建立mariadb.repo檔案。想要那個版本自己去更改即可，需要注意的就是10.4後glera檔案的路徑有所不同由原來的的glera改為了glera-4。

最後更新yum快取 yum clean all ； yum makecache

# MariaDB 10.5 CentOS repository list - created 2020-12-16 12:37 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.5.8/yum/centos8-amd64/
gpgkey = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
module_hotfixes=1
gpgcheck=1

直接使用yum install -y命令安裝，這裡我們會發現映象只給我們安裝了mariadb-client使用者端。

後面我在官網找到了答案，還需要單獨安裝伺服器端

新版本直接將glera加在了安裝包中不再需要我們單獨安裝，成功安裝後就可以正常啟動了

3.啟動初始化並建立授權使用者

systemctl start mariadb   #啟動mariadb
systemctl enable mariadb  #設定開機自啟動
systemctl stop mariadb    #停止MariaDB
systemctl restart mariadb #重啟MariaDB
mysql_secure_installation #設定root密碼等相關
mysql -uroot -p           #測試登入   
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '你的密碼' WITH GRANT OPTION; 允許遠端存取
flush privileges;   重新整理許可權

在成功啟動後我們需要建立一個用於叢集間遠端存取的使用者這個使用者將在後面你的叢集設定中用到，用來建立互相存取驗證

GRANT ALL PRIVILEGES ON *.* TO 'galera_chihai'@'%'IDENTIFIED BY 'xxx' WITH GRANT OPTION;

角色新增成功

後面我們要用到MariaDB-backup需要單獨安裝，MariaDB-backup相對於預設的rsync和 mysqldump, xtrabackup, xtrabackup-v2等方案都有長足的優勢這也是官方目前推薦的方式。

是在mysql的Percona Xtrabackup 2.3.8 備份工具進行的升級與改進。mariadb-backup是官方目前最推崇的同步解決方案，如果為了追求穩定也可以直接使用rsync。

詳情參閱官網：

https://mariadb.com/kb/en/mariabackup-overview/

MariaDB 10.1 introduced features that are exclusive to MariaDB, such as InnoDB Page Compression and Data-at-Rest
Encryption. These exclusive features have been very popular with MariaDB users. However, existing backup solutions
from the MySQL ecosystem, such as Percona XtraBackup, did not support full backup capability for these features.

To address the needs of our users, we decided to develop a backup solution that would fully support these popular
MariaDB-exclusive features. We did this by creating Mariabackup, which is based on the well-known and commonly
used backup tool called Percona XtraBackup. Mariabackup was originally extended from version 2.3.8.

sudo yum install MariaDB-backup

三、加入設定引數啟動叢集

1.新增設定

10.5.8版本目錄結構如下：

官方映象組態檔目錄結構：/etc/mysql
/etc/mysql
|-- conf.d
|   |-- docker.cnf
|   |-- mysqld_safe_syslog.cnf
|-- debian-start
|-- debian.cnf
|-- mariadb.cnf
|-- mariadb.conf.d
|-- my.cnf

下面是我測試用的簡單組態檔，單獨建立一個用於glera叢集的組態檔置於/etc/my.cnf.d中即可

這裡官方給出了幾個必填引數：

地址：https://mariadb.com/kb/en/configuring-mariadb-galera-cluster/

這裡需要注意的一點是在設定了binlog_format=ROW後必須要設定log-bin否則系統會發出警告。

設定中如果不需要自定義埠的話4567可以省略。其餘設定自行根據自己機器情況設定

[galera]
binlog_format=ROW
log-bin=mysql-bin
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name=mariadb
wsrep_cluster_address="gcomm://39.107.xx.xx:4567,120.26.xx.xx:4567,42.192.xx.xx:4567"
wsrep_node_name=controller-88
wsrep_node_address=42.192.53.88
wsrep_sst_auth=sst:sstpass123
wsrep_sst_method=rsync
wsrep_causal_reads=ON
wsrep_slave_threads=4
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=

2.重新啟動叢集節點

這裡需要注意版本不同啟動命令也有區別，自己需留意自己使用的版本。只有第一個主節點啟動的時候需要加入下面步驟，其餘節點正常啟動即可。

第一個節點
sudo galera_new_cluster
其餘節點
systemctl restart mariadb

如果是雲伺服器預設情況下確保自己的如下埠都已經開放，涉及到的埠:

4444:請求SST（全量同步），在新節點加入時起作用
4568:傳輸IST(增量同步)，節點下線，重啟加入時起作用
4567:組成員之間溝通的埠

到這裡我們先看下叢集狀態看看當前這一個節點是否已經設定成功

```bash
show status like '%wsrep%';

可以看到當前節點已經成功加入叢集，我們重複前面的工作在另外兩臺伺服器上設定相應的環境。

這裡如果只有一臺主機的話就按多範例的方式來設定

緊接著剩餘機器重複上述步驟即可，但是切記後面不再需要啟動new-cluster。

踩坑規避：

1.需要修改open-file-limit引數，很多時候預設的最大可開啟檔案數是不夠用的這個時候會直接導致啟動失敗。首先需要檢視linux本身的設定情況，更改/etc/security/limits.conf :這個檔案規定了上限。修改後再去修改/usr/lib/systemd/system下兩個mariadb.service和mairadb@.service中的設定資訊即可。直接在mysql組態檔中修改你會發現並不起作用，因為這裡的設定引數優先順序更高覆蓋掉了你的設定

2.這裡再貼出一個我本人親自經歷的天坑，很多時候叢集武器第一個成功立馬啟動，但是第二個節點開始glera狀態一切正常檢視節點資訊也成功加入到叢集中，但是mariadb服務無法啟動netsate -anpt檢視3306埠並沒有啟動。因為我所有資料庫都是新建立的所以不存在同步資料需要很久的情況，但是再等待了十來分鐘後依然會發現mysql無法連線沒有啟動，glera叢集一切正常。這個時候查詢紀錄檔也找不到什麼關鍵錯誤資訊誤以為是哪裡出問題了就被我手動關閉準備重新構建，但是萬萬忽略了機器本身的效能。因為我測試使用的是3臺最垃圾的1核2g的學生機，忽略了這個問題導致誤判啟動時間。遇到3306沒起來glera正常又沒有報錯的情況只需要耐心多等待一會即可。同時這裡還有一個問題因為我3臺測試機有兩臺是centos8，一臺centos7。兩個系統也有明顯的差別，7比8啟動快了很多。這裡的原因需要打個問號可能是多方原因。如果需要同步的資料量很大sst超時，則記得在設定中修改超時時間

四、測試

show status like ‘%wsrep%’;

這裡我們新建一個資料庫看一下是否能同步成功：

當你測試資料能真正同步成功這一步才算大功告成。搭建期間基本把能踩的坑踩了一遍很痛苦，後續有遇到問題的朋友可以留言一起探討。下一步我們就該為叢集搭建負載均很的設定了。

五、ProxySql

1.安裝：

這裡我直接使用了我當前環境提供的預設版本2.0.9，大家可以自行選擇適合的版本。

在ProxySQL V2.0.0 以上版本可以原生支援 galera 叢集，不再需要 scheduler 排程程式中使用外部指令碼。

yum install proxysql -y

官網galera中使用教學地址：https://proxysql.com/blog/effortlessly-scaling-out-galera-cluster-with-proxysql/

2.結構

proxysql的目錄結構：

資料目錄：/var/lib/proxysql/

proxysql.db：設定資料儲存檔案，後端資料庫的賬號、密碼、路由等儲存在這個資料庫裡面。
proxysql.log：此檔案是紀錄檔檔案。
proxysql.pid：此檔案是是程序pid檔案。

組態檔目錄：/etc/proxysql.cnf，是一些靜態設定項，用來設定一些啟動選項。此組態檔只在第一次啟動的時候讀取進行初始化，後面唯讀取proxysql.db檔案。

啟動指令碼：/etc/init.d/proxysql

proxysql 的預設管理埠是 6032，使用者端伺服器埠是 6033。預設的使用者名稱密碼都是 admin，可以在組態檔裡看到。

ProxySQL預設有五個資料庫：

對每個庫的功能介紹如下：

main庫：
disk庫：
stats庫：
monitor庫：

可見有五個庫： main、disk、stats 、monitor 和 stats_history

main:記憶體設定資料庫，表裡存放後端 db 範例、使用者驗證、路由規則等資訊。表名以runtime_開頭的表示 proxysql 當前執行的設定內容，不能通過 dml 語句修改，只能修改對應的不以runtime_ 開頭的（在記憶體）裡的表，然後LOAD使其生效， SAVE使其存到硬碟以供下次重啟載入。main 庫中有如下資訊：

庫下的主要表：

mysql_servers: 後端可以連線 MySQL 伺服器的列表
mysql_users: 設定後端資料庫的賬號和監控的賬號。
mysql_query_rules: 指定 Query 路由到後端不同伺服器的規則列表。

注：表名以 runtime_開頭的表示 ProxySQL 當前執行的設定內容，不能通過 DML 語句修改。

只能修改對應的不以 runtime 開頭的表，然後 “LOAD” 使其生效，“SAVE” 使其存到硬碟以供下次重啟載入。

disk ：是持久化到硬碟的設定庫，對應/var/lib/proxysql/proxysql.db檔案，也就是 sqlite 的資料檔案。
stats：是 proxysql 執行抓取的統計資訊庫，包括到後端各命令的執行次數、流量、processlist、查詢種類彙總/執行時間等等。
monitor：儲存 monitor 模組收集的資訊，主要是對後端 db 的健康、延遲檢查。
stats_history: 這個庫是 ProxySQL 收集的有關其內部功能的歷史指標

3.啟動：

systemctl start proxysql.service
netstat -anlp | grep proxysql        檢視下是否啟動成功

4.連線

使用MySQL使用者端連線管理：

mysql -uadmin -padmin -h127.0.0.1 -P 6032

但是這個預設的使用者只能在本地使用。如果想要遠端連線到ProxySQL，例如用windows上的navicat連線Linux上的ProxySQL管理介面，必須自定義一個管理員賬戶。

設定 ProxySQL 所需賬戶：

在 Master 的MySQL 上建立 ProxySQL 的監控賬戶和對外存取賬戶

#proxysql 的監控賬戶
create user 'monitor'@'%' identified by '199651ch';
grant all privileges on *.* to 'monitor'@'%' with grant option;

#proxysql 的對外存取賬戶
create user 'proxysql'@'xxx' identified by 'xxxx';
grant all privileges on *.* to 'xxx'@'xxx' with grant option;

5.設定資訊介紹

設定結構如下：

整套設定系統分為三層：頂層為 RUNTIME ,中間層為 MEMORY , 底層也就是持久層 DISK 和 CONFIG FILE 。

RUNTIME：代表 ProxySQL 當前生效的正在使用的設定，無法直接修改這裡的設定，必須要從下一層 “load” 進來。
MEMORY：MEMORY 層上面連線 RUNTIME 層，下面連線持久層。這層可以正常操作 ProxySQL 設定，隨便修改，不會影響生產環境。修改一個設定一般都是現在 MEMORY 層完成的，確認正常之後在載入達到 RUNTIME 和持久化的磁碟上。

DISK 和 CONFIG FILE：持久化設定資訊，重啟後記憶體中的設定資訊會丟失，所需要將設定資訊保留在磁碟中。重啟時，可以從磁碟快速載入回來。

要重新設定 MySQL 使用者，可執行下面的其中一個命令：
1、LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
 將記憶體資料庫中的設定載入到 runtime 資料結構，反之亦然。
2、SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
 將 MySQL 使用者從 runtime 持久化到記憶體資料庫。
3、LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
 從磁碟資料庫中載入 MySQL 使用者到記憶體資料庫中。
4、SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
 將記憶體資料庫中的 MySQL 使用者持久化到磁碟資料庫中。
5、LOAD MYSQL USERS FROM CONFIG
 從組態檔中載入 MySQL 使用者到記憶體資料庫中。


要處理 MySQL server：
1、LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
 將 MySQL server 從記憶體資料庫中載入到 runtime。
2、SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
 將 MySQL server 從 runtime 持久化到記憶體資料庫中。
3、LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
 從磁碟資料庫中載入 MySQL server 到記憶體資料庫。
4、SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
 從記憶體資料庫中將 MySQL server 持久化到磁碟資料庫中。
5、LOAD MYSQL SERVERS FROM CONFIG
 從組態檔中載入 MySQL server 到記憶體資料庫中


要處理 MySQL 的查詢規則(mysql query rules)：
1、 LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
 將 MySQL query rules 從記憶體資料庫載入到 runtime 資料結構。
2、 SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
 將 MySQL query rules 從 runtime 資料結構中持久化到記憶體資料庫。
3、 LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
 從磁碟資料庫中載入 MySQL query rules 到記憶體資料庫中。
4、 SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
 將 MySQL query rules 從記憶體資料庫中持久化到磁碟資料庫中。
5、 LOAD MYSQL QUERY RULES FROM CONFIG
 從組態檔中載入 MySQL query rules 到記憶體資料庫中。
　　

要處理 MySQL 變數(MySQL variables)：
1、 LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
 將 MySQL variables 從記憶體資料庫載入到 runtime 資料結構。
2、 SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
 將 MySQL variables 從 runtime 資料結構中持久化到記憶體資料中。
3、 LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
 從磁碟資料庫中載入 MySQL variables 到記憶體資料庫中。
4、 SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
 將 MySQL variables 從記憶體資料庫中持久化到磁碟資料庫中。
5、 LOAD MYSQL VARIABLES FROM CONFIG
 從組態檔中載入 MySQL variables 到記憶體資料庫中。


要處理管理變數(admin variables)：
1、 LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
 將 admin variables 從記憶體資料庫載入到 runtime 資料結構。
2、 SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
 將 admin variables 從 runtime 持久化到記憶體資料庫中。
3、 LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
 從磁碟資料庫中載入 admin variables 到記憶體資料庫中。
4、 SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
 將 admin variables 從記憶體資料庫中持久化到磁碟資料庫。
5、 LOAD ADMIN VARIABLES FROM CONFIG
 從組態檔中載入 admin variables 到記憶體資料庫中。

ProxySQL組態檔的修改流程一般是：

1、第一次啟動時候，修改必要的CONFIG FILE設定。

2、以後設定修改MEMORY中的表，然後載入到RUNTIME並儲存到DISK持久化。

這裡有幾個最常用的命令：如何讓修改的設定生效(runtime)，以及如何持久化到磁碟上(disk)。記住，只要不是載入到 runtime，修改的設定就不會生效。

LOAD MYSQL USERS TO RUNTIME;  將記憶體資料庫中的設定載入到 runtime 資料結構
SAVE MYSQL USERS TO DISK;   將記憶體資料庫中的 MySQL 使用者持久化到磁碟資料庫中。
LOAD MYSQL SERVERS TO RUNTIME;  將 MySQL server 從記憶體資料庫中載入到 runtime。
SAVE MYSQL SERVERS TO DISK;   從記憶體資料庫中將 MySQL server 持久化到磁碟資料庫中。
LOAD MYSQL QUERY RULES TO RUNTIME;  將 MySQL query rules 從記憶體資料庫載入到 runtime 資料結構。
SAVE MYSQL QUERY RULES TO DISK;  將 MySQL query rules 從記憶體資料庫中持久化到磁碟資料庫中。
LOAD MYSQL VARIABLES TO RUNTIME;  將 MySQL variables 從記憶體資料庫載入到 runtime 資料結構。
SAVE MYSQL VARIABLES TO DISK;   將 MySQL variables 從記憶體資料庫中持久化到磁碟資料庫中。
LOAD ADMIN VARIABLES TO RUNTIME;   將 admin variables 從記憶體資料庫載入到 runtime 資料結構。
SAVE ADMIN VARIABLES TO DISK;  將 admin variables 從記憶體資料庫中持久化到磁碟資料庫。
注意：只有載入到 runtime 狀態時才會去做最後的有效性驗證。在儲存到記憶體資料庫或持久化到磁碟上時，都不會發生任何警告或錯誤。當
載入到 runtime 時，如果出現錯誤，將恢復為之前儲存得狀態，這時可以去檢查錯誤紀錄檔。

disk and config file 持久化組態檔

disk -> 是sqlite3 資料庫，預設位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)

config file 是一個傳統組態檔：一般不更改

在記憶體中動態更改設定，如果重啟，沒進行持久化(save) 則會丟失。

三則之間關係：

proxysql 啟動時，首先去找/etc/proxysql.cnf 找到它的datadir,如果datadir下有proxysql.db 就載入proxysql.db的設定

如果啟動proxysql時帶有–init標誌，會用/etc/proxsql.cnf的設定，把Runtime，disk全部初始化一下。

再呼叫時呼叫–reload 會把/etc/proxysql.cnf 和disk 中設定進行合併。如果衝突需要使用者干預。disk會覆蓋config file。

關於傳統的組態檔：

傳統組態檔預設路徑為/etc/proxysql.cnf，也可以在二進位制程式proxysql上使用-c或–config來手動指定組態檔。

預設情況下：幾乎不需要手動去設定proxysql.cnf。埠號，管理proxysql使用者名稱，密碼，可以在這裡修改。

6.實際應用中修改設定

1.設定讀寫伺服器mariadb引數

這裡由於我只啟動了三個節點的原因，所以測試環境直接設定為兩主一從，實際生產環境下設定為兩主兩從會更好點增加節點容錯性。首先需要確定mysql讀伺服器已進行相應設定，對其中兩個讀節點進行設定為1。寫設定為0

set global read_only=1;  唯讀
set global read_only=0;  讀寫

7.建立 ProxySQL 監控使用者

要在 ProxySQL 中啟用對後端節點的監視，需要建立一個具有USAGE許可權的使用者，並在 ProxySQL 中設定該使用者。為 ProxySQL 設定監控賬號：

set mysql-monitor_username='monitor';
set mysql-monitor_password='xxx';

上面這兩句是修改變數的方式還可以在main庫下面用sql語句方式修改

UPDATE global_variables SET variable_value='monitor'
 WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='xxx'
 WHERE variable_name='mysql-monitor_password';

修改後，儲存到runtime和disk

load mysql variables to runtime;
save mysql variables to disk;

8.建立 ProxySQL 使用者端使用者

ProxySQL 必須具有可以存取後端節點的使用者。要新增使用者需要在mysql_users表中插入，首先在後端伺服器建立使用者，主要根據實際業務需求建立使用者

show create table mysql_usersG;

設定mysql_users 表，將proxysql使用者新增到該表中。

-- 這個使用者預設指向寫組 2
insert into mysql_users (username,password,default_hostgroup) values ('proxysql','xxxx',2);
load mysql users to runtime;
save mysql users to disk;

重要欄位說明

username                 # 前端應用連線ProxySQL，以及 ProxySQL 將 SQL 語句路由給後端 MySQL 所使用的使用者名稱。
password                 # 對應的密碼。可以是明文密碼也可以是 hash 密碼。如果使用hash密碼，先在後端某個 MySQL 節點上執行 select password(PASSWORD)，然後將加密結果複製到該欄位。
default_hostgroup        # 該使用者預設的路由目標。例如，指定 root 使用者的該欄位值為 1 時，則使用 root 使用者傳送的 SQL 語句預設將路由到 hostgroup_id=1 組中的某個節點上。
active: 1                # 1 代表使用者生效，0 代表不生效
default_schema           # 登入後端預設連線的資料庫，為 NULL 時則由全域性變數 mysql-default_schema 決定
transaction_persistent   # 值為 1 時，表示事務持久化：當某連線使用該使用者開啟了一個事務後，那麼在事務提交/回滾之前，所有的語句都路由到同一個組中，避免語句分散到不同組

9.將叢集節點新增到ProxySQL

ProxySQL 使用 hostgroups 設定後端節點的群組。就可以通過將不同型別的流量路由到不同的組來平衡群集中的負載。可以通過多種方式設定主機組（例如主從，讀寫組），每個後端節點可以設定在多個組中。在 ProxySQL 中新增後端 MySQL 叢集節點，需要在mysql_servers表中插入相應的記錄，其中hostgroup_id為2是寫組、3是讀組：

INSERT INTO mysql_servers ( hostgroup_id, hostname, PORT, weight ) values (2,'42.192.53.88',3306,100),(3,'39.107.143.191',3306,100),(3,'120.26.161.80',3306,100);
注：嚴格需要寫明comment
load mysql servers to runtime;
save mysql servers to disk;

新增了節點，三臺機器都是online 狀態

對心跳資訊的監控（ping指標的監控）：

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

設定後如果connect_error的結果為NULL則表示正常。

檢視控制超時和檢查間隔時間的全域性變數：

select * from global_variables where variable_name like '%monitor_galera%';

10.設定 ProxySQL 上關於 Galera 叢集的規則

用於定義 galera 叢集的mysql_galera_hostgroups表的定義：

使用show create table mysql_galera_hostgroupsG命令檢視表結構。

writer_hostgroup          # 定義寫入主機組的ID
backup_writer_hostgroup   # 定義備份寫入組的 ID，如果是多主模式執行，寫入節點數量大於 max_writers，權重低的主機就被放入該值定義的組中。
reader_hostgroup          # 定義讀取主機組ID。
offline_hostgroup         # 當監控主機處於離線狀態時，就放入 offline_hostgroup 定義的組中。
active                    # 啟用設定（0 或 1）。
max_writers               # 限制寫入主機數，大於此值就被放入 backup_writer_hostgroup 定義的組中。
writer_is_also_reader     # 啟用後，寫入組的節點也屬於讀取主機組。（0 或 1）
max_transactions_behind   # 防止讀取的後端主機有延遲資料，延遲事務數超過此值就避開此節點。延遲事務數由 wsrep_local_recv_queue 查詢。
comment                   # 備註資訊。

官方檔案：https://github.com/sysown/proxysql/wiki/Main-(runtime)

新增galera伺服器設定：

INSERT INTO mysql_galera_hostgroups ( writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind )
VALUES
    ( 2, 4, 3, 1, 1, 1, 0, 100 );

-- 載入設定和持久化：
load mysql servers to runtime;
save mysql servers to disk;

檢視設定

select * from mysql_servers;
select * from runtime_mysql_servers;
select * from mysql_galera_hostgroups;

統計MySQL連線池資訊：

select * from stats.stats_mysql_connection_pool;

11.設定路由規則

路由規則官網檔案：https://github.com/sysown/proxysql/wiki/Main-(runtime)

設定讀寫分離，就是設定ProxySQL 路由規則，ProxySQL 的路由規則非常靈活，可以基於使用者，基於schema，以及單個sql語句實現路由規則客製化。
注意： 我這只是試驗，只是設定了幾個簡單的路由規則，實際情況設定路由規則，不應該是就根據所謂的讀、寫操作來進行讀寫分離，而是從收集（慢紀錄檔）的各項指標找出壓力大，執行頻繁的語句單獨寫規則，做快取等等。比如先在測試幾個核心sql語句，分析效能提升的百分比，在逐漸慢慢完善路由規則。

和查詢規則有關的表有兩個：mysql_query_rules和mysql_query_rules_fast_routing
表mysql_query_rules_fast_routing是mysql_query_rules的擴充套件，並在以後評估快速路由策略和屬性（僅在ProxySQL 1.4.7+中可用）。

介紹一下改表mysql_query_rules的幾個欄位：

rule_id                 # 規則ID
active                  # 啟用此條規則
match_digest            # SQL匹配正則
destination_hostgroup   # 匹配的規則路由到此主機組
apply                   # 設定為1表示規則不匹配後繼續匹配其他規則。

這裡我建立兩個規則：

1、把所有以select 開頭的語句全部分配到讀組中，讀組編號是20

2、把 select … for update 語句，這是一個特殊的select語句，會產生一個寫鎖(排他鎖)，把他分到編號為10 的寫組中，其他所有操作都會預設路由到寫組中

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

load mysql query rules to runtime;
save mysql query rules to disk;

檢視叢集中每個節點的狀態：

select * from mysql_server_galera_log order by time_start_us desc limit 3;

select … for update規則的rule_id必須要小於普通的select規則的rule_id，因為ProxySQL是根據rule_id的順序進行規則匹配的。

CREATE USER 'proxysql'@'%' IDENTIFIED BY 'xxxx';
GRANT USAGE ON *.* TO 'proxysql'@'%';
FLUSH PRIVILEGES;

測試讀寫分離:

我們向測試資料庫表中插入一條資料試試：

成功插入

我們再進行查詢看看到底使用了那個節點：

如果想在 ProxySQL 中檢視SQL請求路由資訊stats_mysql_query_digest

select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

從結果來看跟我們預期的一樣，查詢跟插入分別使用了讀寫組中的節點

count_start 統計sql 語句次數，可以分析哪些 sql ，頻繁執行

讀寫分離設定成功後，還可以調權重，比如讓某臺機器承受更多的讀操作

update mysql_servers set weight=10 hostname='xxxx'；
load mysql servers to runtime;
save mysql servers to disk;

總結

在實際生產中我們這樣簡單的環境是遠遠不夠的，起碼需要ProxySQL Cluster雙節點叢集環境，兩個節點間資料自動同步。最後就可以結合Keepalived，利用VIP資源漂移來實現ProxySQL雙節點的無感知故障切換，即對外提供一個統一的vip地址，並且在keepalived.conf檔案中設定proxysql服務的監控指令碼，當宕機或proxysql服務掛掉時就將vip資源漂移到另一個正常的節點上，從而使proxysql的代理層持續無感應地提供服務。來保證我們服務的高可用性。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。