MySQL高可用實現：主從結構下ProxySQL中的讀寫分離

原文： http://www.enmotech.com/web/detail/1/714/1.html （ 復制鏈接，打開瀏覽器即可查看原文 ） 

墨墨導讀：ProxySQL是一個高性能的MySQL中間件，擁有強大的規則引擎。
ProxySQL提供強大的路由規則。當應用程序自身不支持讀寫分離時，DBA可以通過配置路由規則為應用程序提供透明的讀寫分離，使用Keepalived + ProxySQL + Orchestrator為主從提供高可用時，能夠有效的避免keepalived + 雙主結構 由于keepalived腦裂而造成數據被寫錯亂的痛點。

介紹

ProxySQL是一個高性能的MySQL中間件，擁有強大的規則引擎。具有以下特性：

1.為對多個數據庫的應用程序請求提供“智能”的負載均衡。
2.實現了MySQL請求協議，能夠對應用程序提供透明的讀寫分離，避免了應用程序實現復雜的讀寫分離。
3.能夠自動感知數據庫健康狀態和拓撲結構并且能夠自動將應用程序請求路由到處于健康狀態的MySQL實例。
4.保護應用程序不受底層數據庫復雜拓撲結構變化而受影響，能夠自動將寫請求轉發的主庫，自動按照權重將讀請求發送的主庫和從庫。
5.提供了對查詢SQL的監控分析統計。
6.為管理員提供了強大的控制機制，可以在代理層緩存查詢，以便更快地響應查詢、重新路由查詢，甚至重新改寫那些質量較差的查詢語句。

模塊

ProxySQL組成模塊

Qurey Processor 用于匹配查詢規則并根據規則決定是否緩存查詢或者將查詢加入黑名單或者重新路由、重寫查詢或者鏡像查詢到其他hostgroup。
User Auth 為底層后端數據庫認證提供了用戶憑證。
Hostgroup manager – 負責管理發送SQL請求都后端數據庫并跟蹤SQL請求狀態。
Connection pool – 負責管理后端數據庫連接，連接池中建立的連接被所有的前端應用程序共享。
Monitoring – 負責監控后端數據庫健康狀態主從復制延時并臨時下線不正常的數據庫實例。

安裝

1.從 https://github.com/sysown/proxysql/releases 下載相應的版本。
2.yum localinstall proxysql-1.x.rpm
3.啟動ProxySQL

/etc/init.d/proxysql start

配置結構

DISK: 使用SQLite來持久存儲ProxySQL配置，以防ProxySQL重啟后配置丟失。
Memory: 存在于內存中的配置，也是用戶通過SQL直接管理的配置。
Runtime: 當前正在使用的配置，處于生效部分。 

使用ProxySQL修改配置時，可以通過SQL語句直接修改Memory中的配置，然后使用load命令將Memory中的配置加載的到runtime層來驗證配置是否正確，如果驗證通過可以通過save將配置保存到SQLite數據庫中，如果驗證不通過也可以通過load命令將DISK層中的配置加載到Memory和runtime層中，達到回滾到效果。

內置庫表介紹

登錄到proxysql管理端口，默認用戶名密碼為：admin/admin

mysql -uadmin -padmin -h227.0.0.1 -P6032

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.30 (ProxySQL Admin Module)

Admin> show databases;

+-----+---------+-------------------------------+

| seq | name   | file                         |

+-----+---------+-------------------------------+

| 0   | main   |                               |

| 2   | disk   | /var/lib/proxysql/proxysql.db |

| 3   | stats   |                               |

| 4   | monitor |                               |

+-----+---------+-------------------------------+

  * main: 數據庫里存放后端db實例、用戶驗證、路由規則等信息。表名以 runtime開頭的表示proxysql當前運行的配置內容，不能通過dml語句修改，只能修改對應的不以 runtime 開頭的（在內存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盤以供下次重啟加載。
  * disk 是持久化到硬盤的配置，sqlite數據文件。
  * stats 是proxysql運行抓取的統計信息，包括到后端各命令的執行次數、流量、processlist、查詢種類匯總/執行時間，等等。
  * monitor 庫存儲 monitor 模塊收集的信息，主要是對后端db的健康/延遲檢查。

