keepalived + mysqlroute +mysql實現mysql高可用

標籤：mysql高可用

一、環境介紹：1.1伺服器角色配置：

mysql-master 10.0.0.101(外)  172.168.1.101(內) mysql-slave  10.0.0.103 (外) 172.168.1.103(內)mysql-router01   10.0.0.102 (外) 172.168.1.102(內)mysql-router02   10.0.0.104 (外) 172.168.1.104(內)jumpserver      10.0.0.128 (外) 172.168.1.128(內)

1.2設定資料庫的一主兩從庫

Slave01 和master都是允許讀寫的，slave02提供唯讀
同時MySQL的主從複製都是事先配置成功的(此環境是開啟GTID模式的複製)

1.3部署web應用jumpserver

jumpserver web 10.0.0.128(外) 172.168.1.128（內）
jumpsever設定檔如下：

[[email protected] jumpserver]# cat /opt/jumpserver/jumpserver.conf [base]url = http://10.0.0.128key = f1tty6elu8h03x2kip = 0.0.0.0  ###預設監聽任何IPport = 8000  ###為jumpsever的預設的web連接埠log = debug[db]engine = mysqlhost = 172.168.1.20  ####為虛擬VIPport = 7001     ####為mysqlroute讀寫入模式的預設連接埠####jumpserver的串連資料庫的地址user = jumpserverpassword = jumpserverdatabase = jumpserver[mail]mail_enable = 1email_host = [email protected]email_port = 25email_host_user = [email protected]email_host_password = weuidnreemail_use_tls = Falseemail_use_ssl = False[connect]nav_sort_by = ip

啟動jumpsever服務：

[[email protected] jumpserver]# /opt/jumpserver/service.sh stop[[email protected] jumpserver]# /opt/jumpserver/service.sh start[[email protected] jumpserver]# /opt/jumpserver/service.sh statusjumpserver is running...                                   [  OK  ][[email protected] jumpserver]#[[email protected] jumpserver]# ps -ef|grep pythonroot       2443   2441  0 May14 ?        00:00:00 /bin/bash -c ulimit -S -c 0 >/dev/null 2>&1 ; python ./run_server.pyroot       2444   2443  0 May14 ?        00:00:04 python ./run_server.pyroot       9081   8974  0 14:50 pts/0    00:00:00 grep python

1.4安裝keepalived和mysql-router

mysql-router01 10.0.0.102 mysql-router02 10.0.0.104
在10.0.0.102 機器上安裝keepalived 和mysql-router
在10.0.0.104 機器上安裝keepalived 和mysql-router

