Linux安裝筆記十二：MySQL+Keepalived雙主熱備高可用搭建

雙機(jī)熱備是指兩臺(tái)機(jī)器都在運(yùn)行，但并不是兩臺(tái)機(jī)器都同時(shí)在提供服務(wù)。當(dāng)提供服務(wù)的一臺(tái)出現(xiàn)故障的時(shí)候，另外一臺(tái)會(huì)馬上自動(dòng)接管并且提供服務(wù)，而且切換的時(shí)間非常短。MySQL雙主復(fù)制，即互為Master-Slave（只有一個(gè)Master提供寫(xiě)操作），可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器的熱備，但是一個(gè)Master宕機(jī)后不能實(shí)現(xiàn)動(dòng)態(tài)切換。使用Keepalived可以通過(guò)虛擬IP，實(shí)現(xiàn)雙主對(duì)外的統(tǒng)一接口以及自動(dòng)檢查、失敗切換機(jī)制，從而實(shí)現(xiàn)MySQL數(shù)據(jù)庫(kù)的高可用方案。

本文參考文檔:https://blog.51cto.com/13858192/2175265

0、搭建需求

（1）先實(shí)施Master->Slave的主主同步。主主是數(shù)據(jù)雙向同步，主從是數(shù)據(jù)單向同步。一般情況下，主庫(kù)宕機(jī)后，需要手動(dòng)將連接切換到從庫(kù)上。（但是用keepalived就可以自動(dòng)切換）
（2）再結(jié)合Keepalived的使用，通過(guò)VIP實(shí)現(xiàn)MySQLl雙主對(duì)外連接的統(tǒng)一接口。即客戶(hù)端通過(guò)VIP連接數(shù)據(jù)庫(kù)；當(dāng)其中一臺(tái)宕機(jī)后，VIP會(huì)漂移到另一臺(tái)上，這個(gè)過(guò)程對(duì)于客戶(hù)端的數(shù)據(jù)連接來(lái)說(shuō)幾乎無(wú)感覺(jué)，從而實(shí)現(xiàn)高可用。


架構(gòu)圖.png
角色 IP 系統(tǒng)及所需服務(wù)
Master1 172.20.60.8 centos7 mysql keepalived
Master2 172.20.60.11 centos7 mysql keepalived
VIP 172.20.60.199

注意：防火墻與SELINUX確保已經(jīng)關(guān)閉

1、Master1和Master2都安裝好MySQL

步驟參照【Linux安裝筆記十一】

2、MySQL主主同步環(huán)境部署
（1）在Master1上操作如下

在my.cnf文件的[mysqld]配置區(qū)域添加下面內(nèi)容：

server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all

重啟mysql服務(wù)

service mysqld restart

數(shù)據(jù)同步授權(quán)，這樣I/O線(xiàn)程就可以以這個(gè)用戶(hù)的身份連接到主服務(wù)器，并且讀取它的二進(jìn)制日志。

mysql>  grant replication slave,replication client on *.* to cylian@'%' identified by "cylian";
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
//刷新權(quán)限
Query OK, 0 rows affected (0.00 sec)

mysql>  flush tables with read lock;
//最好將庫(kù)鎖住，僅僅允許讀，以保證數(shù)據(jù)一致性；待主主同步環(huán)境部署后再解鎖；
鎖住后，就不能往表里寫(xiě)數(shù)據(jù)，但是重啟mysql服務(wù)后就會(huì)自動(dòng)解鎖！
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
//log bin日志和pos值位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      612 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
（2）在Master2上操作如下

在my.cnf文件的[mysqld]配置區(qū)域添加下面內(nèi)容：

server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all

重啟mysql服務(wù)

service mysqld restart

數(shù)據(jù)同步授權(quán)，這樣I/O線(xiàn)程就可以以這個(gè)用戶(hù)的身份連接到主服務(wù)器，并且讀取它的二進(jìn)制日志。

mysql>  grant replication slave,replication client on *.* to cylian@'%' identified by "cylian";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>  flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      150 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
（3）在master1上做同步操作
mysql> unlock tables;     //先解鎖，將對(duì)方數(shù)據(jù)同步到自己的數(shù)據(jù)庫(kù)中
mysql> stop slave;
mysql> change  master to master_host='172.20.60.11',master_user='cylian',master_password='cylian',master_log_file='mysql-bin.000004',master_log_pos=150;         
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步狀態(tài)，如下出現(xiàn)兩個(gè)“Yes”，表明同步成功！
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.24.130
                  Master_User: doudou
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 150
               Relay_Log_File: linfan-relay-bin.000002
                Relay_Log_Pos: 312
        Relay_Master_Log_File: mysql-bin.000004
             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: 150
              Relay_Log_Space: 512
              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: dc702f48-b7b9-11e8-9caa-000c298fc02c
             Master_Info_File: /opt/data/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:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified
