https://www.2cto.com/database/201607/522147.html

https://blog.csdn.net/ssdbbg/article/details/8205509

https://www.linuxidc.com/Linux/2015-06/118767.htm

Mysql+keepalived主從切換

一、概述

Keepalived看名字就知道，保持存活，在網(wǎng)絡(luò)里面就是保持在線了，也就是所謂的高可用或熱備，用來(lái)防止單點(diǎn)故障（單點(diǎn)故障是指一旦某一點(diǎn)出現(xiàn)故障就會(huì)導(dǎo)致整個(gè)系統(tǒng)架構(gòu)的不可用）的發(fā)生，那說(shuō)到keepalived不得不說(shuō)的一個(gè)協(xié)議不是VRRP協(xié)議，可以說(shuō)這個(gè)協(xié)議就是keepalived實(shí)現(xiàn)的基礎(chǔ)。

二、配置實(shí)現(xiàn)mysql+keepalived主從切換

網(wǎng)絡(luò)結(jié)構(gòu)：

VIP :192.168.1.172

MYSQL1:192.168.1.100

MYSQL2:192.168.1.104

其中MYSQL1為主服務(wù)器

MYSQL2為從服務(wù)器

1、mysql主主同步

要實(shí)現(xiàn)mysql+keepalived主從切換，首先要實(shí)現(xiàn)的就是兩臺(tái)mysql服務(wù)器的主主同步。關(guān)于主主同步，可以參考之前的文章《mysql數(shù)據(jù)庫(kù)遠(yuǎn)程同步及備份?》

2、安裝keepalived

準(zhǔn)備好源碼包keepalived-1.2.7.tar.gz，然后將其放置在home/software目錄下面：

#tar zxvf keepalived-1.2.7.tar.gz

#cd keepalived-1.2.7

#./configure

出錯(cuò)：

sudo apt-get install openssl

# apt-get install libpopt-dev

?sudo apt-get install libssl-dev

??? sudo apt-get install libssl0.9.8

#make & make install

3、keepalived主備配置文件

下面將詳細(xì)講述在MYSQL主服務(wù)器上對(duì)keepalived的配置。

由于keepalived啟動(dòng)的時(shí)候，默認(rèn)到/etc目錄下面去讀取配置文件，所以要先在/etc目錄下創(chuàng)建一目錄keepalived

#mkdir –p /etc/keepalived

#cd /etc/keepalived

#vin keepalived.conf

! Configuration File for keepalived

global_defs {

?notification_email {

?380934769@qq.com

?}

??notification_email_from 380934769@qq.com

?smtp_connect_timeout 3

?smtp_server 127.0.0.1

?router_id MySQL-ha

}

vrrp_script check_run {?#聲明vrrp_script?的函數(shù)check_run

?script "/root/keepalived_check_mysql.sh"

?interval 5

}

vrrp_sync_group VG1 {

??group {

?VI_1

?}

}

vrrp_instance VI_1 {

?state MASTER?#指定主服務(wù)器節(jié)點(diǎn)為主節(jié)點(diǎn)，備用節(jié)點(diǎn)上設(shè)置為BACKUP即可

?interface eth0?#指定虛擬IP的網(wǎng)絡(luò)接口

?virtual_router_id 88 #VRRP組名，兩個(gè)節(jié)點(diǎn)的設(shè)置必須一樣，以指明各個(gè)節(jié)點(diǎn)屬于同一VRRP組

?priority 100?#主節(jié)點(diǎn)的優(yōu)先級(jí)（1-254之間），備用節(jié)點(diǎn)必須比主節(jié)點(diǎn)優(yōu)先級(jí)低。

??advert_int 1?#組播信息發(fā)送間隔，兩個(gè)節(jié)點(diǎn)設(shè)置必須一樣

?nopreempt??

?authentication {?#設(shè)置驗(yàn)證信息，兩個(gè)節(jié)點(diǎn)必須一致

?auth_type PASS

?auth_pass skyai1.cublog.cn

?}

?track_script {?#調(diào)用函數(shù)代碼塊

?check_run

?}

?virtual_ipaddress {?#虛擬IP

?192.168.1.172

?}

}

MYSQL從服務(wù)器的keepalived配置如下：

! Configuration File for keepalived

global_defs {

?notification_email {

?380934769@qq.com

?}

?notification_email_from?380934769@qq.com

?smtp_connect_timeout 3

?smtp_server 127.0.0.1

?router_id MySQL-ha

}

vrrp_script check_run {

?script "/root/keepalived_check_mysql.sh"

?interval 5

}

vrrp_sync_group VG1 {

?group {

?VI_1

?}

}

