mariadb集群搭建---Galera Cluster+ProxySQL教程

更新時(shí)間：2023年03月16日 10:56:20 作者：chihaihai

這篇文章主要介紹了mariadb集群搭建---Galera Cluster+ProxySQL教程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

前言

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

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

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

一、Galera Cluster

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

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

特點(diǎn)：

功能特性
同步復(fù)制 Synchronous replication
Active-active multi-master 拓?fù)溥壿?/li>
可對(duì)集群中任一節(jié)點(diǎn)進(jìn)行數(shù)據(jù)讀寫(xiě)
自動(dòng)成員控制，故障節(jié)點(diǎn)自動(dòng)從集群中移除
自動(dòng)節(jié)點(diǎn)加入
真正并行的復(fù)制，基于行級(jí)
直接客戶端連接，原生的 MySQL 接口
每個(gè)節(jié)點(diǎn)都包含完整的數(shù)據(jù)副本
多臺(tái)數(shù)據(jù)庫(kù)中數(shù)據(jù)同步由 wsrep 接口實(shí)現(xiàn)

局限性：

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

注：Galera集群至少需要三個(gè)節(jié)點(diǎn)

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

1.安裝docker與docker-compose

這里默認(rèn)都已經(jīng)安裝好，下面簡(jiǎn)單給出安裝需要的命令。

下面命令是基于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下增加國(guó)內(nèi)鏡像源，可以自行去阿里注冊(cè)申請(qǐng)。目錄下添加daemon.json鏡像源配置后重啟docker
systemctl restart  docker

docker-compose：

這里有一個(gè)比較低概率的小坑提醒大伙，如果你使用的是arm架構(gòu)的系統(tǒng)就不能采取常規(guī)方式安裝。樓主之前買了華為云鯤鵬務(wù)器因?yàn)槭莂rm架構(gòu)自己沒(méi)有注意在此采坑解決了很久，最后還是通過(guò)給華為兄弟提交工單才解決。

下面給出解決問(wèn)題的地址。

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

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

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

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

想要安裝最新版本，需要添加自己的repo倉(cāng)庫(kù)配置。這里使用國(guó)內(nèi)清華提供的鏡像速度更快一點(diǎn)。/etc/yum.repos.d下創(chuàng)建mariadb.repo文件。想要那個(gè)版本自己去更改即可，需要注意的就是10.4后glera文件的路徑有所不同由原來(lái)的的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命令安裝，這里我們會(huì)發(fā)現(xiàn)鏡像只給我們安裝了mariadb-client客戶端。

后面我在官網(wǎng)找到了答案，還需要單獨(dú)安裝服務(wù)端

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

3.啟動(dòng)初始化并創(chuàng)建授權(quán)用戶

systemctl start mariadb   #啟動(dòng)mariadb
systemctl enable mariadb  #設(shè)置開(kāi)機(jī)自啟動(dòng)
systemctl stop mariadb    #停止MariaDB
systemctl restart mariadb #重啟MariaDB
mysql_secure_installation #設(shè)置root密碼等相關(guān)
mysql -uroot -p           #測(cè)試登錄   
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '你的密碼' WITH GRANT OPTION; 允許遠(yuǎn)程訪問(wèn)
flush privileges;   刷新權(quán)限

在成功啟動(dòng)后我們需要?jiǎng)?chuàng)建一個(gè)用于集群間遠(yuǎn)程訪問(wèn)的用戶這個(gè)用戶將在后面你的集群配置中用到，用來(lái)建立互相訪問(wèn)驗(yàn)證

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

角色添加成功

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

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

詳情參閱官網(wǎng)：

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ù)啟動(dòng)集群

1.添加配置

10.5.8版本目錄結(jié)構(gòu)如下：

官方鏡像配置文件目錄結(jié)構(gòu)：/etc/mysql
/etc/mysql
|-- conf.d
|   |-- docker.cnf
|   |-- mysqld_safe_syslog.cnf
|-- debian-start
|-- debian.cnf
|-- mariadb.cnf
|-- mariadb.conf.d
|-- my.cnf

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

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

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

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

