mysql主從復制與lvs+keepalived實現負載高可用




文件夾
1、前言?? ?4
2、原理?? ?4
2.1、概要介紹?? ?4
2.2、工作原理?? ?4
2.3、實際作用?? ?4
3方案?? ?4
3.1、環境?? ?4
3.2、架構圖?? ?5
3.3、設計原理?? ?6
4、相關軟件安裝?? ?6
4、配置mysql的主從?? ?7
5、通過lvs+keepalived實現負載與熱備，并實現讀寫分離?? ?8

?

1、前言
近期研究了下高可用的東西，這里總結一下mysql主從復制讀寫分離度的高可用方案，能夠提高server的使用效率，也能夠提高提高維護效率。同一時候應用的效率也會有一定的提升，假設改造須要應用改動讀取的ip地址與寫入的ip地址，改造起來還算easy。
2、原理???? ?
2.1、概要介紹
假設將TCP/IP劃分為5層，則Keepalived就是一個類似于3~5層交換機制的軟件，具有3~5層交換功能，其主要作用是檢測webserver的狀 態，假設某臺webserver故障，Keepalived將檢測到并將其從系統中剔除，當該webserver工作正常后Keepalived自己主動將其增加到server 群中，這些工作所有自己主動完畢，而不須要人工干預，僅僅須要人工修復故障的webserver就可以。
2.2、工作原理
Keepalived基于VRRP協議來實現高可用解決方式，利用其避免單點故障，通常這個解決方式中，至少有2臺server執行Keepalived，即一 臺為MASTER，還有一臺為BACKUP，但對外表現為一個虛擬IP，MASTER會發送特定消息給BACKUP，當BACKUP收不到該消息時，則覺得 MASTER故障了，BACKUP會接管虛擬IP，繼續提供服務，從而保證了高可用性，3層機理是發送ICMP數據包即PING給某臺server，假設不痛，則覺得其故障，并從server群中剔除。4層機理是檢測TCPport號狀態來推斷某臺server是否故障，假設故障，則從server群中剔除。5層機理是依據用戶的設定檢查某個server應用程序是否正常執行，假設不正常，則從server群中剔除。3、
2.3、實際作用
Keepalived+lvs主要用作RealServer的健康檢查，以及負載均衡設備MASTER和BACKUP之間failover的實現。
3方案
本案例先使用兩臺linux做雙機MASTER-SLAVE高可用,實現都寫分離,用于提高查詢性能)，採用MYSQL5.6.x的半同步實現數據復制和同步，使用keepalived來監控MYSQL和提供讀寫VIP浮動。Keepalived在這里主要用作RealServer的健康狀態檢查以及LoadBalance主機和BackUP主機之間failover的實現
不論什么一臺主機宕機都不會影響對外提供服務（讀寫vip能夠浮動），保持服務的高可用。

3.1、環境
主機A：192.168.150.171
主機B：192.168.150.172
W-VIP：192.168.150.173? （負責寫入）
R-VIP：192.168.150.174?? （負責讀取）
Client：隨意，僅僅要能訪問以上三個IP就可以

3.2、架構圖
詳細架構圖例如以下：
?
3.3、設計原理(異常情況)
1、?? ?serverA和B，通過mysql的slave進程是用binlog同步數據。
2、?? ?通過keepalived啟用兩個虛IP：W-VIP/R-VIP，一個負責寫入，一個負責讀取，實現讀寫分離。
3、?? ?A和B都存在時，W-VIP下將請求轉發至主機A，R-VIP將請求轉發給A和B，實現負載均衡。
4、?? ?當主機A異常時，B接管服務，W-VIP/R-VIP此時漂到了主機B上，此時這兩個虛IP下都是主機B，實現高可用
5、?? ?當主機B異常時，R-VIP會將B踢出，其它不變

