MHA master_ip_online_change

#!/usr/bin/env perl
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 $_tstart;
my $_running_interval = 0.1;
 
my $vip = "192.168.10.101";
my $if  = "eno16780032";
 
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 drop_vip {
    #my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
    my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/24 dev $if`;
}
 
sub add_vip {
    #my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
    my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ifconfig $if:0 $vip`;
}
 
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.\n";
            }
            else {
                die "Failed!\n";
            }
            $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..\n";
            print current_time_us() . " drop vip $vip..\n";
 
            #drop_app_user($orig_master_handler);
            &drop_vip();
 
            ## 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)\n",
                        current_time_us(), $#threads + 1, $time_until_read_only * 100;
                    if ( $#threads < 5 ) {
                        print Data::Dumper->new( [$_] )->Indent(0)->Terse(1) ->Dump . "\n" 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.\n";
            }
            else {
                die "Failed!\n";
            }
 
            ## 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)\n",
                        current_time_us(), $#threads + 1,
                        $time_until_kill_threads * 100;
                    if ( $#threads < 5 ) {
                        print Data::Dumper->new( [$_] )->Indent(0)->Terse(1) ->Dump . "\n" foreach (@threads);
                    }
                }
                sleep_until();
                $_tstart = [gettimeofday];
                $time_until_kill_threads--;
                @threads = get_threads_util( $orig_master_handler->{dbh},
                    $orig_master_handler->{connection_id} );
            }
 
            ## Terminating all threads
            print current_time_us() . " Killing all application threads..\n";
            $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
            print current_time_us() . " done.\n";
            $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: $@\n";
            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.\n";
            $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..\n";
            print current_time_us() . "Add vip $vip on $if..\n";
 
            # create_app_user($new_master_handler);
            &add_vip();
            $new_master_handler->enable_log_bin_local();
            $new_master_handler->disconnect();
 
            ## Update master ip on the catalog database, etc
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
 
        # do nothing
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}
 
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\n";
    die;
}