雙機熱備是指兩臺機器都在運行淑廊，但并不是兩臺機器都同時在提供服務侥涵。當提供服務的一臺出現(xiàn)故障的時候竞穷，另外一臺會馬上自動接管并且提供服務，而且切換的時間非常短瞒爬。MySQL雙主復制，即互為Master-Slave（只有一個Master提供寫操作）沟堡，可以實現(xiàn)數(shù)據(jù)庫服務器的熱備侧但，但是一個Master宕機后不能實現(xiàn)動態(tài)切換。使用Keepalived可以通過虛擬IP航罗，實現(xiàn)雙主對外的統(tǒng)一接口以及自動檢查禀横、失敗切換機制，從而實現(xiàn)MySQL數(shù)據(jù)庫的高可用方案粥血。
本文參考文檔:https://blog.51cto.com/13858192/2175265
0燕侠、搭建需求
（1）先實施Master->Slave的主主同步。主主是數(shù)據(jù)雙向同步立莉，主從是數(shù)據(jù)單向同步绢彤。一般情況下，主庫宕機后蜓耻，需要手動將連接切換到從庫上茫舶。（但是用keepalived就可以自動切換）
（2）再結合Keepalived的使用，通過VIP實現(xiàn)MySQLl雙主對外連接的統(tǒng)一接口刹淌。即客戶端通過VIP連接數(shù)據(jù)庫饶氏；當其中一臺宕機后，VIP會漂移到另一臺上有勾，這個過程對于客戶端的數(shù)據(jù)連接來說幾乎無感覺疹启，從而實現(xiàn)高可用。
架構圖.png
| 角色 | IP | 系統(tǒng)及所需服務 |
|---|---|---|
| Master1 | 172.20.60.8 | centos7 mysql keepalived |
| Master2 | 172.20.60.11 | centos7 mysql keepalived |
| VIP | 172.20.60.199 |
注意：防火墻與SELINUX確保已經關閉
1蔼卡、Master1和Master2都安裝好MySQL
步驟參照【Linux安裝筆記十一】
2喊崖、MySQL主主同步環(huán)境部署
（1）在Master1上操作如下
在my.cnf文件的[mysqld]配置區(qū)域添加下面內容：
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服務
service mysqld restart
數(shù)據(jù)同步授權，這樣I/O線程就可以以這個用戶的身份連接到主服務器雇逞，并且讀取它的二進制日志荤懂。
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;
//刷新權限
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
//最好將庫鎖住，僅僅允許讀塘砸，以保證數(shù)據(jù)一致性节仿；待主主同步環(huán)境部署后再解鎖；
鎖住后掉蔬，就不能往表里寫數(shù)據(jù)廊宪，但是重啟mysql服務后就會自動解鎖矾瘾！
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ū)域添加下面內容：
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服務
service mysqld restart
數(shù)據(jù)同步授權，這樣I/O線程就可以以這個用戶的身份連接到主服務器箭启，并且讀取它的二進制日志壕翩。
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; //先解鎖，將對方數(shù)據(jù)同步到自己的數(shù)據(jù)庫中
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)兩個“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; //先解鎖赏僧，將對方數(shù)據(jù)同步到自己的數(shù)據(jù)庫中
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)兩個“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、主主同步效果驗證
（1）在master1服務器的數(shù)據(jù)庫寫入數(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ù)庫上查看膛壹，發(fā)現(xiàn)數(shù)據(jù)已經同步過來了!
（2）在master2數(shù)據(jù)庫上寫入新數(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ù)庫上查看驾中，發(fā)現(xiàn)數(shù)據(jù)也已經同步過來了!
Mysql主主同步環(huán)境已經實現(xiàn)。
5模聋、配置MySQL+Keepalived故障轉移的高可用環(huán)境
（1）兩臺機器分別安裝Keepalived
[root@localhost ~] yum -y install keepalived
（2）Master1機器上的keepalived.conf配置
拷貝備份配置文件
[root@localhost ~] cd /etc/keepalived
[root@localhost ~] cp keepalived.conf keepalived.conf.backup
編輯修改keepalived.conf（下面配置中沒有使用lvs的負載均衡功能肩民，所以不需要配置虛擬服務器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" #這里通過腳本監(jiān)測
interval 2 #腳本執(zhí)行間隔，每2s檢測一次
weight -5 #腳本結果導致的優(yōu)先級變更链方，檢測失敵痔怠（腳本返回非0）則優(yōu)先級 -5
fall 2 #檢測連續(xù)2次失敗才算確定是真失敗。會用weight減少優(yōu)先級（1-255之間）
rise 1 #檢測1次成功就算成功祟蚀。但不修改優(yōu)先級
}
vrrp_instance VI_1 {
state MASTER
interface enp2s0f0 #指定虛擬ip的網卡接口
mcast_src_ip 172.20.60.8 #本機ip
virtual_router_id 51 #路由器標識工窍，MASTER和BACKUP必須一致
priority 101 #定義優(yōu)先級，數(shù)字越大前酿，優(yōu)先級越高患雏，在同一個vrrp_instance下，MASTER的優(yōu)先級必須大于BACKUP的優(yōu)先級罢维。
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.20.60.199 #VIP
}
track_script {
chk_mysql_port
}
}
編寫切換腳本淹仑。KeepAlived做心跳檢測，如果Master的MySQL服務掛了(3306端口掛了),那么它就會選擇自殺肺孵。Slave的KeepAlived通過心跳檢測發(fā)現(xiàn)這個情況攻人，就會將VIP的請求接管
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機器上的keepalived.conf配置
master2機器上的keepalived.conf文件只修改priority為90、nopreempt不設置悬槽、real_server設置本地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" #這里通過腳本監(jiān)測
interval 2 #腳本執(zhí)行間隔，每2s檢測一次
weight -5 #腳本結果導致的優(yōu)先級變更初婆，檢測失斉钇隆（腳本返回非0）則優(yōu)先級 -5
fall 2 #檢測連續(xù)2次失敗才算確定是真失敗猿棉。會用weight減少優(yōu)先級（1-255之間）
rise 1 #檢測1次成功就算成功。但不修改優(yōu)先級
}
vrrp_instance VI_1 {
state MASTER
interface enp2s0f0 #指定虛擬ip的網卡接口
mcast_src_ip 172.20.60.11 #本機ip
virtual_router_id 51 #路由器標識屑咳，MASTER和BACKUP必須一致
priority 99 #定義優(yōu)先級萨赁，數(shù)字越大，優(yōu)先級越高兆龙，在同一個vrrp_instance下杖爽，MASTER的優(yōu)先級必須大于BACKUP的優(yōu)先級。
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.20.60.199 #VIP
}
track_script {
chk_mysql_port
}
}
6紫皇、分別啟動Keepalived
[root@localhost ~] systemctl start keepalived
[root@localhost ~] systemctl status keepalived
#查看系統(tǒng)日志
[root@localhost ~] tail -f /var/log/messages
7慰安、高可用測試
（1）通過Mysql客戶端通過VIP連接，看是否連接成功
（2）默認情況下聪铺，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 //這個32位子網掩碼的VIP地址表示該資源目前還在Master1機器上
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機器上的mysql服務，根據(jù)配置中的腳本铃剔，mysql服務停了撒桨，keepalived也會停，從而VIP資源將會切換到master2機器上键兜。（mysql服務沒有起來的時候凤类，keepalived服務也無法順利啟動！）
此時Master1上通過ip addr發(fā)現(xiàn)這個32位子網掩碼的VIP地址沒有了普气，而Master2上有了ip addr32位子網掩碼的VIP谜疤，證明切換過來了。
