前言

本篇主要用于記錄mariaDb環(huán)境下Galera Cluster模式集群環(huán)境的搭建過程，只做演示中間參數(shù)可能會有不當?shù)胤叫枳孕姓{整。

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

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

一、Galera Cluster

MariaDB Galera Cluster 是一套在 mysql innodb 存儲引擎上面實現(xiàn)multi-master及數(shù)據(jù)實時同步的系統(tǒng)架構，業(yè)務層面無需做讀寫分離工作，數(shù)據(jù)庫讀寫壓力都能按照既定的規(guī)則分發(fā)到各個節(jié)點上去。在數(shù)據(jù)方面完全兼容 MariaDB 和 MySQL。

Galera Cluster 與傳統(tǒng)的復制方式不同，不通過I/O_thread和sql_thread進行同步，而是在更底層通過wsrep實現(xiàn)文件系統(tǒng)級別的同步，可以做到幾乎實時同步。

特點：

• 功能特性
• 同步復制 Synchronous replication
• Active-active multi-master 拓撲邏輯
• 可對集群中任一節(jié)點進行數(shù)據(jù)讀寫
• 自動成員控制，故障節(jié)點自動從集群中移除
• 自動節(jié)點加入
• 真正并行的復制，基于行級
• 直接客戶端連接，原生的 MySQL 接口
• 每個節(jié)點都包含完整的數(shù)據(jù)副本
• 多臺數(shù)據(jù)庫中數(shù)據(jù)同步由 wsrep 接口實現(xiàn)

局限性：

• 目前的復制僅僅支持InnoDB存儲引擎,任何寫入其他引擎的表，包括mysql.*表將不會復制,但是DDL語句會被復制的,因此創(chuàng)建用戶將會被復制,但是insert into mysql.user…將不會被復制的.
• DELETE操作不支持沒有主鍵的表,沒有主鍵的表在不同的節(jié)點順序將不同,如果執(zhí)行SELECT…LIMIT… 將出現(xiàn)不同的結果集.
• 在多主環(huán)境下LOCK/UNLOCK TABLES不支持,以及鎖函數(shù)GET_LOCK(), RELEASE_LOCK()…
• 查詢日志不能保存在表中。如果開啟查詢日志，只能保存到文件中。
• 允許最大的事務大小由wsrep_max_ws_rows和wsrep_max_ws_size定義。任何大型操作將被拒絕。如大型的LOAD DATA操作。
• 由于集群是樂觀的并發(fā)控制，事務commit可能在該階段中止。如果有兩個事務向在集群中不同的節(jié)點向同一行寫入并提交，失敗的節(jié)點將中止。對 于集群級別的中止，集群返回死鎖錯誤代碼(Error: 1213 SQLSTATE:
• 40001 (ER_LOCK_DEADLOCK)).
• XA事務不支持，由于在提交上可能回滾。
• 整個集群的寫入吞吐量是由最弱的節(jié)點限制，如果有一個節(jié)點變得緩慢，那么整個集群將是緩慢的。為了穩(wěn)定的高性能要求，所有的節(jié)點應使用統(tǒng)一的硬件。
• 集群節(jié)點建議最少3個。
• 如果DDL語句有問題將破壞集群。

注：Galera集群至少需要三個節(jié)點

二、基礎環(huán)境搭建

1.安裝docker與docker-compose

這里默認都已經安裝好，下面簡單給出安裝需要的命令。

下面命令是基于centos8.0以上環(huán)境

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架構的系統(tǒng)就不能采取常規(guī)方式安裝。樓主之前買了華為云鯤鵬務器因為是arm架構自己沒有注意在此采坑解決了很久，最后還是通過給華為兄弟提交工單才解決。

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

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

x86系統(tǒng)有很多方式 通過curl weget下載安裝包都可以，不過github經常會丟包很慢建議使用國內鏡像。

下面給出centos8下常規(guī)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的方式進行配置但是由于容器的很多局限性普通數(shù)據(jù)庫放在docker并不合適會有很多弊端后面改變了主意，索性將這部分留下當做一個簡單的安裝教程。

2. 常規(guī)環(huán)境搭建

想要安裝最新版本，需要添加自己的repo倉庫配置。這里使用國內清華提供的鏡像速度更快一點。/etc/yum.repos.d下創(chuàng)建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命令安裝，這里我們會發(fā)現(xiàn)鏡像只給我們安裝了mariadb-client客戶端。

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

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

3.啟動初始化并創(chuàng)建授權用戶

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;   刷新權限

在成功啟動后我們需要創(chuàng)建一個用于集群間遠程訪問的用戶這個用戶將在后面你的集群配置中用到，用來建立互相訪問驗證

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

角色添加成功

后面我們要用到MariaDB-backup需要單獨安裝，MariaDB-backup相對于默認的rsync和 mysqldump, xtrabackup, xtrabackup-v2等方案都有長足的優(yōu)勢這也是官方目前推薦的方式。

是在mysql的Percona Xtrabackup 2.3.8 備份工具進行的升級與改進。mariadb-backup是官方目前最推崇的同步解決方案，如果為了追求穩(wěn)定也可以直接使用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

三、加入配置參數(shù)啟動集群

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

下面是我測試用的簡單配置文件，單獨創(chuàng)建一個用于glera集群的配置文件置于/etc/my.cnf.d中即可

這里官方給出了幾個必填參數(shù)：

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

這里需要注意的一點是在設定了binlog_format=ROW后必須要設置log-bin否則系統(tǒng)會發(fā)出警告。

配置中如果不需要自定義端口的話4567可以省略。其余配置自行根據(jù)自己機器情況配置

[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.重新啟動集群節(jié)點

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

第一個節(jié)點
sudo galera_new_cluster
其余節(jié)點
systemctl restart mariadb 

如果是云服務器默認情況下確保自己的如下端口都已經開放，涉及到的端口:

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

到這里我們先看下集群狀態(tài)看看當前這一個節(jié)點是否已經配置成功

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

可以看到當前節(jié)點已經成功加入集群，我們重復前面的工作在另外兩臺服務器上配置相應的環(huán)境。

這里如果只有一臺主機的話就按多實例的方式來配置

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

踩坑規(guī)避：

1.需要修改open-file-limit參數(shù)，很多時候默認的最大可打開文件數(shù)是不夠用的這個時候會直接導致啟動失敗。首先需要查看linux本身的配置情況，更改/etc/security/limits.conf :這個文件規(guī)定了上限。修改后再去修改/usr/lib/systemd/system下兩個mariadb.service和mairadb@.service中的配置信息即可。直接在mysql配置文件中修改你會發(fā)現(xiàn)并不起作用，因為這里的配置參數(shù)優(yōu)先級更高覆蓋掉了你的配置

2.這里再貼出一個我本人親自經歷的天坑，很多時候集群武器第一個成功立馬啟動，但是第二個節(jié)點開始glera狀態(tài)一切正常查看節(jié)點信息也成功加入到集群中，但是mariadb服務無法啟動netsate -anpt查看3306端口并沒有啟動。因為我所有數(shù)據(jù)庫都是新創(chuàng)建的所以不存在同步數(shù)據(jù)需要很久的情況，但是再等待了十來分鐘后依然會發(fā)現(xiàn)mysql無法連接沒有啟動，glera集群一切正常。這個時候查詢日志也找不到什么關鍵錯誤信息誤以為是哪里出問題了就被我手動關閉準備重新構建，但是萬萬忽略了機器本身的性能。因為我測試使用的是3臺最垃圾的1核2g的學生機，忽略了這個問題導致誤判啟動時間。遇到3306沒起來glera正常又沒有報錯的情況只需要耐心多等待一會即可。同時這里還有一個問題因為我3臺測試機有兩臺是centos8，一臺centos7。兩個系統(tǒng)也有明顯的差別，7比8啟動快了很多。這里的原因需要打個問號可能是多方原因。如果需要同步的數(shù)據(jù)量很大sst超時，則記得在配置中修改超時時間

四、 測試

show status like ‘%wsrep%’;

這里我們新建一個數(shù)據(jù)庫看一下是否能同步成功：

當你測試數(shù)據(jù)能真正同步成功這一步才算大功告成。搭建期間基本把能踩的坑踩了一遍很痛苦，后續(xù)有遇到問題的朋友可以留言一起探討。下一步我們就該為集群搭建負載均很的配置了。

五、ProxySql

1.安裝：

這里我直接使用了我當前環(huán)境提供的默認版本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的目錄結構：

數(shù)據(jù)目錄：/var/lib/proxysql/

• proxysql.db：配置數(shù)據(jù)存儲文件，后端數(shù)據(jù)庫的賬號、密碼、路由等存儲在這個數(shù)據(jù)庫里面。
• proxysql.log：此文件是日志文件。
• proxysql.pid：此文件是是進程pid文件。

配置文件目錄：/etc/proxysql.cnf，是一些靜態(tài)配置項，用來配置一些啟動選項。此配置文件只在第一次啟動的時候讀取進行初始化，后面只讀取proxysql.db文件。

啟動腳本：/etc/init.d/proxysql

proxysql 的默認管理端口是 6032，客戶端服務端口是 6033。默認的用戶名密碼都是 admin，可以在配置文件里看到。

ProxySQL默認有五個數(shù)據(jù)庫：

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

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

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

main:內存配置數(shù)據(jù)庫，表里存放后端 db 實例、用戶驗證、路由規(guī)則等信息。表名以runtime_開頭的表示 proxysql 當前運行的配置內容，不能通過 dml 語句修改，只能修改對應的不以runtime_ 開頭的（在內存）里的表，然后LOAD使其生效， SAVE使其存到硬盤以供下次重啟加載。main 庫中有如下信息：

庫下的主要表：

• mysql_servers: 后端可以連接 MySQL 服務器的列表
• mysql_users: 配置后端數(shù)據(jù)庫的賬號和監(jiān)控的賬號。
• mysql_query_rules: 指定 Query 路由到后端不同服務器的規(guī)則列表。

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

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

• disk ：是持久化到硬盤的配置庫，對應/var/lib/proxysql/proxysql.db文件，也就是 sqlite 的數(shù)據(jù)文件。
• stats：是 proxysql 運行抓取的統(tǒng)計信息庫，包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時間等等。
• 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 上創(chuàng)建 ProxySQL 的監(jiān)控賬戶和對外訪問賬戶

#proxysql 的監(jiān)控賬戶
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.配置信息介紹

配置結構如下：

整套配置系統(tǒng)分為三層：頂層為 RUNTIME ,中間層為 MEMORY , 底層也就是持久層 DISK 和 CONFIG FILE 。

• RUNTIME：代表 ProxySQL 當前生效的正在使用的配置，無法直接修改這里的配置，必須要從下一層 “load” 進來。
• MEMORY：MEMORY 層上面連接 RUNTIME 層，下面連接持久層。這層可以正常操作 ProxySQL 配置，隨便修改，不會影響生產環(huán)境。修改一個配置一般都是現(xiàn)在 MEMORY 層完成的，確認正常之后在加載達到 RUNTIME 和 持久化的磁盤上。

DISK 和 CONFIG FILE：持久化配置信息，重啟后內存中的配置信息會丟失，所需要將配置信息保留在磁盤中。重啟時，可以從磁盤快速加載回來。

要重新配置 MySQL 用戶，可執(zhí)行下面的其中一個命令：
1、LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
 將內存數(shù)據(jù)庫中的配置加載到 runtime 數(shù)據(jù)結構，反之亦然。
2、SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
 將 MySQL 用戶從 runtime 持久化到內存數(shù)據(jù)庫。
3、LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
 從磁盤數(shù)據(jù)庫中加載 MySQL 用戶到內存數(shù)據(jù)庫中。
4、SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
 將內存數(shù)據(jù)庫中的 MySQL 用戶持久化到磁盤數(shù)據(jù)庫中。
5、LOAD MYSQL USERS FROM CONFIG
 從配置文件中加載 MySQL 用戶到內存數(shù)據(jù)庫中。


要處理 MySQL server：
1、LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
 將 MySQL server 從內存數(shù)據(jù)庫中加載到 runtime。
2、SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
 將 MySQL server 從 runtime 持久化到內存數(shù)據(jù)庫中。
3、LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
 從磁盤數(shù)據(jù)庫中加載 MySQL server 到內存數(shù)據(jù)庫。
4、SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
 從內存數(shù)據(jù)庫中將 MySQL server 持久化到磁盤數(shù)據(jù)庫中。
5、LOAD MYSQL SERVERS FROM CONFIG
 從配置文件中加載 MySQL server 到內存數(shù)據(jù)庫中


要處理 MySQL 的查詢規(guī)則(mysql query rules)：
1、 LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
 將 MySQL query rules 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
2、 SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
 將 MySQL query rules 從 runtime 數(shù)據(jù)結構中持久化到內存數(shù)據(jù)庫。
3、 LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
 從磁盤數(shù)據(jù)庫中加載 MySQL query rules 到內存數(shù)據(jù)庫中。
4、 SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
 將 MySQL query rules 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫中。
5、 LOAD MYSQL QUERY RULES FROM CONFIG
 從配置文件中加載 MySQL query rules 到內存數(shù)據(jù)庫中。
　　

要處理 MySQL 變量(MySQL variables)：
1、 LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
 將 MySQL variables 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
2、 SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
 將 MySQL variables 從 runtime 數(shù)據(jù)結構中持久化到內存數(shù)據(jù)中。
3、 LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
 從磁盤數(shù)據(jù)庫中加載 MySQL variables 到內存數(shù)據(jù)庫中。
4、 SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
 將 MySQL variables 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫中。
5、 LOAD MYSQL VARIABLES FROM CONFIG
 從配置文件中加載 MySQL variables 到內存數(shù)據(jù)庫中。


要處理管理變量(admin variables)：
1、 LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
 將 admin variables 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
2、 SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
 將 admin variables 從 runtime 持久化到內存數(shù)據(jù)庫中。
3、 LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
 從磁盤數(shù)據(jù)庫中加載 admin variables 到內存數(shù)據(jù)庫中。
4、 SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
 將 admin variables 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫。
5、 LOAD ADMIN VARIABLES FROM CONFIG
 從配置文件中加載 admin variables 到內存數(shù)據(jù)庫中。

ProxySQL配置文件的修改流程一般是：

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

2、以后配置修改MEMORY中的表，然后加載到RUNTIME并保存到DISK持久化。

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

LOAD MYSQL USERS TO RUNTIME;  將內存數(shù)據(jù)庫中的配置加載到 runtime 數(shù)據(jù)結構
SAVE MYSQL USERS TO DISK;   將內存數(shù)據(jù)庫中的 MySQL 用戶持久化到磁盤數(shù)據(jù)庫中。
LOAD MYSQL SERVERS TO RUNTIME;  將 MySQL server 從內存數(shù)據(jù)庫中加載到 runtime。
SAVE MYSQL SERVERS TO DISK;   從內存數(shù)據(jù)庫中將 MySQL server 持久化到磁盤數(shù)據(jù)庫中。
LOAD MYSQL QUERY RULES TO RUNTIME;  將 MySQL query rules 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
SAVE MYSQL QUERY RULES TO DISK;  將 MySQL query rules 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫中。
LOAD MYSQL VARIABLES TO RUNTIME;  將 MySQL variables 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
SAVE MYSQL VARIABLES TO DISK;   將 MySQL variables 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫中。
LOAD ADMIN VARIABLES TO RUNTIME;   將 admin variables 從內存數(shù)據(jù)庫加載到 runtime 數(shù)據(jù)結構。
SAVE ADMIN VARIABLES TO DISK;  將 admin variables 從內存數(shù)據(jù)庫中持久化到磁盤數(shù)據(jù)庫。
注意：只有加載到 runtime 狀態(tài)時才會去做最后的有效性驗證。在保存到內存數(shù)據(jù)庫或持久化到磁盤上時，都不會發(fā)生任何警告或錯誤。當
加載到 runtime 時，如果出現(xiàn)錯誤，將恢復為之前保存得狀態(tài)，這時可以去檢查錯誤日志?！　?

disk and config file 持久化配置文件

disk -> 是sqlite3 數(shù)據(jù)庫 ，默認位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)

config file 是一個傳統(tǒng)配置文件：一般不更改

在內存中動態(tài)更改配置，如果重啟，沒進行持久化(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。

關于傳統(tǒng)的配置文件：

傳統(tǒng)配置文件默認路徑為/etc/proxysql.cnf，也可以在二進制程序proxysql上使用-c或–config來手動指定配置文件。

默認情況下：幾乎不需要手動去配置proxysql.cnf。端口號，管理proxysql用戶名，密碼，可以在這里修改。

6.實際應用中修改配置

1.設置讀寫服務器mariadb參數(shù)

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

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

7.創(chuàng)建 ProxySQL 監(jiān)控用戶

要在 ProxySQL 中啟用對后端節(jié)點的監(jiān)視，需要創(chuàng)建一個具有USAGE權限的用戶，并在 ProxySQL 中配置該用戶。為 ProxySQL 配置監(jiān)控賬號：

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.創(chuàng)建 ProxySQL 客戶端用戶

ProxySQL 必須具有可以訪問后端節(jié)點的用戶。要添加用戶需要在mysql_users表中插入，首先在后端服務器創(chuàng)建用戶，主要根據(jù)實際業(yè)務需求創(chuàng)建用戶

show create table mysql_users\G;

配置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 節(jié)點上執(zhí)行 select password(PASSWORD)，然后將加密結果復制到該字段。
default_hostgroup        # 該用戶默認的路由目標。例如，指定 root 用戶的該字段值為 1 時，則使用 root 用戶發(fā)送的 SQL 語句默認將路由到 hostgroup_id=1 組中的某個節(jié)點上。
active: 1                # 1 代表用戶生效，0 代表不生效
default_schema           # 登錄后端默認連接的數(shù)據(jù)庫，為 NULL 時則由全局變量 mysql-default_schema 決定
transaction_persistent   # 值為 1 時，表示事務持久化：當某連接使用該用戶開啟了一個事務后，那么在事務提交/回滾之前，所有的語句都路由到同一個組中，避免語句分散到不同組

9.將集群節(jié)點添加到ProxySQL

ProxySQL 使用 hostgroups 配置后端節(jié)點的組群。就可以通過將不同類型的流量路由到不同的組來平衡群集中的負載。可以通過多種方式配置主機組（例如主從，讀寫組），每個后端節(jié)點可以配置在多個組中。在 ProxySQL 中添加后端 MySQL 集群節(jié)點，需要在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;

添加了節(jié)點，三臺機器都是online 狀態(tài)

對心跳信息的監(jiān)控（ping指標的監(jiān)控）：

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 集群的規(guī)則

用于定義 galera 集群的mysql_galera_hostgroups表的定義：

使用show create table mysql_galera_hostgroups\G命令查看表結構。

writer_hostgroup          # 定義寫入主機組的ID
backup_writer_hostgroup   # 定義備份寫入組的 ID，如果是多主模式運行，寫入節(jié)點數(shù)量大于 max_writers，權重低的主機就被放入該值定義的組中。
reader_hostgroup          # 定義讀取主機組ID。
offline_hostgroup         # 當監(jiān)控主機處于脫機狀態(tài)時，就放入 offline_hostgroup 定義的組中。
active                    # 啟用配置（0 或 1）。
max_writers               # 限制寫入主機數(shù)，大于此值就被放入 backup_writer_hostgroup 定義的組中。
writer_is_also_reader     # 啟用后，寫入組的節(jié)點也屬于讀取主機組。（0 或 1）
max_transactions_behind   # 防止讀取的后端主機有延遲數(shù)據(jù)，延遲事務數(shù)超過此值就避開此節(jié)點。延遲事務數(shù)由 wsrep_local_recv_queue 查詢。
comment                   # 備注信息。

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

添加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;

統(tǒng)計MySQL連接池信息：

select * from stats.stats_mysql_connection_pool;

11.配置路由規(guī)則

路由規(guī)則官網文檔：https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules

配置讀寫分離，就是配置ProxySQL 路由規(guī)則，ProxySQL 的路由規(guī)則非常靈活，可以基于用戶，基于schema，以及單個sql語句實現(xiàn)路由規(guī)則定制。
注意： 我這只是試驗，只是配置了幾個簡單的路由規(guī)則，實際情況配置路由規(guī)則，不應該是就根據(jù)所謂的讀、寫操作來進行讀寫分離，而是從收集（慢日志）的各項指標找出壓力大，執(zhí)行頻繁的語句單獨寫規(guī)則，做緩存等等。比如 先在測試幾個核心sql語句，分析性能提升的百分比，在逐漸慢慢完善路由規(guī)則。

和查詢規(guī)則有關的表有兩個：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                 # 規(guī)則ID
active                  # 激活此條規(guī)則
match_digest            # SQL匹配正則
destination_hostgroup   # 匹配的規(guī)則路由到此主機組
apply                   # 配置為1表示規(guī)則不匹配后繼續(xù)匹配其他規(guī)則。

這里我創(chuàng)建兩個規(guī)則：

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;

查看集群中每個節(jié)點的狀態(tài)：

select * from mysql_server_galera_log order by time_start_us desc limit 3;

select … for update規(guī)則的rule_id必須要小于普通的select規(guī)則的rule_id，因為ProxySQL是根據(jù)rule_id的順序進行規(guī)則匹配的。

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

測試讀寫分離:

我們向測試數(shù)據(jù)庫表中插入一條數(shù)據(jù)試試：

成功插入

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

如果想在 ProxySQL 中查看SQL請求路由信息stats_mysql_query_digest

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

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

count_start 統(tǒng)計sql 語句次數(shù)，可以分析哪些 sql ，頻繁執(zhí)行

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

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

總結

在實際生產中我們這樣簡單的環(huán)境是遠遠不夠的，起碼需要ProxySQL Cluster雙節(jié)點集群環(huán)境，兩個節(jié)點間數(shù)據(jù)自動同步。最后就可以結合Keepalived，利用VIP資源漂移來實現(xiàn)ProxySQL雙節(jié)點的無感知故障切換，即對外提供一個統(tǒng)一的vip地址，并且在keepalived.conf文件中配置proxysql服務的監(jiān)控腳本，當宕機或proxysql服務掛掉時就將vip資源漂移到另一個正常的節(jié)點上，從而使proxysql的代理層持續(xù)無感應地提供服務。來保證我們服務的高可用性。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論