mysql高可用案例

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

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

普通的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

#check_repl_delay=0

[server3]

hostname=10.0.0.125

no_master=1

[server4]

hostname=10.0.0.126

故障轉移腳本

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.

        # activate new_master_ip here.

        # You can also grant write access (create user, set read_only=0, etc) here.

        my $exit_code = 10;

            print "Enabling the VIP - $vip on the new master - $new_master_host \n";

            &start_vip();

            $exit_code = 0;

            warn $@;

    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

配置keepalived

[root@lvs-01 keepalived]# cat keepalived.conf 

! Configuration File for keepalived

global_defs {

   notification_email {

     [email protected]

   }

   notification_email_from  [email protected]

   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 {

        connect_port 3306

        connect_timeout 10

        nb_get_retry 3

        delay_before_retry 5

          misc_path "/etc/keepalived/check_slave.py 10.0.0.125 3306"

    real_server 10.0.0.126 3306 {

          misc_path "/etc/keepalived/check_slave.py 10.0.0.126 3306"

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組中被剔除

  -> 10.0.0.126:3306              Route   1      0          0

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1933501，如需轉載請自行聯系原作者