詳細實現后的效果
正常狀態
Prot LocalAddress:Port Scheduler Flags
? -> RemoteAddress:Port?????????? Forward Weight ActiveConn InActConn
TCP? 192.168.150.173:3306 wrr persistent 60
? -> 192.168.150.171:3306?????????? Local?? 3????? 0????????? 0??????? ?
TCP? 192.168.150.174:3306 wrr persistent 60
? -> 192.168.150.172:3306?????????? Route?? 3????? 0????????? 0??????? ?
? -> 192.168.150.171:3306?????????? Local?? 1????? 0????????? 0??????? ?

A故障后，B的狀態
Prot LocalAddress:Port Scheduler Flags
? -> RemoteAddress:Port?????????? Forward Weight ActiveConn InActConn
TCP? 192.168.150.173:3306 wrr persistent 60
? -> 192.168.150.172:3306?????????? Local?? 3????? 0????????? 0??????? ?
TCP? 192.168.150.174:3306 wrr persistent 60
? -> 192.168.150.172:3306?????????? Local?? 3????? 0????????? 0 ?
架構圖

4、相關軟件安裝
1、?? ?mysql? 能夠依據須要進行安裝，此處省略
2、?? ?lvs+keepalived的安裝

關聯lvs與keepalived的ipvs所需的內核信息
ln -s /usr/src/kernels/2.6.18-194.el5-x86_64/ /usr/src/linux

安裝lvs
下載：wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz
tar -zxvf ipvsadm-1.24.tar.gz
cd tar -zxvf ipvsadm-1.24
make
make install
yum install ipv* 安裝
驗證
ipvsadm –v
ipvsadm v1.24 2003/06/07 (compiled with getopt_long and IPVS v1.2.0)說明成功安裝

安裝keepalived
tar –zxvf keepalived-1.2.12.tar.gz
cd keepalived-1.2.12
./configure --prefix=/usr/local/keepalived/
make
make install
ln -s /usr/local/keepalived/etc/keepalived /etc/
ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
ln -s /usr/local/keepalived/bin/genhash /bin/
ln -s /usr/local/keepalived/sbin/keepalived /sbin/

configure時注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes，否則無法關聯ipvs功能

4、配置mysql的主從

Master(210.171)的配置
vi /etc/my.cnf

加入例如以下內容：
server-id = 1??? ##master ID
binlog-do-db = ppl? ##同意同步的庫
binlog-ignore-db = mysql? ##忽略同步的庫，也就是不能同步的庫

##配置文件里還需開啟log-bin，比如log-bin = mysql-bin

mysql –uroot –p
下面內容在mysql中運行
mysql> grant replication slave on *.* to ‘repdb01’@’%’ identified by '123456';
mysql>create database db01;
mysql>flush logs;
mysql>show master status;
mysql>use db01
mysql> create table test(name char);



返回一表格例如以下，記住File的內容，等下slave的配置中要用到
?

Slave的配置
vi /etc/my.cnf

加入例如以下內容：
server-id = 2? ##slave ID
master-host = 192.168.150.171? ##指定master的地址
master-user = repdb01??? ##同步所用的賬號
master-password = 123456?? ##同步所用的密碼
master-port = 3306???? ##master上mysql的端口
replicate-do-db = db01?? ##要同步的庫名
replicate-ignore-db = mysql? ##忽略的庫名
slave-skip-errors = 1062?? ##當同步異常時，那些錯誤跳過，本例為1062錯誤
#log-slave-updates? ##同步的同一時候，也記錄自己的binlog日志，假設還有臺slave是通過這臺機器進行同步，那須要添加此項，
#skip-slave-start? ##啟動時不自己主動開啟slave進程
#read-only? ##將庫設為僅僅讀模式，僅僅能從master同步，不能直接寫入（避免自增鍵值沖突）

mysql –uroot –p
下面內容在mysql中運行
mysql>create database db01;
mysql>change master to master_log_file=’mysql-bin.000007’,master_log=106;
mysql>slave start;
mysql>show slave status \G
在返回值中查看，假設slave_IO_Runing與slave_SQL_Runing的值都為Yes說明同步成功
?

