实验环境:
角色 | 主机名 | ip地址 | servre-id | 类型 |
---|---|---|---|---|
Master(Monitor host) | server1 | 172.25.31.1 | 1 | 写入(监控复制组) |
Candicate master | server2 | 172.25.31.2 | 2 | 读 |
Slave | server3 | 172.25.31.3 | 3 | 读 |
配置msyql
配置servre1、2、3为主从复制、开启gtid、设为半同步复制
mysql的主从复制、GTID、半同步复制、并行复制
在servre1、2、3中创建帐号(slave 来复制时用的身份)
mysql> grant replication slave on *.* to 'repl'@'172.25.31.%' identified by 'Cqmyg+666';
在servre2、3中登陆
mysql> change master to master_host='172.25.31.1',master_user='repl', master_password='Cqmyg+666',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.58 sec)
mysql> start slave;
Query OK, 0 rows affected (0.15 sec)
mysql> show slave status\G;#查看状态
在server1中创建一个数据库westos
mysql> create database westos
在server2、3中查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
主从配置成功
在server2、3中设置
set global read_only=1
在server1、2、3中允许数据库的远程登陆
grant all on *.* to root@'%' identified by 'Cqmyg+666'
安装MHA
在server1、2、3安装MHA node
yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
在server1安装MHA Manager
yum install mha4mysql-manager-0.56-0.el6.noarch.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpm perl-Config-Tiny-2.12-7.1.el6.noarch.rpm perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-MIME-Lite-3.027-2.el6.noarch.rpm perl-MIME-Types-1.28-2.el6.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-12.el6.noarch.rpm perl-Email-Date-Format-1.002-5.el6.noarch.rpm
配置SSH登录无密码验证
Server1上
ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.2
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.3
Server2上
ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.1
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.3
Server3上
ssh-keygen -t rsa
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.1
sh-copy-id -i ~/.ssh/id_rsa.pub root@172.25.31.2
配置MHA
添加修改MHA配置文件
vim /etc/masterha/app1.cnf
[server default]
manager_workdir=/etc/masterha/ //设置manager的工作目录
manager_log=/etc/masterha/app1.log //设置manager的日志
master_binlog_dir=/data/mysql //设置master 保存binlog的位置,以便MHA可以找到master的日志,我这里的也就是mysql的数据目录
master_ip_failover_script= /usr/local/bin/master_ip_failover //设置自动failover时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change //设置手动切换时候的切换脚本
password=Cqmyg+666 //设置mysql中root用户的密码
user=root //设置用户root
ping_interval=1 //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp //设置远端mysql在发生切换时binlog的保存位置
repl_password=Cqmyg+666 //设置复制用户的密码
repl_user=repl //设置复制环境中的复制用户名
report_script=/root/MHA/send_report //设置发生切换后发送的报警的脚本
secondary_check_script= /usr/bin/masterha_secondary_check -s 172.25.31.1 -s 172.25.31.2
shutdown_script=""
ssh_user=root //设置ssh的登录用户名
[server1]
hostname=172.25.31.1
port=3306
[server2]
hostname=172.25.31.2
port=3306
candidate_master=1 //设置为候选master,如果设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个主库不是集群中事件最新的slave
check_repl_delay=0 //默认情况下如果一个slave落后master 100M的relay logs的话,MHA将不会选择该slave作为一个新的master,因为对于这个slave的恢复需要花费很长时间,通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master
[server3]
hostname=172.25.31.3
port=3306
设置自动failover时候的切换脚本
vim /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 = '172.25.31.100/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$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";
}
设置手动切换时候的切换脚本
vim /usr/local/bin/master_ip_online_change 设置手动切换时候的切换脚本
#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
use Getopt::Long;
my $vip = '172.25.31.100/24'; # Virtual 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;
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,
);
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 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 "\n\n\n***************************************************************\n";
print "Disabling the VIP - $vip on old master: $orig_master_host\n";
print "***************************************************************\n\n\n\n";
&stop_vip();
$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 "\n\n\n***************************************************************\n";
print "Enabling the VIP - $vip on new master: $new_master_host \n";
print "***************************************************************\n\n\n\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 $orig_master_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 $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $orig_master_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";
}
测试连接情况
测试ssh的连接情况
masterha_check_ssh --conf=/etc/masterha/app1.cnf
测试mysq集群的连接情况
masterha_check_repl --conf=/etc/masterha/app1.cnf
开启MHA Manager监控
nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
检查MHA的状态
masterha_check_status --conf=/etc/masterha/app1.cnf
查看启动日志
tail -n20 /etc/masterha/app1.log
其中”Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..”说明整个系统已经开始监控了。
关闭MHA Manage监控
masterha_stop --conf=/etc/masterha/app1.cnf
手动Failover
masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.31.2 --new_master_port=3306 --orig_master_is_new_slave
Server2由从库变为主库
VIP也已经成功转移
自动切换
开启MHA Manager监控
nohup masterha_manager --conf=/etc/masterha/app1.cnf --ignore_last_failover &
关闭server2上的数据库
查看日志
tail -n20 /etc/masterha/app1.log
切换成功
Vip也已经转移到了server1上