歡迎光臨
每天分享高質量文章

利用 ProxySQL 實現 MySQL 的讀寫分離

來自: 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、資料庫、運維等。

赞(0)

分享創造快樂