1.MHA+LVS
http://www.chocolee.cn/archives/276
http://dbaplus.cn/news-11-754-1.html
2. 一步一步打造MySQL高可用平台
http://www.jianshu.com/p/bc50221972ca?from=jiantop.com
代理层功能
1、授权认证模型；
2、SQL拦截；
3、负载均衡；
4、读写分离；
5、高可用；
6、大SQL隔离；
拓展思想：为了保证数据一致性，MySQL复制中，常常会在Master上使用sync_binlog参数保证binlog持久化，保证数据一致性。但这种方式对磁盘I/O会造成10~20%的影响。但是还有另外一个思路，就是使用MySQL半同步复制来保证数据一致性，MySQL半同步复制是在从服务器的内存中处理数据并进行发聩，虽然也会造成性能影响，但是相对于对Master造成的磁盘I/O的影响来说，反而是个更好的方法。据《高性能MySQL》 第三版中10.9的测试，写入远程的内存（一台从库的反馈）比写入本地的磁盘（写入并刷新）要更快。使用半同步复制相比主在主库上进行强持久化的性能有两倍的改善。
mha实现功能
1.ssh验证
自己也需要验证
2.backup master && slave 设置read only
3.lvs
lvs 虚拟ip只有read ip，write ip在mha配置文件指定
4.write and read 是怎么控制的？
在Master上绑定写VIP，mha控制的
5.为什么backup master 比slave更早的成为master，什么控制的？
通过Failover脚本在Backup Master上绑定WVIP，提升其为主库
6.当MHA把Master切换到了Backup Master上后，LVS如何处理分发在Backup Master上的读操作？
解释：由于Keepalived会通过脚本定期监控Backup Master的状态，包括同步、SQL线程、I/O线程，所以当Backup Master升级为主库后，这些状态都将消失，Keepalived将自动将Backup Master剔除出负载均衡集群。
效果
write 两个master会变成这样
注意back master 变成master后有两个虚拟ip
[root@mysql-02 mysql]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:62:39:F8
inet addr:10.0.0.124 Bcast:10.0.0.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe62:39f8/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:236889 errors:0 dropped:0 overruns:0 frame:0
TX packets:69213 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:221270914 (211.0 MiB) TX bytes:5949360 (5.6 MiB)
eth0:1 Link encap:Ethernet HWaddr 00:0C:29:62:39:F8
inet addr:10.0.0.131 Bcast:10.0.0.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:244 errors:0 dropped:0 overruns:0 frame:0
TX packets:244 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:22781 (22.2 KiB) TX bytes:22781 (22.2 KiB)
lo:Rvip Link encap:Local Loopback
inet addr:10.0.0.132 Mask:0.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
普通的slave只有一个read虚拟ip
#!/bin/bash
vip=10.0.0.132
open() {
ifconfig lo:Rvip ${vip}/32 up
sysctl -w net.ipv4.conf.lo.arp_announce=2
sysctl -w net.ipv4.conf.lo.arp_ignore=1
sysctl -w net.ipv4.conf.all.arp_announce=2
sysctl -w net.ipv4.conf.all.arp_ignore=1
}
close() {
ifconfig lo:Rvip down
sysctl -w net.ipv4.conf.lo.arp_announce=0
sysctl -w net.ipv4.conf.lo.arp_ignore=0
sysctl -w net.ipv4.conf.all.arp_announce=0
sysctl -w net.ipv4.conf.all.arp_ignore=0
}
case $1 in
start)
open
;;
stop)
close
;;
*)
echo "Usage: $0 need argument [start|stop]"
;;
esac
具体配置，不想再看第二遍了
mysql-01 master
mysql-02 slave
mysql-03,mysql-04 slave操作与mysql-02操作相同，由于这两台只做从库，只需要修改server id,不需要开启log-bin
配置ssh免密码登陆
配置master到所有node
配置manager到所有node
配置backup master到所有node
配置slave到所有node
在数据库中创建mha管理用户
在master上创建管理用户
在从库检查是否同步
安装MHA
lvs-02安装manager
配置MHA
manager MHA 配置文件路径: /etc/mha
[root@lvs-02 app1]# cat app1.conf
[server default]
manager_workdir=/etc/mha/app1
manager_log=/etc/mha/app1/manager.log
master_binlog_dir= /dbdata/data
ssh_user=root
user=mha
password=mhapwd
repl_user=rep
repl_password=reppasswd
secondary_check_script= masterha_secondary_check -s 10.0.0.126 -s 10.0.0.123
ping_interval=3
master_ip_failover_script= /etc/mha/app1/master_ip_failover
#shutdown_script= /script/masterha/power_manager
#report_script= /script/masterha/send_report
#master_ip_online_change_script= /etc/mha/master_ip_failover
[server1]
hostname=10.0.0.123
port=3306
candidate_master=1
[server2]
hostname=10.0.0.124
port=3306
candidate_master=1
#check_repl_delay=0
[server3]
hostname=10.0.0.125
port=3306
no_master=1
[server4]
hostname=10.0.0.126
port=3306
no_master=1
故障转移脚本
cat master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.131/24'; # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
$ssh_user = "root";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
#eval {
# print "Disabling the VIP on old master: $orig_master_host \n";
# &stop_vip();
# $exit_code = 0;
#};
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
#my $ping=`ping -c 1 10.0.0.13 | grep "packet loss" | awk -F',' '{print $3}' | awk '{print $1}'`;
#if ( $ping le "90.0%" && $ping gt "0.0%" ){
#$exit_code = 0;
#}
#else {
&stop_vip();
# updating global catalog, etc
$exit_code = 0;
#}
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_ip \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
# the end.
backup master & slave 设置read_only防止被写
set global read_only=1;
检查并启动mha
检查SSH情况：masterha_check_ssh --conf=/etc/mha/app1/app1.conf
检查复制情况：masterha_check_repl --conf=/etc/mha/app1/app1.conf
启动mha
当有slave节点宕掉的情况是启动不了的，加上--ignore_fail_on_start即使有节点宕掉也能启动mha
检查mysql-01虚拟IP
安装lvs,keepalived
ipvsadm-1.26适用于内核2.6.28及之后的内核版本。
CentOS5.X安装LVS，使用1.2.4版本，不要用1.2.6.
lsmod |grep ip_vs 出现了ip_vs等信息，证明安装成功了。
backup master & slave配置arp抑制及绑定vip
#!/bin/bash
vip=10.0.0.132
open() {
ifconfig lo:Rvip ${vip}/32 up
sysctl -w net.ipv4.conf.lo.arp_announce=2
sysctl -w net.ipv4.conf.lo.arp_ignore=1
sysctl -w net.ipv4.conf.all.arp_announce=2
sysctl -w net.ipv4.conf.all.arp_ignore=1
}
close() {
ifconfig lo:Rvip down
sysctl -w net.ipv4.conf.lo.arp_announce=0
sysctl -w net.ipv4.conf.lo.arp_ignore=0
sysctl -w net.ipv4.conf.all.arp_announce=0
sysctl -w net.ipv4.conf.all.arp_ignore=0
}
case $1 in
start)
open
;;
stop)
close
;;
*)
echo "Usage: $0 need argument [start|stop]"
;;
esac
配置keepalived
[root@lvs-01 keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
test@gmail.com
}
notification_email_from alert-noreply@test.com.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id blade1
}
# db master server.
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 200
advert_int 5
authentication {
auth_type PASS
auth_pass 123qwe
}
virtual_ipaddress {
10.0.0.132/24
}
}
# VIP 10.0.0.132
virtual_server 10.0.0.132 3306 {
delay_loop 10
lb_algo rr
lb_kind DR
nat_mask 255.255.255.0
protocol TCP
#sorry_server 10.0.0.124 3306
real_server 10.0.0.124 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 10
nb_get_retry 3
delay_before_retry 5
}
MISC_CHECK {
misc_path "/etc/keepalived/check_slave.py 10.0.0.124 3306"
misc_dynamic
}
}
real_server 10.0.0.125 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 10
nb_get_retry 3
delay_before_retry 5
}
MISC_CHECK {
misc_path "/etc/keepalived/check_slave.py 10.0.0.125 3306"
misc_dynamic
}
}
real_server 10.0.0.126 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 10
nb_get_retry 3
delay_before_retry 5
}
MISC_CHECK {
misc_path "/etc/keepalived/check_slave.py 10.0.0.126 3306"
misc_dynamic
}
}
}
check_slave.py文件
#!/usr/bin/env python
#encoding:utf-8
import MySQLdb
import sys
ip=sys.argv[1]
user='rep'
pwd='reppasswd'
port=int(sys.argv[2])
sbm=200
Slave_IO_Running = ''
Slave_SQL_Running = ''
Seconds_Behind_Master = ''
e=''
try:
conn = MySQLdb.connect(host=ip,user=user,passwd=pwd,port=port,charset='utf8')
cur = conn.cursor()
cur.execute('show slave status')
db_info = cur.fetchall()
for n in db_info:
Slave_IO_Running = n[10]
Slave_SQL_Running = n[11]
Seconds_Behind_Master = n[32]
cur.close()
conn.close()
except MySQLdb.Error,e:
print "MySQLdb Error",e
if e == "":
if db_info != ():
if Slave_IO_Running == "No" or Slave_SQL_Running == "No":
#print 'thread err'
exit(1)
else:
if Seconds_Behind_Master > sbm:
#print 'timeout err'
exit(1)
else:
#print 'OK'
exit(0)
else:
#print 'slave err'
exit(1)
else:
#print 'db err'
exit(1)
启动keepalived并检查vip
[root@lvs-01 keepalived]# /etc/init.d/keepalived start
[root@lvs-01 keepalived]# ip addr|grep 10.0.0.132
inet 10.0.0.132/24 scope global secondary eth0
[root@lvs-01 keepalived]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 10.0.0.132:3306 rr
-> 10.0.0.124:3306 Route 1 0 0
-> 10.0.0.125:3306 Route 1 0 0
-> 10.0.0.126:3306 Route 1 0 0
[root@lvs-01 keepalived]#
测试
测试read vip负载均衡
测试从库故障被剔除，恢复被挂起
测试keepalived高可用vip切换
测试 write vip切换，backup master 成为master
lvs检查新主是否在read组中被剔除
[root@lvs-01 keepalived]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 10.0.0.132:3306 rr
-> 10.0.0.125:3306 Route 1 0 0
-> 10.0.0.126:3306 Route 1 0 0
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1933501，如需转载请自行联系原作者
