MySQL 8.x配置MGR高可用+ProxySQL讀寫分離（二）：ProxySQL配置MySQL代理及讀寫分離

#作者：stackofumbrella

文章目錄

ProxySQL簡介
ProxySQL架構
ProxySQL的安裝與配置
- 在不同層次間移動配置

ProxySQL簡介

ProxySQL是基于MySQL的一款開源的中間件的產品，是一個靈活的MySQL代理層，可以實現讀寫分離，支持Query路由功能，支持動態指定某個SQL進行緩存，支持動態加載（無需重啟 ProxySQL 服務），故障切換和一些 SQL 的過濾功能。

具有以下優點和特性：

支持動態加載配置，可以在線修改配置。
支持query cache。
支持對query的路由，可以針對某個語句進行分配執行實例。
監控后端節點，包括：ProxySQL和后端的心跳信息，后端節點的read-only/read-write，slave和master的數據同步延遲性 (replication lag)。
能處理千億級的數據。
最基本的讀/寫分離，且方式有多種。
可定制基于用戶、基于schema、基于語句的規則對SQL語句進行路由。
理解MySQL協議，具有高級連接處理能力。
具有數據庫防火墻功能，保護數據和流量免受惡意活動的侵害。

官方地址：https://www.proxysql.com/
Github倉庫：https://github.com/sysown/proxysql/wiki
軟件下載：https://github.com/sysown/proxysql/releases

ProxySQL架構

在這里插入圖片描述
ProxySQL 2.0.x版本以后開始支持MGR高可用，這樣結合MGR，使得MySQL主從集群具有更加完備的對外訪問能力。

ProxySQL的安裝與配置

環境準備
在這里插入圖片描述
首先需要安裝MySQL主從集群，即一主兩從集群，過程比較簡單，這里不再詳述。
ProxySQL安裝

$ sudo apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates
$ wget https://github.com/sysown/proxysql/releases/download/v2.7.3/proxysql_2.7.3-ubuntu22_amd64.deb
$ sudo dpkg -i proxysql_2.7.3-ubuntu22_amd64.deb

啟動ProxySQL

$ sudo systemctl start proxysql
$ ss -tnl

在這里插入圖片描述
6032是ProxySQL的管理端口號，6033是對外服務的端口號
連接ProxySQL

$ sudo apt install mysql-client-core-8.0

登錄proxysql，proxysql的用戶名和密碼都是默認的admin

$ mysql -uadmin -padmin -h 127.0.0.1 -P 6032
mysql> show databases;

在這里插入圖片描述
可見有五個庫：main、disk、stats、monitor和stats_history
main：內存配置數據庫，即MEMORY，表里存放后端db 實例、用戶驗證、路由規則等信息。main庫中有如下信息：
mysql> use main;
mysql> show tables;

main庫下的主要表
mysql_servers：后端可以連接MySQL服務器的列表
mysql_users：配置后端數據庫的賬號和監控的賬號
mysql_query_rules：指定Query路由到后端不同服務器的規則列表
注：表名以runtime_開頭的表示ProxySQL當前運行的配置內容，不能通過DML語句修改。只能修改對應的不以 runtime開頭的表，然后“LOAD”使其生效，“SAVE”使其存到硬盤以供下次重啟加載。
disk庫下的主要表
主要是一些持久化磁盤的配置
在這里插入圖片描述
stats庫下的主要表
主要是統計信息的匯總

stats_mysql_connection_pool表
hostgroup：后端服務器所屬的主機組，單個后端服務器可以屬于多個主機組
srv_host,srv_port：mysql后端服務器正在偵聽連接的TCP端點的IP和Port
status：后端服務器的狀態。可以有ONLINE，SHUNNED，OFFLINE_SOFT，OFFLINE_HARD
ConnUsed：ProxySQL當前使用多少個連接來向后端服務器發送查詢
ConnFree：目前有多少個連接是空閑
ConnOK：成功建立了多少個連接
ConnERR：失敗的連接數
Queries：路由到此特定后端服務器的查詢數
Bytes_data_sent：發送到后端的數據量
Bytes_data_recv：從后端接收的數據量
Latency_ms：從Monitor報告的當前ping以毫秒為單位的延遲時間
monitor庫下的主要表
主要是一些監控的收集信息，比如數據庫的健康狀態等
在這里插入圖片描述
stats_history庫下的主要表
這個庫是ProxySQL收集有關其內部功能的歷史指標

