• MySQL数据库MHA+keepalive实现


    MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中,MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。

    该软件由两部分组成:MHA Manager(管理节点)和MHA Node(数据节点)。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上,MHA Manager会定时探测集群中的master节点,当master出现故障时,它可以自动将最新数据的slave提升为新的master,然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

    在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访问,MHA没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制,可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的slave服务器上,因此可以保证所有节点的数据一致性。

    目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库,因为至少需要三台服务器。

    Manager工具包主要包括以下几个工具:

    masterha_check_ssh 检查MHA的SSH配置状况
    masterha_check_repl 检查MySQL复制状况
    masterha_manger 启动MHA
    masterha_check_status 检测当前MHA运行状态
    masterha_master_monitor 检测master是否宕机
    masterha_master_switch 控制故障转移(自动或者手动)
    masterha_conf_host 添加或删除配置的server信息
    

      

    Node工具包(这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具:

    save_binary_logs 保存和复制master的二进制日志
    apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
    filter_mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
    purge_relay_logs 清除中继日志(不会阻塞SQL线程)
    

      

    角色                       ip地址            主机名   server_id 类型
    Master                192.168.165.135     MySQL-A      1      写入
    Candicate master      192.168.165.136     MySQL-B      2      读
    Monitor host /Slave   192.168.165.137     MySQL-C      3      读
    

      

    建立MHA账号:

    MariaDB [(none)]> grant all privileges on *.* to 'admin'@'%' identified by '123456';

    创建主从复制账号:

    MariaDB [(none)]> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';
    

      


    安装依赖包:

    rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
    rpm -ivh perl-DBD-MySQL-4.013-3.el6.i686.rpm
    rpm -ivh compat-db43-4.3.29-15.el6.x86_64.rpm
    rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
    rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
    rpm -ivh perl-TimeDate-1.16-11.1.el6.noarch.rpm
    rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm
    rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm
    rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm
    rpm -ivh perl-Params-Validate-0.92-3.el6.
    rpm -ivh perl-Params-Validate-0.92-3.el6.x86_64.rpm
    rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm
    rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
    rpm -ivg perl-Log-Dispatch-2.27-1.el6.noarch.rpm

    或安装epel源

    在管理节点安装MHA管理和客户端安装包:

    tar -zxvf mha4mysql-manager-0.56.tar.gz
    cd mha4mysql-manager-0.56
    perl Makefile.PL
    make
    make install
    
    tar -zxvf mha4mysql-node-0.56.tar.gz
    cd mha4mysql-node-0.56
    perl Makefile.PL
    make
    make install

    将脚本拷贝到/usr/local/bin目录下面:

    [root@mysql-C bin]# ll
    total 40
    -rwxr-xr-x 1 4984 users 1995 Apr 1 2014 masterha_check_repl
    -rwxr-xr-x 1 4984 users 1779 Apr 1 2014 masterha_check_ssh
    -rwxr-xr-x 1 4984 users 1865 Apr 1 2014 masterha_check_status
    -rwxr-xr-x 1 4984 users 3201 Apr 1 2014 masterha_conf_host
    -rwxr-xr-x 1 4984 users 2517 Apr 1 2014 masterha_manager
    -rwxr-xr-x 1 4984 users 2165 Apr 1 2014 masterha_master_monitor
    -rwxr-xr-x 1 4984 users 2373 Apr 1 2014 masterha_master_switch
    -rwxr-xr-x 1 4984 users 5171 Apr 1 2014 masterha_secondary_check
    -rwxr-xr-x 1 4984 users 1739 Apr 1 2014 masterha_stop
    [root@mysql-C bin]# pwd
    /opt/software/mha4mysql-manager-0.56/bin
    [root@mysql-C bin]# cp * /usr/local/bin/

    最后,在master和slave节点安装MHA客户端安装包:

    [root@mysql-A bin]# tar -zxvf mha4mysql-node-0.56.tar.gz
    [root@mysql-A bin]# cd mha4mysql-node-0.56
    [root@mysql-A bin]# perl Makefile.PL
    [root@mysql-A bin]# make
    [root@mysql-A bin]# make install

    创建ssh互信,在每个节点执行如下命令:

    [root@mysql-A bin]# ssh-keygen
    [root@mysql-A bin]# ssh-copy-id '-p 22 root@192.168.165.135'
    [root@mysql-A bin]# ssh-copy-id '-p 22 root@192.168.165.136'
    [root@mysql-A bin]# ssh-copy-id '-p 22 root@192.168.165.137'

    配置主从复制:(略)

    配置MHA管理脚本:

    mkdir -p /mha/log ---这里是存放日志的文件夹路径
    mkdir -p /etc/mha ---这里是存放配置文件的路径
    touch /etc/mha/mha.conf
    [root@mysql-C bin]# cat /etc/mha/mha.conf 
    [server default]
    manager_log=/mha/log/mha.log	 //设置manager的日志
    manager_workdir=/mha/	      //设置manager的工作目录
    user=admin	           //设置MHA管理账号
    password=123456	           //设置MHA管理账号密码
    ping_interval=1	           //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
    ping_type=CONNECT	      //定义故障切换和在线切换
    master_ip_failover_script=/usr/local/bin/master_ip_failover	     //设置自动failover时候的切换脚本
    master_ip_online_change_script=/usr/local/bin/master_ip_online_change	//设置手动切换时候的切换脚本
    remote_workdir=/mha/	      //设置远端mysql在发生切换时binlog的保存位置
    repl_password=repl	      //设置主从复制的账号
    repl_user=repl	           //设置主从复制的账号密码
    secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.165.137 -s 192.168.165.136 
    --user=root --master_host=mysql-A --master_ip=192.168.165.135 --master_port=3306 ssh_port=22 ssh_user=root [server1] hostname=192.168.165.135 master_binlog_dir=/data/mysql/binlog port=3306 ssh_port=22 candidate_master=1 [server2] candidate_master=1 check_repl_delay=0 hostname=192.168.165.136 master_binlog_dir=/data/mysql/binlog port=3306 ssh_port=22 [server3] hostname=192.168.165.137 master_binlog_dir=/data/mysql/binlog port=3306 ssh_port=22

      

    设置relay log的清除方式(在每个slave节点上):

    mysql -e 'set global relay_log_purge=0'

    设置定期清理relay脚本(两台slave服务器)

    [root@mysql-C ~]# cat purge_relay_log.sh 
    #!/bin/bash
    user=root
    passwd=123456
    port=3306
    log_dir='/data/masterha/log'
    work_dir='/data'
    purge='/usr/local/bin/purge_relay_logs'
    
    if [ ! -d $log_dir ]
    then
    mkdir $log_dir -p
    fi
    $purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

    添加到crontab定期执行

    [root@mysql-C ~]# crontab -l
    0 4 * * * /bin/bash /root/purge_relay_log.sh

    编辑自动切换脚本和手动切换脚本:

    [root@mysql-C log]# cat /usr/local/bin/master_ip_failover 
    #!/usr/bin/env perl
    
    # Copyright (C) 2011 DeNA Co.,Ltd.
    #
    # This program is free software; you can redistribute it and/or modify
    # it under the terms of the GNU General Public License as published by
    # the Free Software Foundation; either version 2 of the License, or
    # (at your option) any later version.
    #
    # This program is distributed in the hope that it will be useful,
    # but WITHOUT ANY WARRANTY; without even the implied warranty of
    # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    # GNU General Public License for more details.
    #
    # You should have received a copy of the GNU General Public License
    # along with this program; if not, write to the Free Software
    # Foundation, Inc.,
    # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
    
    ## Note: This is a sample script and is not complete. Modify the script based on your environment.
    
    use strict;
    use warnings FATAL => 'all';
    
    use Getopt::Long;
    #use MHA::DBHelper;
    
    my (
    $command, $ssh_user, $orig_master_host,
    $orig_master_ip, $orig_master_port, $new_master_host,
    $new_master_ip, $new_master_port
    );
    
    my $vip = '192.168.165.130';
    my $ssh_start_vip = '/etc/init.d/keepalived start';
    my $ssh_stop_vip = '/etc/init.d/keepalived stop';
    
    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 "
    
    IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===
    
    ";
    
    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 
    ";
    &stop_vip();
    $exit_code = 0;
    # updating global catalog, etc
    $exit_code = 0;
    };
    if ($@) {
    warn "Got Error: $@
    ";
    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 
    ";
    &start_vip();
    $exit_code = 0;
    };
    if ($@) {
    warn $@;
    
    # If you want to continue failover, exit 10.
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "status" ) {
    print "Checking the Status of the script.. OK 
    ";
    # do nothing
    exit 0;
    }
    else {
    &usage();
    exit 1;
    }
    }
    
    sub start_vip() {
    `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
    }
    
    sub stop_vip() {
    return 0 unless ($ssh_user);
    `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 "; }
    [root@mysql-C log]# cat /usr/local/bin/master_ip_online_change 
    #!/usr/bin/env perl
    
    # Copyright (C) 2011 DeNA Co.,Ltd.
    #
    # This program is free software; you can redistribute it and/or modify
    # it under the terms of the GNU General Public License as published by
    # the Free Software Foundation; either version 2 of the License, or
    # (at your option) any later version.
    #
    # This program is distributed in the hope that it will be useful,
    # but WITHOUT ANY WARRANTY; without even the implied warranty of
    # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    # GNU General Public License for more details.
    #
    # You should have received a copy of the GNU General Public License
    # along with this program; if not, write to the Free Software
    # Foundation, Inc.,
    # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
    
    ## Note: This is a sample script and is not complete. Modify the script based on your environment.
    
    use strict;
    use warnings FATAL => 'all';
    
    use Getopt::Long;
    use MHA::DBHelper;
    use MHA::NodeUtil;
    use Time::HiRes qw( sleep gettimeofday tv_interval );
    use Data::Dumper;
    
    my $vip = '192.168.165.130';
    my $key = '1';
    my $ssh_start_vip = '/etc/init.d/keepalived start';
    my $ssh_stop_vip = '/etc/init.d/keepalived stop';
    my $ssh_user = 'root';
    my $_tstart;
    my $_running_interval = 0.1;
    my (
    $command, $orig_master_is_new_slave, $orig_master_host,
    $orig_master_ip, $orig_master_port, $orig_master_user,
    $orig_master_password, $orig_master_ssh_user, $new_master_host,
    $new_master_ip, $new_master_port, $new_master_user,
    $new_master_password, $new_master_ssh_user,
    );
    GetOptions(
    'command=s' => $command,
    'orig_master_is_new_slave' => $orig_master_is_new_slave,
    'orig_master_host=s' => $orig_master_host,
    'orig_master_ip=s' => $orig_master_ip,
    'orig_master_port=i' => $orig_master_port,
    'orig_master_user=s' => $orig_master_user,
    'orig_master_password=s' => $orig_master_password,
    'orig_master_ssh_user=s' => $orig_master_ssh_user,
    'new_master_host=s' => $new_master_host,
    'new_master_ip=s' => $new_master_ip,
    'new_master_port=i' => $new_master_port,
    'new_master_user=s' => $new_master_user,
    'new_master_password=s' => $new_master_password,
    'new_master_ssh_user=s' => $new_master_ssh_user,
    );
    
    exit &main();
    
    sub current_time_us {
    my ( $sec, $microsec ) = gettimeofday();
    my $curdate = localtime($sec);
    return $curdate . " " . sprintf( "%06d", $microsec );
    }
    
    sub sleep_until {
    my $elapsed = tv_interval($_tstart);
    if ( $_running_interval > $elapsed ) {
    sleep( $_running_interval - $elapsed );
    }
    }
    
    sub get_threads_util {
    my $dbh = shift;
    my $my_connection_id = shift;
    my $running_time_threshold = shift;
    my $type = shift;
    $running_time_threshold = 0 unless ($running_time_threshold);
    $type = 0 unless ($type);
    my @threads;
    
    my $sth = $dbh->prepare("SHOW PROCESSLIST");
    $sth->execute();
    
    while ( my $ref = $sth->fetchrow_hashref() ) {
    my $id = $ref->{Id};
    my $user = $ref->{User};
    my $host = $ref->{Host};
    my $command = $ref->{Command};
    my $state = $ref->{State};
    my $query_time = $ref->{Time};
    my $info = $ref->{Info};
    $info =~ s/^s*(.*?)s*$/$1/ if defined($info);
    next if ( $my_connection_id == $id );
    next if ( defined($query_time) && $query_time < $running_time_threshold );
    next if ( defined($command) && $command eq "Binlog Dump" );
    next if ( defined($user) && $user eq "system user" );
    next
    if ( defined($command)
    && $command eq "Sleep"
    && defined($query_time)
    && $query_time >= 1 );
    
    if ( $type >= 1 ) {
    next if ( defined($command) && $command eq "Sleep" );
    next if ( defined($command) && $command eq "Connect" );
    }
    
    if ( $type >= 2 ) {
    next if ( defined($info) && $info =~ m/^select/i );
    next if ( defined($info) && $info =~ m/^show/i );
    }
    
    push @threads, $ref;
    }
    return @threads;
    }
    
    sub main {
    if ( $command eq "stop" ) {
    ## Gracefully killing connections on the current master
    # 1. Set read_only= 1 on the new master
    # 2. DROP USER so that no app user can establish new connections
    # 3. Set read_only= 1 on the current master
    # 4. Kill current queries
    # * Any database access failure will result in script die.
    my $exit_code = 1;
    eval {
    ## Setting read_only=1 on the new master (to avoid accident)
    my $new_master_handler = new MHA::DBHelper();
    
    # args: hostname, port, user, password, raise_error(die_on_error)_or_not
    $new_master_handler->connect( $new_master_ip, $new_master_port,
    $new_master_user, $new_master_password, 1 );
    print current_time_us() . " Set read_only on the new master.. ";
    $new_master_handler->enable_read_only();
    if ( $new_master_handler->is_read_only() ) {
    print "ok.
    ";
    }
    else {
    die "Failed!
    ";
    }
    $new_master_handler->disconnect();
    
    # Connecting to the orig master, die if any database error happens
    my $orig_master_handler = new MHA::DBHelper();
    $orig_master_handler->connect( $orig_master_ip, $orig_master_port,
    $orig_master_user, $orig_master_password, 1 );
    
    ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
    #$orig_master_handler->disable_log_bin_local();
    #print current_time_us() . " Drpping app user on the orig master..
    ";
    #FIXME_xxx_drop_app_user($orig_master_handler);
    
    ## Waiting for N * 100 milliseconds so that current connections can exit
    my $time_until_read_only = 15;
    $_tstart = [gettimeofday];
    my @threads = get_threads_util( $orig_master_handler->{dbh},
    $orig_master_handler->{connection_id} );
    while ( $time_until_read_only > 0 && $#threads >= 0 ) {
    if ( $time_until_read_only % 5 == 0 ) {
    printf
    "%s Waiting all running %d threads are disconnected.. (max %d milliseconds)
    ",
    current_time_us(), $#threads + 1, $time_until_read_only * 100;
    if ( $#threads < 5 ) {
    print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "
    "
    foreach (@threads);
    }
    }
    sleep_until();
    $_tstart = [gettimeofday];
    $time_until_read_only--;
    @threads = get_threads_util( $orig_master_handler->{dbh},
    $orig_master_handler->{connection_id} );
    }
    
    ## Setting read_only=1 on the current master so that nobody(except SUPER) can write
    print current_time_us() . " Set read_only=1 on the orig master.. ";
    $orig_master_handler->enable_read_only();
    if ( $orig_master_handler->is_read_only() ) {
    print "ok.
    ";
    }
    else {
    die "Failed!
    ";
    }
    
    ## Waiting for M * 100 milliseconds so that current update queries can complete
    my $time_until_kill_threads = 5;
    @threads = get_threads_util( $orig_master_handler->{dbh},
    $orig_master_handler->{connection_id} );
    while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
    if ( $time_until_kill_threads % 5 == 0 ) {
    printf
    "%s Waiting all running %d queries are disconnected.. (max %d milliseconds)
    ",
    current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
    if ( $#threads < 5 ) {
    print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "
    "
    foreach (@threads);
    }
    }
    sleep_until();
    $_tstart = [gettimeofday];
    $time_until_kill_threads--;
    @threads = get_threads_util( $orig_master_handler->{dbh},
    $orig_master_handler->{connection_id} );
    }
    
    print "Disabling the VIP on old master: $orig_master_host 
    ";
    &stop_vip();
    
    ## Terminating all threads
    print current_time_us() . " Killing all application threads..
    ";
    $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
    print current_time_us() . " done.
    ";
    $orig_master_handler->enable_log_bin_local();
    $orig_master_handler->disconnect();
    
    ## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
    $exit_code = 0;
    };
    if ($@) {
    warn "Got Error: $@
    ";
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "start" ) {
    ## Activating master ip on the new master
    # 1. Create app user with write privileges
    # 2. Moving backup script if needed
    # 3. Register new master's ip to the catalog database
    
    # We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
    # If exit code is 0 or 10, MHA does not abort
    my $exit_code = 10;
    eval {
    my $new_master_handler = new MHA::DBHelper();
    
    # args: hostname, port, user, password, raise_error_or_not
    $new_master_handler->connect( $new_master_ip, $new_master_port,
    $new_master_user, $new_master_password, 1 );
    
    ## Set read_only=0 on the new master
    $new_master_handler->disable_log_bin_local();
    print current_time_us() . " Set read_only=0 on the new master.
    ";
    $new_master_handler->disable_read_only();
    
    ## Creating an app user on the new master
    #print current_time_us() . " Creating app user on the new master..
    ";
    #FIXME_xxx_create_app_user($new_master_handler);
    #$new_master_handler->enable_log_bin_local();
    $new_master_handler->disconnect();
    
    
    print "Enabling the VIP - $vip on the new master - $new_master_host 
    ";
    &start_vip();
    $exit_code = 0;
    
    ## Update master ip on the catalog database, etc
    $exit_code = 0;
    };
    if ($@) {
    warn "Got Error: $@
    ";
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "status" ) {
    
    # do nothing
    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_online_change --command=start|stop|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 "; die; }

    检查MHA Manger到所有MHA Node的SSH连接状态:

    [root@mysql-C ~]# masterha_check_ssh --conf=/etc/mha/mha.conf

    通过masterha_check_repl脚本查看整个集群的状态:

    [root@mysql-C ~]# masterha_check_repl --conf=/etc/mha/mha.conf

    报错:Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.
    解决办法(所有节点):

    ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
    ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

    检查MHA Manager的状态:

    通过master_check_status脚本查看Manager的状态:

    [root@mysql-C ~]# masterha_check_status --conf=/etc/mha/mha.conf
    mha is stopped(2:NOT_RUNNING).

    安装keepalive:

    yum install -y gcc openssl-devel kernel-devel net-snmp net-snmp-utils net-snmp-libs libnl libnl-devel
    cd /usr/src/kernels/
    ln -s 2.6.32-642.el6.x86_64/ /usr/src/linux
    ./configure --prefix=/usr/local/keepalived
    make && make install

    添加服务:

    cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
    cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    mkdir /etc/keepalived
    cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
    cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

    配置keepalive:

    [root@192.168.0.60 ~]# cat /etc/keepalived/keepalived.conf 
    ! Configuration File for keepalived
    
    global_defs {
    notification_email {
    saltstack@163.com
    }
    notification_email_from dba@dbserver.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL-HA
    }
    
    vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 120
    advert_int 1
    nopreempt
    
    authentication {
    auth_type PASS
    auth_pass 1111
    }
    
    virtual_ipaddress {
    192.168.165.130
    }
    } 

    启动/停止keepalive:

    /etc/init.d/keepalived start
    /etc/init.d/keepalived stop

    启动/关闭MHA:

    nohup /usr/local/bin/masterha_manager --conf=/etc/mha/mha.conf --ignore_last_failover </dev/null> /mha/log/mha.log 2>&1 &
    masterha_stop --conf=/etc/mha/mha.conf

    查看MHA状态信息:

    [root@mysql-C ~]# masterha_master_monitor --conf=/etc/mha/mha.conf

    MHA自动failover,手动failover,在线切换三种方式


    手动切换脚本(当主库挂了):

    masterha_master_switch --master_state=dead --conf=/etc/mha/mha.conf --dead_master_host=192.168.165.136 
    --dead_master_port=3306 --new_master_host=192.168.165.135 --new_master_port=3306 --ignore_last_failover

    在线平滑切换:

    masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --orig_master_is_new_slave

    自动切换后恢复:
    查看日志:

    [root@mysql-C log]# grep -i "All other slaves should start" mha.log 
    Fri Dec 22 18:57:38 2017 - [info] All other slaves should start replication from here. 
    Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.165.135', MASTER_PORT=3306, 
    MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=326, MASTER_USER='repl', MASTER_PASSWORD='xxx';
    
    change master to
    MASTER_HOST='192.168.165.135',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='repl',
    MASTER_LOG_FILE='mysql-bin.000007',
    MASTER_LOG_POS=326;

    检查网卡上的虚拟ip
    ip addr | grep eth1

  • 相关阅读:
    sql 行列转换之关键字pivot,unpivot
    构建动态表达式(初级)
    HttpApplication事件执行顺序
    【转】delegate.BeginInvoke 注意事项
    Jquery.extend函数详解【转】
    【转】NET中反射实现 可空类型 与基础类型的转换 以及获取指定属性的大小问题
    sql执行字符串
    Javascript获取浏览器地址栏url各项值
    你妹的浏览器DNS缓存
    LazyLoad 延迟加载图片的jQuery插件介绍
  • 原文地址:https://www.cnblogs.com/zx3212/p/8177425.html
Copyright © 2020-2023  润新知