vrrp_instance VI_1 {

?state BACKUP

?interface eth0

?virtual_router_id 88

?priority 99

??advert_int 1

?nopreempt

?authentication {

?auth_type PASS

?auth_pass skyai1.cublog.cn

?}

?track_script {

?check_run

?}

?virtual_ipaddress {

?192.168.1.172

?}

}

4、mysql狀態(tài)檢測(cè)腳本

在目錄root下創(chuàng)建腳本keepalived_check_mysql.sh：

#vim /root/keepalived_check_mysql.sh

#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql

MYSQL_HOST=localhost

MYSQL_USER=root

MYSQL_PASSWORD=123456

CHECK_TIME=3

#mysql?is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){

?$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1

?if [ $? = 0 ] ;then

?MYSQL_OK=1

?else

?MYSQL_OK=0

?fi

?return $MYSQL_OK

}

while [ $CHECK_TIME -ne 0 ]

do

?let "CHECK_TIME -= 1"

?check_mysql_helth

if [ $MYSQL_OK = 1 ] ; then

?CHECK_TIME=0

?exit 0

fi

if [ $MYSQL_OK -eq 0 ] &&?[ $CHECK_TIME -eq 0 ]

then

?pkill keepalived

?exit 1

fi

sleep 1

done

5、啟動(dòng)keepalived

#keepalived start

6、查看vrrp通讀記錄

#tcpdump vrrp

此時(shí)顯示是主服務(wù)器在提供服務(wù)。

關(guān)閉主服務(wù)器上的mysql

#mysql stop

然后由keepalived配置文件可以知道，mysql關(guān)閉的話，將會(huì)執(zhí)行keepalived_check_mysql.sh這一腳本。這個(gè)腳本在執(zhí)行的時(shí)候，會(huì)判斷mysql的狀態(tài)，如果mysql關(guān)閉了，將會(huì)關(guān)閉主服務(wù)器上的keepalived。主服務(wù)器上的keepalived一旦關(guān)閉，那么從服務(wù)器馬上變?yōu)橹鞣?wù)器，為用戶提供服務(wù)，如下圖所示：

由上圖可以看到，現(xiàn)在主從服務(wù)器已經(jīng)切換了。

7、查看虛擬IP

由于前面所述，主服務(wù)器的mysql已經(jīng)關(guān)閉，從服務(wù)器已經(jīng)切換為主服務(wù)器，所以在從服務(wù)器上就可以查看到虛擬IP。

#ip a

由圖中可以看到虛擬IP。

此時(shí)，一旦主服務(wù)器上的mysql重啟，然后再啟動(dòng)keepalived之后，主服務(wù)器又會(huì)切換成向用戶提供服務(wù)的服務(wù)器。

由圖可知，已經(jīng)切換成功。

在主服務(wù)器上查看虛擬IP

#ip a

可以看到虛擬IP。OK，mysql+keepalived自動(dòng)切換完成。

環(huán)境描述：

OS：CentOS6.5_X64

MASTER：192.168.0.202

BACKUP：192.168.0.203

VIP：192.168.0.204

1、配置兩臺(tái)Mysql主主同步

[root@master ~]# yum install mysql-server mysql -y?

[root@master ~]# service mysqld start?

[root@master ~]# mysqladmin -u root password 123.com?

[root@master ~]# vi /etc/my.cnf? #開(kāi)啟二進(jìn)制日志，設(shè)置id?

[mysqld]?

server-id = 1? ? ? ? ? ? ? ? ? ? #backup這臺(tái)設(shè)置2?

log-bin = mysql-bin?

binlog-ignore-db = mysql,information_schema? ? ? #忽略寫(xiě)入binlog日志的庫(kù)?

auto-increment-increment = 2? ? ? ? ? ? #字段變化增量值?

auto-increment-offset = 1? ? ? ? ? ? ? #初始字段ID為1?

slave-skip-errors = all? ? ? ? ? ? ? ? ? ? ? #忽略所有復(fù)制產(chǎn)生的錯(cuò)誤?

[root@master ~]# service mysqld restart

#先查看下log bin日志和pos值位置

master配置如下：

[root@ master ~]# mysql -u root -p123.com?

mysql> GRANT? REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED? BY 'replication';?

mysql> flush? privileges;?

mysql> change? master to?

->? master_host='192.168.0.203',?

->? master_user='replication',?

->? master_password='replication',?

->? master_log_file='mysql-bin.000002',?

->? master_log_pos=106;? #對(duì)端狀態(tài)顯示的值?

mysql> start? slave;? ? ? ? #啟動(dòng)同步

backup配置如下：

[root@backup ~]#? mysql -u root -p123.com?

