Mysql+Keepalived實現(xiàn)雙主互備高可用詳細配置-創(chuàng)新互聯(lián)

Mysql+Keepalived雙主互備架構(gòu)搭架
Mysql + Keepalived 實現(xiàn)雙主互備高可用詳細配置
配置前進行校時操作

十余年的臺安網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。全網(wǎng)整合營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整臺安建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。成都創(chuàng)新互聯(lián)公司從事“臺安網(wǎng)站設(shè)計”,“臺安網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。

#安裝ntpdate工具
yum install ntpdate -y
#使用ntpdate校時（后面的是ntp服務(wù)器）
ntpdate pool.ntp.org

一、配置mysql雙主備

0.測試mysql安裝

yum install mariadb mariadb-server -y
systemctl restart mariadb
systemctl enable mariadb
mysql_secure_installation

1.DB1修改配置文件（需重啟）

vim /etc/my.cnf
#在[mysqld]添加
server-id=166
#開啟mysql日志功能
log-bin=mysql-bin
#定義日志命名格式 
relay-log=mysql-relay-bin
#以下table復(fù)制過濾
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=performance_schema.%

2.DB2修改配置文件（需重啟）

vim /etc/my.cnf
#在[mysqld]添加
server-id=168
#開啟mysql日志功能
log-bin=mysql-bin
#定義日志命名格式 
relay-log=mysql-relay-bin

3.DB1,DB2分別創(chuàng)建復(fù)制帳號

mysql -u root -p
#創(chuàng)建用戶slave_up允許從192.168.1網(wǎng)段登錄
create user 'slave_cp'@'192.168.1.%' identified by 'pass';
grant replication slave on *.* to 'slave_cp'@'192.168.1.%';
exit

4.DB1獲取二進制日志信息

mysql -u root -p
#對數(shù)據(jù)庫進行只讀鎖定（防止查看二進制日志同時有人對數(shù)據(jù)庫修改操作）
flush tables with read lock;
#查詢主機二進制文件信息
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 494 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#解除只讀鎖定
unlock tables;
exit

5.同步數(shù)據(jù)庫

#DB1
#備份主數(shù)據(jù)庫，并上傳給從數(shù)據(jù)庫
mysqldump -uroot -p123456 --all-databases --lock-all-tables >/tmp/dbdump.sql
scp /tmp/dbdump.sql 192.168.1.168:/tmp/
#DB2
#從服務(wù)器導入主服務(wù)器數(shù)據(jù)庫
mysql -uroot -p

6.在DB2上將DB1設(shè)置為主服務(wù)器

#開啟mysql防火墻端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
 change master to
master_host='192.168.1.168' ,
master_user='slave_cp', 
master_password='pass',
master_log_file='mysql-bin.000001', 
master_log_pos=494;
#啟動slave
start slave;
#查看是否正常工作
show slave status\G
exit

7.在DB2獲取二進制日志信息

mysql -u root -p
#對數(shù)據(jù)庫進行只讀鎖定（防止查看二進制日志同時有人對數(shù)據(jù)庫修改操作）
flush tables with read lock;
#查詢主機二進制文件信息
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 513721| | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#解除只讀鎖定
unlock tables;
exit

8.在DB1將DB2設(shè)置為主服務(wù)器

change master to
master_host='192.168.1.168' ,
master_user='slave_cp', 
master_password='pass',
master_log_file='mysql-bin.000001', 
master_log_pos=513721;
#啟動slave
start slave;
#查看是否正常工作
show slave status\G
exit

`二、Keepalived配置（/etc/keepalived/keepalived.conf）(yum install keepalived -y)（注意：關(guān)閉selinux策略 setenforce 0）`

1.keepalived配置文件配置

! Configuration File for keepalived
global_defs {
 #設(shè)置報警通知郵件地址，可以設(shè)置多個
 notification_email { 
 msun1996@163.com
 } 
 #設(shè)置郵件的發(fā)送地址 
 notification_email_from keepalived@msun.com 
 #設(shè)置smtp server的地址,該地址必須是存在的
 smtp_server 127.0.0.1 
 #設(shè)置連接smtp server的超時時間 
 smtp_connect_timeout 30 
 #運行Keepalived服務(wù)器的標識，發(fā)郵件時顯示在郵件標題中的信息 
 router_id mysql_msun 
}
#檢測haproxy腳本
vrrp_script chk_mysql {
 script "/etc/keepalived/mysqlcheck/check_slave.sh"
 interval 2
 weight 2
}
#定義VRRP實例,實例名自定義
vrrp_instance mysql_msun {
 #指定Keepalived的角色，MASTER主機 BACKUP備份
 state BACKUP #此處兩個都設(shè)置為BACKUP
 #指定HA監(jiān)測的接口 
 interface eno16777736 
 #虛擬路由標識，這個標識是一個數(shù)字(1-255)，在一個VRRP實例中主備服務(wù)器ID必須一樣 
 virtual_router_id 68 
 #優(yōu)先級，數(shù)字越大優(yōu)先級越高，在一個實例中主服務(wù)器優(yōu)先級要高于備服務(wù)器 
 priority 100 #從服務(wù)器99
 #設(shè)置主備之間同步檢查的時間間隔單位秒 
 advert_int 1 
 #設(shè)置不搶占模式（DB1設(shè)置即可）
 nopreempt 
 #設(shè)置驗證類型和密碼 
 authentication { 
 #驗證類型有兩種{PASS|HA} 
 auth_type PASS 
 #設(shè)置驗證密碼，在一個實例中主備密碼保持一樣 
 auth_pass 1689 
 }
 track_script {
 chk_mysql # 執(zhí)行監(jiān)控的服務(wù)
 } 
 #定義虛擬IP地址,可以有多個，每行一個
 virtual_ipaddress { 
 192.168.1.160
 } 
}

