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

篇主要用于記錄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以上環境

1 2 3 4 5 6

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

1 2 3 4 5 6 7 8

# 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.啟動初始化并創建授權用戶

1 2 3 4 5 6 7 8

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

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

1	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.

1	sudo yum install MariaDB-backup

三、加入配置參數啟動集群

1.添加配置

10.5.8版本目錄結構如下：

1 2 3 4 5 6 7 8 9 10

官方鏡像配置文件目錄結構：/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/#mandatory-options

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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

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

1 2 3 4

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

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

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

1 2 3 4

到這里我們先看下集群狀態看看當前這一個節點是否已經配置成功   ```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 調度程序中使用外部腳本。

1	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.啟動：

1 2	systemctl start proxysql.service netstat -anlp | grep proxysql        查看下是否啟動成功

4.連接

使用MySQL客戶端連接管理：

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

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

配置 ProxySQL 所需賬戶：

在 Master 的MySQL 上創建 ProxySQL 的監控賬戶和對外訪問賬戶

1 2 3 4 5 6 7

#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：持久化配置信息，重啟后內存中的配置信息會丟失，所需要將配置信息保留在磁盤中。重啟時，可以從磁盤快速加載回來。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

要重新配置 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，修改的配置就不會生效。

1 2 3 4 5 6 7 8 9 10 11 12

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 時，如果出現錯誤，將恢復為之前保存得狀態，這時可以去檢查錯誤日志?！　?/code>

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

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

7.創建 ProxySQL 監控用戶

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

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

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

1 2 3 4

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

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

8.創建 ProxySQL 客戶端用戶

ProxySQL 必須具有可以訪問后端節點的用戶。要添加用戶需要在mysql_users表中插入，首先在后端服務器創建用戶，主要根據實際業務需求創建用戶

1	show create table mysql_users\G;

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

1 2 3 4

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

重要字段說明

1 2 3 4 5 6

username                 # 前端應用連接ProxySQL，以及 ProxySQL 將 SQL 語句路由給后端 MySQL 所使用的用戶名。 password                 # 對應的密碼?？梢允敲魑拿艽a也可以是 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是讀組：

1 2 3 4

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指標的監控）：

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

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

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

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

10.配置 ProxySQL 上關于 Galera 集群的規則

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

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

1 2 3 4 5 6 7 8 9

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)#mysql_galera_hostgroups

添加galera服務器配置：

1 2 3 4 5 6 7

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;

查看配置

1 2 3

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

統計MySQL連接池信息：

1	select * from stats.stats_mysql_connection_pool;

11.配置路由規則

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

配置讀寫分離，就是配置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的幾個字段：

1 2 3 4 5

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

這里我創建兩個規則：

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

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

1 2 3 4

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;

查看集群中每個節點的狀態：

1	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的順序進行規則匹配的。

1 2 3

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

測試讀寫分離:

我們向測試數據庫表中插入一條數據試試：

成功插入

 

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

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

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

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

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

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

1 2 3

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的代理層持續無感應地提供服務。來保證我們服務的高可用性。