一簡介
Keepalived+lvs+mysql主主復制是比較常用的一種Mysql高可用方案,其中lvs 提供讀負載均衡,Keepalived通過虛擬vip漂移實現故障自動轉移,而Mysql雖然配置成主主復制,但是一般都是采用單點寫入來保證數據一致性。
整個方案實現非常簡單,僅需兩台服務器即可,安裝配置也相對簡單,但需要自己寫腳本實現keepalived服務的開啟和關閉,適用於只有兩台數據庫服務器並且未實現讀寫分離的場景。系統整體結構框圖如下
二環境搭建
2.1 環境配置如下
Keepalived
Server
LVS
Server
LVS
Realserver
MySQL
Master-Master
Write VIP
Read VIP
OS
MySQL
Master
10.0.11.243
10.0.11.243
10.0.11.243
10.0.11.243
10.0.11.190
10.0.11.191
Centos6
Percona5.6
Backup
10.0.11.244
10.0.11.244
10.0.11.244
10.0.11.244
10.0.11.190
10.0.11.191
Centos6
Percona5.6
2.2 mysql master-master復制配置
主主復制配置本質上就是配置兩台服務器互相的主從,實現比較簡單,大致步驟如下
2.2.1 在Master上創建一個復制賬戶
mysql>grantreplication slave on *.* to [email protected] by‘123456’;
mysql>flushprivileges;
2.2.2 修改兩台mysql配置文件my.cnf如下
##Replication General Config (both master and slave) server-id= 101 log-bin= mysql-log-bin auto-increment-increment= 2 auto-increment-offset= 1 binlog_format= row relay_log= mysql-relay-bin
2.2.3 在Master服務器上執行show master status,記錄下File和Position
mysql>show master status\G; ***************************1. row *************************** File:mysql-log-bin.000005 Position:327933 Binlog_Do_DB: Binlog_Ignore_DB:test,mysql,information_schema,performance_schema
2.2.4 在Slave服務上執行
mysql> slave stop; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to -> master_host='10.0.11.243', -> master_user='rep71', -> master_password='123456', -> master_log_file=' mysql-log-bin.000005', -> master_log_pos=327933; Query OK, 0 rows affected (0.01 sec) mysql> start slave;
2.2.5重復以上四個步驟,在Slave服務器上創建賬戶rep72,show master status,在Master上start slave,這樣mysql的主主同步就配置好了,分別在兩台服務器上運行show slave status,如果結果都顯示Slave_IO_Running: Yes,Slave_SQL_Running: Yes,則證明同步正常。
2.3 Keepalived的安裝
2.3.1 在 master、backup 服務器都進行安裝:
wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz sudotar zxvf ipvsadm-1.24.tar.gz cdipvsadm-1.24 sudoln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux make sudomake install
2.4 Keepalived 的安裝
2.4.1在 master、backup 服務器都進行安裝:
wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz tarzxvf keepalived-1.1.19.tar.gz cdkeepalived-1.1.19 yuminstall -y openssl openssl-devel sudoyum install popt-devel –y ./configure--sysconf=/etc/ --with-kernel-dir=/usr/src/kernels/2.6.18-308.el5-x86_64/ make sudomake install ln -s /usr/local/sbin/keepalived /sbin/
2.4.2 Master上的Keepalived 的配置
global_defs{ router_id mysqlmha1 } vrrp_scriptcheck_run { script"/etc/keepalived/check_mysqll.sh" interval 1 } vrrp_instanceVI_1 { state MASTER interface em1 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { check_run } virtual_ipaddress { 10.0.11.190 dev em1 label em1:1 10.0.11.191 dev em1 label em1:2 } } virtual_server10.0.11.190 3306 { delay_loop6 lb_algorr lb_kindDR persistence_timeout20 protocolTCP sorry_server10.0.11.244 3306 real_server10.0.11.243 3306 { weight3 TCP_CHECK{ connect_timeout3 nb_get_retry3 delay_before_retry3 connect_port3306 } } } virtual_server10.0.11.191 3306 { delay_loop6 lb_algorr lb_kindDR #persistence_timeout20 protocolTCP real_server10.0.11.243 3306 { weight3 TCP_CHECK{ connect_timeout3 nb_get_retry3 delay_before_retry3 connect_port3306 } } real_server10.0.11.244 3306 { weight3 TCP_CHECK{ connect_timeout3 nb_get_retry3 delay_before_retry3 connect_port3306 } } }
2.4.3 Backup 的 keepalived 的配置
global_defs { router_id mysqlmha1 } vrrp_script check_run { script "/etc/keepalived/check_mysqll.sh" interval 1 } vrrp_instance VI_1 { state BACKUP interface em1 virtual_router_id 51 priority 90 advert_int 1 authentication { auth_type PASS auth_pass 1111 } track_script { check_run } virtual_ipaddress { 10.0.11.190 dev em1 label em1:1 10.0.11.191 dev em1 label em1:2 } } virtual_server 10.0.11.190 3306 { delay_loop 6 lb_algo rr lb_kind DR persistence_timeout 20 protocol TCP sorry_server 10.0.11.244 3306 real_server 10.0.11.243 3306 { weight 3 TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } } virtual_server 10.0.11.191 3306 { delay_loop 6 lb_algo rr lb_kind DR #persistence_timeout 20 protocol TCP real_server 10.0.11.243 3306 { weight 3 TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } real_server 10.0.11.244 3306 { weight 3 TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } }
2.5 Master 和 backup 的 realserver 的配置
對於 realserver 的配置 master 和 backup 是一致的,腳本內容如下:
#!/bin/bash #description: Config realserver lo and apply noarp SNS_VIP=10.1.11.190 SNS_VIP2=10.0.11.191 /etc/rc.d/init.d/functions case"$1" in start) ifconfiglo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP ifconfiglo:1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2 /sbin/routeadd -host $SNS_VIP dev lo:0 /sbin/routeadd -host $SNS_VIP2 dev lo:1 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 echo"1" >/proc/sys/net/ipv4/conf/em1/arp_ignore echo"2" >/proc/sys/net/ipv4/conf/em1/arp_announce sysctl-p >/dev/null 2>&1 echo"RealServer Start OK" ;; stop) ifconfiglo:0 down ifconfiglo:1 down routedel $SNS_VIP >/dev/null 2>&1 routedel $SNS_VIP2 >/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"0" >/proc/sys/net/ipv4/conf/em1/arp_ignore echo"0" >/proc/sys/net/ipv4/conf/em1/arp_announce echo"RealServer Stoped" ;; *) echo"Usage: $0 {start|stop}" exit1 esac exit0
2.6 編輯腳本控制Keepalived的開啟和停止
大體意思是只要檢測到mysql服務停止keepalived服務也停止 ,因為keepalived是通過組播方式告訴本網段自己還活著當mysql服務停止後keepalived還依然運行 這時就需要停止keepalived讓另一個主機獲得虛擬IP,可以在後台運行這個腳本 也可以在keepalived配置文件加入這個腳本,master和slave節點都需要,只是數據庫用戶名和密碼有區別
[root@244 ~]#more /etc/keepalived/check_mysql.sh #20140722 #!/bin/bash MYSQL=/usr/bin/mysql MYSQL_HOST=10.0.11.243 MYSQL_USER=root MYSQL_PASSWORD=mysql CHECK_TIME=3 #mysql isworking 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 "showstatus;" >/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