MySQL Router 簡介
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隻是一個玩具,不僅功能少,而且需要在應用程式代碼中指定讀/寫的不同端口(見後文關于配置檔案的解釋),在實際環境中應該沒人會這樣用。更多關于MySQL學習的文章,請參閱:死磕資料庫系列之 MySQL ,本系列持續更新中。
配置MySQL Router
以下是實驗環境。
因為後端MySQL主從複制沒有實作高可用,是以隻有一個master節點負責寫操作。
所有後端MySQL節點都是剛安裝好的全新MySQL執行個體,是以直接開啟主從複制即可。
安裝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:https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz。
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
這就完了,就這麼簡單。
解壓二進制包後,解壓目錄下有以下幾個檔案。
[root@s1 mr]# ls
bin data include lib run share
bin目錄下隻有一個二進制程式mysqlrouter,這也是MySQL Router的主程式。
share目錄下有示例配置檔案和示例SysV風格的啟動腳本,但是很不幸該腳本基于debian平台,在redhat系列上需要修改和安裝一些東西才能使用。是以後文我自己寫了一個centos下的SysV腳本。
[root@s1 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.sh
chmod +x /etc/profile.d/mysqlrouter.sh
source /etc/profile.d/mysqlrouter.sh
啟動并測試 MySQL Router
以下是上述實驗環境的配置檔案,這裡隻有一個master節點192.168.100.22:3306,如果有多個寫節點(master),則使用逗号分隔各節點。關于配置檔案,後文會解釋。
[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 所在的機器上建立上面使用的目錄。
shell> mkdir /etc/mysqlrouter /usr/local/mysqlrouter/log /var/run/mysqlrouter
這樣就可以啟動MySQL Router來提供服務了(啟動之前,請確定後端MySQL已被配置好主從複制)。
[root@s1 mr]# mysqlrouter &
[1] 16122
檢視監聽狀态。這裡監聽的兩個端口7001和7002是前端連接配接MySQL Router用的,它們用來接收前端發送的SQL請求,并按照讀、寫規則,将SQL請求路由到後端MySQL主從節點。
[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
最後進行測試即可。測試前,先在後端Master上授權MySQL Router節點允許連接配接,它将會複制到兩個slave節點上。
mysql> grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';
連上MySQL Router的7002端口,這個端口是負責寫的端口。由于沒有配置主從高可用,是以,簡單測試下是否能寫即可。
[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 |
+--------------------+
再測試下各slave節點,是否能實作rr排程算法的讀請求的負載均衡。
[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 |
+--------------------+
顯然,測試的結果一切正常。
這樣看來MySQL Router好簡單,确實好簡單。隻需提供一個合理的配置檔案,一切都完成了。那麼,下面解釋下MySQL Router的配置檔案。更多關于MySQL學習的文章,請參閱:死磕資料庫系列之 MySQL ,本系列持續更新中。
MySQL Router的配置檔案解釋
MySQL Router的配置檔案也很簡單,需要配置的項不多。
mysql router預設會尋找安裝目錄下的"mysqlrouter.conf"和家目錄下的".mysqlrouter.conf"。也可以在二進制程式mysqlrouter指令下使用"-c"或者"--config"手動指定配置檔案。
MySQL router的配置檔案是片段式的,常用的就3個片段:[DEFAULT]、[logger]、[routing:NAME]。片段名稱區分大小寫,且隻支援單行"#"或";"注釋,不支援行中、行尾注釋。
以上面示例的配置檔案為例。
[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
DEFAULT片段的配置
[DEFAULT]片段通常配置配置檔案的目錄、日志的目錄、MySQL router運作時的目錄(如pid檔案)。
例如:
[DEFAULT]
config_folder=/etc/mysqlrouter # 指定額外的配置檔案目錄,該目錄下的conf檔案都會被加載
logging_folder=/usr/local/mysqlrouter/log # 指定日志目錄,日志檔案名為mysqlrouter.log
runtime_folder=/var/run/mysqlrouter # 指定運作時目錄,預設為/run/mysqlrouter
logger片段的配置
[logger]片段隻有一個選項,設定日志的記錄級别。
[logger]
level=debug # 有debug、info(預設)、warning、error、fatal,不區分大小寫
routing片段的配置
[routing:NAME]是MySQL router主要部分,設定不同的路由執行個體,其中NAME可以随意命名。如[routing:slaves]、[routing:masters]。
在routing配置片段,可以設定的選項包括: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.21
bind_port = 7001
[routing:slaves]
bind_address = 192.168.100.21:7001
一般來說,通過不同端口實作讀/寫分離,并非好方法,最大的原因是需要在應用程式代碼中指定這些連接配接端口。但是,MySQL Router隻能通過這種方式實作讀寫分離,是以MySQL Router拿來當玩具玩玩就好。
destinations
定義routing規則的轉發目标,格式為HOST:PORT,HOST可以是IP也可以是主機名,多個轉發目标使用逗号分隔。如定義的目标清單是多個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
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會不斷的檢查它們的狀況,當重新可用時會重新加入到目标清單。
connect_timeout
MySQL Router聯系destinations的逾時時間,預設為1秒,值的範圍為1-65536。應該盡量設定值小點,免得等待時間過長。
對于read-write模式,可以将逾時時間設定的稍長一點點,防止誤認為主master不可用而去聯系備master。
對于read-only模式,可以将逾時時間設定的稍短一點點,因為這種模式下是destinations清單輪詢的,即使誤判了影響也不會太大。
其他選項
還能設定一些其他的指令,如使用的協定、最大請求數等,但是都可以不用設定使用預設值,它們都是MySQL Router結合MySQL優化過的一些選項,本身已經較完美了。
配置檔案大概就這些内容,配置好後,記得先建立default片段中涉及到的目錄。之後就可以啟動mysql router提供讀/寫分離服務了。
為 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 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
然後賦予執行權限。
shell> chmod +x /etc/init.d/mysqlrouter