mysql> GRANT? REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED? BY 'replication';?

mysql> flush? privileges;?

mysql> change? master to?

->? master_host='192.168.0.202',?

->? master_user='replication',?

->? master_password='replication',?

->? master_log_file='mysql-bin.000002',?

->? master_log_pos=106;?

mysql> start? slave;

#主主同步配置完畢，查看同步狀態(tài)Slave_IO和Slave_SQL是YES說(shuō)明主主同步成功。

在master插入數(shù)據(jù)測(cè)試下：

在backup查看是否同步成功：

可以看到已經(jīng)成功同步過(guò)去，同樣在backup插入到user表數(shù)據(jù)，一樣同步過(guò)去，雙主就做成功了。

2、配置keepalived實(shí)現(xiàn)熱備

[root@backup ~]# yum install -y pcre-devel openssl-devel popt-devel #安裝依賴包

[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz?

[root@master ~]# tar zxvf keepalived-1.2.7.tar.gz?

[root@master ~]# cd keepalived-1.2.7?

[root@master ~]#./configure --prefix=/usr/local/keepalived?

make && make install

#將keepalived配置成系統(tǒng)服務(wù)

[root@master ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/?

[root@master ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/?

[root@master ~]# mkdir /etc/keepalived/?

[root@master ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/?

[root@master ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

[root@master ~]# vi /etc/keepalived/keepalived.conf?

! Configuration File forkeepalived?

global_defs {?

notification_email {?

test@sina.com?

}?

notification_email_from? admin@test.com?

smtp_server 127.0.0.1?

smtp_connect_timeout 30?

router_id MYSQL_HA? ? ? #標(biāo)識(shí)，雙主相同?

}?

vrrp_instance VI_1 {?

state BACKUP? ? ? ? ? #兩臺(tái)都設(shè)置BACKUP?

interface eth0?

virtual_router_id 51? ? ? #主備相同?

priority 100? ? ? ? ? #優(yōu)先級(jí)，backup設(shè)置90?

advert_int 1?

nopreempt? ? ? ? ? ? #不主動(dòng)搶占資源，只在master這臺(tái)優(yōu)先級(jí)高的設(shè)置，backup不設(shè)置?

authentication {?

auth_type PASS?

auth_pass 1111?

}?

virtual_ipaddress {?

192.168.0.204?

}?

}?

virtual_server 192.168.0.204 3306 {?

delay_loop 2?

#lb_algo rr? ? ? ? ? ? ? #LVS算法，用不到，我們就關(guān)閉了?

#lb_kind DR? ? ? ? ? ? ? #LVS模式，如果不關(guān)閉，備用服務(wù)器不能通過(guò)VIP連接主MySQL?

persistence_timeout 50? #同一IP的連接60秒內(nèi)被分配到同一臺(tái)真實(shí)服務(wù)器?

protocol TCP?

real_server 192.168.0.202 3306 {? #檢測(cè)本地mysql，backup也要寫(xiě)檢測(cè)本地mysql?

weight 3?

notify_down /usr/local/keepalived/mysql.sh? ? #當(dāng)mysq服down時(shí)，執(zhí)行此腳本，殺死keepalived實(shí)現(xiàn)切換?

TCP_CHECK {?

connect_timeout 3? ? #連接超時(shí)?

nb_get_retry 3? ? ? #重試次數(shù)?

delay_before_retry 3 #重試間隔時(shí)間?

}?

}

[root@master ~]# vi /usr/local/keepalived/mysql.sh?

#!/bin/bash?

pkill keepalived?

[root@master ~]# chmod +x /usr/local/keepalived/mysql.sh?

[root@master ~]# /etc/init.d/keepalived start

#backup服務(wù)器只修改priority為90、nopreempt不設(shè)置、real_server設(shè)置本地IP。

#授權(quán)兩臺(tái)Mysql服務(wù)器允許root遠(yuǎn)程登錄，用于在其他服務(wù)器登陸測(cè)試！

mysql> grant all on *.* to'root'@'192.168.0.%' identified by '123.com';

mysql> flush privileges;

3、測(cè)試高可用性

1、通過(guò)Mysql客戶端通過(guò)VIP連接，看是否連接成功。

2、停止master這臺(tái)mysql服務(wù)，是否能正常切換過(guò)去，可通過(guò)ip addr命令來(lái)查看VIP在哪臺(tái)服務(wù)器上。

3、可通過(guò)查看/var/log/messges日志，看出主備切換過(guò)程

4、master服務(wù)器故障恢復(fù)后，是否主動(dòng)搶占資源，成為活動(dòng)服務(wù)器。