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

I. Environmental deployment:

Mysql1:192.168.110.131 as master

Mysql2:192.168.110.132 as slave

Mysql3:192.168.110.130 acts as a slave and as a management machine for MHA

Virtual IP:192.168.110.100

Second, mysql master-slave environment building and MHA installation

1. Mysql master / slave build self-build (based on GTID replication, open log_bin, copy rules default, copy all database tables), which is not described here.

2. Install the MHA node software:

Rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

3. Install the MHA management node:

Rpm-ivh mha4mysql-manager-0.56-0.el6.noarch.rpmyum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes-y

4. Configure MHA management directory and configuration:

Mkdir-p / etc/masterha # working directory

Mkdir-p / var/log/masterha/app1/ # log directory

Cat / etc/masterha/app1.cnf # configuration file

[server default] manager_log=/var/log/masterha/app1/manager.logmanager_workdir=/var/log/masterha/app1master_ip_failover_script= "/ usr/bin/master_ip_failover" master_ip_online_change_script= "/ usr/bin/master_ip_online_change" ping_interval=1remote_workdir=/tmprepl_password=replrepl_user=replsecondary_check_script= "/ usr/bin/masterha_secondary_check-s 192.168.110.132-s 192.168.110.130-user=root-- master _ host=mysql1-- master_ip=192.168.110.131-- master_port=3306 "ssh_user=rootuser=mhapassword= mha [server1] hostname=mysql1master_binlog_dir=" / data/mysql/mysql3306/binlog "port= 3306 [server2] hostname=mysql2master_binlog_dir=" / data/mysql/mysql3306/binlog "port=3306candidate_master=1check_repl_delay= 0 [server3] hostname=ansiblemaster_binlog_dir=" / data/mysql/mysql3306/binlog "port=3306no_master= 1 [binlog1] hostname=mysql2master_binlog_dir=/tmp/binlogignore_fail=1no_master=1

5. Configure MHA to manage the database account mha:

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'mha'@'192.168.110.%' IDENTIFIED BY' mha'

6. Configure the binlog real-time backup of the main database, and deploy the script on mysql2 (if it is not necessary, please remove the [binlog1] configuration information in app1.cnf):

Cat binlog.sh

#! / bin/shBACKUP_BIN= "/ usr/bin/mysqlbinlog" LOCAL_BACKUP_DIR= "/ tmp/binlog" BACKUP_LOG= "/ tmp/binlog/binary.log" REMOTE_HOST= "192.168.110.131" REMOTE_PORT= "3306" REMOTE_USER= "root" REMOTE_PASS= "root" FIRST_BINLOG= "mysql-bin.000001" # time to wait before reconnecting after failureSLEEP_SECONDS=10##create local_backup_dir if necessarymkdir-p ${LOCAL_BACKUP_DIR} cd ${LOCAL_BACKUP_DIR} # # run while cycle Wait for a specified time after disconnecting. Reconnect while: do if [`ls-A "${LOCAL_BACKUP_DIR}" | wc-l`-eq 0] Then LAST_FILE=$ {FIRST_BINLOG} else LAST_FILE= `ls-l ${LOCAL_BACKUP_DIR} | grep-v backuplog | grep mysql-bin | tail-n 1 | awk'{print $9} '`fi ${BACKUP_BIN}-- raw-- read-from-remote-server-- stop-never-- host=$ {REMOTE_HOST}-port=$ {REMOTE_PORT}-- user=$ {REMOTE_USER}-password=$ {REMOTE_PASS} ${LAST_FILE} echo "`date + "Y/%m/%d H:%M:%S" `mysqlbinlog stop Return code: $? "| tee-a ${BACKUP_LOG} echo" ${SLEEP_SECONDS} seconds later connect again and continue backup "| tee-a ${BACKUP_LOG} sleep ${SLEEP_SECONDS} done |

7. Modify / usr/bin/master_ip_failover and master_ip_online_change scripts. Here, the script is used to switch VIP to achieve VIP drift. You need to add processing logic to VIP drift to the script.

The / usr/bin/master_ip_failover script is as follows:

#! / 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 $gateway =' 192.168.110.2 transactions my $vip = '192.168.110.100; # Virtual IPmy $key = "2" My $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip;/sbin/arping-I eth0-c 3-s $vip $gateway > / dev/null 2 > & 1"; 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-exit_code'{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. # 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\ n"; & start_vip (); $exit_code = 0;}; if ($@) {warn $@; exit $exit_code;} exit $exit_code } 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 mastersub start_vip () {`ssh $ssh_user\ @ $new_master_host\" $ssh_start_vip\ "` } # A simple system call that disable the VIP on the old_mastersub 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.

The / usr/bin/master_ip_online_change script is as follows:

#! / 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 $_ 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,) My $vip = '192.168.110.100dollars; # Virtual IPmy $key = "2"; my $gateway =' 192.168.110.2transactions my $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip;/sbin/arping-I eth0-c 3-s $vip $gateway > / dev/null 2 > & 1"; my $ssh_stop_vip = "/ sbin/ifconfig eth0:$key down"; my $sshuser = "root" 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 ("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"; # 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->

< /dev/null >

< /dev/null >

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.