mysql主從復(fù)制與lvs+keepalived實(shí)現(xiàn)負(fù)載高可用
文件夾
1、前言?? ?4
2、原理?? ?4
2.1、概要介紹?? ?4
2.2、工作原理?? ?4
2.3、實(shí)際作用?? ?4
3方案?? ?4
3.1、環(huán)境?? ?4
3.2、架構(gòu)圖?? ?5
3.3、設(shè)計(jì)原理?? ?6
4、相關(guān)軟件安裝?? ?6
4、配置mysql的主從?? ?7
5、通過lvs+keepalived實(shí)現(xiàn)負(fù)載與熱備，并實(shí)現(xiàn)讀寫分離?? ?8
?
1、前言
近期研究了下高可用的東西，這里總結(jié)一下mysql主從復(fù)制讀寫分離度的高可用方案，能夠提高server的使用效率，也能夠提高提高維護(hù)效率。同一時(shí)候應(yīng)用的效率也會(huì)有一定的提升，假設(shè)改造須要應(yīng)用改動(dòng)讀取的ip地址與寫入的ip地址，改造起來還算easy。
2、原理???? ?
2.1、概要介紹
假設(shè)將TCP/IP劃分為5層，則Keepalived就是一個(gè)類似于3~5層交換機(jī)制的軟件，具有3~5層交換功能，其主要作用是檢測webserver的狀 態(tài)，假設(shè)某臺(tái)webserver故障，Keepalived將檢測到并將其從系統(tǒng)中剔除，當(dāng)該webserver工作正常后Keepalived自己主動(dòng)將其增加到server 群中，這些工作所有自己主動(dòng)完畢，而不須要人工干預(yù)，僅僅須要人工修復(fù)故障的webserver就可以。
2.2、工作原理
Keepalived基于VRRP協(xié)議來實(shí)現(xiàn)高可用解決方式，利用其避免單點(diǎn)故障，通常這個(gè)解決方式中，至少有2臺(tái)server執(zhí)行Keepalived，即一 臺(tái)為MASTER，還有一臺(tái)為BACKUP，但對(duì)外表現(xiàn)為一個(gè)虛擬IP，MASTER會(huì)發(fā)送特定消息給BACKUP，當(dāng)BACKUP收不到該消息時(shí)，則覺得 MASTER故障了，BACKUP會(huì)接管虛擬IP，繼續(xù)提供服務(wù)，從而保證了高可用性，3層機(jī)理是發(fā)送ICMP數(shù)據(jù)包即PING給某臺(tái)server，假設(shè)不痛，則覺得其故障，并從server群中剔除。4層機(jī)理是檢測TCPport號(hào)狀態(tài)來推斷某臺(tái)server是否故障，假設(shè)故障，則從server群中剔除。5層機(jī)理是依據(jù)用戶的設(shè)定檢查某個(gè)server應(yīng)用程序是否正常執(zhí)行，假設(shè)不正常，則從server群中剔除。3、
2.3、實(shí)際作用
Keepalived+lvs主要用作RealServer的健康檢查，以及負(fù)載均衡設(shè)備MASTER和BACKUP之間failover的實(shí)現(xiàn)。
3方案
本案例先使用兩臺(tái)linux做雙機(jī)MASTER-SLAVE高可用,實(shí)現(xiàn)都寫分離,用于提高查詢性能)，採用MYSQL5.6.x的半同步實(shí)現(xiàn)數(shù)據(jù)復(fù)制和同步，使用keepalived來監(jiān)控MYSQL和提供讀寫VIP浮動(dòng)。Keepalived在這里主要用作RealServer的健康狀態(tài)檢查以及LoadBalance主機(jī)和BackUP主機(jī)之間failover的實(shí)現(xiàn)
不論什么一臺(tái)主機(jī)宕機(jī)都不會(huì)影響對(duì)外提供服務(wù)（讀寫vip能夠浮動(dòng)），保持服務(wù)的高可用。
3.1、環(huán)境
主機(jī)A：192.168.150.171
主機(jī)B：192.168.150.172
W-VIP：192.168.150.173? （負(fù)責(zé)寫入）
R-VIP：192.168.150.174?? （負(fù)責(zé)讀取）
Client：隨意，僅僅要能訪問以上三個(gè)IP就可以
3.2、架構(gòu)圖
詳細(xì)架構(gòu)圖例如以下：
?
3.3、設(shè)計(jì)原理(異常情況)
1、?? ?serverA和B，通過mysql的slave進(jìn)程是用binlog同步數(shù)據(jù)。
2、?? ?通過keepalived啟用兩個(gè)虛IP：W-VIP/R-VIP，一個(gè)負(fù)責(zé)寫入，一個(gè)負(fù)責(zé)讀取，實(shí)現(xiàn)讀寫分離。
3、?? ?A和B都存在時(shí)，W-VIP下將請(qǐng)求轉(zhuǎn)發(fā)至主機(jī)A，R-VIP將請(qǐng)求轉(zhuǎn)發(fā)給A和B，實(shí)現(xiàn)負(fù)載均衡。
4、?? ?當(dāng)主機(jī)A異常時(shí)，B接管服務(wù)，W-VIP/R-VIP此時(shí)漂到了主機(jī)B上，此時(shí)這兩個(gè)虛IP下都是主機(jī)B，實(shí)現(xiàn)高可用
5、?? ?當(dāng)主機(jī)B異常時(shí)，R-VIP會(huì)將B踢出，其它不變
詳細(xì)實(shí)現(xiàn)后的效果
正常狀態(tài)
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的狀態(tài)
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 ?
架構(gòu)圖
4、相關(guān)軟件安裝
1、?? ?mysql? 能夠依據(jù)須要進(jìn)行安裝，此處省略
2、?? ?lvs+keepalived的安裝
關(guān)聯(lián)lvs與keepalived的ipvs所需的內(nèi)核信息
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* 安裝
驗(yàn)證
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時(shí)注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes，否則無法關(guān)聯(lián)ipvs功能
4、配置mysql的主從
Master(210.171)的配置
vi /etc/my.cnf
加入例如以下內(nèi)容：
server-id = 1??? ##master ID
binlog-do-db = ppl? ##同意同步的庫
binlog-ignore-db = mysql? ##忽略同步的庫，也就是不能同步的庫
##配置文件里還需開啟log-bin，比如log-bin = mysql-bin
mysql –uroot –p
下面內(nèi)容在mysql中運(yùn)行
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的內(nèi)容，等下slave的配置中要用到
?
Slave的配置
vi /etc/my.cnf
加入例如以下內(nèi)容：
server-id = 2? ##slave ID
master-host = 192.168.150.171? ##指定master的地址
master-user = repdb01??? ##同步所用的賬號(hào)
master-password = 123456?? ##同步所用的密碼
master-port = 3306???? ##master上mysql的端口
replicate-do-db = db01?? ##要同步的庫名
replicate-ignore-db = mysql? ##忽略的庫名
slave-skip-errors = 1062?? ##當(dāng)同步異常時(shí)，那些錯(cuò)誤跳過，本例為1062錯(cuò)誤
#log-slave-updates? ##同步的同一時(shí)候，也記錄自己的binlog日志，假設(shè)還有臺(tái)slave是通過這臺(tái)機(jī)器進(jìn)行同步，那須要添加此項(xiàng)，
#skip-slave-start? ##啟動(dòng)時(shí)不自己主動(dòng)開啟slave進(jìn)程
#read-only? ##將庫設(shè)為僅僅讀模式，僅僅能從master同步，不能直接寫入（避免自增鍵值沖突）
mysql –uroot –p
下面內(nèi)容在mysql中運(yùn)行
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
在返回值中查看，假設(shè)slave_IO_Runing與slave_SQL_Runing的值都為Yes說明同步成功
?
5、通過lvs+keepalived實(shí)現(xiàn)負(fù)載與熱備，并實(shí)現(xiàn)讀寫分離
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
將這兩個(gè)參數(shù)前邊的 # 去掉，重新啟動(dòng)mysql
#skip-slave-start ?
#read-only
登陸mysql，手動(dòng)將slave進(jìn)程啟動(dòng)
mysql>slave start;
先啟動(dòng)master上的keepalived，正常后再啟動(dòng)slave上的。
啟動(dòng)后 主庫能夠查看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
發(fā)現(xiàn)210.174 讀的vip 在主備機(jī)上都能夠看到
210.173 寫入vip在主上才干看到
興許多臺(tái)實(shí)驗(yàn)進(jìn)行中，敬請(qǐng)等待
文件夾
1、前言?? ?4
2、原理?? ?4
2.1、概要介紹?? ?4
2.2、工作原理?? ?4
2.3、實(shí)際作用?? ?4
3方案?? ?4
3.1、環(huán)境?? ?4
3.2、架構(gòu)圖?? ?5
3.3、設(shè)計(jì)原理?? ?6
4、相關(guān)軟件安裝?? ?6
4、配置mysql的主從?? ?7
5、通過lvs+keepalived實(shí)現(xiàn)負(fù)載與熱備，并實(shí)現(xiàn)讀寫分離?? ?8
?
1、前言
近期研究了下高可用的東西，這里總結(jié)一下mysql主從復(fù)制讀寫分離度的高可用方案，能夠提高server的使用效率，也能夠提高提高維護(hù)效率。同一時(shí)候應(yīng)用的效率也會(huì)有一定的提升，假設(shè)改造須要應(yīng)用改動(dòng)讀取的ip地址與寫入的ip地址，改造起來還算easy。
2、原理???? ?
2.1、概要介紹
假設(shè)將TCP/IP劃分為5層，則Keepalived就是一個(gè)類似于3~5層交換機(jī)制的軟件，具有3~5層交換功能，其主要作用是檢測webserver的狀 態(tài)，假設(shè)某臺(tái)webserver故障，Keepalived將檢測到并將其從系統(tǒng)中剔除，當(dāng)該webserver工作正常后Keepalived自己主動(dòng)將其增加到server 群中，這些工作所有自己主動(dòng)完畢，而不須要人工干預(yù)，僅僅須要人工修復(fù)故障的webserver就可以。
2.2、工作原理
Keepalived基于VRRP協(xié)議來實(shí)現(xiàn)高可用解決方式，利用其避免單點(diǎn)故障，通常這個(gè)解決方式中，至少有2臺(tái)server執(zhí)行Keepalived，即一 臺(tái)為MASTER，還有一臺(tái)為BACKUP，但對(duì)外表現(xiàn)為一個(gè)虛擬IP，MASTER會(huì)發(fā)送特定消息給BACKUP，當(dāng)BACKUP收不到該消息時(shí)，則覺得 MASTER故障了，BACKUP會(huì)接管虛擬IP，繼續(xù)提供服務(wù)，從而保證了高可用性，3層機(jī)理是發(fā)送ICMP數(shù)據(jù)包即PING給某臺(tái)server，假設(shè)不痛，則覺得其故障，并從server群中剔除。4層機(jī)理是檢測TCPport號(hào)狀態(tài)來推斷某臺(tái)server是否故障，假設(shè)故障，則從server群中剔除。5層機(jī)理是依據(jù)用戶的設(shè)定檢查某個(gè)server應(yīng)用程序是否正常執(zhí)行，假設(shè)不正常，則從server群中剔除。3、
2.3、實(shí)際作用
Keepalived+lvs主要用作RealServer的健康檢查，以及負(fù)載均衡設(shè)備MASTER和BACKUP之間failover的實(shí)現(xiàn)。
3方案
本案例先使用兩臺(tái)linux做雙機(jī)MASTER-SLAVE高可用,實(shí)現(xiàn)都寫分離,用于提高查詢性能)，採用MYSQL5.6.x的半同步實(shí)現(xiàn)數(shù)據(jù)復(fù)制和同步，使用keepalived來監(jiān)控MYSQL和提供讀寫VIP浮動(dòng)。Keepalived在這里主要用作RealServer的健康狀態(tài)檢查以及LoadBalance主機(jī)和BackUP主機(jī)之間failover的實(shí)現(xiàn)
不論什么一臺(tái)主機(jī)宕機(jī)都不會(huì)影響對(duì)外提供服務(wù)（讀寫vip能夠浮動(dòng)），保持服務(wù)的高可用。
3.1、環(huán)境
主機(jī)A：192.168.150.171
主機(jī)B：192.168.150.172
W-VIP：192.168.150.173? （負(fù)責(zé)寫入）
R-VIP：192.168.150.174?? （負(fù)責(zé)讀取）
Client：隨意，僅僅要能訪問以上三個(gè)IP就可以
3.2、架構(gòu)圖
詳細(xì)架構(gòu)圖例如以下：
?
3.3、設(shè)計(jì)原理(異常情況)
1、?? ?serverA和B，通過mysql的slave進(jìn)程是用binlog同步數(shù)據(jù)。
2、?? ?通過keepalived啟用兩個(gè)虛IP：W-VIP/R-VIP，一個(gè)負(fù)責(zé)寫入，一個(gè)負(fù)責(zé)讀取，實(shí)現(xiàn)讀寫分離。
3、?? ?A和B都存在時(shí)，W-VIP下將請(qǐng)求轉(zhuǎn)發(fā)至主機(jī)A，R-VIP將請(qǐng)求轉(zhuǎn)發(fā)給A和B，實(shí)現(xiàn)負(fù)載均衡。
4、?? ?當(dāng)主機(jī)A異常時(shí)，B接管服務(wù)，W-VIP/R-VIP此時(shí)漂到了主機(jī)B上，此時(shí)這兩個(gè)虛IP下都是主機(jī)B，實(shí)現(xiàn)高可用
5、?? ?當(dāng)主機(jī)B異常時(shí)，R-VIP會(huì)將B踢出，其它不變
詳細(xì)實(shí)現(xiàn)后的效果
正常狀態(tài)
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的狀態(tài)
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 ?
架構(gòu)圖
4、相關(guān)軟件安裝
1、?? ?mysql? 能夠依據(jù)須要進(jìn)行安裝，此處省略
2、?? ?lvs+keepalived的安裝
關(guān)聯(lián)lvs與keepalived的ipvs所需的內(nèi)核信息
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* 安裝
驗(yàn)證
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時(shí)注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes，否則無法關(guān)聯(lián)ipvs功能
4、配置mysql的主從
Master(210.171)的配置
vi /etc/my.cnf
加入例如以下內(nèi)容：
server-id = 1??? ##master ID
binlog-do-db = ppl? ##同意同步的庫
binlog-ignore-db = mysql? ##忽略同步的庫，也就是不能同步的庫
##配置文件里還需開啟log-bin，比如log-bin = mysql-bin
mysql –uroot –p
下面內(nèi)容在mysql中運(yùn)行
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的內(nèi)容，等下slave的配置中要用到
?
Slave的配置
vi /etc/my.cnf
加入例如以下內(nèi)容：
server-id = 2? ##slave ID
master-host = 192.168.150.171? ##指定master的地址
master-user = repdb01??? ##同步所用的賬號(hào)
master-password = 123456?? ##同步所用的密碼
master-port = 3306???? ##master上mysql的端口
replicate-do-db = db01?? ##要同步的庫名
replicate-ignore-db = mysql? ##忽略的庫名
slave-skip-errors = 1062?? ##當(dāng)同步異常時(shí)，那些錯(cuò)誤跳過，本例為1062錯(cuò)誤
#log-slave-updates? ##同步的同一時(shí)候，也記錄自己的binlog日志，假設(shè)還有臺(tái)slave是通過這臺(tái)機(jī)器進(jìn)行同步，那須要添加此項(xiàng)，
#skip-slave-start? ##啟動(dòng)時(shí)不自己主動(dòng)開啟slave進(jìn)程
#read-only? ##將庫設(shè)為僅僅讀模式，僅僅能從master同步，不能直接寫入（避免自增鍵值沖突）
mysql –uroot –p
下面內(nèi)容在mysql中運(yùn)行
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
在返回值中查看，假設(shè)slave_IO_Runing與slave_SQL_Runing的值都為Yes說明同步成功
?
5、通過lvs+keepalived實(shí)現(xiàn)負(fù)載與熱備，并實(shí)現(xiàn)讀寫分離
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
將這兩個(gè)參數(shù)前邊的 # 去掉，重新啟動(dòng)mysql
#skip-slave-start ?
#read-only
登陸mysql，手動(dòng)將slave進(jìn)程啟動(dòng)
mysql>slave start;
先啟動(dòng)master上的keepalived，正常后再啟動(dòng)slave上的。
啟動(dòng)后 主庫能夠查看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
發(fā)現(xiàn)210.174 讀的vip 在主備機(jī)上都能夠看到
210.173 寫入vip在主上才干看到
興許多臺(tái)實(shí)驗(yàn)進(jìn)行中，敬請(qǐng)等待
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