配置中如果不需要自定義端口的話4567可以省略。其余配置自行根據(jù)自己機(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.重新啟動(dòng)集群節(jié)點(diǎn)

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

第一個(gè)節(jié)點(diǎn)
sudo galera_new_cluster
其余節(jié)點(diǎn)
systemctl restart mariadb

如果是云服務(wù)器默認(rèn)情況下確保自己的如下端口都已經(jīng)開(kāi)放，涉及到的端口:

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

到這里我們先看下集群狀態(tài)看看當(dāng)前這一個(gè)節(jié)點(diǎn)是否已經(jīng)配置成功

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

可以看到當(dāng)前節(jié)點(diǎn)已經(jīng)成功加入集群，我們重復(fù)前面的工作在另外兩臺(tái)服務(wù)器上配置相應(yīng)的環(huán)境。

這里如果只有一臺(tái)主機(jī)的話就按多實(shí)例的方式來(lái)配置

緊接著剩余機(jī)器重復(fù)上述步驟即可，但是切記后面不再需要啟動(dòng)new-cluster。

踩坑規(guī)避：

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

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

四、測(cè)試

show status like ‘%wsrep%’;

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

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

五、ProxySql

1.安裝：

這里我直接使用了我當(dāng)前環(huán)境提供的默認(rèn)版本2.0.9，大家可以自行選擇適合的版本。

在ProxySQL V2.0.0 以上版本可以原生支持 galera 集群，不再需要 scheduler 調(diào)度程序中使用外部腳本。

yum install proxysql -y

官網(wǎng)galera中使用教程地址：https://proxysql.com/blog/effortlessly-scaling-out-galera-cluster-with-proxysql/

2.結(jié)構(gòu)

proxysql的目錄結(jié)構(gòu)：

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

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

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

啟動(dòng)腳本：/etc/init.d/proxysql

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

ProxySQL默認(rèn)有五個(gè)數(shù)據(jù)庫(kù)：

對(duì)每個(gè)庫(kù)的功能介紹如下：

main庫(kù)：
disk庫(kù)：
stats庫(kù)：
monitor庫(kù)：

可見(jiàn)有五個(gè)庫(kù)： main、disk、stats 、monitor 和 stats_history

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

庫(kù)下的主要表：

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

注：表名以 runtime_開(kāi)頭的表示 ProxySQL 當(dāng)前運(yùn)行的配置內(nèi)容，不能通過(guò) DML 語(yǔ)句修改。

只能修改對(duì)應(yīng)的不以 runtime 開(kāi)頭的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盤(pán)以供下次重啟加載。

disk ：是持久化到硬盤(pán)的配置庫(kù)，對(duì)應(yīng)/var/lib/proxysql/proxysql.db文件，也就是 sqlite 的數(shù)據(jù)文件。
stats：是 proxysql 運(yùn)行抓取的統(tǒng)計(jì)信息庫(kù)，包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時(shí)間等等。
monitor：存儲(chǔ) monitor 模塊收集的信息，主要是對(duì)后端 db 的健康、延遲檢查。
stats_history: 這個(gè)庫(kù)是 ProxySQL 收集的有關(guān)其內(nèi)部功能的歷史指標(biāo)

3.啟動(dòng)：

systemctl start proxysql.service
netstat -anlp | grep proxysql        查看下是否啟動(dòng)成功

4.連接

使用MySQL客戶端連接管理：

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

但是這個(gè)默認(rèn)的用戶只能在本地使用。如果想要遠(yuǎn)程連接到ProxySQL，例如用windows上的navicat連接Linux上的ProxySQL管理接口，必須自定義一個(gè)管理員賬戶。

配置 ProxySQL 所需賬戶：

在 Master 的MySQL 上創(chuàng)建 ProxySQL 的監(jiān)控賬戶和對(duì)外訪問(wèn)賬戶

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

#proxysql 的對(duì)外訪問(wèn)賬戶
create user 'proxysql'@'xxx' identified by 'xxxx';
grant all privileges on *.* to 'xxx'@'xxx' with grant option;

5.配置信息介紹

配置結(jié)構(gòu)如下：

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

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

DISK 和 CONFIG FILE：持久化配置信息，重啟后內(nèi)存中的配置信息會(huì)丟失，所需要將配置信息保留在磁盤(pán)中。重啟時(shí)，可以從磁盤(pán)快速加載回來(lái)。

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


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


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

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


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

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

1、第一次啟動(dòng)時(shí)候，修改必要的CONFIG FILE配置。

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

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

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

disk and config file 持久化配置文件

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

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

在內(nèi)存中動(dòng)態(tài)更改配置，如果重啟，沒(méi)進(jìn)行持久化(save) 則會(huì)丟失。

三則之間關(guān)系：

proxysql 啟動(dòng)時(shí)，首先去找/etc/proxysql.cnf 找到它的datadir,如果datadir下有proxysql.db 就加載proxysql.db的配置

如果啟動(dòng)proxysql時(shí)帶有–init標(biāo)志，會(huì)用/etc/proxsql.cnf的配置，把Runtime，disk全部初始化一下。

再調(diào)用時(shí)調(diào)用–reload 會(huì)把/etc/proxysql.cnf 和disk 中配置進(jìn)行合并。如果沖突需要用戶干預(yù)。disk會(huì)覆蓋config file。

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

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

默認(rèn)情況下：幾乎不需要手動(dòng)去配置proxysql.cnf。端口號(hào)，管理proxysql用戶名，密碼，可以在這里修改。

6.實(shí)際應(yīng)用中修改配置

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

這里由于我只啟動(dòng)了三個(gè)節(jié)點(diǎn)的原因，所以測(cè)試環(huán)境直接配置為兩主一從，實(shí)際生產(chǎn)環(huán)境下配置為兩主兩從會(huì)更好點(diǎn)增加節(jié)點(diǎn)容錯(cuò)性。首先需要確定mysql讀服務(wù)器已進(jìn)行相應(yīng)配置，對(duì)其中兩個(gè)讀節(jié)點(diǎn)進(jìn)行設(shè)置為1。寫(xiě)設(shè)置為0

set global read_only=1;  只讀
set global read_only=0;  讀寫(xiě)

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

要在 ProxySQL 中啟用對(duì)后端節(jié)點(diǎn)的監(jiān)視，需要?jiǎng)?chuàng)建一個(gè)具有USAGE權(quán)限的用戶，并在 ProxySQL 中配置該用戶。為 ProxySQL 配置監(jiān)控賬號(hào)：

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