Admin> show tables;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                   |
| mysql_replication_hostgroups         |
| mysql_servers                       |
| mysql_users                         |
| runtime_global_variables             |
| runtime_mysql_query_rules           |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers               |
| runtime_mysql_users                 |
| runtime_scheduler                   |
| scheduler                           |
+--------------------------------------+
13 rows in set (0.00 sec)

Admin> show tables from stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| global_variables               |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool   |
| stats_mysql_global             |
| stats_mysql_processlist       |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules       |
+--------------------------------+
8 rows in set (0.00 sec)

mysql_servers

Admin> show create table mysql_servers\G

    CREATE TABLE mysql_servers (  

hostgroup_id INT NOT NULL DEFAULT 0,  

hostname VARCHAR NOT NULL,  

port INT NOT NULL DEFAULT 3306,  

status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',  

weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,  

compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,  

max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,  

max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,  

use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,  

max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, 

comment VARCHAR NOT NULL DEFAULT '',  

PRIMARY KEY (hostgroup_id, hostname, port) )

1 row in set (0.00 sec)

mysql_servers對數據庫實例進行了分組和實例信息配置。
 ·hostgroup_id: MySQL實例所屬組
 · status:
 · ONLINE: 運行狀態
 · SHUNNED: 數據庫被處于暫時踢出狀態，由于后臺數據庫出現“too many connections error”或者后端數據庫主從延時超過了允許的閾值。
 · OFFLINE_SOFT: “軟離線”狀態，不再接受新的連接，但已建立的連接會等待活躍事務完
 · OFFLINE_HARD: “硬離線”狀態，不再接受新的連接，已建立的連接或被強制中斷。當后端實例宕機或網絡不可達，會出現。
 · weight：負載均衡時選擇后端數據庫的權重值，權重越高被選中的比率越高。
 · max_connections：允許連接到該后端實例的最大連接數，不要設置此值大于后端數據庫的最多連接數。
 · max_latency_ms：mysql_ping 響應時間，大于這個閥值會把它從連接池剔除（即使是ONLINE）

mysql_replication_hostgroups

此表用于傳統的異步/半同步的主從復制，對于MGT/GALERA需要使用mysql_group_replication_hostgroups或者mysql_galera_hostgroups（ProxySQL 2.x之后）。在此表中的每一行代表一對writer_hostgroup和reader_hostgroup。ProxySQL將監控read_only的值，ProxySQL將基于read_only的值來分配MySQL實例為reader_hostgroup還是writer_hostgroup,，如果發現從庫的 read_only 變為0、主庫變為1，則認為角色互換了，自動改寫 mysql_servers 表里面 hostgroup 關系，達到自動 Failover 效果。

Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G

*************************** 1. row *************************** 

     table: mysql_replication_hostgroupsCreate Table: 

CREATE TABLE mysql_replication_hostgroups (

  writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,

  reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),

  check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',

  comment VARCHAR,  UNIQUE (reader_hostgroup))

1 row in set (0.00 sec)

mysql_users

CREATE TABLE mysql_users (  

 username VARCHAR NOT NULL,  

 password VARCHAR, 

 active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,  use_ssl INT CHECK (

 use_ssl IN (0,1)) NOT NULL DEFAULT 0,

 default_hostgroup INT NOT NULL DEFAULT 0, 

 default_schema VARCHAR, 

 schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,

 transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0, 

 fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0, 

 backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1, 

 frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1, 

 max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000, 

 PRIMARY KEY (username, backend), 

 UNIQUE (username, frontend)

)

