1 配置主从
主:192.168.16.105
从:192.168.16.102
192.168.16.106
管理:192.168.16.148
2 配置ssh免秘钥登录
创建ssh登录用户mysql5
useradd -d /home/mysql5 mysql5
su - mysql5
其中一台mysql节点执行:
ssh-keygen -t rsa
3台mysql节点都使用这一组秘钥
重命名公钥
mv id_rsa.pub authorized_keys
在mysql用户目录下创建.ssh目录,授予700权限
chmod -R 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
chmod 600 ~/.ssh/id_rsa
管理节点要连接mysql节点,而mysql节点不需要连接管理节点
在管理节点生成一对秘钥
scp id_rsa.pub到其中一台mysql节点
mv authorized_keys authorized_keys.bak
cat authorized_keys id_rsa.pub > authorized_keys
rm -rf authorized_keys.bak
测试,排错
3 配置mha
3.1 安装
所有节点
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
有需要安装的包就安装
管理节点
yum -y install perl-DBD-MySQL*
yum -y install perl-Config-Tiny*
yum -y install perl-Parallel-ForkManager*
yum -y install perl-MailTools*
yum -y install perl-Email-Date-Format*
yum -y install perl-Mail-Sender*
yum -y install perl-MIME-Types*
yum -y install perl-MIME-Lite*
yum -y install perl-Mail-Sendmail*
yum -y install perl-Log-Dispatch*
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
3.2 配置文件
创建目录
$
mkdir /etc/mha
==============================================================
cat mha.cnf
[server default]
# 需要在 master 中创建这个用户
# mysql> grant ALLPRIVILEGES on *.* to mha@"%" Identified by "111111";
user=mhamgr
password=123456
# 在各台服务器上创建目录 /opt/mha
manager_workdir=/data1/mha/masterha/app
# 日志位置
manager_log=/data1/mha/masterha/app/manager.log
remote_workdir=/data1/mha/masterha/app
ssh_user=mysql5
# 配置主从复制时创建的复制用户
repl_user=rep
repl_password=123456
ping_interval=1
# 在 master 上查询日志目录
# mysql> show variableslike 'log_bin_basename%';
# 取此变量值的目录
#master_binlog_dir=/var/log/mysql/mysql-bin
# VIP 切换脚本
#master_ip_failover_script="/etc/mha/master_ip_failover.sh 10.0.0.10 1 " #失败切换,在 MySQL 从服务器提升为新的主服务器时,调用此脚本,因此可以将
master_ip_failover_script=/etc/mha/master_ip_failover.sh
#vip 信息写到此配置文件
#master_ip_online_change_script="/etc/mha/master_ip_online_change.sh10.0.0.10 1 " #在线切脚本,使用masterha_master_switch 命令手动切换 MySQL 主服
master_ip_online_change_script=/etc/mha/master_ip_online_change.sh
#务器时后会调用此脚本
#secondary_check_script=/usr/bin/masterha_secondary_check-s 192.168.11.102 -s 192.168.11.103 --user=root --master_host=192.168.11.101
#--master_ip=192.168.11.101--master_port=5310
[server1]
hostname=192.168.16.105
port=3306
# 说明此服务器作为 master 候选服务器
candidate_master=1
[server2]
hostname=192.168.16.106
port=3306
candidate_master=1
[server3]
hostname=192.168.16.102
port=3306
# 此服务器不作为 master 候选服务器,因为 manager 在这台服务器上
no_master=1
=============================================================================
cat master_ip_failover.sh
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you canredistribute it and/or modify
# it under the terms of the GNU General PublicLicense as published by
# the Free Software Foundation; either version2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope thatit will be useful,
# but WITHOUT ANY WARRANTY; without even theimplied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULARPURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNUGeneral Public License
# along with this program; if not, write to theFree Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston,MA 02110-1301 USA
## Note: This is a samplescript and is not complete. Modify the script based on your environment.
#!/usr/bin/env perl
#!/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 ='10.77.133.1';
my $vip = '192.168.17.10';
#my $bcast ='10.77.133.255';
#my $netmask ='255.255.255.0';
my $interface = 'eth0';
my $key = 1;
#my $ssh_start_vip ="sudo /sbin/ifconfig $interface:$key $vip netmask $netmask && sudo/sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip -U $gateway";
my $ssh_stop_vip ="sudo /sbin/ifconfig $interface:$key down";
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 SCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" ||$command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP onold master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vipon 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 thescript.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
my $bcast = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$3}' | awk -F":" '{print \$2}'`;
chomp $bcast;
my $gateway = `ssh $ssh_user\@$new_master_host sudo /sbin/route -n | grep 'UG' | awk '{print \$2}'`;
chomp $gateway;
my$netmask = `ssh$ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' | head -1 | awk'{print \$4}' | awk -F":" '{print \$2}'`;
chomp $netmask;
my $ssh_start_vip = "sudo/sbin/ifconfig $interface:$key $vip broadcast $bcast netmask $netmask&& sudo /sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip -U $gateway";
print"=======$ssh_start_vip=================\n";
`ssh $ssh_user\@$new_master_host \"$ssh_start_vip \"`;
}
sub stop_vip() {
my $ssh_user = "mysql5";
print "=======$ssh_stop_vip==================\n";
`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";
}
====================================================================
黄色背景为需要修改参数
=====================================================================
cat /etc/mha/master_ip_online_change.sh
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you canredistribute it and/or modify
# it under the terms of the GNU General PublicLicense as published by
# the Free Software Foundation; either version2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope thatit will be useful,
# but WITHOUT ANY WARRANTY; without even theimplied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULARPURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNUGeneral Public License
# along with this program; if not, write tothe Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston,MA 02110-1301 USA
## Note: This is a samplescript 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( sleepgettimeofday tv_interval );
use Data::Dumper;
my $_tstart;
my $_running_interval = 0.1;
my (
$command, $orig_master_host, $orig_master_ip,
$orig_master_port, $orig_master_user,
$new_master_host, $new_master_ip, $new_master_port,
$new_master_user, $orig_master_password,$new_master_password,
);
#my $gateway = '10.77.133.1';
my $vip = '192.168.17.10';
#my $bcast = '10.77.133.255';
#my $netmask ='255.255.255.0';
my $interface = 'eth0';
my $key = 1;
#my $ssh_start_vip ="sudo /sbin/ifconfig $interface:$key $vip netmask $netmask && sudo/sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip -U $gateway";
my $ssh_stop_vip = "sudo/sbin/ifconfig $interface:$key down";
my $ssh_user ="mysql5";
#my$orig_master_user='mha_user';
#my$orig_master_password='UKIw57RnC76m';
#my$new_master_user='mha_user';
#my$new_master_password='UKIw57RnC76m';
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,
'orig_master_user=s' => \$orig_master_user,
'orig_master_password=s' =>\$orig_master_password,
'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,
);
exit &main();
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("SHOWPROCESSLIST");
$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/ ifdefined($info);
next if ( $my_connection_id == $id );
next if ( defined($query_time) &&$query_time < $running_time_threshold );
next if ( defined($command) && $command eq "BinlogDump" );
next if ( defined($user) && $user eq "systemuser" );
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 thecurrent master
# 1. Set read_only= 1 on the new master
# 2. DROP USER so that no app user canestablish new connections
# 3. Set read_only= 1 on the current master
# 4. Kill current queries
# * Any database access failure will resultin script die.
my $exit_code = 1;
eval {
## Setting read_only=1 on the new master(to avoid accident)
my $new_master_handler = newMHA::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() . " Setread_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 ifany database error happens
my $orig_master_handler = newMHA::DBHelper();
$orig_master_handler->connect($orig_master_ip, $orig_master_port,
$orig_master_user,$orig_master_password, 1 );
## Drop application user so that nobodycan connect. Disabling per-session binlog beforehand
#$orig_master_handler->disable_log_bin_local();
#print current_time_us() . " Drppingapp user on the orig master..\n";
#FIXME_xxx_drop_app_user($orig_master_handler);
## Waiting for N * 100 milliseconds sothat 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 currentmaster so that nobody(except SUPER) can write
print current_time_us() . " Setread_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 sothat 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});
}
print "Disabling the VIPon old master: $orig_master_host \n";
&stop_vip();
## Terminating all threads
print current_time_us() . " Killingall 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, MHAexecutes 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 thecatalog database
# We don't return error eventhough activating updatable accounts/ip failed so that we don't interruptslaves' recovery.
# If exit code is 0 or 10,MHA does not abort
my $exit_code = 10;
eval {
my $new_master_handler = newMHA::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() . " Setread_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";
#FIXME_xxx_create_app_user($new_master_handler);
#$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalogdatabase, etc
print "Enabling the VIP -$vip on the new master - $new_master_host \n";
&start_vip();
$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;
}
}
# A simple system call thatenable the VIP on the new master
sub start_vip() {
my $bcast = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$3}' | awk -F":" '{print \$2}'`;
chomp $bcast;
my $gateway = `ssh $ssh_user\@$new_master_host sudo /sbin/route -n | grep 'UG' | awk '{print \$2}'`;
chomp $gateway;
my $netmask = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$4}' | awk -F":" '{print \$2}'`;
chomp $netmask;
my $ssh_start_vip = "sudo/sbin/ifconfig $interface:$key $vip broadcast $bcast netmask $netmask&& sudo /sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip -U $gateway";
print"===========$ssh_start_vip===========================\n";
`ssh $ssh_user\@$new_master_host \"$ssh_start_vip \"`;
}
# A simple system call thatdisable the VIP on the old_master
sub stop_vip() {
print"===========$ssh_stop_vip===========================\n";
`ssh $ssh_user\@$orig_master_host \"$ssh_stop_vip \"`;
}
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;
}
也有需要改的
权限777 用户组ssh用户
3.3 配置VIP
ifconfig eth0:1 192.168.17.10 broadcast 192.168.17.1 netmask 255.255.255.0
arping -f -q -c 5 -w 5 -I eth0 -s 192.168.17.10 -U 192.168.17.1
##发一个arp广播,告诉别的机器此地址的存在
4 测试
ssh测试
masterha_check_ssh --conf=/etc/mha/mha.cnf
主从复制测试
masterha_check_repl --conf=/etc/mha/mha.cnf
5 启动
masterha_manager --conf=/etc/mha/mha.cnf--ignore_last_failover < /dev/null > 2 &>1 &
--ignore_last_failover
作用
忽略上次mha切换遗留的文件,也可以删除工作目录下的mha.failover.complete
6 问题
添加其他用户后会有问题:
权限问题:
/dev/null权限
chmod 666 /dev/null
usermod -a -G root mysql5
mysql日志目录权限
ssh用户加到mysql属组,日志目录给权限
6.1 sudo
sudo免密码权限
在/etc/sudoers文件的root行添加一行用户的免密码sudo权限
root ALL=(ALL) ALL
mysql5 ALL=(ALL) NOPASSWD:ALL
sudo报错:sorry, you musthave a tty to run sudo
后台执行不了sudo的解决办法:
调用shell需要使用终端,而不用终端调用shell需要配置:
在/etc/sudoers文件中注释下行:
Defaults requiretty
然后修改为下行
Defaults:mysql5 !requiretty
表示mysql5可以不用控制终端
6.2 目录权限
免秘钥登录的用户目录下的.ssh
.ssh目录权限为700
里面的公钥和私钥权限为600
用户属组为该用户
mha的工作目录用户属组为ssh免秘钥登录的用户
权限给777
/etc/mha 目录权限777