天天看點
傳回

mysql主從之keepalive+MySQL高可用

一 keepalive介紹

1.1 keepalived 是什麼

keepalived 是叢集管理中保證叢集高可用的一個服務軟體，用來防止單點故障。

1.2 keepalived 工作原理

mysql主從之keepalive+MySQL高可用

keepalived 是以VRRP 協定為實作基礎的，VRRP 全稱VirtualRouter Redundancy Protocol，即虛拟路由備援協定。虛拟路由備援協定，可以認為是實作路由器高可用的協定，即将N 台提供相同功能的路由器組成一個路由器組，這個組裡面有一個master 和多個backup，master 上面有一個對外提供服務的vip（該路由器所在區域網路内其他機器的預設路由為該vip），master 會發多點傳播，當backup 收不到vrrp 包時就認為master 宕掉了，這時就需要根據VRRP 的優先級來選舉一個backup 當master。這樣的話就可以保證路由器的高可用了。

keepalived 主要有三個子產品，分别是core、check 和vrrp。

core 子產品為keepalived 的核心，負責主程序的啟動、維護以及全局配置檔案的加載和解析。

check 負責健康檢查，包括常見的各種檢查方式。

vrrp 子產品是來實作VRRP 協定的。

1.3 實驗環境

master1:192.168.132.121

master2:192.168.132.122

虛拟IP:192.168.132.120

雙主環境

master1

master1
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.132.122
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 4975
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 913
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4975
              Relay_Log_Space: 2618
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 662134df-a196-11e9-b432-000c2963fd11
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 662134df-a196-11e9-b432-000c2963fd11:1-7
            Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,
662134df-a196-11e9-b432-000c2963fd11:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name:

master2

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.132.121
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000008
          Read_Master_Log_Pos: 650
               Relay_Log_File: relay-log.000008
                Relay_Log_Pos: 865
        Relay_Master_Log_File: master-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 650
              Relay_Log_Space: 2098
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 63a7f26f-a196-11e9-a2b2-000c2991dd19
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18
            Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,
662134df-a196-11e9-b432-000c2963fd11:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

 二 配置keepalive

2.1 開始安裝keepalived

[root@master1 ~]# cd /usr/local/src/

[root@master1 src]# wget https://www.keepalived.org/software/keepalived-2.0.17.tar.gz

[root@master1 src]# tar -xf keepalived-2.0.17.tar.gz

[root@master1 src]# cd keepalived-2.0.17

[root@master1 keepalived-2.0.17]# yum install openssl* libnl‐dev* gcc-c++

[root@master1 keepalived-2.0.17]# ./configure --prefix=/usr/local/keepalived

[root@master1 keepalived-2.0.17]# make

[root@master1 keepalived-2.0.17]# make install

[root@master1 keepalived-2.0.17]# cd /usr/local/keepalived/sbin/

[root@master1 sbin]# ll

-rwxr-xr-x. 1 root root 2381368 Jul 9 11:59 keepalived

[root@master1 sbin]# cp keepalived /usr/sbin/

[root@master1 sbin]# ll /usr/sbin/keepalived

-rwxr-xr-x. 1 root root 2381368 Jul 9 12:01 /usr/sbin/keepalived

[root@master1 sbin]# systemctl list-unit-files|grep keepalived

mysql主從之keepalive+MySQL高可用

[root@master1 sbin]# systemctl enable keepalived

mysql主從之keepalive+MySQL高可用

[root@master1 sbin]# mkdir /etc/keepalived

[root@master1 sbin]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@master1 sbin]# ll /etc/keepalived/

-rw-r--r--. 1 root root 3550 Jul 9 12:06 keepalived.conf 

2.2 配置keepailve

