1.准备工作
1.准备机器（5台）
keepalived+mha（2台）：
10.11.13.11 keepalived master及mha管理机公用
10.11.13.12 keepalived slave
10.11.13.241 keepalived vip
10.11.13.242 mha vip
mysql（3台）：
10.11.13.13 master
10.11.13.14 slave
10.11.13.15 slave
2.host 解析（5台）
vi /etc/hosts
10.11.13.11 hostname1
10.11.13.12 hostname2
10.11.13.13 hostname3
10.11.13.14 hostname4
10.11.13.15 hostname5
3.主从配置 mysql（3台）
grant replication slave on.to ‘repl’@’%’ identified by ‘repl’;
从库两台执行
change master to master_host='10.11.13.13 ',master_user=‘repl’,master_password=‘repl’,MASTER_AUTO_POSITION =1;
2.MHA搭建
1.安装mha
在管理机（10.11.13.11）上
yum install perl-DBD-MySQL -y
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
mysql（3台）上
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
2.设置虚拟ip
/sbin/ifconfig eth0:1 10.11.13.242/24
此处的eth0代表的网卡可以输入ip a查看，根据具体环境替换
3.添加mha账号
useradd mha
passwd mha
chage -M 99999 mha
grant all privileges on.to ‘mha’@’%’ identified by ‘mha’;
4.添加互信(4台)
su - mha
ssh-keygen -t rsa
一直回车即可
ssh-copy-id -i .ssh/id_rsa.pub mha@10.11.13.11
ssh-copy-id -i .ssh/id_rsa.pub mha@10.11.13.13
ssh-copy-id -i .ssh/id_rsa.pub mha@10.11.13.14
ssh-copy-id -i .ssh/id_rsa.pub mha@10.11.13.15
5.更改mha脚本（管理机上）
mkdir /data/mha/
vi master_ip_failover
#!/usr/bin/env perluse 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 = '10.11.13.242';my $key = '1';my $gateway = '10.11.13.1';#Gateway IPmy $interface = 'eth0';my $ssh_start_vip = "sudo /sbin/ifconfig $interface:$key $vip/24; /sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";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 SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";if ( $command eq "stop" || $command eq "stopssh" ) {my $exit_code = 1;eval {print "Disabling the VIP on old 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 - $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";exit 0;}else {&usage();exit 1;}}sub start_vip() {`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;}sub stop_vip() {return 0 unless ($ssh_user);`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";}
vi master_ip_online_change
#!/usr/bin/env perluse 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_host, $orig_master_ip,$orig_master_port, $orig_master_user, $new_master_host, $new_master_ip, $new_master_port,$new_master_user, );my $vip = '10.11.13.242';my $key = '1';my $gateway = '10.11.13.1';#Gateway IPmy $interface = 'eth0';my $ssh_start_vip = "sudo /sbin/ifconfig $interface:$key $vip;sudo /sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";my $ssh_stop_vip = "sudo /sbin/ifconfig $interface:$key down";my $ssh_user = "mha";my $new_master_password='mha';my $orig_master_password='mha';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("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*$/$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" );nextif ( 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, 'admin+1234', 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 happensmy $orig_master_handler = new MHA::DBHelper();$orig_master_handler->connect( $orig_master_ip, $orig_master_port,$orig_master_user, 'admin+1234', 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 exitmy $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 writeprint 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 completemy $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 VIP on old master: $orig_master_host \n";&stop_vip();## Terminating all threadsprint current_time_us() . " Killing all 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, MHA executes 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 the catalog database# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.# If exit code is 0 or 10, MHA does not abortmy $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, 'admin+1234', 1 );## Set read_only=0 on the new master#$new_master_handler->disable_log_bin_local();print current_time_us() . " Set read_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 catalog database, etcprint "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 nothingexit 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_mastersub stop_vip() {`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;}
vi f
[server default]manager_log=/data/mha/mha.logmanager_workdir=/data/mha/master_binlog_dir=/data/mysql/datamaster_ip_failover_script=/data/mha/master_ip_failover //设置自动failover时候的切换脚本master_ip_online_change_script=/data/mha/master_ip_online_change //设置手动切换时候的切换脚本password="mha"ping_interval=6ping_type=CONNECTremote_workdir=/tmprepl_password=replrepl_user=replreport_script=/data/mha/send_reportsecondary_check_script=masterha_secondary_check -s 10.11.13.13 -s 10.11.13.14 -s 10.11.13.15ssh_user=mhauser=mha[server1]candidate_master=1check_repl_delay=0hostname=10.11.13.13master_binlog_dir=/data/mysqlport=3306[server2]candidate_master=1check_repl_delay=0hostname=10.11.13.13master_binlog_dir=/data/mysqlport=3306[server3]check_repl_delay=0hostname=10.11.13.13master_binlog_dir=/data/mysqlport=3306
6.检查mha及启动
masterha_check_ssh --conf=/data/mha/f
masterha_check_repl --conf=/data/mha/f
检查都ok的话启动mha
nohup /usr/bin/masterha_manager --conf=/data/mha/f &
3.keepalived搭建
1.设置虚拟ip
/sbin/ifconfig eth0:1 10.11.13.241/24
2.安装keepalived（2台）
yum install -y ipvsadm
yum install -y keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalivedglobal_defs {router_id LVS_MASTER}vrrp_instance VI_1 {stateMASTER #主MASTER,从BACKUP，主优先级高；interface eth0virtual_router_id 60#主从需一致；priority 100 #主100,从90优先级不同；advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {10.11.13.241}}virtual_server 10.11.13.241 3306 {delay_loop 6lb_algo wrrlb_kind DRnat_mask 255.255.255.0persistence_timeout 0protocol TCPreal_server 10.11.13.14 3306 {weight 1TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3connect_port 3306}}real_server 10.11.13.15 3306 {weight 2TCP_CHECK {connect_timeout 3nb_get_retry 3delay_before_retry 3connect_port 3306}}}
vi /etc/init.d/realserver
#!/bin/bashSNS_VIP=10.11.13.241 #vip唯一修改处case "$1" instart)ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP/sbin/route add -host $SNS_VIP dev lo:0echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignoreecho "2" >/proc/sys/net/ipv4/conf/all/arp_announcesysctl -p >/dev/null 2>&1echo "RealServer Start OK";;stop)ifconfig lo:0 downroute del $SNS_VIP >/dev/null 2>&1echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignoreecho "0" >/proc/sys/net/ipv4/conf/all/arp_announceecho "RealServer Stoped";;*)echo "Usage: $0 {start|stop}"exit 1esacexit 0
3.启动keepalived
keepalived两台
/etc/init.d/keepalived restart
或者centos7使用
systemctl start keepalived
mysql两台
chmod +x /etc/init.d/realserver
/etc/init.d/realserver start
5.验证
mysql -urepl -prepl -h10.11.13.241 -P3306 -e"show variables like 'hostname'";
若每次输出的hostname为轮询，则keepalived搭建成功，应用程序需配置两个vip，mha vip做写操作，keepalived vip做读操作，实现高可用切换及读写分离。