2.創(chuàng)建監(jiān)控腳本（/etc/keepalived/mysqlcheck/check_slave.sh）

#!/bin/bash
#This scripts is check for Mysql Slave status

Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#大延時
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
 echo 'Mysqlbin not found,check the variable Mysqlbin'
 exit 99
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
 exit 1
fi
#Check if the thread run 
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
 exit 1
 elif [[ $SBM -ge $sbm ]];then
 exit 1
 else
 exit 0
fi

3.防火墻策略

#運行VPPR組播（keepalived）
firewall-cmd --permanent --add-rich='rule family="ipv4" destination address="224.0.0.18" protocol value="vrrp" accept'
firewall-cmd --reload

4.測試

1.正常啟動DB1、DB2信息

[root@DB1 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever

[root@DB2 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

2.關(guān)閉DB1數(shù)據(jù)庫，觀察IP

[root@DB1 ~]# systemctl stop mariadb
[root@DB1 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591817sec preferred_lft 604617sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever

[root@DB2 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591669sec preferred_lft 604469sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

3.重啟主機mariadb,觀察IP

[root@DB1 ~]# ip a #DB1不切換成主機（因為設(shè)置為不搶占模式）
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591605sec preferred_lft 604405sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever

[root@DB2 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591533sec preferred_lft 604333sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

4.再次關(guān)閉DB2的maraiadb,問題出現(xiàn)了，預(yù)想當DB2宕機，IP未能正常漂移?。?！

[root@DB2 ~]# systemctl stop mariadb 
[root@DB2 ~]# ip a 
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591326sec preferred_lft 604126sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

[root@DB1 ~]# ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN 
 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: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591241sec preferred_lft 604041sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever

關(guān)于這個問題的思考：先說說本案例中采用的是Keepalived+mysql的組合，Keepalived用了雙備模式，采用腳本檢測應(yīng)用是否正常來決定keepalived的權(quán)重，從而讓正常的數(shù)據(jù)庫能得到VIP而正常工作，考慮到了如果DB1宕掉（僅mysql服務(wù)）（DB1的權(quán)重會低于DB2）,DB2接管服務(wù)，而DB1再次正常時（權(quán)重又會高于DB2），為了避免數(shù)據(jù)庫來回切換，而在DB1的keepalived配置中設(shè)置了不搶占模式來避免此問題。但是，導致了一個新的問題，就算是DB2的數(shù)據(jù)庫不正常工作，(權(quán)重更低于DB2),但因為DB1不搶占模式，DB2仍然會占有VIP,(除非是Keepalived服務(wù)宕掉)。

關(guān)于這個問題的思考解決方案：

1.keepalived取消使用不搶占模式，從而使主機切換正常，但這樣也就使DB1的數(shù)據(jù)庫服務(wù)宕掉再正常啟動時，會搶占DB2的VIP,使數(shù)據(jù)庫進行不必要的切換。

2.修改檢測腳本，keepalived使用檢測腳本正常時返回0，不正常時，直接讓keepalived服務(wù)本身宕掉。這樣情況下，DB1服務(wù)mysql服務(wù)再次正常（并啟動keepalived）,不會搶占DB2的VIP；當DB2mysql服務(wù)不正常工作時，（會自動停止keepalived）,從而使DB1正常得到VIP。

#!/bin/bash
#This scripts is check for Mysql Slave status

Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#大延時
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
 echo 'Mysqlbin not found,check the variable Mysqlbin'
 pkill keepalived
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
 pkill keepalived
fi
#Check if the thread run 
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
 pkill keepalived
 elif [[ $SBM -ge $sbm ]];then
 pkill keepalived
 else
 exit 0
fi

創(chuàng)新互聯(lián)www.cdcxhl.cn，專業(yè)提供香港、美國云服務(wù)器，動態(tài)BGP最優(yōu)骨干路由自動選擇，持續(xù)穩(wěn)定高效的網(wǎng)絡(luò)助力業(yè)務(wù)部署。公司持有工信部辦法的idc、isp許可證，機房獨有T級流量清洗系統(tǒng)配攻擊溯源，準確進行流量調(diào)度，確保服務(wù)器高可用性。佳節(jié)活動現(xiàn)已開啟，新人活動云服務(wù)器買多久送多久。

            

            文章題目：Mysql+Keepalived實現(xiàn)雙主互備高可用詳細配置-創(chuàng)新互聯(lián)            

            網(wǎng)站地址：http://m.jiaotiyi.com/article/dsjpch.html


    
        其他資訊
        
            
                    python parser函數(shù)
                

                    python move函數(shù)
                

                    python loss函數(shù)
                

                    python join函數(shù)
                

                    python item函數(shù)

網(wǎng)站建設(shè)知識

Mysql+Keepalived實現(xiàn)雙主互備高可用詳細配置-創(chuàng)新互聯(lián)

一、配置mysql雙主備

二、Keepalived配置（/etc/keepalived/keepalived.conf）(yum install keepalived -y)（注意：關(guān)閉selinux策略 setenforce 0）

其他資訊

一、配置mysql雙主備

`二、Keepalived配置（/etc/keepalived/keepalived.conf）(yum install keepalived -y)（注意：關(guān)閉selinux策略 setenforce 0）`