1.簡(jiǎn)介

MySQL Router是MySQL官方提供的一個(gè)輕量級(jí)MySQL中間件，用于取代以前老版本的SQL proxy。

既然MySQL Router是一個(gè)數(shù)據(jù)庫(kù)的中間件，那么MySQL Router必須能夠分析來(lái)自前面客戶(hù)端的SQL請(qǐng)求是寫(xiě)請(qǐng)求還是讀請(qǐng)求，以便決定這個(gè)SQL請(qǐng)求是發(fā)送給master還是slave，以及發(fā)送給哪個(gè)master、哪個(gè)slave。這樣，MySQL Router就實(shí)現(xiàn)了MySQL的讀寫(xiě)分離，對(duì)MySQL請(qǐng)求進(jìn)行了負(fù)載均衡。

因此，MySQL Router的前提是后端實(shí)現(xiàn)了MySQL的主從復(fù)制。

MySQL Router很輕量級(jí)，只能通過(guò)不同的端口來(lái)實(shí)現(xiàn)簡(jiǎn)單的讀/寫(xiě)分離，且讀請(qǐng)求的調(diào)度算法只能使用默認(rèn)的rr(round-robin)，更多一點(diǎn)、更復(fù)雜一點(diǎn)的能力都不具備。所以，在實(shí)現(xiàn)MySQL Router時(shí)，需要自行配置好后端MySQL的高可用。高可用建議通過(guò)Percona XtraDB Cluster或MariaDB Galera或MySQL官方的group replication實(shí)現(xiàn)，如果實(shí)在沒(méi)有選擇，還可以通過(guò)MHA實(shí)現(xiàn)。

所以，一個(gè)簡(jiǎn)單的MySQL Router部署圖如下。

本文將使用MySQL Router分別實(shí)現(xiàn)后端無(wú)MySQL主從高可用情形的讀寫(xiě)分離，至于為什么不實(shí)現(xiàn)后端有MySQL高可用的讀寫(xiě)分離情形。在我看來(lái)，MySQL Router只是一個(gè)玩具，不僅功能少，而且需要在應(yīng)用程序代碼中指定讀/寫(xiě)的不同端口(見(jiàn)后文關(guān)于配置文件的解釋)，在實(shí)際環(huán)境中應(yīng)該沒(méi)人會(huì)這樣用。

2.配置MySQL Router

以下是實(shí)驗(yàn)環(huán)境。

因?yàn)楹蠖薓ySQL主從復(fù)制沒(méi)有實(shí)現(xiàn)高可用，所以只有一個(gè)master節(jié)點(diǎn)負(fù)責(zé)寫(xiě)操作。

所有后端MySQL節(jié)點(diǎn)都是剛安裝好的全新MySQL實(shí)例，所以直接開(kāi)啟主從復(fù)制即可。如果是已有數(shù)據(jù)的主從復(fù)制，需要先保證它們已同步好，方法見(jiàn)：將slave恢復(fù)到master指定的坐標(biāo)。

2.1 安裝MySQL Router

二進(jìn)制版MySQL Router下載地址：https://dev.mysql.com/downloads/router/
rpm倉(cāng)庫(kù)：http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/

此處使用二進(jìn)制版的MySQL Router 2.1.6。

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

這就完了，就這么簡(jiǎn)單。

解壓二進(jìn)制包后，解壓目錄下有以下幾個(gè)文件。

[root@s1 mr]# ls
bin  data  include  lib  run  share

bin目錄下只有一個(gè)二進(jìn)制程序mysqlrouter，這也是MySQL Router的主程序。

share目錄下有示例配置文件和示例SysV風(fēng)格的啟動(dòng)腳本，但是很不幸該腳本基于debian平臺(tái)，在redhat系列上需要修改和安裝一些東西才能使用。所以后文我自己寫(xiě)了一個(gè)centos下的SysV腳本。

[root@s1 mr]# ls share/doc/mysqlrouter/
License.txt  README.txt  sample_mysqlrouter.conf  sample_mysqlrouter.init

最后，將主程序添加到PATH環(huán)境變量中。

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

2.2 啟動(dòng)并測(cè)試MySQL Router

