Shulou(Shulou.com)06/01 Report--

MySQL highly available handover architecture environment based on MHA architecture:

​ CentOS7+MySQL 5.7 + GTID Business system: mainBusiness

​ node1: 192.168.1.109 port:3109

​ node2: 192.168.1.110 port:3110

​ VIP: 192.168.1.88

​ manager:192.168.1.8

1. Background:

​ in addition to galera cluster (Mariadb Cluster,GroupReplication,PXC) and KeepAlived, the high availability of MySQL widely used in the industry is the MHA architecture.

​ MHA authors have rarely updated this tool since they left DeNA to join facebook.

two。 Installation:

The ​ rpm package is the easiest way to install, but the author added the optimization of the super-read-only parameter enabled on the slave library 27 days ago, in short: when this parameter is turned on, there may be a problem that users in the configuration file cannot apply differential transactions. As a result, a logical judgment is added to determine whether the super-read-only parameter is enabled or not. If it is enabled, the parameter is turned off first, and then the difference transaction is applied and then turned on again.

​ so here we install it by compiling the latest code on Github. The address is:

​ https://github.com/yoshinorim/mha4mysql-manager

​ https://github.com/yoshinorim/mha4mysql-node

On node1 and node2: # create a working folder mkdir-p / data/mha/# download the source package wget https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master-O / usr/local/src/mha-node.zipwget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master-O / usr/local/src/mha-manager.zip# extract unzip / usr/local/src/mha-manager.zip-d / usr/local/srcunzip / Usr/local/src/mha-node.zip-d / usr/local/src# installation perl and its related dependencies yum-y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager perl-Config-Tiny yum-y install 'perl (inc::Module::Install)' yum-y install 'perl (Test::Without::Module)' yum-y install 'perl (Log::Dispatch)' # compile Manager cd / usr/local/src/mha4mysql-manager-master/perl Makefile.PL # when prompted, enter ymake & & make install# to compile node-side cd / usr/local/src/mha4mysql-node-master/perl Makefile.PL make & & make install# to change the configuration file cp / usr/# to create an administrator account # node1mysql-- login-path=3109-e'create user mha@'192.168.1.8' identified by 'sa123456''mysql-- login-path=3109-e' grant all privileges on * in the database for the work of the MHA system. . * to mha@'192.168.1.8''mysql-- login-path=3109-e 'flush privilges'#node2mysql-- login-path=3110-e' create user mha@'192.168.1.8' identified by 'sa123456''mysql-- login-path=3110-e' grant all privileges on *. * to mha@'192.168.1.8''mysql-- login-path=3110-e 'flush privilges' on manager # create working directory mkdir-p / data/mha/mainBusiness# download source Wget https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master-O / usr/local/src/mha-node.zipwget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master-O / usr/local/src/mha-manager.zip# decompress unzip / usr/local/src/mha-manager.zip-d / usr/local/srcunzip / usr/local/src/mha-node.zip-d / usr/local/src# install perl and its phase Dependent on yum-y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager perl-Config-Tiny yum-y install 'perl (inc::Module::Install)' yum-y install 'perl (Test::Without::Module)' yum-y install 'perl (Log::Dispatch)' # compiler manager cd / usr/local/src/mha4mysql-manager-master/perl Makefile.PL # enter ymake & & make install# to compile node-side cd / usr/local/ when prompted Src/mha4mysql-node-master/perl Makefile.PL make & & make install# create configuration file cp / usr/local/src/mha4mysql-manager-master/samples/conf/* / etc/mhamv / etc/mha/app1.cnf / etc/mha/mainBusiness.cnf configuration file contents: manager_workdir=/data/mha/mainBusiness # set the log of MHA's working directory manager_log=/data/mha/mainBusiness/manager.log # MHA manager Output the working directory master_binlog_dir= / data/mysql/3109/log/ of the remote_workdir=/data/mha/ # preset MHA node side / data/mysql/3110/log/ # binlog directory on the default MHA node side # secondary_check_script= masterha_secondary_check-s 192.168.1.109-s 192.168.1.110 VIP changes when master_ip_failover_script= / etc/mha/master_ip_failover # fails master_ip_online_change_script= / etc/mha/master_ip_online_change # VIP changes report_script= / etc/mha/send_report # alarm script ping_interval=1 # sets the detection interval for MHA manager ( 1 second) [server1] hostname=192.168.1.109port=3109ssh_user=rootssh_port=22candidate_master=1 # sets whether the node can be promoted to primary. 1 Yes, 0 No check_repl_delay=0 # check the master-slave backwardness of this instance after a failure, 0 No, 1 is whether the node can be promoted to master by setting [server2] hostname=192.168.1.110port=3110ssh_user=rootssh_port=22candidate_master=1 # 1 Yes, 0 No check_repl_delay=0 # check the master-slave backwardness of this instance after a failure, 0 No, 1 alarm script modification: vim / EtcUniqqmnd sendworthy reportcards vim / EtcBINGUR binUniverse perluse strict Use warnings FATAL = > 'all';use Mail::Sender;use Getopt::Long;#new_master_host and new_slave_hosts are set only when recovering master succeededmy ($dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body); my $smtp='smtp.mxhichina.com';my $mail_from='zabbix@xxx.com';my $mail_user='zabbix@xxx.com';my $mail_pass='123456';my $mail_to= [' psyduck007@outlook.com'] GetOptions ('orig_master_host=s' = >\ $dead_master_host,' new_master_host=s' = >\ $new_master_host, 'new_slave_hosts=s' = >\ $new_slave_hosts,' subject=s' = >\ $subject, 'body=s' = >\ $body,); # Do whatever you want heremailToContacts ($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body) Sub mailToContacts {my ($smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg) = @ _; open my $DEBUG, "> / tmp/monitormail.log" or die "Can't open the debug filesmtp!\ n"; my $sender = new Mail::Sender {ctype = > 'text/plain Charset=utf-8', encoding = > 'utf-8', smtp = > $smtp, from = > $mail_from, auth = >' LOGIN', TLS_allowed = > '0mm, authid = > $user, authpwd = > $passwd, to = > $mail_to, subject = > $subject, debug = > $DEBUG} $sender- > MailMsg ({msg = > $msg, debug = > $DEBUG}) or print $Mail::Sender::Error; return 1;} add scripts to regularly clean up relay log: cat > / etc/auto_clean_relay_log.sh $log_dir/purge_relay_logs.log 2 > & 1