· username, password: 連接后端db的用戶密碼。這個密碼你可以插入明文，也可以插入hash加密后的密文。
· default_hostgroup: 這個用戶的請求沒有匹配到規則時，默認發到這個 hostgroup。
· default_schema: 這個用戶連接時沒有指定 database name 時，默認使用的schema 注意表面上看默認為NULL，但實際上受到變量 mysql-default_schema 的影響，默認為 information_schema。
· transaction_persistent: 如果設置為1，連接上ProxySQL的會話后，如果在一個hostgroup上開啟了事務，那么后續的sql都繼續維持在這個hostgroup上，不倫是否會匹配上其它路由規則，直到事務結束。
· fast_forward: 忽略查詢重寫/緩存層，直接把這個用戶的請求透傳到后端DB。相當于只用它的連接池功能，一般不用，路由規則 .* 就行了。

mysql_query_rules

CREATE TABLE mysql_query_rules (  

  rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  

  active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, 

  username VARCHAR, 

  schemaname VARCHAR, 

  flagIN INT NOT NULL DEFAULT 0, 

  client_addr VARCHAR, 

  proxy_addr VARCHAR,

  proxy_port INT,

  digest VARCHAR, 

  match_digest VARCHAR, 

  match_pattern VARCHAR, 

  negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0, 

  flagOUT INT, 

  replace_pattern VARCHAR,

  destination_hostgroup INT DEFAULT NULL,

  cache_ttl INT CHECK(cache_ttl > 0),

  reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,

  timeout INT UNSIGNED,

  retries INT CHECK (retries>=0 AND retries <=1000),

  delay INT UNSIGNED,  mirror_flagOUT INT UNSIGNED,

  mirror_hostgroup INT UNSIGNED,  error_msg VARCHAR,

  log INT CHECK (log IN (0,1)),

  apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,

  comment VARCHAR)

 · rule_id: 表主鍵，自增。規則處理是以 rule_id 的順序進行。

 · active: 只有 active=1 的規則才會參與匹配。
username: 如果非 NULL，只有連接用戶是 username 的值才會匹配
 · : 如果非 NULL，只有查詢連接使用的db是 schemaname 的值才會匹配。
 · client_addr: 匹配客戶端來源IP
  · digest: 精確的匹配一類查詢。
 · match_digest: 使用正則來匹配查詢指紋（去掉查詢參數后的查詢）
  · match_pattern: 正則匹配查詢文本。
  · negate_match_pattern: 反向匹配，相當于對 match_digest/match_pattern 的匹配取反。
  · re_modifiers: 修改正則匹配的參數，比如默認的：忽略大小寫CASELESS、禁用GLOBAL上面都是匹配規則。
  · replace_pattern: 查詢重寫，默認為空，不rewrite。
  · destination_hostgroup: 路由查詢到這個 hostgroup。當然如果用戶顯式 start transaction 且 transaction_persistent=1，那么即使匹配到了，也依然按照事務里第一條sql的路由規則去走。
  · cache_ttl: 查詢結果緩存的毫秒數。
  · timeout: 這一類查詢執行的最大時間（毫秒），超時則自動kill.
  · retries: 語句在執行時失敗時，重試次數。默認由 mysql-query_retries_on_failure變量指定，為1。
  · delay: 查詢延遲執行，這是ProxySQL提供的限流機制，會讓其它的查詢優先執行。
  · error_msg: 默認為NULL，如果指定了則這個查詢直接被 block 掉，馬上返回這個錯誤信息。
  · multiplex: 連接是否復用。
  · log: 是否記錄查詢日志。可以看到log是否記錄的對象是根據規則。

讀寫分離

ProxySQL作為中間件能夠監聽接收到應用程序端的數據庫請求，并解析前端的SQL語句并將解析結果與查詢規則進行匹配，將匹配的SQL發送到相應的MySQL實例從而實現讀寫分離。這個過程入下圖所示：

1. 搭建MySQL主從，結構如下：

master： 192.168.20.31:3306

slave1： 192.168.20.32:3306

slave2:  192.168.20.33:3306

2. 在主庫中創建監控賬號用于ProxySQL監控目標主機。

