MySQL Router實現MySQL的讀寫分離

標籤：部署   調度演算法   head   get   path   red   eve   不用   amp   

1.簡介

MySQL Router是MySQL官方提供的一個輕量級MySQL中介軟體，用於取代以前老版本的SQL proxy。

既然MySQL Router是一個資料庫的中介軟體，那麼MySQL Router必須能夠分析來自前面用戶端的SQL請求是寫請求還是讀請求，以便決定這個SQL請求是發送給master還是slave，以及發送給哪個master、哪個slave。這樣，MySQL Router就實現了MySQL的讀寫分離，對MySQL請求進行了負載平衡。

因此，MySQL Router的前提是後端實現了MySQL的主從複製。

MySQL Router很輕量級，只能通過不同的連接埠來實現簡單的讀/寫分離，且讀請求的調度演算法只能使用預設的rr(round-robin)，更多一點、更複雜一點的能力都不具備。所以，在實現MySQL Router時，需要自行配置好後端MySQL的高可用。高可用建議通過Percona XtraDB Cluster或MariaDB Galera或MySQL官方的group replication實現，如果實在沒有選擇，還可以通過MHA實現。

所以，一個簡單的MySQL Router部署圖如下。

本文將使用MySQL Router分別實現後端無MySQL主從高可用情形的讀寫分離，至於為什麼不實現後端有MySQL高可用的讀寫分離情形。在我看來，MySQL Router只是一個玩具，不僅功能少，而且需要在應用程式代碼中指定讀/寫的不同連接埠(見後文關於設定檔的解釋)，在實際環境中應該沒人會這樣用。

2.配置MySQL Router

以下是實驗環境。

角色名稱	主機IP	MySQL版本	資料狀態
MySQL Router	192.168.100.21	MySQL 5.7.22	無
master	192.168.100.22	MySQL 5.7.22	全新執行個體
slave1	192.168.100.23	MySQL 5.7.22	全新執行個體
slave2	192.168.100.24	MySQL 5.7.22	全新執行個體

因為後端MySQL主從複製沒有實現高可用，所以只有一個master節點負責寫操作。

所有後端MySQL節點都是剛安裝好的全新MySQL執行個體，所以直接開啟主從複製即可。如果是已有資料的主從複製，需要先保證它們已同步好，方法見：將slave恢複到master指定的座標。

2.1 安裝MySQL Router

二進位版MySQL Router：https://dev.mysql.com/downloads/router/
rpm倉庫：http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

此處使用二進位版的MySQL Router 2.1.6。

tar xf mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit.tar.gzmv mysqlrouter-2.1.6-linux-glibc2.12-x86-64bit /usr/local/mysqlrouter

這就完了，就這麼簡單。

解壓二進位包後，解壓目錄下有以下幾個檔案。

[[email protected] mr]# lsbin  data  include  lib  run  share

bin目錄下只有一個二進位程式mysqlrouter，這也是MySQL Router的主程式。

share目錄下有樣本設定檔和樣本SysV風格的啟動指令碼，但是很不幸該指令碼基於debian平台，在redhat系列上需要修改和安裝一些東西才能使用。所以後文我自己寫了一個centos下的SysV指令碼。

[[email protected] mr]# ls share/doc/mysqlrouter/License.txt  README.txt  sample_mysqlrouter.conf  sample_mysqlrouter.init

最後，將主程式添加到PATH環境變數中。

echo "PATH=$PATH:/usr/local/mysqlrouter/bin" >/etc/profile.d/mysqlrouter.shchmod +x /etc/profile.d/mysqlrouter.shsource /etc/profile.d/mysqlrouter.sh

2.2 啟動並測試MySQL Router

以下是上述實驗環境的設定檔，這裡只有一個master節點192.168.100.22:3306，如果有多個寫節點(master)，則使用逗號分隔各節點。關於設定檔，後文會解釋。