[[email protected] ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6slave01 172.168.1.103master01 172.168.1.101router01  172.168.1.102router02  172.168.1.104slave02 172.168.1.105

重要提示：
為快速實現環境達到示範效果，建議6台台虛擬機器先關閉selinux和iptables，保證伺服器的系統時間都是一致的，必須保證iptables不是開機自啟動，安裝keepalived和mysql-router後，要保證這2個服務是開啟自啟動的。

二、keepalived相關原理介紹：

Keepalived 是一種高效能的伺服器高可用或熱備解決方案，Keepalived可以用來防止伺服器單點故障(單點故障是指一旦某一點出現故障就會導致整個系統架構的不可用)的發生，通過配合Nginx可以實現web前端服務的高可用。以下本文會介紹keepalived結合mysql-router實現資料庫得高可用

Keepalived實現的基礎是VRRP協議，Keepalived就是巧用VRRP協議來實現高可用性(HA)的.

VRRP(Virtual Router Redundancy Protocol)協議是用於實現路由器冗餘的協議，VRRP協議將兩台或多台路由器裝置虛擬成一個裝置，對外提供虛擬路由器IP(一個或多個)，而在路由器組內部，如果實際擁有這個對外IP的路由器如果工作正常的話就是MASTER，或者是通過演算法選舉產生，MASTER實現針對虛擬路由器IP的各種網路功能，如ARP請求，ICMP，以及資料的轉寄等；其他裝置不擁有該IP，狀態是BACKUP，除了接收MASTER的VRRP狀態通告資訊外，不執行對外的網路功能。當主機失效時，BACKUP將接管原先MASTER的網路功能。

VRRP協議使用多播資料來傳輸VRRP資料，VRRP資料使用特殊的虛擬源MAC地址發送資料而不是自身網卡的MAC地址，VRRP運行時只有MASTER路由器定時發送VRRP通告資訊，表示MASTER工作正常以及虛擬路由器IP(組)，BACKUP只接收VRRP資料，不發送資料，如果一定時間內沒有接收到MASTER的通告資訊，各BACKUP將宣告自己成為MASTER，發送通告資訊，重新進行MASTER選舉狀態。

三．Mysql-router相關原理介紹：3.1.什麼是mysql route

MySQL Router是處於應用client和dbserver之間的輕量級代理程式，它能檢測，分析和轉寄查詢到後端資料庫執行個體，並把結果返回給client。是mysql-proxy的一個替代品。其架構圖和功能如下：

（1）Router實現讀寫分離，程式不是直接連接資料庫IP，而是固定串連到mysql router。MySQL Router對前端應用是透明的。應用程式把MySQL Router當作是普通的mysql執行個體，把查詢發給MySQL Router,而MySQL Router會把查詢結果返回給前端的應用程式。

（2）從資料庫伺服器故障，業務可以正常運行。由MySQL Router來進行自動下線不可用伺服器。程式配置不需要任何修改。

（3）主要資料庫故障，由MySQL Router來決定主從自動切換，業務可以正常訪問。程式配置不需要做任何修改。

3.2.讀寫分離原理

MySQL Router接受前端應用程式請求後，根據不同的連接埠來區分讀寫，把串連讀寫連接埠的所有查詢發往主庫，把串連唯讀連接埠的select查詢以輪詢方式發往多個從庫，從而實現讀寫分離的目的。讀寫返回的結果會交給MySQL Router,由MySQL Router返回給用戶端的應用程式。

3.3.Mysql router用途

MySQL Router的主要用途是讀寫分離，主主故障自動切換，負載平衡，串連池等。

3.4.Mysql router主主故障自動切換的坑

Mysql router主主故障切換功能經過測試沒有問題，但是有一個比較大的坑需要注意
Mysql router的主主故障切換如果是運行在一主一從的情況下，從庫作為主庫的備份，這種情況是使用mysql router主主故障切換是沒有問題的，但是在一主多從的情況下使用主主故障切換就會處在如下問題：mysql主庫掛掉了，從庫slave01和slave02卻一直是同步的原來的主庫master的IP地址，導致slave上的IO線程一直是connecting狀態,導致slave02複製失敗。

四．軟體具體安裝部署

10.0.0.102機器上安裝mysql-router 和keepalived
此機器上的keepalived是主

yum -y install openssl-devel wget http://www.keepalived.org/software/keepalived-1.4.0.tar.gz tar xf keepalived-1.4.0.tar.gz  -C /usr/local/ cd /usr/local/keepalived-1.4.0/ ./configure make make install  mkdir /etc/keepalived find /usr/local/keepalived-1.4.0/ -name "keepalived.conf" cp /usr/local/keepalived-1.4.0/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/  find / -name "keepalived" cp /usr/local/etc/sysconfig/keepalived  /etc/sysconfig/ cp /usr/local/keepalived-1.4.0/keepalived/etc/init.d/keepalived /etc/init.d/ chmod +x /etc/init.d/keepalived  chkconfig keepalived on cp /usr/local/sbin/keepalived /usr/sbin/ which keepalived cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.ori

下面的設定檔是master主keepalived的設定檔

vim /etc/keepalived/keepalived.conf [[email protected] ~]# cat /etc/keepalived/keepalived.confglobal_defs {   notification_email {   [email protected]   }   notification_email_from [email protected]   smtp_server 192.168.200.1   smtp_connect_timeout 30   **router_id LVS_01**}vrrp_instance VI_1 {   ** state MASTER**    **interface eth1**    virtual_router_id 51    **priority 120**    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {    **172.168.1.20/24**    }} /etc/init.d/keepalived status /etc/init.d/keepalived start

同樣的方式在10.0.0.104機器上安裝mysql-router 和keepalived

此機器上的keepalived是從
下面是從keepalived機器的設定檔介紹：

[[email protected] ~]# cat /etc/keepalived/keepalived.confglobal_defs {   notification_email {   [email protected]   }   notification_email_from [email protected]   smtp_server 192.168.200.1   smtp_connect_timeout 30  ** router_id LVS_02**}vrrp_instance VI_1 {**    state BACKUP    interface eth1**    virtual_router_id 51   ** priority 80**    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {   ** 172.168.1.20/24**    }}

主從keepalived參數介紹：
先是主伺服器：
global_defs
{
notification_email #通知email，根據實際情況配置br/>{
[email protected]
notification_email_from [email protected]
smtp_server 127.0.0.1
stmp_connect_timeout 30
router_id node1 #節點名標識，主要用於通知中
}

vrrp_instance VI_NODE {
state MASTER #配置為主伺服器
interface eth0 #通訊網卡
virtual_router_id 100 #路由辨別碼
priority 200 #優先順序，0-254
advert_int 5 #通知間隔，實際部署時可以設定小一點，減少延時

authentication {
auth_type PASS
auth_pass 123456 #驗證密碼，用於通訊主機間驗證
}

virtual_ipaddress {
192.168.1.206 #虛擬ip，可以定義多個
}
}
接下是從伺服器設定：
global_defs {
notification_email {br/>[email protected]
notification_email_from [email protected]
smtp_server 127.0.0.1
stmp_connect_timeout 30
router_id node2
}

vrrp_instance VI_NODE {
state BACKUP #與主伺服器對應
interface eth0 #從伺服器的通訊網卡
virtual_router_id 100 #路由辨別碼，和主伺服器相同
priority 100 #優先順序，小於主伺服器即可
advert_int 5 #這裡是接受通知間隔，與主伺服器要設定相同

authentication {
auth_type PASS
auth_pass 123456 #驗證密碼，與主伺服器相同
}

virtual_ipaddress {
192.168.1.206 #虛擬IP，也要和主伺服器相同
}
}
上面的設定是最基礎的設定，實現的功能是如果主伺服器的Keepalived停止服務（一般情況下伺服器宕機），則將虛擬IP切換至從伺服器，主伺服器恢複後從新切換回主伺服器。

4.1、Keepalived的設定檔介紹來實現虛擬VIP漂移

注意：1、兩個keepalived.conf設定檔中添加的虛擬網卡要一樣，並且不要和已使用伺服器的內外網卡一樣這樣就實現不了心跳了。
可以是同一網段局域內網卡，也可以是同一網段外網網卡
2、當主keepalived應用開啟的時候，是可以看到虛擬IP地址的

[[email protected] ~]# ps -ef|grep keepalivedroot       9265      1  0 07:24 ?        00:00:00 keepalived -Droot       9267   9265  0 07:24 ?        00:00:00 keepalived -Droot       9268   9265  0 07:24 ?        00:00:05 keepalived -Droot       9517   9492  0 10:02 pts/3    00:00:00 grep keepalived[[email protected] ~]# ip addr|grep 172.168.1.20inet 172.168.1.20/24 scope global secondary eth1

這時從keepalived程式也開啟，但是在從本地是grep不到虛擬IP地址的

[[email protected] ~]#  ps -ef|grep keepalivedroot      13115      1  0 07:08 ?        00:00:00 keepalived -Droot      13117  13115  0 07:08 ?        00:00:00 keepalived -Droot      13118  13115  0 07:08 ?        00:00:01 keepalived -Droot      13271  13232  0 10:02 pts/2    00:00:00 grep keepalived[[email protected] ~]# ip a|grep 172.168.1.20[[email protected] ~]#

一旦主keepalived程式關閉或者是伺服器掛掉，這時虛擬VIP就可以實現跳躍到從keepalived機器上。

[[email protected] ~]# /etc/init.d/keepalived stopStopping keepalived:                                   [  OK  ][[email protected] ~]# ps -ef|grep keepalivedroot       9540   9492  0 10:21 pts/3    00:00:00 grep keepalived[[email protected] ~]#[[email protected] ~]# ip a|grep 172.168.1.20    inet 172.168.1.20/24 scope global secondary eth1[[email protected] ~]#

提示：
如果從負載平衡伺服器grep不到虛擬網卡原因有：
1、本機伺服器keepalived程式可能關閉，得restart從啟才行，才能實現虛擬網卡的跳躍。
2、就是主負載平衡伺服器keepalived程式沒有關閉而導致
3、主負載平衡伺服器只要keepalived服務啟動就不會出現上述的問題。
4.伺服器的iptables沒關閉導致，或者是selinxu沒關閉也有可能導致

4.2．安裝mysql-router

10.0.0.102 10.0.0.104兩個機器安裝mysql-router

採用二進位安裝mysql-router：

tar xf mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/cd /usr/local/ln -s mysql-router-2.1.6-linux-glibc2.12-x86-64bit mysql-routermkdir /etc/mysql-route/mkdir /data/log/mysql-route -pcp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.confcp  /etc/mysql-route/mysqlrouter.conf  /etc/mysql-route/mysqlrouter.conf.ori

4.3設定檔介紹如下：

[[email protected] mysql-route]# cat mysqlrouter.conf[DEFAULT]##日誌存放目錄logging_folder = /data/log/mysql-route##外掛程式存放目錄plugin_folder = /usr/local/mysql-router/lib/mysqlrouter###設定檔存放目錄config_folder = /etc/mysql-route###運行目錄runtime_folder = /var/run[logger]###日誌運行層級level = INFO#[fabric_cache]#address = your_fabric_node.example.com:32275#user =###主節點容錯移轉 [routing:basic_failover]#To be more transparent, use MySQL Server port 3306##寫節點地址bind_address=172.168.1.20  ##{此IP地址為虛擬VIP}##寫節點連接埠bind_port = 7001##主庫為讀寫入模式mode = read-write###172.168.1.101為mysql 主 172.168.1.103為mysql從#### 主節點地址：預設情況下第一台主要資料庫為寫主庫，當第一台主要資料庫DOWN機後，第二台資料庫被提升為主庫destinations = 172.168.1.101:3306,172.168.1.103:3306[routing:balancing]bind_address=172.168.1.20bind_port = 7002connect_timeout = 3max_connections = 1024###為mysql從庫（此處只負責讀）destinations = 172.168.1.105:3306mode = read-only#[routing:homepage_reads_fabric]#bind_port = 7002#destinations = fabric+cache:///group/homepage_group?allow_primary_reads=yes#mode = read-only#If no plugin is configured which starts a service, keepalive#will make sure MySQL Router will not immediately exit. It is#safe to remove once Router is configured.[keepalive]interval = 60

啟動mysqlrouter服務

/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

關閉iptables 保證mysql-router開機自啟動

[[email protected] ~]# chkconfig --list|grep iptablesiptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off[[email protected] ~]# chkconfig  iptables off[[email protected]~]# vim /etc/rc.local #!/bin/shtouch /var/lock/subsys/local/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &[[email protected] ~]# chkconfig --list|grep iptablesiptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off[[email protected] ~]# chkconfig  iptables off[[email protected] ~]# vim /etc/rc.local #!/bin/shtouch /var/lock/subsys/local/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

4.4建立測試賬戶進行測試：

登入mysql master主庫進行建立後，賬戶會同步到各個slave上

mysql> grant all on jumpserver.* to [email protected]‘172.168.1.%‘ identified by ‘jumpserver‘;[[email protected] ~]#  mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like ‘hostname‘;"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+----------+| Variable_name | Value    |+---------------+----------+| hostname      | master01 |[[email protected] ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like ‘hostname‘;"+---------------+----------+| Variable_name | Value    |+---------------+----------+| hostname      | master01 |[[email protected] ~]# mysql -h172.168.1.20 -P7001 -ujumpserver -pjumpserver -e "show variables like ‘hostname‘;"mysql: [Warning] Using a password on the command line interface can be insecure.+---------------+----------+| Variable_name | Value    |+---------------+----------+| hostname      | master01 |

keepalived + mysqlroute +mysql實現mysql高可用