MySQL5.7 one master and two slaves MHA manual failover
= 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"; # 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)\ 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->