[DEFAULT]config_folder = /etc/mysqlrouterlogging_folder = /usr/local/mysqlrouter/logruntime_folder = /var/run/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 192.168.100.21:7001destinations = 192.168.100.23:3306,192.168.100.24:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 192.168.100.21:7002destinations = 192.168.100.22:3306mode = read-writeconnect_timeout = 2

然後在MySQL Router所在的機器上建立上面使用的目錄。

shell> mkdir /etc/mysqlrouter /usr/local/mysqlrouter/log /var/run/mysqlrouter

這樣就可以啟動MySQL Router來提供服務了(啟動之前，請確保後端MySQL已被配置好主從複製)。

[[email protected] mr]# mysqlrouter &[1] 16122

查看監聽狀態。這裡監聽的兩個連接埠7001和7002是前端串連MySQL Router用的，它們用來接收前端發送的SQL請求，並按照讀、寫規則，將SQL請求路由到後端MySQL主從節點。

[r[email protected] mr]# netstat -tnlpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address        Foreign Address  State   PID/Program name tcp        0      0 0.0.0.0:6032         0.0.0.0:*        LISTEN  1231/proxysql    tcp        0      0 0.0.0.0:6033         0.0.0.0:*        LISTEN  1231/proxysql    tcp        0      0 0.0.0.0:22           0.0.0.0:*        LISTEN  1152/sshd        tcp        0      0 192.168.100.21:7001  0.0.0.0:*        LISTEN  16122/mysqlroutertcp        0      0 127.0.0.1:25         0.0.0.0:*        LISTEN  2151/master      tcp        0      0 192.168.100.21:7002  0.0.0.0:*        LISTEN  16122/mysqlroutertcp6       0      0 :::22                :::*             LISTEN  1152/sshd        tcp6       0      0 ::1:25               :::*             LISTEN  2151/master      

查看日誌：

[[email protected] mr]# cat /usr/local/mysqlrouter/log/mysqlrouter.log 2018-07-07 10:14:29 INFO  [7f8a8e253700] [routing:slaves] started: listening on 192.168.100.21:7001; read-only2018-07-07 10:14:29 INFO  [7f8a8ea54700] [routing:masters] started: listening on 192.168.100.21:7002; read-write

最後進行測試即可。測試前，先在後端Master上授權MySQL Router節點允許串連，它將會複製到兩個slave節點上。

mysql> grant all on *.* to [email protected]'192.168.100.%' identified by '[email protected]!';

連上MySQL Router的7002連接埠，這個連接埠是負責寫的連接埠。由於沒有配置主從高可用，所以，簡單測試下是否能寫即可。

