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

10年積累的網(wǎng)站設(shè)計制作、網(wǎng)站建設(shè)經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識你，你也不認(rèn)識我。但先網(wǎng)站設(shè)計后付款的網(wǎng)站建設(shè)流程，更有海州免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

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

介紹

---

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

1.為對多個數(shù)據(jù)庫的應(yīng)用程序請求提供“智能”的負(fù)載均衡。
2.實現(xiàn)了MySQL請求協(xié)議，能夠?qū)?yīng)用程序提供透明的讀寫分離，避免了應(yīng)用程序?qū)崿F(xiàn)復(fù)雜的讀寫分離。
3.能夠自動感知數(shù)據(jù)庫健康狀態(tài)和拓?fù)浣Y(jié)構(gòu)并且能夠自動將應(yīng)用程序請求路由到處于健康狀態(tài)的MySQL實例。
4.保護(hù)應(yīng)用程序不受底層數(shù)據(jù)庫復(fù)雜拓?fù)浣Y(jié)構(gòu)變化而受影響，能夠自動將寫請求轉(zhuǎn)發(fā)的主庫，自動按照權(quán)重將讀請求發(fā)送的主庫和從庫。
5.提供了對查詢SQL的監(jiān)控分析統(tǒng)計。
6.為管理員提供了強大的控制機(jī)制，可以在代理層緩存查詢，以便更快地響應(yīng)查詢、重新路由查詢，甚至重新改寫那些質(zhì)量較差的查詢語句。

模塊

---

ProxySQL組成模塊

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

安裝

---

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

/etc/init.d/proxysql start

配置結(jié)構(gòu)

---

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

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

內(nèi)置庫表介紹

---

登錄到proxysql管理端口，默認(rèn)用戶名密碼為：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: 數(shù)據(jù)庫里存放后端db實例、用戶驗證、路由規(guī)則等信息。表名以 runtime開頭的表示proxysql當(dāng)前運行的配置內(nèi)容，不能通過dml語句修改，只能修改對應(yīng)的不以 runtime 開頭的（在內(nèi)存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盤以供下次重啟加載。
  * disk 是持久化到硬盤的配置，sqlite數(shù)據(jù)文件。
  * stats 是proxysql運行抓取的統(tǒng)計信息，包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時間，等等。
  * 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對數(shù)據(jù)庫實例進(jìn)行了分組和實例信息配置。
 ·hostgroup_id: MySQL實例所屬組
 · status:
 · ONLINE: 運行狀態(tài)
 · SHUNNED: 數(shù)據(jù)庫被處于暫時踢出狀態(tài)，由于后臺數(shù)據(jù)庫出現(xiàn)“too many connections error”或者后端數(shù)據(jù)庫主從延時超過了允許的閾值。
 · OFFLINE_SOFT: “軟離線”狀態(tài)，不再接受新的連接，但已建立的連接會等待活躍事務(wù)完
 · OFFLINE_HARD: “硬離線”狀態(tài)，不再接受新的連接，已建立的連接或被強制中斷。當(dāng)后端實例宕機(jī)或網(wǎng)絡(luò)不可達(dá)，會出現(xiàn)。
 · weight：負(fù)載均衡時選擇后端數(shù)據(jù)庫的權(quán)重值，權(quán)重越高被選中的比率越高。
 · max_connections：允許連接到該后端實例的最大連接數(shù)，不要設(shè)置此值大于后端數(shù)據(jù)庫的最多連接數(shù)。
 · max_latency_ms：mysql_ping 響應(yīng)時間，大于這個閥值會把它從連接池剔除（即使是ONLINE）

mysql_replication_hostgroups