5、通過lvs+keepalived實現負載與熱備，并實現讀寫分離

Master上的配置
vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
?? router_id MySQL-HA
}

vrrp_instance VI_1 {
??? state BACKUP
??? interface eth0
??? virtual_router_id 90
??? priority 100
??? advert_int 1
??? notify_master "/usr/local/mysql/bin/remove_slave.sh"
??? nopreempt
??? authentication {
??????? auth_type PASS
??????? auth_pass abcd1234
??? }
??? virtual_ipaddress {
??????? 192.168.150.173 label eth0:1
?? ???? 192.168.150.174 label eth0:2
??? }
}

virtual_server 192.168.150.173 3306 {
??? delay_loop 2
lb_algo wrr
lb_kind DR
??? persistence_timeout 60
??? protocol TCP
??? real_server 192.168.150.171 3306 {
??? weight 3
?? ?notify_down /usr/local/mysql/bin/mysql.sh
?? ?TCP_CHECK {
??????????? connect_timeout 10
??????????? nb_get_retry 3
??????????? delay_before_retry 3
?? ???? connect_port 3306
??????? }
??? }
}

virtual_server 192.168.150.174 3306 {
??? delay_loop 2
??? lb_algo wrr
??? lb_kind DR
??? persistence_timeout 60
??? protocol TCP
??? real_server 192.168.150.171 3306 {
??? weight 1
?? ?notify_down /usr/local/mysql/bin/mysql.sh
?? ?TCP_CHECK {
??????????? connect_timeout 10
??????????? nb_get_retry 3
??????????? delay_before_retry 3
?? ???? connect_port 3306
??????? }
??? }
??? real_server 192.168.150.172 3306 {
??? weight 3
?? ?TCP_CHECK {
??????????? connect_timeout 10
??????????? nb_get_retry 3
??????????? delay_before_retry 3
?? ???? connect_port 3306
??????? }
??? }
}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash
user=root
password=123456
log=/root/mysqllog/remove_slave.log
#--------------------------------------------------------------------------------------
echo "`date`" >> $log
/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log
/bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash
/etc/init.d/keepalived stop

Slave上的配置

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
?? router_id MySQL-HA
}

vrrp_instance VI_1 {
??? state BACKUP
??? interface eth0
??? virtual_router_id 90
??? priority 99
??? advert_int 1
??? notify_master "/usr/local/mysql/bin/remove_slave.sh"
??? authentication {
??????? auth_type PASS
??????? auth_pass ppl.com
??? }
??? virtual_ipaddress {
??????? 192.168.150.173 label eth0:1
?? ???? 192.168.150.174 label eth0:2
??? }
}

virtual_server 192.168.150.173 3306 {
??? delay_loop 2
??? lb_algo wrr
??? lb_kind DR
??? persistence_timeout 60
??? protocol TCP
??? real_server 192.168.150.172 3306 {
??????? weight 3
?? ?notify_down /usr/local/mysql/bin/mysql.sh
?? ?TCP_CHECK {
??????????? connect_timeout 10
??????????? nb_get_retry 3
??????????? delay_before_retry 3
?? ???? connect_port 3306
??????? }
??? }
}

virtual_server 192.168.150.174 3306 {
??? delay_loop 2
??? lb_algo wrr
??? lb_kind DR
??? persistence_timeout 60
??? protocol TCP
??? real_server 192.168.150.172 3306 {
??????? weight 3
?? ?notify_down /usr/local/mysql/bin/mysql.sh
?? ?TCP_CHECK {
??????????? connect_timeout 10
??????????? nb_get_retry 3
??????????? delay_before_retry 3
?? ???? connect_port 3306
??????? }
??? }
#??? real_server 192.168.150.172 3306 {
#??????? weight 3
#??????? TCP_CHECK {
#??????????? connect_timeout 10
#??????????? nb_get_retry 3
#??????????? delay_before_retry 3
#??????????? connect_port 3306
#??????? }
#??? }
}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash
user=root
password=123456
log=/root/mysqllog/remove_slave.log
#--------------------------------------------------------------------------------------
echo "`date`" >> $log
/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log
/bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash
/etc/init.d/keepalived stop