以下是上述實(shí)驗(yàn)環(huán)境的配置文件，這里只有一個(gè)master節(jié)點(diǎn)192.168.100.22:3306，如果有多個(gè)寫(xiě)節(jié)點(diǎn)(master)，則使用逗號(hào)分隔各節(jié)點(diǎn)。關(guān)于配置文件，后文會(huì)解釋。

[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

然后在MySQL Router所在的機(jī)器上創(chuàng)建上面使用的目錄。

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

這樣就可以啟動(dòng)MySQL Router來(lái)提供服務(wù)了(啟動(dòng)之前，請(qǐng)確保后端MySQL已被配置好主從復(fù)制)。

[root@s1 mr]# mysqlrouter &
[1] 16122

查看監(jiān)聽(tīng)狀態(tài)。這里監(jiān)聽(tīng)的兩個(gè)端口7001和7002是前端連接MySQL Router用的，它們用來(lái)接收前端發(fā)送的SQL請(qǐng)求，并按照讀、寫(xiě)規(guī)則，將SQL請(qǐng)求路由到后端MySQL主從節(jié)點(diǎn)。

[root@s1 mr]# netstat -tnlp
Active 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/mysqlrouter
tcp        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/mysqlrouter
tcp6       0      0 :::22                :::*             LISTEN  1152/sshd        
tcp6       0      0 ::1:25               :::*             LISTEN  2151/master      

查看日志：

[root@s1 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-only

2018-07-07 10:14:29 INFO  [7f8a8ea54700] [routing:masters] started: listening on 192.168.100.21:7002; read-write

最后進(jìn)行測(cè)試即可。測(cè)試前，先在后端Master上授權(quán)MySQL Router節(jié)點(diǎn)允許連接，它將會(huì)復(fù)制到兩個(gè)slave節(jié)點(diǎn)上。

mysql> grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';

連上MySQL Router的7002端口，這個(gè)端口是負(fù)責(zé)寫(xiě)的端口。由于沒(méi)有配置主從高可用，所以，簡(jiǎn)單測(cè)試下是否能寫(xiě)即可。

[root@s1 mr]# mysql -uroot -pP@ssword1! -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 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -h192.168.100.21 -P7002 -e 'create database mytest;'
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@s1 mr]# mysql -uroot -pP@ssword1! -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                |
+--------------------+

再測(cè)試下各slave節(jié)點(diǎn)，是否能實(shí)現(xiàn)rr調(diào)度算法的讀請(qǐng)求的負(fù)載均衡。

[root@s1 mr]# mysql -uroot -pP@ssword1! -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 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -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 |
+-------------+

[root@s1 mr]# mysql -uroot -pP@ssword1! -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                |
+--------------------+

顯然，測(cè)試的結(jié)果一切正常。

這樣看來(lái)MySQL Router好簡(jiǎn)單，確實(shí)好簡(jiǎn)單。只需提供一個(gè)合理的配置文件，一切都完成了。那么，下面解釋下MySQL Router的配置文件。

3.MySQL Router的配置文件解釋

MySQL Router的配置文件也很簡(jiǎn)單，需要配置的項(xiàng)不多。

mysql router默認(rèn)會(huì)尋找安裝目錄下的"mysqlrouter.conf"和家目錄下的".mysqlrouter.conf"。也可以在二進(jìn)制程序mysqlrouter命令下使用"-c"或者"--config"手動(dòng)指定配置文件。

MySQL router的配置文件是片段式的，常用的就3個(gè)片段：[DEFAULT]、[logger]、[routing:NAME]。片段名稱(chēng)區(qū)分大小寫(xiě)，且只支持單行"#"或";"注釋?zhuān)恢С中兄?、行尾注釋?/p>

以上面示例的配置文件為例。

[DEFAULT]
config_folder = /etc/mysqlrouter
logging_folder = /usr/local/mysqlrouter/log
runtime_folder = /var/run/mysqlrouter

[logger]
level = INFO

[routing:slaves]
bind_address = 192.168.100.21:7001
destinations = 192.168.100.23:3306,192.168.100.24:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 192.168.100.21:7002
destinations = 192.168.100.22:3306
mode = read-write
connect_timeout = 2

1.DEFAULT片段的配置。