此表用于傳統(tǒng)的異步/半同步的主從復(fù)制，對于MGT/GALERA需要使用mysql_group_replication_hostgroups或者mysql_galera_hostgroups（ProxySQL 2.x之后）。在此表中的每一行代表一對writer_hostgroup和reader_hostgroup。ProxySQL將監(jiān)控read_only的值，ProxySQL將基于read_only的值來分配MySQL實例為reader_hostgroup還是writer_hostgroup,，如果發(fā)現(xiàn)從庫的 read_only 變?yōu)?、主庫變?yōu)?，則認(rèn)為角色互換了，自動改寫 mysql_servers 表里面 hostgroup 關(guān)系，達(dá)到自動 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: 這個用戶的請求沒有匹配到規(guī)則時，默認(rèn)發(fā)到這個 hostgroup。
· default_schema: 這個用戶連接時沒有指定 database name 時，默認(rèn)使用的schema 注意表面上看默認(rèn)為NULL，但實際上受到變量 mysql-default_schema 的影響，默認(rèn)為 information_schema。
· transaction_persistent: 如果設(shè)置為1，連接上ProxySQL的會話后，如果在一個hostgroup上開啟了事務(wù)，那么后續(xù)的sql都繼續(xù)維持在這個hostgroup上，不倫是否會匹配上其它路由規(guī)則，直到事務(wù)結(jié)束。
· fast_forward: 忽略查詢重寫/緩存層，直接把這個用戶的請求透傳到后端DB。相當(dāng)于只用它的連接池功能，一般不用，路由規(guī)則 .* 就行了。

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: 表主鍵，自增。規(guī)則處理是以 rule_id 的順序進(jìn)行。

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

讀寫分離

---

ProxySQL作為中間件能夠監(jiān)聽接收到應(yīng)用程序端的數(shù)據(jù)庫請求，并解析前端的SQL語句并將解析結(jié)果與查詢規(guī)則進(jìn)行匹配，將匹配的SQL發(fā)送到相應(yīng)的MySQL實例從而實現(xiàn)讀寫分離。這個過程入下圖所示：

 

1. 搭建MySQL主從，結(jié)構(gòu)如下：

master： 192.168.20.31:3306

slave1： 192.168.20.32:3306

slave2:  192.168.20.33:3306

2. 在主庫中創(chuàng)建監(jiān)控賬號用于ProxySQL監(jiān)控目標(biāo)主機(jī)。

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個數(shù)據(jù)庫實例節(jié)點到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. 配置讀寫分離規(guī)則

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實現(xiàn)高可用

---

Orchestrator（https://github.com/github/orchestrator）是MySQL復(fù)制結(jié)構(gòu)的一個拓?fù)涔芾砉ぞ?能夠自動檢測MySQL拓?fù)浣Y(jié)構(gòu)，當(dāng)主庫出現(xiàn)故障時能夠自動將“最優(yōu)”從庫提升為主庫。Orchestrator提供了豐富的API接口和故障檢測以及故障切換的鉤子函數(shù)，MyData正是通過Orchestrator提供的鉤子函數(shù)配合ProxySQL、Keepalived、HAProxy實現(xiàn)對應(yīng)用程序無感知的故障切換。

切換流程：

1. 首先Orchestrator檢測的后端主庫出現(xiàn)故障后會通過從庫二次確認(rèn)主庫出現(xiàn)故障，避免了主庫高負(fù)載時出現(xiàn)誤判主庫down機(jī)。

2. Orchestrator切換之前將調(diào)用PreFailoverProcesses鉤子函數(shù)，MyData在鉤子函數(shù)中首先將“down”機(jī)的主庫從ProxySQL中踢出,避免應(yīng)用程序?qū)⒄埱髮懭氲健凹偎馈钡闹鲙?，?zhí)行語句如下：

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

#將假死的老主庫提出集群，避免后續(xù)數(shù)據(jù)被寫入到老主庫
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能夠根據(jù)用戶RPO、RTO“智能”作出恢復(fù)決定。

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

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

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

總結(jié)

---

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

關(guān)于MyData

---

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

想了解更多關(guān)于數(shù)據(jù)庫、云技術(shù)嗎？

快來關(guān)注”數(shù)據(jù)和云“公眾號，”云和恩墨“官方網(wǎng)站，我們期待與大家一同學(xué)習(xí)和進(jìn)步。

網(wǎng)站欄目：MySQL高可用實現(xiàn)：主從結(jié)構(gòu)下ProxySQL中的讀寫分離
標(biāo)題網(wǎng)址：http://www.rwnh.cn/article4/jepiie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、移動網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、網(wǎng)站改版、Google、動態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)