vi /usr/local/keepalived/bin/lvs-rs.sh

#!/bin/bash
WEB_VIP=192.168.150.174

. /etc/rc.d/init.d/functions

case "$1" in
start)
?????? ifconfig lo:0 $WEB_VIP netmask 255.255.255.255 broadcast $WEB_VIP
?????? /sbin/route add -host $WEB_VIP dev lo:0
?????? echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
?????? echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
?????? echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
?????? echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
?????? sysctl -p >/dev/null 2>&1
?????? echo "RealServer Start OK"

?????? ;;
stop)
?????? ifconfig lo:0 down
?????? route del $WEB_VIP >/dev/null 2>&1
?????? echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
?????? echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
?????? echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
?????? echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
?????? echo "RealServer Stoped"
?????? ;;
status)
??????? # Status of LVS-DR real server.
??????? islothere=`/sbin/ifconfig lo:0 | grep $WEB_VIP`
??????? isrothere=`netstat -rn | grep "lo:0" | grep $web_VIP`
??????? if [ ! "$islothere" -o ! "isrothere" ];then
??????????? # Either the route or the lo:0 device
??????????? # not found.
??????????? echo "LVS-DR real server Stopped."
??????? else
??????????? echo "LVS-DR Running."
??????? fi
;;
*)
??????? # Invalid entry.
??????? echo "$0: Usage: $0 {start|status|stop}"
??????? exit 1
;;
esac
exit 0

chmod 755 /usr/local/keepalived/bin/lvs-rs.sh
echo “/usr/local/keepalived/bin/lvs-rs.sh start” >>/etc/rc.local

vi /etc/my.cnf
將這兩個參數前邊的 # 去掉，重新啟動mysql
#skip-slave-start ?
#read-only
登陸mysql，手動將slave進程啟動
mysql>slave start;

先啟動master上的keepalived，正常后再啟動slave上的。
啟動后 主庫能夠查看ip a
[root@rac3 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
??? link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
??? inet 127.0.0.1/8 scope host lo
??? inet6 ::1/128 scope host
?????? valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
??? link/ether 00:50:56:95:06:1f brd ff:ff:ff:ff:ff:ff
??? inet 192.168.150.171.171/24 brd 192.168.0.255 scope global eth0
??? inet 192.168.150.173/32 scope global eth0:1
??? inet 192.168.150.174/32 scope global eth0:2
??? inet6 fe80::250:56ff:fe95:61f/64 scope link
?????? valid_lft forever preferred_lft forever
3: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
slave上查看
[root@rac1 keepalive]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue
??? link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
??? inet 127.0.0.1/8 scope host lo
??? inet 192.168.150.174/32 brd 192.168.150.174 scope global lo:0
??? inet6 ::1/128 scope host
?????? valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
??? link/ether 00:50:56:95:5e:b4 brd ff:ff:ff:ff:ff:ff
??? inet 192.168.150.188/24 brd 192.168.0.255 scope global eth0
??? inet 192.168.150.252/24 brd 192.168.0.255 scope global secondary eth0:1
??? inet 192.168.150.186/24 brd 192.168.0.255 scope global secondary eth0:4
??? inet6 fe80::250:56ff:fe95:5eb4/64 scope link
?????? valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
??? link/ether 00:50:56:95:11:ba brd ff:ff:ff:ff:ff:ff
??? inet 10.10.10.188/24 brd 10.10.10.255 scope global eth1
??? inet 169.254.157.163/16 brd 169.254.255.255 scope global eth1:1
??? inet6 fe80::250:56ff:fe95:11ba/64 scope link
?????? valid_lft forever preferred_lft forever
4: sit0: <NOARP> mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0

發現210.174 讀的vip 在主備機上都能夠看到
210.173 寫入vip在主上才干看到

興許多臺實驗進行中，敬請等待