[DEFAULT]片段通常配置配置文件的目錄、日志的目錄、MySQL router運(yùn)行時(shí)的目錄(如pid文件)。

例如：

[DEFAULT]
config_folder=/etc/mysqlrouter   # 指定額外的配置文件目錄，該目錄下的conf文件都會(huì)被加載
logging_folder=/usr/local/mysqlrouter/log  # 指定日志目錄，日志文件名為mysqlrouter.log
runtime_folder=/var/run/mysqlrouter        # 指定運(yùn)行時(shí)目錄，默認(rèn)為/run/mysqlrouter

2.logger片段的配置。

[logger]片段只有一個(gè)選項(xiàng)，設(shè)置日志的記錄級(jí)別。

[logger]
level=debug   # 有debug、info(默認(rèn))、warning、error、fatal，不區(qū)分大小寫(xiě)

3.routing片段的配置。

[routing:NAME]是MySQL router主要部分，設(shè)置不同的路由實(shí)例，其中NAME可以隨意命名。如[routing:slaves]、[routing:masters]。

在routing配置片段，可以設(shè)置的選項(xiàng)包括：

(1).bind_address和bind_port

bind_address和bind_port是mysql router監(jiān)聽(tīng)前端SQL請(qǐng)求的地址和端口。其中端口是MySQL Router要求強(qiáng)制提供的，但可以不用bind_port綁定，因?yàn)樗捎猛ㄟ^(guò)bind_address的IP:PORT格式指定。
一個(gè)routing規(guī)則中只能設(shè)置一個(gè)地址監(jiān)聽(tīng)指令，但可以通過(guò)"0.0.0.0"來(lái)監(jiān)聽(tīng)主機(jī)上所有的地址。如果沒(méi)有提供監(jiān)聽(tīng)地址，則默認(rèn)監(jiān)聽(tīng)127.0.0.1。

另外，監(jiān)聽(tīng)地址不能出現(xiàn)在destinations指令指定的列表中。

示例如下：

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

一般來(lái)說(shuō)，通過(guò)不同端口實(shí)現(xiàn)讀/寫(xiě)分離，并非好方法，最大的原因是需要在應(yīng)用程序代碼中指定這些連接端口。但是，MySQL Router只能通過(guò)這種方式實(shí)現(xiàn)讀寫(xiě)分離，所以MySQL Router拿來(lái)當(dāng)玩具玩玩就好。

(2).destinations

定義routing規(guī)則的轉(zhuǎn)發(fā)目標(biāo)，格式為HOST:PORT，HOST可以是IP也可以是主機(jī)名，多個(gè)轉(zhuǎn)發(fā)目標(biāo)使用逗號(hào)分隔。如定義的目標(biāo)列表是多個(gè)slave。

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

(3).mode

MySQL router提供兩種mode：read-only和read-write。這兩種方式會(huì)產(chǎn)生不同的轉(zhuǎn)發(fā)調(diào)度方式。

設(shè)置為read-write，常用于設(shè)置destinations為master時(shí)，實(shí)現(xiàn)master的高可用。

• 調(diào)度方式：當(dāng)MySQL router第一次收到客戶(hù)端請(qǐng)求時(shí)，會(huì)將請(qǐng)求轉(zhuǎn)發(fā)給destinations列表中的第一個(gè)目標(biāo)，第二次收到客戶(hù)端請(qǐng)求還是會(huì)轉(zhuǎn)發(fā)給第一個(gè)目標(biāo)，只有當(dāng)?shù)谝粋€(gè)目標(biāo)聯(lián)系不上(如關(guān)閉了MySQL服務(wù)、宕機(jī)等)才會(huì)聯(lián)系第二個(gè)目標(biāo)，如果所有目標(biāo)都聯(lián)系不上，MySQL Router會(huì)中斷。這種調(diào)度方式被稱(chēng)為"first-available"。
• 當(dāng)聯(lián)系上了某一個(gè)目標(biāo)時(shí)，MySQL Router會(huì)將其緩存下來(lái)，下次收到請(qǐng)求還會(huì)繼續(xù)轉(zhuǎn)發(fā)給該目標(biāo)。既然是緩存的目標(biāo)，就意味著在MySQL Router重啟之后就會(huì)失效。
• 所以通過(guò)MySQL Router實(shí)現(xiàn)讀寫(xiě)分離的寫(xiě)時(shí)，可以設(shè)置多個(gè)master，讓性能好的master放在destinations列表的第一個(gè)位置，其他的master放在后面的位置作為備用master。