Crontab-e

0 * / 3 * * sh / etc/auto_clean_relay_log.sh

Modify the failover VIP script: vim / ETC _ vip _ vipmy _ my _ if = "eth0" for this group of machines customized by 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, $new_master_user, $new_master_password) 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,' new_master_user=s' = >\ $new_master_user, 'new_master_password=s' = >\ $new_master_password,) Sub add_vip {my $output1 = `ssh-o ConnectTimeout=15-o ConnectionAttempts=3 $orig_master_host / sbin/ip addr del $vip/32 dev $if`; my $output2 = `ssh-o ConnectTimeout=15-o ConnectionAttempts=3 $new_master_host / sbin/ip addr add $vip/32 dev $if`;} exit & main (); sub main {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 {# 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 {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 "Set read_only=0 on the new master.\ n"; $new_master_handler- > disable_read_only (); # # Creating an app user on the new master # print "Creating app user on the new master..\ n"; # FIXME_xxx_create_user ($new_master_handler- > {dbh}); $new_master_handler- > enable_log_bin_local (); $new_master_handler- > disconnect () # # Update master ip on the catalog database, etc & add_vip (); $exit_code = 0;}; if ($@) {warn $@; # If you want to continue failover, exit 10. Exit $exit_code;} exit $exit_code;} elsif ($command eq "status") {# do nothing exit 0;} else {& usage (); exit 1 } 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";} vim / etc/mha/master_ip_online_changeuse 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 # add vip definition my $vip = "192.168.1.100"; my $if = "eth0" 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`; # FIXME} sub add_vip {my $output = `ssh-o ConnectTimeout=15-o ConnectionAttempts=3 $new_master_host / sbin/ip addr add $vip/32 dev $if`;} sub current_time_us {my ($sec, $microsec) = gettimeofday (); my $curdate = localtime ($sec); return $curdate. "". Sprintf ("d", $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 steps 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->

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.