配置ProxySQL所需賬戶
請在MySQL的主庫執行
注意：MySQL 8.0.x用戶認證的方式需要修改為mysql_native_password，需要在MySQL的配置文件中加上這個用戶認證方式，再來創建用戶
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
default_authentication_plugin=mysql_native_password
mysql> select User,Host,plugin from mysql.user;
在這里插入圖片描述
然后執行創建用戶

#proxysql的監控賬戶
create user 'monitor'@'192.168.1.%' identified by '123456';
grant all privileges on *.* to 'monitor'@'192.168.1.%';
#proxysql的對外訪問賬戶
create user 'proxysql'@'192.168.1.%' identified by '123456';
grant all privileges on *.* to 'proxysql'@'192.168.1.%';
flush privileges;

mysql> select User,Host,plugin from mysql.user;
在這里插入圖片描述
ProxySQL的多層架構

整套配置系統分為三層：頂層為RUNTIME，中間層為MEMORY，底層也就是持久層DISK和CONFIG FILE。RUNTIME ：代表ProxySQL當前正在使用的配置，無法直接修改這里的配置，必須要從下一層LOAD進來。
MEMORY：MEMORY層上面連接RUNTIME層，下面連接持久層。這層可以正常操作ProxySQL配置，隨便修改，不會影響生產環境。修改一個配置一般都是在MEMORY層完成的，確認正常之后在加載達到RUNTIME和持久化的磁盤上。
DISK和CONFIG FILE：持久化配置信息，重啟后內存中的配置信息會丟失，所需要將配置信息保留在磁盤中。重啟時，可以從磁盤快速加載回來。

在不同層次間移動配置

重新配置MySQL用戶
為了將配置持久化到磁盤或者應用到runtime，在管理接口下有一系列管理命令來實現它們。要重新配置MySQL用戶，可執行下面的其中一個命令：

LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
將內存數據庫中的配置加載到runtime數據結構，反之亦然。
SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
將MySQL用戶從runtime持久化到內存數據庫。
LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
從磁盤數據庫中加載MySQL用戶到內存數據庫中。
SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
將內存數據庫中的MySQL用戶持久化到磁盤數據庫中。
LOAD MYSQL USERS FROM CONFIG
從配置文件中加載MySQL用戶到內存數據庫中。

處理MySQL Server

LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
將MySQL server從內存數據庫中加載到runtime。
SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
將MySQL server從runtime持久化到內存數據庫中。
LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
從磁盤數據庫中加載MySQL server到內存數據庫。
SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
從內存數據庫中將MySQL server持久化到磁盤數據庫中。
LOAD MYSQL SERVERS FROM CONFIG
從配置文件中加載MySQL server到內存數據庫中

處理MySQL的查詢規則

LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
將MySQL query rules從內存數據庫加載到runtime數據結構。
SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
將MySQL query rules從runtime數據結構中持久化到內存數據庫。
LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
從磁盤數據庫中加載MySQL query rules到內存數據庫中。
SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
將MySQL query rules從內存數據庫中持久化到磁盤數據庫中。
LOAD MYSQL QUERY RULES FROM CONFIG
從配置文件中加載MySQL query rules到內存數據庫中。

處理MySQL變量

LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
將MySQL variables從內存數據庫加載到runtime數據結構。
SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
將MySQL variables從runtime數據結構中持久化到內存中。
LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
從磁盤數據庫中加載MySQL variables到內存數據庫中。
SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
將MySQL variables從內存數據庫中持久化到磁盤數據庫中。
LOAD MYSQL VARIABLES FROM CONFIG
從配置文件中加載MySQL variables到內存數據庫中。

處理管理變量

LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
將admin variables從內存數據庫加載到runtime數據結構。
SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
將admin variables從runtime持久化到內存數據庫中。
LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
從磁盤數據庫中加載admin variables到內存數據庫中。
SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
將admin variables從內存數據庫中持久化到磁盤數據庫。
LOAD ADMIN VARIABLES FROM CONFIG
從配置文件中加載admin variables到內存數據庫中。

說明
修改配置的步驟一般是在MEMORY層進行修改，然后保存到RUNTIME和DISK
管理配置
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。記住，只要不是加載到runtime，修改的配置就不會生效。
只有加載到runtime狀態時才會去做最后的有效性驗證。在保存到內存數據庫或持久化到磁盤上時，都不會發生任何警告或錯誤。當加載到runtime時，如果出現錯誤，將恢復為之前保存得狀態，這時可以去檢查錯誤日志。