Translated from 日本語 • View original Deployment Settings of MHA (MasterHigh AvailabilityManager) Versions used: mha4mysql-manager-0.53-0, MySQL-5.5
(1) Explanation of simple benefits, etc.
MHA is a product that uses the latest slave as the master in the event of a mysql master failure, compensates for the differences of other slaves, and changes the direction of the master.
Compared to Heartbeat+mon+mysql, it also reconstructs replication, so even if you switch, the DB will not become a single. (For 3 or more configurations)
Author's slide official website
MHA Limitations: LOAD DATA INFILE cannot be used for mysql 5.0 or higher and SBR (Statement-Based Replication)
*The manager is the admin server, and the node is the DB server (common to master and slave).
(2) Install in the manager *Hereinafter performed from the ADM server
Installation
[php]
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.53-0.noarch.rpm
yum --enablerepo=rpmforge install \
perl-Config-Tiny \ perl-Time-HiRes \ perl-Log-Dispatch \ perl-Parallel-ForkManager \ perl-Params-Validate
yum install perl-DBD-MySQL
rpm -ivh mha4mysql*
[/php]
[shell]yum install --enablerepo=remi mysql mysql-server perl-DBD-MySQL[/shell] *The manager may not be needed for mysql-server
(3) Installation on a node *Hereinafter performed from a DB server
[shell]
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
rpm -ivh mha4mysql-node*
[/shell] (4) Changing Settings (Manager)
・SSH public key setting
You need to be able to log in to the node with root from the manager
Create a key (ssh-keygen -t rsa -N "")
Add the public key (~.pub) to the public key ring (/root/.ssh/authorized_keys) on each node.
・Installation of configuration files and scripts
[shell]
# vi /etc/app1.cnf
[server default]
mysql user and password
user=root password= ssh_user=root
working directory on the manager
manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log
working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
master binlog dir
master_binlog_dir=/usr/local/mysql/var
master_ip_failover_script=/usr/local/bin/master_ip_failover report_host=/usr/local/bin/send_report ping_interval=3
[server1] hostname=192.168.100.1 port=3306
[server2] hostname=192.168.100.2 port=3306 candidate_master=1
[server3] hostname=192.168.100.3 port=3306
no_master=1
[/shell] main configuration parameters for details here
*Other parameters that may be better
secondary_check_script
Specify the script and host that will be checked from the second interface.
ignore_fail
Even if the second slave falls, if the master falls, if you want to switch, you can ignore it as a slave.
shutdown_script
It seems that it is good to specify a script with this option if you want to completely drop the master for the purpose of preventing SplitBrain, etc.
Script to switch VIP
[shell]
# vi /usr/local/bin/master_ip_failover
#!/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 $vip = '192.168.100.5/24'; # Virtual IP my $key = "1"; my $ssh_start_vip = "sudo /sbin/ifconfig eth1:$key $vip"; my $ssh_stop_vip = "sudo /sbin/ifconfig eth1:$key down"; my $ssh_stop_mysqld = "sudo /sbin/service mysqld 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 "\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"; eval { &stop_vip(); print "Stop mysqld on old master: $orig_master_host \n"; system("ssh $ssh_user\@$orig_ master_host \" $ssh_stop_mysqld \""); }; system("/usr/local/bin/mod_lvs_weight.sh"); $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\@gentoo8 \" $ssh_start_vip \";
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_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";
}
[/shell] *Processing parts such as dropping IP, IF and mysql, and changing the weight of LVS should be changed as necessary.
Original script
Script to change the weight of the LVS called from the script that switches VIP
[shell]
# vi /usr/local/bin/mod_lvs_weight.sh
#!/bin/bash #
this script is kicked from master_ip_failover-script.
script-task: modify lvs-weight and backup-script-weight from admin-server.
isao-unyou 2012.11.5 - Creation
please modify. (and check the ldirectod.cf.failover!)
TITLE=TEST READVIP=192.168.100.8 LVSSRV1=192.168.100.6 LVSSRV2=192.168.100.7 NIC=eth1 DB1=192.168.100.1 DB2=192.168.100.2 DB3=192.168.100.3 NETWORK=192.168.100 LVS_CF=/etc/ ha.d/ldirectord.cf MAILADDR=XXXX@isao.net BKUPSRV=192.168.100.3 BKUP_SH=/opt/bin/mysql-back.sh DB1_WEIGHT=0 DB2_WEIGHT=2 DB3_WEIGHT=4 BK_WEIGHT=4 MHA_LOG=/var/log/ masterha/app1/manager.log
read vip server check
srv_chk() { RVIP_CHK=ssh $LVSSRV1 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK" ]; then LVSSRV=$LVSSRV1 LVSBKSRV=$LVSSRV2 RVIP_CHK2=ssh $LVSSRV2 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK2" ]; then echo "LANG=C;date: LVS read vip split-brain uname -n." \ | mail -s "NG_READ_VIP $READVIP" $MAILADDR echo 'ERROR: LVS read vip split-brain. ' exit 1 fi else RVIP_CHK2=ssh $LVSSRV2 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK2" ]; then LVSSRV=$LVSSRV2 LVSBKSRV=$LVSSRV1 else echo "LANG=C;date: LVS read vip unknown uname -n." \ | mail -s "NG_READ_VIP $READVIP" $MAILADDR echo 'ERROR: LVS read vip unknown. ' exit 1 fi fi }
modify lvs-weight and mail to unyou. (lvs weight info)
mod_weight() { ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 -r $DB1 -g -w $DB1_WEIGHT" ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 -r $DB2 -g -w $DB2_WEIGHT" ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 - r $DB3 -g -w $DB3_WEIGHT" ssh $LVSSRV "cp -p $LVS_CF{,.date +%Y%m%d.%H%M} \ && \cp -pf $LVS_CF.failover $LVS_CF \ && service ldirectord force-reload" \ && scp -Cp $LVSSRV:$LVS_CF / tmp/ \ && scp -Cp /tmp/ldirectord.cf $LVSBKSRV:/etc/ha.d/ echo -e "LANG=C;date\nCurrent weights: \n\n ssh $LVSSRV ipvsadm -Ln" \ | mail -s "$TITLE mysql-master Failover. modify LVS and bkupscript weight." $MAILADDR
modify backup-script.
#ssh $BKUPSRV "cp -p $BKUP_SH{,.date +%Y%m%d.%H%M} && sed -i \"s/WEIGHT=[0-9]{1,100}/WEIGHT=$BK_WEIGHT/g\" $BKUP_SH" }
main
exec >> $MHA_LOG exec 2>&1 echo "START lvs weight modify. date '+%Y%m%d %T'" srv_chk mod_weight echo "END lvs weight modify. date '+%Y%m%d %T'"
exit 0
chmod +x /usr/local/bin/mod_lvs_weight.sh
[/shell]
Script to send a report email
[shell]# vi /usr/local/bin/send_report
#!/usr/bin/perl
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;
#new_master_host and new_slave_hosts are set only when recovering master succeeded my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body ); 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 here
#my $vip = /sbin/ip addr show eth0; my $vip = /usr/bin/ssh $new_master_host /sbin/ip addr show eth0; my $datetime = /bin/date +%Y%m%d_%H:%M:%S; my $manager_cnf='/etc/app1.cnf'; my $manager_workdir=grep manager_workdir $manager_cnf|sed -e 's/[^=]*=//g'; my $remote_workdir=grep remote_workdir $manager_cnf|sed -e 's/[^=]*=//g'; my $sendmail = '/usr/sbin/sendmail'; # sendmailcommand path my $from = 'hoge@isao.net'; # From email #my $to = 'hoge@isao.co.jp'; # Email address my $to = 'hoge@isao.net'; # Email address my $cc = 'hoge@ezweb.ne.jp'; # CC to email #$subject = 'test'; # Email subject #$msg = <"_TEXT_"; #="" メールの本文(ヒアドキュメントで変数に代入)="" #message="" #_text_="" #="" sendmail="" コマンド起動="" open(sdml,"|="" $sendmail="" -t="" -i")="" ||="" die="" 'sendmail="" error';="" #="" メールヘッダ出力="" print="" sdml="" "from:="" $from\n";="" print="" sdml="" "to:="" $to\n";="" print="" sdml="" "cc:="" $cc\n";="" print="" sdml="" "subject:="" $subject="" $datetime\n";="" print="" sdml="" "content-transfer-encoding:="" 7bit\n";="" print="" sdml="" "content-type:="" text/plain;charset="\"ISO-2022-JP\"\n\n";" #="" メール本文出力="" #print="" sdml="" "$msg";="" print="" sdml="" "$body";="" print="" sdml="" "\n";="" print="" sdml="" "ip="" addr="" show="" eth0:\n";="" print="" sdml="" "="" $vip";="" print="" sdml="" "\n";="" print="" sdml="" "="" manager_workdir:\n";="" print="" sdml="" /bin/ls="" -l="" $manager_workdir\n;="" print="" sdml="" "="" remote_workdir:\n";="" print="" sdml="" /bin/ls="" -l="" $remote_workdir\n;="" #="" sendmail="" コマンド閉じる="" close(sdml);="" exit="" 0;="" ------------------------------------------[/shell]="" ※tarからダウンロードしてきたものに追記。="" ※適宜、sendmailのセットアップとレポートが送付されるか動作確認する。="" ⑤="" 設定変更(ノード)="" ・sudo設定="" [shell]="" ##="" #="" visudo="" defaults="" requiretty="" ↓="" ##="" #defaults="" requiretty="" [/shell]="" ※ssh越しにsudoして以下のメッセージが出る場合の設定(master_ip_failoverスクリプトを利用する場合に必要)="" sudo:="" sorry,="" you="" must="" have="" a="" tty="" to="" run="" sudo="" ・mysql的な初期ユーザ設定(ノード側)="" rootでセグメント許可でgrantつけず(mysqlのユーザ名はrootでなくても大丈夫)="" [sql]="" #="" mysql="" -u="" root="" -p=""> GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.100.%' IDENTIFIED BY '****'; select User,Host,Password from mysql.user; [/sql] ・Replication Build
Check the log files and positions of the > master
Master
[sql] RESET MASTER; show master status; [/sql] Check and record the log file name and position just in case.Specify master information on the slave server and start replication
Slave
[sql] RESET MASTER; show master status; CHANGE MASTER TO MASTER_HOST='Master's IP address', MASTER_USER='repl', MASTER_PASSWORD='**', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; START SLAVE; show slave status\G set global read_only=1; show global variables like 'read_only'; [/sql] reset master if it is in the initial state; reset slave; All you have to do is match the position.
If you have data, record the position in the master, retrieve and transfer the data with mysqldump, input the data in mysql, match the positions, and start slave.
Filtering rules such as Replicate_Ignore_DB must be common・Relay log switching settings
purge_relay_logs is a complementary command to the setting that prevents the relay log set on the node when MHA is introduced.
When the master is switched, the relay log is used to compensate for the difference with the master, so do not delete it because it is necessary.
[shell]
mysql -u root -pcat /path_to_file
set global relay_log_purge=0; show global variables like 'relay_log%';
ls -l /var/lib/mysql/mysqld-relay-bin.*
/usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/log/masterha/purge_relay_logs.log 2>&1
ls -l /var/lib/mysql/mysqld-relay-bin.*
tail /var/log/masterha/purge_relay_logs.log
crontab -e
----------slave1---------- 30 2,4,6,10,14,16 * * * /usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/ log/masterha/purge_relay_logs.log 2>&1 ----------slave2----------
30 3,5,9,11,15,17 * * * /usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/log/masterha/purge_relay_ logs.log 2>&1
[/shell] *It is recommended to call cron at different times between slaves. (If all slaves start purge_relay_logs at the same time, none of the slaves may have the required relay log events in the event of a crash.) )
*Be careful not to overlap with other aggregate batches. (Load-wise)
If the relay log accumulates too much and causes problems, it should be implemented every hour.
*In the case of operation that does not return the master, it is a good idea to register the master in a comment-out state at a staggered time.
*If you are mounted on an ioDrive and cannot hardlink to /var/tmp, you need to specify the directory you are mounting with the –workdir option.
purge_relay_logs For more information on command options and specifications, see here.
Relay Log Specifications
http://dev.mysql.com/doc/refman/5.1/ja/slave-logs-relaylog.htmlhttp://open-groove.net/mysql/binlog-relay-log/ Next is the switching test.
・Reference URL
mha-for-mysqldenaMHA for MySQL: Master High Availability Manager and tools for MySQLImplementing MySQL-MHA What to consider when applying MySQL-MHA to your environmentTry MySQL-MHA, try using MySQL-MHA to achieve automatic MySQL failover, compare DeNA's MySQL operation with Amazon RDS at the NHN Technology Conference, etc. I tried putting mysql-mha! Part 2MHA, Murakumo & Meatomd / master_ip_failover Let's failover MySQL replication with MHA and HAProxy Let's redundant MySQL with MHA + HAProxyDeployment Settings of MHA (MasterHigh AvailabilityManager) Versions used: mha4mysql-manager-0.53-0, MySQL-5.5
(1) Explanation of simple benefits, etc.
MHA is a product that uses the latest slave as the master in the event of a mysql master failure, compensates for the differences of other slaves, and changes the direction of the master.
Compared to Heartbeat+mon+mysql, it also reconstructs replication, so even if you switch, the DB will not become a single. (For 3 or more configurations)
Author's slide official website
MHA Limitations: LOAD DATA INFILE cannot be used for mysql 5.0 or higher and SBR (Statement-Based Replication)
*The manager is the admin server, and the node is the DB server (common to master and slave).
(2) Install in the manager *Hereinafter performed from the ADM server
Installation
[php]
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.53-0.noarch.rpm
yum --enablerepo=rpmforge install \
perl-Config-Tiny \ perl-Time-HiRes \ perl-Log-Dispatch \ perl-Parallel-ForkManager \ perl-Params-Validate
yum install perl-DBD-MySQL
rpm -ivh mha4mysql*
[/php]
[shell]yum install --enablerepo=remi mysql mysql-server perl-DBD-MySQL[/shell] *The manager may not be needed for mysql-server
(3) Installation on a node *Hereinafter performed from a DB server
[shell]
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
rpm -ivh mha4mysql-node*
[/shell] (4) Changing Settings (Manager)
・SSH public key setting
You need to be able to log in to the node with root from the manager
Create a key (ssh-keygen -t rsa -N "")
Add the public key (~.pub) to the public key ring (/root/.ssh/authorized_keys) on each node.
・Installation of configuration files and scripts
[shell]
# vi /etc/app1.cnf
[server default]
mysql user and password
user=root password= ssh_user=root
working directory on the manager
manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log
working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
master binlog dir
master_binlog_dir=/usr/local/mysql/var
master_ip_failover_script=/usr/local/bin/master_ip_failover report_host=/usr/local/bin/send_report ping_interval=3
[server1] hostname=192.168.100.1 port=3306
[server2] hostname=192.168.100.2 port=3306 candidate_master=1
[server3] hostname=192.168.100.3 port=3306
no_master=1
[/shell] main configuration parameters for details here
*Other parameters that may be better
secondary_check_script
Specify the script and host that will also check from the second interface
ignore_fail
Even if the second slave falls, if the master falls, if you want to switch, you can ignore it as a slave.
Script to switch VIP
[shell]
# vi /usr/local/bin/master_ip_failover
#!/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 $vip = '192.168.100.5/24'; # Virtual IP my $key = "1"; my $ssh_start_vip = "sudo /sbin/ifconfig eth1:$key $vip"; my $ssh_stop_vip = "sudo /sbin/ifconfig eth1:$key down"; my $ssh_stop_mysqld = "sudo /sbin/service mysqld 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 "\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"; eval { &stop_vip(); print "Stop mysqld on old master: $orig_master_host \n"; system("ssh $ssh_user\@$orig_ master_host \" $ssh_stop_mysqld \""); }; system("/usr/local/bin/mod_lvs_weight.sh"); $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\@gentoo8 \" $ssh_start_vip \";
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_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";
}
[/shell] *Processing parts such as dropping IP, IF and mysql, and changing the weight of LVS should be changed as necessary.
Original script
Script to change the weight of the LVS called from the script that switches VIP
[shell]
# vi /usr/local/bin/mod_lvs_weight.sh
#!/bin/bash #
this script is kicked from master_ip_failover-script.
script-task: modify lvs-weight and backup-script-weight from admin-server.
isao-unyou 2012.11.5 - Creation
please modify. (and check the ldirectod.cf.failover!)
TITLE=TEST READVIP=192.168.100.8 LVSSRV1=192.168.100.6 LVSSRV2=192.168.100.7 NIC=eth1 DB1=192.168.100.1 DB2=192.168.100.2 DB3=192.168.100.3 NETWORK=192.168.100 LVS_CF=/etc/ ha.d/ldirectord.cf MAILADDR=XXXX@isao.net BKUPSRV=192.168.100.3 BKUP_SH=/opt/bin/mysql-back.sh DB1_WEIGHT=0 DB2_WEIGHT=2 DB3_WEIGHT=4 BK_WEIGHT=4 MHA_LOG=/var/log/ masterha/app1/manager.log
read vip server check
srv_chk() { RVIP_CHK=ssh $LVSSRV1 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK" ]; then LVSSRV=$LVSSRV1 LVSBKSRV=$LVSSRV2 RVIP_CHK2=ssh $LVSSRV2 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK2" ]; then echo "LANG=C;date: LVS read vip split-brain uname -n." \ | mail -s "NG_READ_VIP $READVIP" $MAILADDR echo 'ERROR: LVS read vip split-brain. ' exit 1 fi else RVIP_CHK2=ssh $LVSSRV2 ip addr show $NIC|grep $READVIP|awk '{print $2}'|awk -F / '{print $1}' if [ "$READVIP" = "$RVIP_CHK2" ]; then LVSSRV=$LVSSRV2 LVSBKSRV=$LVSSRV1 else echo "LANG=C;date: LVS read vip unknown uname -n." \ | mail -s "NG_READ_VIP $READVIP" $MAILADDR echo 'ERROR: LVS read vip unknown. ' exit 1 fi fi }
modify lvs-weight and mail to unyou. (lvs weight info)
mod_weight() { ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 -r $DB1 -g -w $DB1_WEIGHT" ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 -r $DB2 -g -w $DB2_WEIGHT" ssh $LVSSRV "ipvsadm -e -t $READVIP:3306 - r $DB3 -g -w $DB3_WEIGHT" ssh $LVSSRV "cp -p $LVS_CF{,.date +%Y%m%d.%H%M} \ && \cp -pf $LVS_CF.failover $LVS_CF \ && service ldirectord force-reload" \ && scp -Cp $LVSSRV:$LVS_CF / tmp/ \ && scp -Cp /tmp/ldirectord.cf $LVSBKSRV:/etc/ha.d/ echo -e "LANG=C;date\nCurrent weights: \n\n ssh $LVSSRV ipvsadm -Ln" \ | mail -s "$TITLE mysql-master Failover. modify LVS and bkupscript weight." $MAILADDR
modify backup-script.
#ssh $BKUPSRV "cp -p $BKUP_SH{,.date +%Y%m%d.%H%M} && sed -i \"s/WEIGHT=[0-9]{1,100}/WEIGHT=$BK_WEIGHT/g\" $BKUP_SH" }
main
exec >> $MHA_LOG exec 2>&1 echo "START lvs weight modify. date '+%Y%m%d %T'" srv_chk mod_weight echo "END lvs weight modify. date '+%Y%m%d %T'"
exit 0
chmod +x /usr/local/bin/mod_lvs_weight.sh
[/shell]
Script to send a report email
[shell]# vi /usr/local/bin/send_report
#!/usr/bin/perl
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;
#new_master_host and new_slave_hosts are set only when recovering master succeeded my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body ); 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 here
#my $vip = /sbin/ip addr show eth0; my $vip = /usr/bin/ssh $new_master_host /sbin/ip addr show eth0; my $datetime = /bin/date +%Y%m%d_%H:%M:%S; my $manager_cnf='/etc/app1.cnf'; my $manager_workdir=grep manager_workdir $manager_cnf|sed -e 's/[^=]*=//g'; my $remote_workdir=grep remote_workdir $manager_cnf|sed -e 's/[^=]*=//g'; my $sendmail = '/usr/sbin/sendmail'; # sendmailcommand path my $from = 'unyou-all@isao.net'; # From email #my $to = 'komiyay@isao.co.jp'; # Recipient email address my $to = 'unyou-all@isao.net'; # Email address my $cc = 'komi325_y@ezweb.ne.jp'; # CC to email #$subject = 'test'; # Email subject #$msg = <"_TEXT_"; #="" メールの本文(ヒアドキュメントで変数に代入)="" #message="" #_text_="" #="" sendmail="" コマンド起動="" open(sdml,"|="" $sendmail="" -t="" -i")="" ||="" die="" 'sendmail="" error';="" #="" メールヘッダ出力="" print="" sdml="" "from:="" $from\n";="" print="" sdml="" "to:="" $to\n";="" print="" sdml="" "cc:="" $cc\n";="" print="" sdml="" "subject:="" $subject="" $datetime\n";="" print="" sdml="" "content-transfer-encoding:="" 7bit\n";="" print="" sdml="" "content-type:="" text/plain;charset="\"ISO-2022-JP\"\n\n";" #="" メール本文出力="" #print="" sdml="" "$msg";="" print="" sdml="" "$body";="" print="" sdml="" "\n";="" print="" sdml="" "ip="" addr="" show="" eth0:\n";="" print="" sdml="" "="" $vip";="" print="" sdml="" "\n";="" print="" sdml="" "="" manager_workdir:\n";="" print="" sdml="" /bin/ls="" -l="" $manager_workdir\n;="" print="" sdml="" "="" remote_workdir:\n";="" print="" sdml="" /bin/ls="" -l="" $remote_workdir\n;="" #="" sendmail="" コマンド閉じる="" close(sdml);="" exit="" 0;="" ------------------------------------------[/shell]="" ※tarからダウンロードしてきたものに追記。="" ※適宜、sendmailのセットアップとレポートが送付されるか動作確認する。="" ⑤="" 設定変更(ノード)="" ・sudo設定="" [shell]="" ##="" #="" visudo="" defaults="" requiretty="" ↓="" ##="" #defaults="" requiretty="" [/shell]="" ※ssh越しにsudoして以下のメッセージが出る場合の設定(master_ip_failoverスクリプトを利用する場合に必要)="" sudo:="" sorry,="" you="" must="" have="" a="" tty="" to="" run="" sudo="" ・mysql的な初期ユーザ設定(ノード側)="" rootでセグメント許可でgrantつけず(mysqlのユーザ名はrootでなくても大丈夫)="" [sql]="" #="" mysql="" -u="" root="" -p=""> GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.100.%' IDENTIFIED BY '****'; select User,Host,Password from mysql.user; [/sql] ・Replication Build
Check the log files and positions of the > master
Master
[sql] RESET MASTER; show master status; [/sql] Check and record the log file name and position just in case.Specify master information on the slave server and start replication
Slave
[sql] RESET MASTER; show master status; CHANGE MASTER TO MASTER_HOST='Master's IP address', MASTER_USER='repl', MASTER_PASSWORD='**', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; START SLAVE; show slave status\G set global read_only=1; show global variables like 'read_only'; [/sql] reset master if it is in the initial state; reset slave; All you have to do is match the position.
If you have data, record the position in the master, retrieve and transfer the data with mysqldump, input the data in mysql, match the positions, and start slave.
Filtering rules such as Replicate_Ignore_DB must be common・Relay log switching settings
purge_relay_logs is a complementary command to the setting that prevents the relay log set on the node when MHA is introduced.
When the master is switched, the relay log is used to compensate for the difference with the master, so do not delete it because it is necessary.
[shell]
mysql -u root -pcat /path_to_file
set global relay_log_purge=0; show global variables like 'relay_log%';
ls -l /var/lib/mysql/mysqld-relay-bin.*
/usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/log/masterha/purge_relay_logs.log 2>&1
ls -l /var/lib/mysql/mysqld-relay-bin.*
tail /var/log/masterha/purge_relay_logs.log
crontab -e
----------slave1---------- 30 2,4,6,10,14,16 * * * /usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/ log/masterha/purge_relay_logs.log 2>&1 ----------slave2----------
30 3,5,9,11,15,17 * * * /usr/bin/perl /usr/bin/purge_relay_logs --user=root --password=cat /path_to_file --disable_relay_log_purge >> /var/log/masterha/purge_relay_ logs.log 2>&1
[/shell] *It is recommended to call cron at different times between slaves. (If all slaves start purge_relay_logs at the same time, none of the slaves may have the required relay log events in the event of a crash.) )
*Be careful not to overlap with other aggregate batches. (Load-wise)
If the relay log accumulates too much and causes problems, it should be implemented every hour.
*In the case of operation that does not return the master, it is a good idea to register the master in a comment-out state at a staggered time.
*If you are mounted on an ioDrive and cannot hardlink to /var/tmp, you need to specify the directory you are mounting with the –workdir option.
purge_relay_logs For more information on command options and specifications, please refer to here.
Relay Log Specifications
http://dev.mysql.com/doc/refman/5.1/ja/slave-logs-relaylog.htmlhttp://open-groove.net/mysql/binlog-relay-log/
Next is the switching test.
・Reference URL
mha-for-mysqldenaMHA for MySQL: Master High Availability Manager and tools for MySQLImplementing MySQL-MHA What to consider when applying MySQL-MHA to your environmentTry MySQL-MHA, try using MySQL-MHA to achieve automatic MySQL failover, compare DeNA's MySQL operation with Amazon RDS at the NHN Technology Conference, etc. I tried putting mysql-mha! Part 2MHA, Murakumo & Meatomd / master_ip_failoverFailover MySQL replication with MHA and HAProxy Let's redundant MySQL with MHA + HAProxy
</"_TEXT_";>
</"_TEXT_";>