MySQL高可用方案——雙主

MySQL的高可用方案有很多種，雙主、MHA、MMM等等，這里只是寫下最簡單的雙主這種高可用方案。

一、配置MySQL互為主從

1、環境準備

注：MySQL已部署完成，可參考博文Centos部署MySQL 5.7進行部署。

2、開啟二進制日志及中繼日志

#主機mysql01配置文件如下：
[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=1          #server_id必須唯一
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
binlog_format = mixed     #指定二進制格式
log-bin=/usr/local/mysql/data/log_bin            #指定二進制日志文件
relay-log=/usr/local/mysql/data/relay-bin        #指定中繼日志
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=1

#主機mysql02配置文件如下：
[root@mysql02 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=2 
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
binlog_format = mixed
log-bin=/usr/local/mysql/data/log_bin
relay-log=/usr/local/mysql/data/relay-bin
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=2

注：mysql01和mysql02只有server-id和auto_increment_offset不同

mysql中有自增長字段，在做數據庫的主主同步時需要設置自增長的兩個相關配置：auto_increment_offset和auto_increment_increment。 auto-increment-increment表示自增長字段每次遞增的量，其默認值是1。它的值應設為整個結構中服務器的總數，我這里用到兩臺服務器，所以值設為2。 auto-increment-offset是用來設定數據庫中自動增長的起點(即初始值)，因為這兩能服務器都設定了一次自動增長值2，所以它們的起點必須得不同，這樣才能避免兩臺服務器數據同步時出現主鍵沖突。

關于“binlog_format = mixed”配置項，是用來定義二進制日志的格式的，有以下三個值可選，如下：

• STATEMENT：基于sql語句來記錄二進制日志，比如有些sql語句可能會影響上百條數據的改動，那么也只是記錄一條sql語句。優點：可以減少二進制日志的大小，減少日志寫入的I/O量。缺點：需要進行數據恢復時，某些自定義的存儲過程或函數可能會失效，數據可能無法恢復。
• ROW：基于行來記錄二進制日志，如果某一條SQL語句影響了多行數據，那么將會記錄多條二進制日志，優點：可以通過二進制日志來精準的恢復數據。缺點：當發生變化的數據量較大時，會給磁盤I/O帶來一定的壓力。
• mixed：基于混合模式來記錄二進制日志。MySQL自行判斷是基于行還是基于sql語句來記錄日志，建議采用這種格式，如果基于sql語句來記錄就可以精準記錄數據的變化，那么就會基于sql語句，如果sql語句中包含存儲過程或環境變量等，那么就會基于行來記錄。

關于二進制日志的更多介紹，可以參考MySQL的官方文檔。

注：可以在my.cnf文件中添加“binlog_do_db=數據庫名”配置項（可以添加多個）來指定要同步的數據庫

3、將mysql02設置為mysql01的從服務器

1）防火墻放行3306端口的流量（兩臺主機都需要放行3306端口，如果防火墻沒有開啟，則可忽略）

[root@mysql01 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@mysql01 ~]# firewall-cmd --reload

2）mysql01上創建授權用戶

[root@mysql01 ~]# mysql -uroot -p123.com
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';

3）查看mysql01的當前binlog狀態信息

mysql> show master status\G
*************************** 1. row ***************************
             File: log_bin.000001     #這個值會用到
         Position: 609         #這個值會用到
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

4）在mysql02上指定mysql01為master，并開啟slave功能

#指定master地址
mysql> change master to master_host='192.168.20.2',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000001',    #必須和master上查看到的名字一樣
    -> master_log_pos=609;      #同上，這個值也是在master上查看到的
#啟動slave功能
mysql> start slave;
#確定配置成功
mysql> show slave status\G            #查看slave狀態
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.2
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000001
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000001
             Slave_IO_Running: Yes               # 這個值必須為Yes
            Slave_SQL_Running: Yes             # 這個值也必須為Yes
#只要上面兩個值為yes，則表示主從沒有問題，
#其中，IO線程是去master上面讀取二進制日志到本地的中繼日志中；
SQL線程是將本地的中繼日志中的內容轉換為sql語句并執行。

4、將mysql01設置為mysql02的從服務器

#主機mysql02上操作如下：
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status\G          #獲取所需的file和Position
*************************** 1. row ***************************
             File: log_bin.000002
         Position: 609
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

#主機mysql01上操作如下：
#指定mysql02為master
mysql> change master to master_host='192.168.20.3',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000002',
    -> master_log_pos=609;
mysql> start slave;       #啟動slave
mysql> show slave status\G          #查看slave狀態
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.3
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000002
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000002
                #確保下面兩個值為yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、測試主主同步

1）主機mysql01創建測試數據

mysql> create database test;
mysql> use test
mysql> create table t1(id int,name varchar(4));
mysql> insert into t1 values(1,'a'),(2,'b');
#確認mysql01的數據
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

2）確認mysql02已經同步并插入新的數據

#以下操作在主機mysql02上進行
mysql> select * from t1;        #確定數據已同步
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
#插入數據測試
mysql> insert into t1 values(3,'c'),(4,'d');

mysql> select * from t1;        #確定最新數據
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

3）確定mysql01可以同步mysql02的數據

#在mysql01上查詢，是否同步mysql02主機上的數據
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

至此，現在任何一臺MySQL上更新數據都會同步到另一臺MySQL，MySQL同步完成。

注：若主MySQL服務器已經存在，只是后期業務拓展才搭建從服務器，在配置數據庫同步前應先將MySQL服務器的要同步的數據庫拷貝到從服務器上（如先在主MySQL上備份數據庫，再用備份再從MySQL服務器上恢復）。

二、配置keepalived高可用

1、安裝keepalived

兩個節點都需要執行以下命令，以便安裝keepalived。

[root@mysql01 ~]# yum -y install keepalived

2、配置防火墻放行相關流量

注：兩臺主機都需要執行以下命令，以便放行相關流量。224.0.0.18是keepalived的組播地址，使用的是vrrp協議。

[root@mysql02 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
[root@mysql02 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
[root@mysql02 ~]# firewall-cmd --reload

2、修改主機mysql01的keepalived配置文件

[root@mysql01 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-01      #此處的值必須唯一
}

vrrp_instance VI_1 {
    state BACKUP       #指定角色為backup，兩臺MySQL服務器的角色均為backup，設置backup將根據優先級決定主從

    interface ens33      #指定承載虛擬IP的網卡
    virtual_router_id 51         #指定組，同一個集群內的值必須一致。并且不可和局域網中的其他組沖突
    priority 100          #優先級范圍為：0~100
    advert_int 1     #發vrrp包的時間間隔，即多久進行一次master選舉（可認為是健康檢查時間間隔）
    nopreempt             #不搶占，即允許一個priority比較低的節點作為master，     
    authentication {          #認證區域
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {         #VIP區域，指定vip地址
        192.168.20.20
    }
}

virtual_server 192.168.20.20 3306 {    #設置虛擬服務器，需要指定虛擬IP地址和服務端口，IP與端口之間用空格隔開
    delay_loop 2         #設置運行情況檢查時間，單位是秒
    lb_algo rr      #設置后端調度算法
    lb_kind DR    #設置lvs實現負載均衡的機制，有NAT、TUN、DR三個模式，DR模式效率最高
    persistence_timeout 60     #會話保持時間，單位是秒
    protocol TCP     #指定轉發協議類型，有TCP和UDP兩種

    real_server 192.168.20.2 3306 {          #配置服務節點，這里指定的也就是本機的真實IP
        weight 1     #設置權重
    notify_down /etc/keepalived/bin/mysql.sh    #檢測到real_server的MySQL服務宕機后執行的腳本。
    TCP_CHECK {
        connect_port 3306    #健康檢查端口
        connect_timeout 3       #連接超時時間
        retry 3    #重試次數
        delay_before_retry 3      #重連間隔時間
     }
   }
}
#準備指定的腳本
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql01 keepalived]# mkdir bin
[root@mysql01 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived    #停止keepalived服務
[root@mysql01 keepalived]# chmod +x bin/mysql.sh    #賦予腳本執行權限
[root@mysql01 ~]# systemctl start keepalived        #啟動keepalived服務
#確定ens33網卡有虛擬Ip

[root@mysql01 ~]# ip a show ens33    #必須使用ip a命令才可以查看到，ifconfig命令查看不到
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:39:80 brd ff:ff:ff:ff:ff:ff
    inet 192.168.20.2/24 brd 192.168.20.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.20.20/32 scope global ens33     #可以看到指定的VIP已經綁定到ens33上
       valid_lft forever preferred_lft forever
    inet6 fe80::659e:9312:318a:e52b/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

#將keepalived的配置文件發送到mysql02主機上
[root@mysql01 ~]# scp /etc/keepalived/keepalived.conf root@192.168.20.3:/etc/keepalived/

3、修改主機mysql02的keepalived配置文件

#修改msyql01發送來的配置文件
[root@mysql02 keepalived]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-02         #更改router_id，此處在熱備組中必須要唯一
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90             #更改優先級
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.20.20
    }
}

virtual_server 192.168.20.20 3306 {
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 60
    protocol TCP

    real_server 192.168.20.3 3306 {        #更改為本機的IP地址及監聽端口
        weight 1
    notify_down /etc/keepalived/bin/mysql.sh
    TCP_CHECK {
        connect_port 3306
        connect_timeout 3
        retry 3
        delay_before_retry 3
     }
   }
}
#準備所需腳本
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql02 keepalived]# mkdir bin
[root@mysql02 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived
[root@mysql02 keepalived]# chmod +x bin/mysql.sh 
#啟動keepalived
[root@mysql02 ~]# systemctl start keepalived

至此，即可實現MySQL的雙主效果（只要VIP所在的節點，MySQL服務端口無法連接，那么VIP將切換至另一臺節點，即使宕機的mysql服務器恢復，也不會對VIP進行搶占）。雖然有兩臺MySQL數據庫，但是其使用keepalived提供的虛擬IP地址來對外提供服務，不管這個虛擬Ip地址落在哪臺服務器上，都可以保證數據的一致性，因為它們互為主從，并且keepalived的狀態都為backup，也設置了不搶占（減少VIP的切換次數），這樣可以大大的避免keepalived的腦裂問題。