实现环境
centos6.7
MYSQL5.6.36
主：192.168.1.191
从1：192.168.1.145
从2：192.168.1.146
监测：放在从2上 192.168.1.146
虚拟IP:192.168.1.222
准备软件包：下载链接: https://pan.baidu.com/s/1jHYafcU 密码: irbv
epel-release-6-8.noarch.rpm (所有服务器上都要)
mha4mysql-node-0.56-0.el6.noarch.rpm (所有服务器上都要)
mha4mysql-manager-0.56-0.el6.noarch.rpm (监测服务器)
步骤
1.在主DB服务器上建立复制账号 (在Master上执行)
# mysql
mysql> CREATE USER 'repl'@'192.168.1.%' identified by '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
2.主服务器配置 (在Master上执行)
# vim /etc/my.cnf
----------------------------内容如下---------------------------------
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
# binary logging #
log-bin = /data/mysql/log-bin
# replice #
server_id = 191
gtid_mode = on
enforce_gtid_consiste = on
log_slave_updates = on
----------------------------内容如上---------------------------------
3.从服务器配置 (在两个从服务器上执行)
# vim /etc/my.cnf
----------------------------内容如下----------------------------------
[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
# binary logging #
log-bin = /data/mysql/mysql-bin
# replice #
# server-id = 145 #选择对应的
# server-id = 146 #选择对应的
relay_log = /data/mysql/relay-bin
gtid_mode = on
enforce_gtid_consistency = on
log_slave_update = on
read_only = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
----------------------------内容如上----------------------------------
4.重启所有的MYSQL （所有服务器上都执行）
# service mysqld restart
5.备份主数据库(如果主从上的数据库版本一样，可以全备) (在Master上执行)
# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases > full.sql
6.将主数据库备份传输到从数据库中 (在Master上执行)
# scp /root/full.sql root@192.168.1.145:/root
# scp /root/full.sql root@192.168.1.146:/root
7.将主数据库的备份导入到从数据库中 (在两个从上执行)
# mysql -uroot -p < full.sql
8.初始复制链路 (在两个从上执行)
# mysql
mysql>
change master to
master_host='192.168.1.191',
master_user='repl',
master_password='123456',
master_auto_position=1;
mysql>start slave;
mysql>show slave statusG
以上全部操作为基于GTID的复制配置部分
##########################################################################
9.SSH配置（当前用户为root）（所有服务器上都执行）
# ssh-keygen #全部按回车
# ssh-copy-id -i /root/.ssh/id_rsa '-p 22 192.168.1.191'
# ssh-copy-id -i /root/.ssh/id_rsa '-p 22 192.168.1.145'
# ssh-copy-id -i /root/.ssh/id_rsa '-p 22 192.168.1.146'
10.安装 mha4mysql-node-0.56-0.el6.noarch.rpm （所有服务器上都执行）
# rpm -ivh epel-release-6-8.noarch.rpm
# yum -y install perl-DBD-MySQL ncftp
# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
11.安装 mha4mysql-manager-0.56-0.el6.noarch.rpm （监测服务器上执行）
# yum -y install perl-Config-Tiny.noarch perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch-Perl.noarch
# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
12.创建MHA工作目录及数据库账号
# mkdir -p /home/mysql_mha #创建MHA的工作目录 （所有服务器上都执行）
# mysql
mysql> grant all privileges on *.* to mha@'192.168.1.%'identified by '123456'; (只在主数据库上执行，可以同步到其他服务器上)
mysql> show grants for mha@'192.168.1.%' ; #检查创建结果（所有服务器上都执行）
13.配置mha的配置文件 （监测服务器上执行）
# mkdir -p /etc/mha
# touch /etc/mha/mysql_mha.cnf
# vim /etc/mha/mysql_mha.cnf
----------------------------内容如下----------------------------------
[server default]
user=mha
password=123456
manager_workdir=/home/mysql_mha #MHA工作目录
manager_log=/home/mysql_mha/manager.log #MHA管理日志
remote_workdir=/home/mysql_mha #MHA远程工作目录
ssh_user=root #ssh时的用户
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/data/mysql #对应修改
master_ip_failover_script=/usr/bin/master_ip_failover
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.1.191 -s 192.168.1.145 -s 192.168.1.146
[server1]
hostname=192.168.1.191
candidate_master=1
[server2]
hostname=192.168.1.145
candidate_master=1
[server3]
hostname=192.168.1.146
no_master=1 #不参入选举
----------------------------内容如上----------------------------------
14.创建master_ip_failover脚本，用于自动切换 （监测服务器上执行）
# touch /usr/bin/master_ip_failover
# chomd 777 /usr/bin/master_ip_failover
# vim /usr/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.1.222/24'; #对应修改虚拟IP就可以了
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $exit_code = 0;
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 "
IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===
";
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 "
***************************************************************
";
print "Disabling the VIP - $vip on old master: $orig_master_host
";
print "***************************************************************
";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@
";
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 "
***************************************************************
";
print "Enabling the VIP - $vip on new master: $new_master_host
";
print "***************************************************************
";
&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
";
`ssh $ssh_user@$orig_master_host " $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=po
rt –new_master_host=host –new_master_ip=ip –new_master_port=port
";
}
----------------------------内容如上----------------------------------
15.检查相关配置并启动MHA（监测服务器上执行）
# masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf
# masterha_check_repl --conf=/etc/mha/mysql_mha.cnf
# nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf & #后台启动
16.配置虚拟IP (在Master上执行)
# ip addr
# ifconfig eth0:1 192.168.1.222/24
# ip addr
17.测试能否自动转移
# service mysqld stop (在Master上执行)
# ip addr (在Master上执行)
# ip addr (在从1上执行)
如果192.168.1.222成功的从192.168.1.191 飘移到192.168.1.146上就成功了。
以上操作步骤为本人实操记录，实验成功实现了VIP 飘移。