[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7002 -e 'select @@server_id;'mysql: [Warning] Using a password on the command line interface can be insecure.+-------------+| @@server_id |+-------------+|         110 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7002 -e 'create database mytest;'mysql: [Warning] Using a password on the command line interface can be insecure.[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7002 -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || mytest             || performance_schema || sys                |+--------------------+

再測試下各slave節點，是否能實現rr調度演算法的讀請求的負載平衡。

[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7001 -e 'select @@server_id;' mysql: [Warning] Using a password on the command line interface can be insecure.+-------------+| @@server_id |+-------------+|         120 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7001 -e 'select @@server_id;'mysql: [Warning] Using a password on the command line interface can be insecure.+-------------+| @@server_id |+-------------+|         130 |+-------------+[[email protected] mr]# mysql -uroot [email protected]! -h192.168.100.21 -P7001 -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || mytest             || performance_schema || sys                |+--------------------+

顯然，測試的結果一切正常。

這樣看來MySQL Router好簡單，確實好簡單。只需提供一個合理的設定檔，一切都完成了。那麼，下面解釋下MySQL Router的設定檔。

3.MySQL Router的設定檔解釋

MySQL Router的設定檔也很簡單，需要配置的項不多。

mysql router預設會尋找安裝目錄下的"mysqlrouter.conf"和家目錄下的".mysqlrouter.conf"。也可以在二進位程式mysqlrouter命令下使用"-c"或者"--config"手動指定設定檔。

MySQL router的設定檔是片段式的，常用的就3個片段：[DEFAULT]、[logger]、[routing:NAME]。片段名稱區分大小寫，且只支援單行"#"或";"注釋，不支援行中、行章節附註釋。

以上面樣本的設定檔為例。

[DEFAULT]config_folder = /etc/mysqlrouterlogging_folder = /usr/local/mysqlrouter/logruntime_folder = /var/run/mysqlrouter[logger]level = INFO[routing:slaves]bind_address = 192.168.100.21:7001destinations = 192.168.100.23:3306,192.168.100.24:3306mode = read-onlyconnect_timeout = 1[routing:masters]bind_address = 192.168.100.21:7002destinations = 192.168.100.22:3306mode = read-writeconnect_timeout = 2

1.DEFAULT片段的配置。

[DEFAULT]片段通常配置設定檔的目錄、日誌的目錄、MySQL router運行時的目錄(如pid檔案)。

例如：

[DEFAULT]config_folder=/etc/mysqlrouter   # 指定額外的設定檔目錄，該目錄下的conf檔案都會被載入logging_folder=/usr/local/mysqlrouter/log  # 指定日誌目錄，記錄檔名為mysqlrouter.logruntime_folder=/var/run/mysqlrouter        # 指定運行時目錄，預設為/run/mysqlrouter

2.logger片段的配置。

[logger]片段只有一個選項，設定日誌的記錄層級。

[logger]level=debug   # 有debug、info(預設)、warning、error、fatal，不區分大小寫

3.routing片段的配置。

[routing:NAME]是MySQL router主要部分，設定不同的路由執行個體，其中NAME可以隨意命名。如[routing:slaves]、[routing:masters]。

在routing配置片段，可以設定的選項包括：

• (1).bind_address和bind_port
  bind_address和bind_port是mysql router監聽前端SQL請求的地址和連接埠。其中連接埠是MySQL Router要求強制提供的，但可以不用bind_port綁定，因為它可用通過bind_address的IP:PORT格式指定。
  一個routing規則中只能設定一個地址監聽指令，但可以通過"0.0.0.0"來監聽主機上所有的地址。如果沒有提供監聽地址，則預設監聽127.0.0.1。
  另外，監聽地址不能出現在destinations指令指定的列表中。
  樣本如下：
  

[routing:slaves]bind_port = 7001[routing:slaves]bind_address = 192.168.100.21bind_port = 7001[routing:slaves]bind_address = 192.168.100.21:7001

一般來說，通過不同連接埠實現讀/寫分離，並非好方法，最大的原因是需要在應用程式代碼中指定這些串連連接埠。但是，MySQL Router只能通過這種方式實現讀寫分離，所以MySQL Router拿來當玩具玩玩就好。

• (2).destinations
  定義routing規則的轉寄目標，格式為HOST:PORT，HOST可以是IP也可以是主機名稱，多個轉寄目標使用逗號分隔。如定義的目標列表是多個slave。

[routing:slaves]bind_address = 192.168.100.21:7001destinations = 192.168.100.23:3306,192.168.100.24:3306[routing:masters]bind_address = 192.168.100.21:7002destinations = 192.168.100.22:3306,192.168.100.100:3306

• (3).mode
  MySQL router提供兩種mode：read-only和read-write。這兩種方式會產生不同的轉寄調度方式。
  • 設定為read-write，常用於設定destinations為master時，實現master的高可用。
    • 調度方式：當MySQL router第一次收到用戶端請求時，會將請求轉寄給destinations列表中的第一個目標，第二次收到用戶端請求還是會轉寄給第一個目標，只有當第一個目標聯絡不上(如關閉了MySQL服務、宕機等)才會聯絡第二個目標，如果所有目標都聯絡不上，MySQL Router會中斷。這種調度方式被稱為"first-available"。
      
    • 當聯絡上了某一個目標時，MySQL Router會將其緩衝下來，下次收到請求還會繼續轉寄給該目標。既然是緩衝的目標，就意味著在MySQL Router重啟之後就會失效。
      
    • 所以通過MySQL Router實現讀寫分離的寫時，可以設定多個master，讓效能好的master放在destinations列表的第一個位置，其他的master放在後面的位置作為備用master。
      
  • 設定為read-only，常用於設定destinations為slave時，實現MySQL讀請求負載平衡。
    • 調度方式：當MySQL route收到用戶端請求時，會從destinations列表中的第一個目標開始向後輪詢(round-robin)，第一個請求轉寄給第一個目標，第二個請求轉寄給第二個目標，轉寄給最後一個目標之後的下一個請求又轉寄給第一個目標。如果第一個目標不可用，會依次向後檢查，直到目標可用，如果所有目標都不可用，則MySQL Router中斷。
    • 那些停用目標會暫時被隔離，並且mysql router會不斷的檢查它們的狀況，當重新可用時會重新加入到目標列表。
      
• (4).connect_timeout
  MySQL Router聯絡destinations的逾時時間，預設為1秒，值的範圍為1-65536。應該盡量設定值小點，免得等待時間過長。
  對於read-write模式，可以將逾時時間設定的稍長一點點，防止誤認為主master不可用而去聯絡備master。
  對於read-only模式，可以將逾時時間設定的稍短一點點，因為這種模式下是destinations列表輪詢的，即使誤判了影響也不會太大。
  
• (5).其他選項
  還能設定一些其他的指令，如使用的協議、最大請求數等，但是都可以不用設定使用預設值，它們都是MySQL Router結合MySQL最佳化過的一些選項，本身已經較完美了。

設定檔大概就這些內容，配置好後，記得先建立default片段中涉及到的目錄。之後就可以啟動mysql router提供讀/寫分離服務了。

4.為MySQL Router提供SysV指令碼

MySQL Router只提供了一個主程式(bin目錄下的mysqlrouter)，且該程式只能啟動，沒有停止選項，所以只能使用kill命令來殺掉進程。

MySQL Router也提供了樣本啟動指令碼，該指令碼在位置為$basedir/share/doc/mysqlrouter/sample_mysqlrouter.init，但是該指令碼是基於Debian平台的，在CentOS上需要設定和安裝一些東西，所以不用它，自己寫個粗糙點的指令碼即可。

shell> vim /etc/init.d/mysqlrouter#!/bin/bash# chkconfig: - 78 30# Description: Start / Stop MySQL RouterDAEMON=/usr/local/mysqlrouterproc=$DAEMON/bin/mysqlrouterDAEMON_OPTIONS="-c ${DAEMON}/mysqlrouter.conf". /etc/init.d/functionsstart() {    if [ -e /var/lock/subsys/mysqlrouter ]; then        action "MySQL Router is working" /bin/false    else        $proc $DAEMON_OPTIONS & &>/dev/null        retval=$?        echo    if [ $retval -eq 0 ]; then             touch /var/lock/subsys/mysqlrouter        action "Starting MySQL Router" /bin/true        else        echo "Starting MySQL Router Failure"        fi    fi}    stop() {    if [ -e /var/lock/subsys/mysqlrouter ]; then        killall $proc        retval=$?        echo        if [ $retval -eq 0 ]; then            rm -f /var/lock/subsys/mysqlrouter            action "Stoping MySQL Router" /bin/true        fi    else        action "MySQL Router is not working" /bin/false    fi}status() {    if [ -e /var/lock/subsys/mysqlrouter ]; then        echo "MySQL Router is running"    else        echo "MySQL Router is not running"    fi}case "$1" in    start)        start        sleep 1        ;;     stop)        stop        sleep 1        ;;    restart)        stop        start        sleep 1        ;;    status)        status        ;;    *)        echo "Usage: $0 {start|stop|status|restart}"        retval=1        ;;esacexit $retval   

然後賦予執行許可權。

shell> chmod +x /etc/init.d/mysqlrouter

MySQL Router實現MySQL的讀寫分離