上面這兩句是修改變量的方式還可以在main庫(kù)下面用sql語(yǔ)句方式修改

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 必須具有可以訪問(wèn)后端節(jié)點(diǎn)的用戶。要添加用戶需要在mysql_users表中插入，首先在后端服務(wù)器創(chuàng)建用戶，主要根據(jù)實(shí)際業(yè)務(wù)需求創(chuàng)建用戶

show create table mysql_users\G;

配置mysql_users 表，將proxysql用戶添加到該表中。

-- 這個(gè)用戶默認(rèn)指向?qū)懡M 2
insert into mysql_users (username,password,default_hostgroup) values ('proxysql','xxxx',2);
load mysql users to runtime;
save mysql users to disk;

重要字段說(shuō)明

username                 # 前端應(yīng)用連接ProxySQL，以及 ProxySQL 將 SQL 語(yǔ)句路由給后端 MySQL 所使用的用戶名。
password                 # 對(duì)應(yīng)的密碼?？梢允敲魑拿艽a也可以是 hash 密碼。如果使用hash密碼，先在后端某個(gè) MySQL 節(jié)點(diǎn)上執(zhí)行 select password(PASSWORD)，然后將加密結(jié)果復(fù)制到該字段。
default_hostgroup        # 該用戶默認(rèn)的路由目標(biāo)。例如，指定 root 用戶的該字段值為 1 時(shí)，則使用 root 用戶發(fā)送的 SQL 語(yǔ)句默認(rèn)將路由到 hostgroup_id=1 組中的某個(gè)節(jié)點(diǎn)上。
active: 1                # 1 代表用戶生效，0 代表不生效
default_schema           # 登錄后端默認(rèn)連接的數(shù)據(jù)庫(kù)，為 NULL 時(shí)則由全局變量 mysql-default_schema 決定
transaction_persistent   # 值為 1 時(shí)，表示事務(wù)持久化：當(dāng)某連接使用該用戶開(kāi)啟了一個(gè)事務(wù)后，那么在事務(wù)提交/回滾之前，所有的語(yǔ)句都路由到同一個(gè)組中，避免語(yǔ)句分散到不同組

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