create user 'monitor'@'%' identified with mysql_native_password by 'monitor';

grant select on sys.* to 'monitor'@'%';

grant select on performance_schema.* to 'monitor'@'%';

flush privileges;

3. 插入3個數據庫實例節點到ProxySQL

Admin> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(10,20);

Admin> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'192.168.20.31',3306);

Admin> insert into mysql_servers (hostgroup_id,hostname,port) values (20,'192.168.20.32',3306);

Admin> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'192.168.20.33',3306);

Admin> save mysql servers to disk;

Admin> load mysql servers to runtime;

4. 配置讀寫分離規則

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES

(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;

Admin> SAVE MYSQL QUERY RULES TO DISK;

5. 配置MySQL用戶到ProxySQL

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app','pass',10);

Admin> save mysql users to disk;

Admin> load mysql users to runtime;

ProxySQL + Orchestrator實現高可用

Orchestrator（https://github.com/github/orchestrator）是MySQL復制結構的一個拓撲管理工具,能夠自動檢測MySQL拓撲結構，當主庫出現故障時能夠自動將“最優”從庫提升為主庫。Orchestrator提供了豐富的API接口和故障檢測以及故障切換的鉤子函數，MyData正是通過Orchestrator提供的鉤子函數配合ProxySQL、Keepalived、HAProxy實現對應用程序無感知的故障切換。

切換流程：

1. 首先Orchestrator檢測的后端主庫出現故障后會通過從庫二次確認主庫出現故障，避免了主庫高負載時出現誤判主庫down機。

2. Orchestrator切換之前將調用PreFailoverProcesses鉤子函數，MyData在鉤子函數中首先將“down”機的主庫從ProxySQL中踢出,避免應用程序將請求寫入到“假死”的主庫，執行語句如下：

#強制關閉與假死主庫的連接，避免數據被寫入的假死的老主庫。
Admin> update runtime_mysql_servers set status="HARD_OFFLINE" where hostname='192.168.20.31' and port='3306'

#將假死的老主庫提出集群，避免后續數據被寫入到老主庫
Admin> delete from mysql_servers where hostname='192.168.20.31' and port='3306'
Admin> load mysql servers to runtime
Admin> save mysql serbers to disk

3. MyData能夠根據用戶RPO、RTO“智能”作出恢復決定。

4. MyData通知Orchestrator開發切換，Orchestrator切換完成之后，將修改新主庫的read_only值為0。

5. ProxySQL將新的寫請求路由到主庫。

整個流程之中MyData作了大量優化，能夠在滿足用戶RTO情況下，能將用戶丟失的數據減少到原來的10%以下，MyData還為ProxySQL本身提供了高可用方案，避免了ProxySQL本身的單點故障。

總結

ProxySQL提供強大的路由規則。當應用程序自身不支持讀寫分離時，DBA可以通過配置路由規則為應用程序提供透明的讀寫分離，使用Keepalived + ProxySQL + Orchestrator為主從提供高可用時，能夠有效的避免keepalived + 雙主結構 由于keepalived腦裂而造成數據被寫錯亂的痛點。

關于MyData

MyData是云和恩墨自主研發的，針對MySQL數據庫提供高可用、高可靠、高安全性和易于使用的整體解決方案。MyData融合了云和恩墨資深數據庫工程師的經驗和最佳實踐，來幫助客戶快速構建高可用的數據庫集群環境，保證了MySQL數據庫運行環境符合企業級數據庫的要求，幫助客戶提高快速交付的能力。
云和恩墨對MyData提供專業、靈動的端到端服務，涵蓋規劃設計、建設實施、運營管理和優化提升四個階段，為客戶構建安全、連續、高效和穩定的數據環境。
MyData目前已經在政府和金融行業擁有多個最佳實踐的案例，致力于為企業提供開展開源數據庫一體化的解決方案。

想了解更多關于數據庫、云技術嗎？

快來關注”數據和云“公眾號，”云和恩墨“官方網站，我們期待與大家一同學習和進步。