root@master1 keepalived-2.0.17]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id db01
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.132.120
    }
}
virtual_server 192.168.132.120 3306 {
    delay_loop 6
    persistence_timeout 50
    protocol TCP

    real_server 192.168.132.121 3306 {
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

配置簡介

global_defs 區域主要是配置故障發生時的通知對象以及機器辨別
notification_email_from [email protected] //通知郵件從哪個位址發出
smtp_server 192.168.200.1 //通知郵件的smtp 位址
smtp_connect_timeout 30 //連接配接smtp 伺服器的逾時時間
router_id db01 // 辨別本節點的字元串， 通常為hostname
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {//VRRP 執行個體state MASTER // MASTER 或BACKUP，為了防止腦裂現象，主備均需要設定為backup 模式，master 模式會搶占VIP
interface eth0 //節點固有IP（非VIP）的網卡，用來發VRRP 包
virtual_router_id 51 //取值在0-255 之間，用來區分多個instance 的VRRP 多點傳播priority 100 //優先級，同一個vrrp_instance 的MASTER優先級必須比BACKUP 高nopreempt //非搶占，配合backup,防止切換後，主庫服務恢複正常後，IP 漂移過來advert_int 1// MASTER 與BACKUP 負載均衡器之間同步檢查的時間間隔，機關為秒。
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { //虛拟ip 位址,可以有多個位址，每個位址占一行，不需要子網路遮罩
192.168.132.120
                  }
}
virtual_server 192.168.200.16 3306 {
delay_loop 6 //健康檢查間隔，機關為秒
persistence_timeout 50 // 會話保持時間，就是把使用者請求轉發給同一個伺服器
protocol TCP //轉發協定，有TCP 和UDP 兩種，一般用TCP
real_server 192.168.132.121 3306 { //真實伺服器，包括IP和端口号
notify_down kill_keepalived.sh
TCP_CHECK {          #通過tcpcheck 判斷RealServer 的健康狀态
connect_timeout 3    #連接配接逾時時間
nb_get_retry 3       #重連次數
delay_before_retry 3 #重連時間間隔
connect_port 3306    #檢測端口
}
}
}

 keepalived 服務配置

#設定不搶占模式（DB1 設定即可）

nopreempt

#虛拟路由辨別，這個辨別是一個數字(1‐255)，在一個VRRP

執行個體中主備伺服器ID 必須一樣

virtual_router_id 68

#優先級，數字越大優先級越高，在一個執行個體中主伺服器優

先級要高于備伺服器

priority 100 #從伺服器99

#設定主備之間同步檢查的時間間隔機關秒

advert_int 1

[root@master1 keepalived-2.0.17]# vim /etc/keepalived/kill_keepalived.sh

[root@master1 keepalived-2.0.17]# chmod +x /etc/keepalived/kill_keepalived.sh

#!/bin/bash
kill -9 $(cat /var/run/keepalived.pid)

[root@master1 keepalived-2.0.17]# systemctl start  keepalived

[root@master1 keepalived-2.0.17]# cat /var/run/keepalived.pid

16287

master2相同做法

配置檔案

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

bal_defs {
   router_id db02
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.132.120
    }
}
virtual_server 192.168.132.120 3306 {
    delay_loop 6
    persistence_timeout 50
    protocol TCP

    real_server 192.168.132.122 3306 {
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

2.3 檢查狀态

[root@master2 ~]# systemctl status keepalived

mysql主從之keepalive+MySQL高可用

 [root@master2 ~]# ps -ef|grep keepalived

mysql主從之keepalive+MySQL高可用

[root@master1 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:91:dd:19 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.121/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32 scope global ens33          #虛拟IP位址
       valid_lft forever preferred_lft forever
    inet6 fe80::b469:7426:8024:a47c/64 scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:91:dd:23 brd ff:ff:ff:ff:ff:ff
    inet 192.168.43.85/24 brd 192.168.43.255 scope global dynamic ens34
       valid_lft 2810sec preferred_lft 2810sec
    inet 192.168.254.121/24 brd 192.168.254.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::4d8:1809:407a:5c0a/64 scope link 
       valid_lft forever preferred_lft forever

 master2檢視

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:11 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64 scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
    inet 192.168.254.122/24 brd 192.168.254.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64 scope link 
       valid_lft forever preferred_lft forever

 三 驗證

授權後嘗試使用192.168.132.20這個IP連接配接資料庫

mysql> grant all on *.*  to [email protected] identified by '1234567';
mysql> flush privileges;

[root@slave ~]# mysql -ureplication -h192.168.132.120 -p1234567

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

 關閉192.168.132.121的mysqld

[root@master1 ~]# systemctl stop mysqld

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host 
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:91:dd:19 brd ff:ff:ff:ff:ff:ff
inet 192.168.132.121/24 brd 192.168.132.255 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b469:7426:8024:a47c/64 scope link 
valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:91:dd:23 brd ff:ff:ff:ff:ff:ff
inet 192.168.43.85/24 brd 192.168.43.255 scope global dynamic ens34
valid_lft 3348sec preferred_lft 3348sec
inet 192.168.254.121/24 brd 192.168.254.255 scope global ens34
valid_lft forever preferred_lft forever
inet6 fe80::4d8:1809:407a:5c0a/64 scope link 
valid_lft forever preferred_lft forever

檢視master2

[root@master2 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:11 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32 scope global ens33    #虛拟IP已經轉移過來
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64 scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
    inet 192.168.254.122/24 brd 192.168.254.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64 scope link 
       valid_lft forever preferred_lft forever

使用slave連接配接

[root@slave ~]# mysql -uroot -h192.168.132.120 -p1234567
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

依然可以通路,雙主的高可用配置完成

在master1開啟MySQL和keepalive後,虛拟IP并不會搶占回來,是因為設定的是非搶占模式,防止虛拟IP跳動影響性能

作者：夢中淚

出處：http://www.cnblogs.com/zyxnhr/

關于作者：雲計算，linux，虛拟化，存儲

---------------------------------------------------------------------------

個性簽名：我以為我很頹廢,今天我才知道,原來我早報廢了。

如果覺得本篇文章最您有幫助，歡迎轉載，且在文章頁面明顯位置給出原文連結！記得在右下角點個“推薦”，部落客在此感謝！

Linux mysql 主從 keepalive高可用
上一篇: Python：json子產品
下一篇: python（json 子產品）

繼續閱讀