設(shè)置為read-only，常用于設(shè)置destinations為slave時(shí)，實(shí)現(xiàn)MySQL讀請(qǐng)求負(fù)載均衡。

• 調(diào)度方式：當(dāng)MySQL route收到客戶(hù)端請(qǐng)求時(shí)，會(huì)從destinations列表中的第一個(gè)目標(biāo)開(kāi)始向后輪詢(xún)(round-robin)，第一個(gè)請(qǐng)求轉(zhuǎn)發(fā)給第一個(gè)目標(biāo)，第二個(gè)請(qǐng)求轉(zhuǎn)發(fā)給第二個(gè)目標(biāo)，轉(zhuǎn)發(fā)給最后一個(gè)目標(biāo)之后的下一個(gè)請(qǐng)求又轉(zhuǎn)發(fā)給第一個(gè)目標(biāo)。如果第一個(gè)目標(biāo)不可用，會(huì)依次向后檢查，直到目標(biāo)可用，如果所有目標(biāo)都不可用，則MySQL Router中斷。
• 那些不可用的目標(biāo)會(huì)暫時(shí)被隔離，并且mysql router會(huì)不斷的檢查它們的狀況，當(dāng)重新可用時(shí)會(huì)重新加入到目標(biāo)列表。

(4).connect_timeout

• MySQL Router聯(lián)系destinations的超時(shí)時(shí)間，默認(rèn)為1秒，值的范圍為1-65536。應(yīng)該盡量設(shè)置值小點(diǎn)，免得等待時(shí)間過(guò)長(zhǎng)。
• 對(duì)于read-write模式，可以將超時(shí)時(shí)間設(shè)置的稍長(zhǎng)一點(diǎn)點(diǎn)，防止誤認(rèn)為主master不可用而去聯(lián)系備master。
• 對(duì)于read-only模式，可以將超時(shí)時(shí)間設(shè)置的稍短一點(diǎn)點(diǎn)，因?yàn)檫@種模式下是destinations列表輪詢(xún)的，即使誤判了影響也不會(huì)太大。

(5).其他選項(xiàng)

還能設(shè)置一些其他的指令，如使用的協(xié)議、最大請(qǐng)求數(shù)等，但是都可以不用設(shè)置使用默認(rèn)值，它們都是MySQL Router結(jié)合MySQL優(yōu)化過(guò)的一些選項(xiàng)，本身已經(jīng)較完美了。

配置文件大概就這些內(nèi)容，配置好后，記得先創(chuàng)建default片段中涉及到的目錄。之后就可以啟動(dòng)mysql router提供讀/寫(xiě)分離服務(wù)了。

4.為MySQL Router提供SysV腳本

MySQL Router只提供了一個(gè)主程序(bin目錄下的mysqlrouter)，且該程序只能啟動(dòng)，沒(méi)有停止選項(xiàng)，所以只能使用kill命令來(lái)殺掉進(jìn)程。

MySQL Router也提供了示例啟動(dòng)腳本，該腳本在位置為$basedir/share/doc/mysqlrouter/sample_mysqlrouter.init，但是該腳本是基于Debian平臺(tái)的，在CentOS上需要設(shè)置和安裝一些東西，所以不用它，自己寫(xiě)個(gè)粗糙點(diǎn)的腳本即可。

shell> vim /etc/init.d/mysqlrouter
#!/bin/bash

# chkconfig: - 78 30
# Description: Start / Stop MySQL Router

DAEMON=/usr/local/mysqlrouter
proc=$DAEMON/bin/mysqlrouter
DAEMON_OPTIONS="-c ${DAEMON}/mysqlrouter.conf"

. /etc/init.d/functions

start() {
    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
        ;;
esac

exit $retval   

然后賦予執(zhí)行權(quán)限。

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

到此這篇關(guān)于MySQL Router實(shí)現(xiàn)MySQL的讀寫(xiě)分離的方法的文章就介紹到這了,更多相關(guān)MySQL Router 讀寫(xiě)分離內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論