知識星球來自： star_glm 的部落格
https://my.oschina.net/starglm/blog/1607412
一、Proxysql 的基礎知識
ProxySQL是一個可以實現MySQL讀寫分離的輕量級工具。
ProxySQL的特點
-
將所有配置儲存寫入到SQLit表中。
-
支援動態載入配置，即一般可以線上修改配置，但有少部分引數還是需要重啟來生效。
-
支援query cache。
-
支援對query的路由，可以針對某個陳述句進行分配去哪個實體執行。
-
故障切換。
-
過濾危險的SQL。
-
不支援分表，可以分庫，但是利用規則配置實現分表。
ProxySQL的管理配置
Proxysql中有四層配置：
-
runtime：執行中使用的配置檔案
-
memory：提供使用者動態修改配置檔案
-
disk：將修改的配置儲存到磁碟SQLit表中（即：proxysql.db）
-
config：一般不使用它（即：proxysql.cnf）
四層配置圖：
註釋：
-
一般，修改的配置都是在memory層。可以load到runtime，使配置在不用重啟proxysql的情況下也可以生效，也可以save到disk，將對配置的修改持久化。
-
[1] LOAD MYSQL USERS FROM MEMORY 或 LOAD MYSQL USERS TO RUNTIME
[2] SAVE MYSQL USERS TO MEMORY 或 SAVE MYSQL USERS FROM RUNTIME
[3] LOAD MYSQL USERS TO MEMORY 或 LOAD MYSQL USERS FROM DISK
[4] SAVE MYSQL USERS FROM MEMORY 或 SAVE MYSQL USERS TO DISK
[5] LOAD MYSQL USERS FROM CONFIG
二、ProxySQL的安裝
安裝步驟：
$ rpm –ivh proxysql-1.3.9-1-centos67.x86_64.rpm // RPM安裝
$ service proxysql start // 啟動ProxySQL
$ rpm -ql proxysql // 檢視配置檔案的位置
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
由此得知：proxysql的配置檔案在/etc/proxysql.cnf$ mysql -uadmin -padmin -P6032 -h127.0.0.1 // 登入，從配置檔案中找到預設的使用者名稱和密碼
ProxySQL的啟動原理:
ProxySQL在啟動時，會啟動兩個行程，監聽兩個埠號（6032是管理的埠；6033是對外提供服務的埠），如下圖：
啟動解釋：
在proxysql初次啟動時，會從配置檔案中獲取資訊啟動；
在proxysql再次啟動時，首先從proxysql.cnf中找到datadir，獲取proxysql.db的位置，從proxysql.db獲取資料，將其載入到記憶體memory，然後從記憶體載入到runtime中，因此這種正常的啟動，不會從配置檔案中獲取引數。
由此得知：線上修改時，一定要儲存到磁碟，否則再次啟動時丟失配置的資料。
三、配置讀寫分離
機器的資訊：
-
主庫：IP：192.168.152.128；port：3306
-
從庫：IP：192.168.152.128；port：3307
-
Proxysql安裝在主庫。
以下操作是在main庫下的表操作的！
1、設定讀寫的分組編號（即 hostgroup_id）
-
insert into mysql_replication_hostgroups values(1,2,db-128);
-
load mysql servers to runtime;save mysql servers to disk;
註：設定1為寫分組；2為讀分組；備註：db-128；將其載入到執行中，儲存到磁碟。
2、將MySQL實體分配到各個組中：主庫分到寫組；從庫分到讀組。
-
insert into mysql_servers(hostgroup_id,hostname,port,transaction_persistent,max_connections) values(1,’192.168.152.128′,3306,1,100),(2,’192.168.152.128′,’3307′,1,100);
-
load mysql servers to runtime;save mysql servers to disk;
註：
-
若這裡有多個從庫，可以將其他從庫的資訊新增到讀分組（即：hostgroup_id=2）中。
-
每個server需要屬於一個組；一個組裡可以有多個server；一個server可以屬於多個組。
3、建立賬號，用於對外服務連線進來proxysql的賬號
① 在proxysql中建立賬號，在外部使用它連線進來時，會在表main.mysql_users中檢查該賬號的相關資訊。
-
insert into mysql_users(username,password,active,default_hostgroup,default_schema)
values(‘proxysql’,’proxysql’,1,1,’guolmdb’);
-
load mysql users to runtime;save mysql users to disk;
註：proxysql/proxysql賬號用於外部連線進來使用，預設進入寫分組的guolmdb庫，active=1代表活躍的賬號。
② 修改提供外部連線的IP和埠號
-
update global_variables set variable_value=’192.168.152.128:6033′ where variable_name=’mysql-interfaces’;
-
或 set mysql-interfaces=’192.168.152.128:6033′;
-
save mysql variables to disk;
-
> proxysql restart;
註：
-
該變數的值可以有多個，即可以設定透過多個IP連線進來，格式：’IP1:port1;IP2:port2’。
-
修改該變數需要重啟才能生效！
-
有兩種設定變數的方式：set 設定 或 update 設定。
③ 將以上建立的賬號，在MySQL主庫中建立一遍，要不然，proxysql沒有許可權連線到MySQL。
-
GRANT ALL PRIVILEGES ON *.* TO ‘monitor’@’%’ IDENTIFIED BY ‘monitor’;
-
GRANT ALL PRIVILEGES ON *.* TO ‘proxysql’@’%’ IDENTIFIED BY ‘proxysql’;
解釋：
-
monitor/ monitor 對應著proxysql的表main. global_variables 的mysql-monitor_username
-
proxysql/proxysql 對應著在proxysql中的表main.mysql_users建立的賬戶
註意：
若不在MySQL中建立使用者並賦許可權，proxysql連線不到MySQL中，在proxysql.log中會報錯：
# tail -100f proxysql.log
2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3306 is returning “Access denied” for monitoring user
2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3307 is returning “Access denied” for monitoring user
4、設定讀寫的路由規則
-
insert into mysql_query_rules (active,username,match_pattern,schemaname,destination_hostgroup,apply) values(1,’proxysql’,’^select’,’guolmdb’,2,1);
-
load mysql query rules to runtime;save mysql query rules to disk;
解釋：
-
透過賬戶proxysql連線進來的，一個以select開頭的SQL並操作guolmdb庫的陳述句將會被分配到讀分組執行。
註意：
-
這種設定若一個事務為：begin；select；update；commit；可能會將該事務分配到從庫操作，這樣是錯誤的！
-
為避免這種錯誤，可以針對併發高的SQL設定讀的負載均衡。如下操作：
-
查看錶 stats_mysql_query_digest，找到併發高的SQL對應的欄位digest。
-
在main.mysql_query_rules中設定路由規則時，將查詢到的digest，新增到路由表的欄位digest中。
-
官網相關部落格：http://proxysql.com/blog/configure-read-write-split
5、測試 配置的讀寫分離是否生效
執行一些操作：
# mysql -uproxysql -pproxysql -P6033 -h192.168.152.128
[guolmdb]>insert into t1 values(1);
[guolmdb]>select * from t1 where id=1;
註：透過對外提供服務的埠號和賬號連線進proxysql，進而轉接到主庫上（因為該賬戶預設進入的是主庫）
檢視以上的SQL在哪裡執行的：
# mysql -uadmin -padmin -P6032 -h127.0.0.1
[none] >select hostgroup,digest_text from stats_mysql_query_digest;
+———–+———————————-+
| hostgroup | digest_text |
+———–+———————————-+
| 1 | insert into t1 values(?) |
| 2 | select * from t1 where id=? |
+———–+———————————-+
註：檢視到剛才select的陳述句使用的是讀分組；insert陳述句使用的是寫分組。
四、總結
-
ProxySQL主要的作用是：線上修改配置使之生效 、Query路由、Query cache。其中，Query路由可以指定一個SQL放在哪個資料庫上執行；Query cache可以針對實時請求很高的SQL，在Query cache中快取一些時間。
-
一般地，只需要將請求頻繁的查詢陳述句，做讀的負載均衡，不頻繁的可以直接在主庫查詢。
-
對ProxySQL做HA：使用兩個ProxySQL，一個對外提供服務，一個做故障切換使用。
●本文編號280，以後想閱讀這篇文章直接輸入280即可
●輸入m獲取到文章目錄
Web開發
更多推薦《18個技術類公眾微信》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。
