我們通常說的雙機熱備是指兩臺機器都在運行,但并不是兩臺機器都同時在提供服務。當提供服務的一臺出現故障的時候,另外一臺會馬上自動接管并且提供服務,而且切換的時間非常短。MySQL雙主復制,即互為Master-Slave(只有一個Master提供寫操作),可以實現數據庫服務器的熱備,但是Master宕機后不能實現動態切換。使用Keepalived,可以通過虛擬IP,實現雙主對外的統一接口以及自動檢查、失敗切換機制,從而實現MySQL數據庫的高可用方案。
過多內容在這里就不做詳細介紹了,下面詳細記錄下Mysql+Keepalived雙主熱備的高可用方案實踐
1)先實施ctcdb1->ctcdb2的主主同步。主主是數據雙向同步,主從是數據單向同步。一般情況下,主庫宕機后,需要手動將連接切換到從庫上。(但是用keepalived就可以自動切換)
2)再結合Keepalived的使用,通過vip實現MySQL雙主對外連接的統一接口。即客戶端通過vip連接數據庫;當其中一臺宕機后,vip會漂移到另一臺上,這個過程對于客戶端的數據連接來說幾乎無感覺,從而實現高可用。
環境描述:
Centos 7.5版本
ctcdb1:192.168.2.101 安裝mysql和keepalived
ctcdb2:192.168.2.102 安裝mysql和keepalived
vip: 192.168.2.103
---------------ctcdb1服務器操作記錄---------------
在my.cnf文件的[mysqld]配置區域添加下面內容:
[root@ctcdb1~]# vi /etc/my.cnf
server-id= 1 #唯一值
auto-increment-increment= 2 #自增值,從1開始,每次遞增2.數值是1,3,5,7……
auto-increment-offset= 1 #第一次加載數值時的偏移值的個位值
[root@ctcdb1~]# /usr/local/mysql/bin/mysqld &
mysql>createuser repl@’192.168.2.101’ identified by ‘msyql123’;
mysql> grantreplication slave,replication client on *.* to repl@’192.168.2.101’identified by ‘msyql123’;
mysql>createuser repl@’192.168.2.102’ identified by ‘msyql123’;
mysql>grant replication slave,replication client on *.*to repl@’192.168.2.102’ identified by ‘msyql123’;
mysql>flush privileges;
(如由主從升級為主主,則由master端執行create user命令,否則會報數據不一致錯)
---------------ctcdb2服務器操作記錄---------------
在my.cnf文件的[mysqld]配置區域添加下面內容:
[root@ctcdb2~]# vi /etc/my.cnf
server-id= 2 #唯一值
auto-increment-increment= 2 #自增值,從2開始,每次遞增2.數值是2,4,6,8……
auto-increment-offset= 2 #第一次加載數值時的偏移值的個位值
[root@ctcdb2~]# /usr/local/mysql/bin/mysqld &
mysql>changemaster tomaster_host=192.168.2.101,master_user=repl,master_password=mysql123,master_auto_position=1;
mysql> startslave;
---查看同步狀態,如下出現兩個“Yes”,表明同步成功!---
mysql> showslave status G;
***************************1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------ctcdb1服務器做同步操作---------------
mysql> changemaster tomaster_host=192.168.2.102,master_user=repl,master_password=mysql123,master_auto_position=1;
mysql> startslave;
mysql> showslave status G;
***************************1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
這樣,ctcdb1就和ctcdb2實現了主主同步
下面開始進行數據驗證:
-----------------主主同步效果驗證---------------------
1) 在ctcdb1數據庫上寫入新數據
mysql> createdatabase text1;
QueryOK, 1 row affected (0.01 sec)
然后在ctcdb2數據庫上查看,發現數據已經同步過來了!
mysql> showdatabases;
+--------------------+
|Database |
+--------------------+
|information_schema |
|text1 |
|mysql |
|performance_schema |
+--------------------+
4 rowsin set (0.00 sec)
2)在ctcdb2數據庫上寫入新數據
mysql> createdatabase text2;
QueryOK, 1 row affected (0.00 sec)
然后在ctcdb1數據庫上查看,發現數據也已經同步過來了!
mysql> showdatabases;
+--------------------+
|Database |
+--------------------+
|information_schema |
|text1 |
|text2 |
|mysql |
|performance_schema |
+--------------------+
5 rowsin set (0.00 sec)
至此,Mysql主主同步環境已經實現。
1、下載keepalived
https://www.keepalived.org/
2、安裝依賴包
[root@ctcdb1~]# yum install kernel-devel openssl-devel pdata/soft-devel gcc
3、解壓
[root@ctcdb1~]#cd /data/soft
[root@ctcdb1~]# tar zxvf /data/soft/keepalived-2.0.16.tar.gz
4、部署keepalived
[root@ctcdb1~]# cd /data/soft/keepalived-2.0.16 ./configure --prefix=/ &&make && make install
[root@ctcdb1~]# cp/data/soft/keepalived-2.0.16/keepalived/etc/init.d/keepalived /etc/init.d/
5、配置文件
[root@ctcdb1~]# cat /etc/keepalived/keepalived.conf
!Configuration File for keepalived
global_defs{
notification_email{
873387660@qq.com
}
notification_email_from873387660@qq.com
smtp_server127.0.0.1
smtp_connect_timeout30
router_idctcdb1 #主機名
}
vrrp_scriptcheck_mysqld { #檢測mysql服務是否在運行。比如進程,用腳本檢測等等
script "/etc/keepalived/mysqlcheck/keepalived_check_mysql.sh"
interval 2 #腳本執行間隔,每2s檢測一次
weight -5 #腳本結果導致的優先級變更,檢測失敗(腳本返回非0)則優先級-5
fall 2 #檢測連續2次失敗才算確定是真失敗。會用weight減少優先級(1-255之間)
rise 1 #檢測1次成功就算成功。但不修改優先級
}
vrrp_instanceVI_1 {
state MASTER #備節點為BACKUP
interface enp0s8 #指定虛擬ip的網卡接口
mcast_src_ip 192.168.2.101 #本機IP
virtual_router_id 51 #路由器標識,MASTER和BACKUP必須是一致的
priority 110 #定義優先級,數字越大,優先級越高,在同一個vrrp_instance下,MASTER的優先級必須大于BACKUP的優先級。這樣MASTER故障恢復后,就可以將vip資源再次搶回來
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.2.103 #vip
}
track_script{
check_mysqld
}
}
[root@ctcdb1~]# cat /etc/keepalived/mysqlcheck/keepalived_check_mysql.sh
#!/bin/bash
counter=$(netstat-na|grep "LISTEN"|grep "3306"|wc -l)
if ["${counter}" -eq 0 ]; then
systemctl stopkeepalived
fi
[root@ctcdb1~]# chmod 755/etc/keepalived/mysqlcheck/keepalived_check_mysql.sh
6、啟動keepalived服務
[root@ctcdb1~]# systemctl start keepalived
(MySQL要先于keepalived啟動)
1、利用Navicat通過vip連接,看是否連接成功。
(192.168.2.100為Navicat地址)
root@localhost[(none)]>show processlist;
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| Id |User |Host |db | Command |Time |State |Info
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| 2| root |localhost |NULL | Query | 0| starting |show processlist |
| 3| system user | |NULL | Connect | 121| Waiting for master to send event |NULL |
| 4| system user | |NULL | Connect | 121| Slave has read all relay log; waiting for more updates |NULL |
| 5| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 6| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 7| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 8| system user | |NULL | Connect | 121| Waiting for an event from Coordinator |NULL |
| 9| repl |192.168.2.102:56970 | NULL | Binlog Dump GTID | 111 |Master has sent all binlog to slave; waiting for more updates |NULL |
| 10 |root |192.168.2.100:59777 | NULL | Sleep | 4| |NULL
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
9 rowsin set (0.00 sec)
2、查看vip
[root@ctcdb1~]# ip a
3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 08:00:27:de:5c:48brd ff:ff:ff:ff:ff:ff
inet 192.168.2.101/24brd 192.168.2.255 scope global enp0s8
valid_lft foreverpreferred_lft forever
inet 192.168.2.103/32scope global enp0s8 #這個32位子網掩碼的vip地址表示該資源目前還在ctcdb1機器上
valid_lft foreverpreferred_lft forever
inet6 fe80::a00:27ff:fede:5c48/64scope link
valid_lft foreverpreferred_lft forever
[root@ctcdb2~]# ip a
3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 08:00:27:ab:4f:cbbrd ff:ff:ff:ff:ff:ff
inet 192.168.2.102/24brd 192.168.2.255 scope global enp0s8
valid_lft foreverpreferred_lft forever
inet6 fe80::a00:27ff:feab:4fcb/64scope link
valid_lft foreverpreferred_lft forever
(2節點未見vip)
3、高可用測試
停止ctcdb1機器上的mysql服務,根據配置中的腳本,mysql服務停了,keepalived也會停,從而vip資源將會切換到ctcdb2機器上。(mysql服務沒有起來的時候,keepalived服務也無法順利啟動!)
---------------ctcdb1服務器操作記錄---------------
root@localhost[(none)]>shutdown;
QueryOK, 0 rows affected (0.00 sec)
[root@ctcdb1~]# tail -f /var/log/messages
May 2519:46:46 ctcdb1 Keepalived[21086]: Stopping
May 2519:46:46 ctcdb1 systemd: Stopping LVS and VRRP High AvailabilityMonitor...
May 2519:46:46 ctcdb1 Keepalived_vrrp[21088]: (VI_1) sent 0 priority
May 2519:46:46 ctcdb1 Keepalived_vrrp[21088]: (VI_1) removingvips. #移除vip
May 2519:46:46 ctcdb1 avahi-daemon[3160]: Withdrawing address record for192.168.2.103 on enp0s8.
May 2519:46:47 ctcdb1 Keepalived_vrrp[21088]: Stopped - used 0.030909 usertime, 0.042863 system time
May 2519:46:47 ctcdb1 Keepalived[21086]: Stopped Keepalived v2.0.16(05/03,2019), git commit v2.0.15-96-g4d492740+
[root@ctcdb1~]# ip a
3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 08:00:27:de:5c:48brd ff:ff:ff:ff:ff:ff
inet 192.168.2.101/24brd 192.168.2.255 scope global enp0s8
valid_lft foreverpreferred_lft forever
inet6 fe80::a00:27ff:fede:5c48/64scope link
valid_lft foreverpreferred_lft forever
---------------ctcdb2服務器操作記錄---------------
[root@ctcdb2~]# tail -f /var/log/messages
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Backup receivedpriority 0 advertisement
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Receive advertisementtimeout
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Entering MASTER STATE
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) settingvips. #設置vip
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: (VI_1) Sending/queueinggratuitous ARPs on enp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 Keepalived_vrrp[20346]: Sending gratuitous ARP onenp0s8 for 192.168.2.103
May 2519:46:46 ctcdb2 avahi-daemon[3149]: Registering new address recordfor 192.168.2.103 on enp0s8.IPv4.
[root@ctcdb2~]# ip a
3:enp0s8:mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 08:00:27:ab:4f:cbbrd ff:ff:ff:ff:ff:ff
inet 192.168.2.102/24brd 192.168.2.255 scope global enp0s8
valid_lft foreverpreferred_lft forever
inet 192.168.2.103/32scope global enp0s8
valid_lft foreverpreferred_lft forever
inet6 fe80::a00:27ff:feab:4fcb/64scope link
valid_lft foreverpreferred_lft forever
root@localhost[(none)]>show processlist;
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| Id |User |Host |db | Command |Time |State |Info
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
| 4| root |localhost |NULL | Query | 0| starting |show processlist |
| 5| repl |192.168.2.101:38742 | NULL | Binlog Dump GTID | 231 |Master has sent all binlog to slave; waiting for more updates |NULL |
| 6| system user | |NULL | Connect | 221| Reconnecting after a failed master event read |NULL |
| 7| system user | |NULL | Connect | 221| Slave has read all relay log; waiting for more updates |NULL |
| 8| system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 9| system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 10 |system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 11 |system user | |NULL | Connect | 221| Waiting for an event from Coordinator |NULL |
| 12 |root |192.168.2.100:59799 | NULL | Sleep | 4| |NULL
+----+-------------+---------------------+------+------------------+------+---------------------------------------------------------------+-----
9 rowsin set (0.00 sec)
4、回切
將1節點MySQL+keepalived啟動即可自動完成切換
本MySQL高可用方案配置簡單,對現有MySQL架構無任何影響,也不需要停止數據庫服務,完全聯機操作即可。有一點需要注意,主從庫的端口必須一樣。
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129963.html
MySQL高可用方案測試 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; margin...
摘要:雙主是一個比較簡單的高可用架構,適用于中小集群,今天就說說怎么用做的高可用。缺點也比較明顯,就是增加從節點的情況下,從節點不會主動切換同步對象,而且腳本需要自己實現,有一定風險。 雙主 + keepalived 是一個比較簡單的 MySQL 高可用架構,適用于中小 MySQL 集群,今天就說說怎么用 keepalived 做 MySQL 的高可用。 1 概述 1.1 keepalive...
摘要:雙主是一個比較簡單的高可用架構,適用于中小集群,今天就說說怎么用做的高可用。缺點也比較明顯,就是增加從節點的情況下,從節點不會主動切換同步對象,而且腳本需要自己實現,有一定風險。 雙主 + keepalived 是一個比較簡單的 MySQL 高可用架構,適用于中小 MySQL 集群,今天就說說怎么用 keepalived 做 MySQL 的高可用。 1 概述 1.1 keepalive...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4099·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3594·2023-01-11 13:20