（4）在master2上做同步操作：
mysql> unlock tables;     //先解鎖，將對(duì)方數(shù)據(jù)同步到自己的數(shù)據(jù)庫(kù)中
mysql> stop slave;
mysql> change  master to master_host='172.20.60.8',master_user='cylian',master_password='cylian',master_log_file='mysql-bin.000001',master_log_pos=612;         
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看同步狀態(tài)，如下出現(xiàn)兩個(gè)“Yes”，表明同步成功！
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.24.130
                  Master_User: doudou
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 150
               Relay_Log_File: linfan-relay-bin.000002
                Relay_Log_Pos: 312
        Relay_Master_Log_File: mysql-bin.000001
             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: 150
              Relay_Log_Space: 512
              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: dc702f48-b7b9-11e8-9caa-000c298fc02c
             Master_Info_File: /opt/data/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:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

4、主主同步效果驗(yàn)證

（1）在master1服務(wù)器的數(shù)據(jù)庫(kù)寫(xiě)入數(shù)據(jù)：
mysql> create database tom;
Query OK, 1 row affected (0.01 sec)

mysql> use tom;
Database changed

mysql> create table mary(id int,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.06 sec)

mysql> insert mary values(1,"lisi",10),(2,"zhangshan",28),(3,"wangwu",18);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mary;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | lisi      |   10 |
|    2 | zhangshan |   28 |
|    3 | wangwu    |   18 |
+------+-----------+------+
3 rows in set (0.00 sec)

然后在master2數(shù)據(jù)庫(kù)上查看，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)同步過(guò)來(lái)了!

（2）在master2數(shù)據(jù)庫(kù)上寫(xiě)入新數(shù)據(jù)
mysql> insert mary values(4,"zhaosi",66),(5,"lida",88);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mary;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | lisi      |   10 |
|    2 | zhangshan |   28 |
|    3 | wangwu    |   18 |
|    4 | zhaosi    |   66 |
|    5 | lida      |   88 |
+------+-----------+------+
5 rows in set (0.00 sec)

然后在master1數(shù)據(jù)庫(kù)上查看，發(fā)現(xiàn)數(shù)據(jù)也已經(jīng)同步過(guò)來(lái)了!

Mysql主主同步環(huán)境已經(jīng)實(shí)現(xiàn)。

5、配置MySQL+Keepalived故障轉(zhuǎn)移的高可用環(huán)境

（1）兩臺(tái)機(jī)器分別安裝Keepalived
[root@localhost ~] yum -y install keepalived
（2）Master1機(jī)器上的keepalived.conf配置

拷貝備份配置文件

[root@localhost ~] cd /etc/keepalived
[root@localhost ~] cp keepalived.conf keepalived.conf.backup

編輯修改keepalived.conf（下面配置中沒(méi)有使用lvs的負(fù)載均衡功能，所以不需要配置虛擬服務(wù)器virtual server）

! Configuration File for keepalived

global_defs {
   notification_email {
     ops@wangshibo.cn
     tech@wangshibo.cn
   }
   notification_email_from ops@wangshibo.cn
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MASTER-HA
}
vrrp_script chk_mysql_port {
    script "/opt/chk_mysql.sh"   #這里通過(guò)腳本監(jiān)測(cè)
    interval 2                   #腳本執(zhí)行間隔，每2s檢測(cè)一次
    weight -5                    #腳本結(jié)果導(dǎo)致的優(yōu)先級(jí)變更，檢測(cè)失?。_本返回非0）則優(yōu)先級(jí) -5
    fall 2                    #檢測(cè)連續(xù)2次失敗才算確定是真失敗。會(huì)用weight減少優(yōu)先級(jí)（1-255之間）
    rise 1                    #檢測(cè)1次成功就算成功。但不修改優(yōu)先級(jí)
}
vrrp_instance VI_1 {
    state MASTER
    interface enp2s0f0                #指定虛擬ip的網(wǎng)卡接口
    mcast_src_ip 172.20.60.8    #本機(jī)ip
    virtual_router_id 51              #路由器標(biāo)識(shí)，MASTER和BACKUP必須一致
    priority 101       #定義優(yōu)先級(jí)，數(shù)字越大，優(yōu)先級(jí)越高，在同一個(gè)vrrp_instance下，MASTER的優(yōu)先級(jí)必須大于BACKUP的優(yōu)先級(jí)。
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.20.60.199  #VIP
    }
    track_script {
       chk_mysql_port
    }
}