ProxySQL 使用 hostgroups 配置后端節(jié)點(diǎn)的組群。就可以通過(guò)將不同類型的流量路由到不同的組來(lái)平衡群集中的負(fù)載?？梢酝ㄟ^(guò)多種方式配置主機(jī)組（例如主從，讀寫(xiě)組），每個(gè)后端節(jié)點(diǎn)可以配置在多個(gè)組中。在 ProxySQL 中添加后端 MySQL 集群節(jié)點(diǎn)，需要在mysql_servers表中插入相應(yīng)的記錄，其中hostgroup_id為2是寫(xiě)組、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);
注：嚴(yán)格需要寫(xiě)明comment
load mysql servers to runtime;
save mysql servers to disk;

添加了節(jié)點(diǎn)，三臺(tái)機(jī)器都是online 狀態(tài)

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

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

配置后如果connect_error的結(jié)果為NULL則表示正常。

查看控制超時(shí)和檢查間隔時(shí)間的全局變量：

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

10.配置 ProxySQL 上關(guān)于 Galera 集群的規(guī)則

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

使用show create table mysql_galera_hostgroups\G命令查看表結(jié)構(gòu)。

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

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

添加galera服務(wù)器配置：

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)計(jì)MySQL連接池信息：

select * from stats.stats_mysql_connection_pool;

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

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

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

和查詢規(guī)則有關(guān)的表有兩個(gè)：mysql_query_rules和mysql_query_rules_fast_routing
表mysql_query_rules_fast_routing是mysql_query_rules的擴(kuò)展，并在以后評(píng)估快速路由策略和屬性（僅在ProxySQL 1.4.7+中可用）。

介紹一下改表mysql_query_rules的幾個(gè)字段：

rule_id                 # 規(guī)則ID
active                  # 激活此條規(guī)則
match_digest            # SQL匹配正則
destination_hostgroup   # 匹配的規(guī)則路由到此主機(jī)組
apply                   # 配置為1表示規(guī)則不匹配后繼續(xù)匹配其他規(guī)則。

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

1、把所有以select 開(kāi)頭的語(yǔ)句全部分配到讀組中，讀組編號(hào)是20

2、把 select … for update 語(yǔ)句，這是一個(gè)特殊的select語(yǔ)句，會(huì)產(chǎn)生一個(gè)寫(xiě)鎖(排他鎖)，把他分到編號(hào)為10 的寫(xiě)組中，其他所有操作都會(huì)默認(rèn)路由到寫(xiě)組中

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;

查看集群中每個(gè)節(jié)點(diǎn)的狀態(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，因?yàn)镻roxySQL是根據(jù)rule_id的順序進(jìn)行規(guī)則匹配的。

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

測(cè)試讀寫(xiě)分離:

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

成功插入

我們?cè)龠M(jìn)行查詢看看到底使用了那個(gè)節(jié)點(diǎn)：

如果想在 ProxySQL 中查看SQL請(qǐng)求路由信息stats_mysql_query_digest

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

從結(jié)果來(lái)看跟我們預(yù)期的一樣，查詢跟插入分別使用了讀寫(xiě)組中的節(jié)點(diǎn)

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

讀寫(xiě)分離設(shè)置成功后，還可以調(diào)權(quán)重，比如讓某臺(tái)機(jī)器承受更多的讀操作

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

總結(jié)

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

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: