mysql高可用案例

cheun

　　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>　　配置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 {
　　print
　　"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