編寫(xiě)切換腳本。KeepAlived做心跳檢測(cè)，如果Master的MySQL服務(wù)掛了(3306端口掛了),那么它就會(huì)選擇自殺。Slave的KeepAlived通過(guò)心跳檢測(cè)發(fā)現(xiàn)這個(gè)情況，就會(huì)將VIP的請(qǐng)求接管

vim /opt/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived 
fi
（2）Master2機(jī)器上的keepalived.conf配置

master2機(jī)器上的keepalived.conf文件只修改priority為90、nopreempt不設(shè)置、real_server設(shè)置本地IP。

! Configuration File for keepalived

global_defs {
   notification_email {
     ops@wangshibo.cn
     tech@wangshibo.cn
   }
   notification_email_from ops@wangshibo.cn
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MASTER-HA
}
vrrp_script chk_mysql_port {
    script "/opt/chk_mysql.sh"   #這里通過(guò)腳本監(jiān)測(cè)
    interval 2                   #腳本執(zhí)行間隔，每2s檢測(cè)一次
    weight -5                    #腳本結(jié)果導(dǎo)致的優(yōu)先級(jí)變更，檢測(cè)失?。_本返回非0）則優(yōu)先級(jí) -5
    fall 2                    #檢測(cè)連續(xù)2次失敗才算確定是真失敗。會(huì)用weight減少優(yōu)先級(jí)（1-255之間）
    rise 1                    #檢測(cè)1次成功就算成功。但不修改優(yōu)先級(jí)
}
vrrp_instance VI_1 {
    state MASTER
    interface enp2s0f0                #指定虛擬ip的網(wǎng)卡接口
    mcast_src_ip 172.20.60.11    #本機(jī)ip
    virtual_router_id 51              #路由器標(biāo)識(shí)，MASTER和BACKUP必須一致
    priority 99       #定義優(yōu)先級(jí)，數(shù)字越大，優(yōu)先級(jí)越高，在同一個(gè)vrrp_instance下，MASTER的優(yōu)先級(jí)必須大于BACKUP的優(yōu)先級(jí)。
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        172.20.60.199  #VIP
    }
    track_script {
       chk_mysql_port
    }
}

6、分別啟動(dòng)Keepalived

[root@localhost ~] systemctl start keepalived
[root@localhost ~] systemctl status keepalived
#查看系統(tǒng)日志
[root@localhost ~] tail -f /var/log/messages

7、高可用測(cè)試

（1）通過(guò)Mysql客戶(hù)端通過(guò)VIP連接，看是否連接成功
（2）默認(rèn)情況下，VIP是在master1上的。使用"ip addr"命令查看VIP切換情況
[root@localhost opt]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    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: enp2s0f0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether c4:b8:b4:5d:7b:a0 brd ff:ff:ff:ff:ff:ff
    inet 172.20.60.8/24 brd 172.20.60.255 scope global noprefixroute enp2s0f0
       valid_lft forever preferred_lft forever
    inet 172.20.60.199/32 scope global enp2s0f0  //這個(gè)32位子網(wǎng)掩碼的VIP地址表示該資源目前還在Master1機(jī)器上
       valid_lft forever preferred_lft forever
    inet6 fe80::1d46:a502:f064:c2e2/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: enp2s0f1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether c4:b8:b4:5d:7b:a1 brd ff:ff:ff:ff:ff:ff

停止master1機(jī)器上的mysql服務(wù)，根據(jù)配置中的腳本，mysql服務(wù)停了，keepalived也會(huì)停，從而VIP資源將會(huì)切換到master2機(jī)器上。（mysql服務(wù)沒(méi)有起來(lái)的時(shí)候，keepalived服務(wù)也無(wú)法順利啟動(dòng)?。?/p>

此時(shí)Master1上通過(guò)ip addr發(fā)現(xiàn)這個(gè)32位子網(wǎng)掩碼的VIP地址沒(méi)有了，而Master2上有了ip addr32位子網(wǎng)掩碼的VIP，證明切換過(guò)來(lái)了。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀(guān)點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容