MySQL之MHA+keepalived方案演示(二)
一、配置MHA工作环境
192.168.2.130机器操作:
1.1.创建MHA的工作目录,并且创建相关配置文件
(在软件包解压后的目录里面有样例配置文件)。指的是可以下载 mha4mysql-manager-0.56.tar.gz解压后找到样例配置文件
[root@server03 ~]# ifconfig eth1|awk -F '[ :]+' 'NR==2{print $4}'
192.168.2.130
[root@server03 ~]# ll /usr/bin/masterha*
-rwxr-xr-x 1 root root 1995 Apr 1 2014 /usr/bin/masterha_check_repl
-rwxr-xr-x 1 root root 1779 Apr 1 2014 /usr/bin/masterha_check_ssh
-rwxr-xr-x 1 root root 1865 Apr 1 2014 /usr/bin/masterha_check_status
-rwxr-xr-x 1 root root 3201 Apr 1 2014 /usr/bin/masterha_conf_host
-rwxr-xr-x 1 root root 2517 Apr 1 2014 /usr/bin/masterha_manager
-rwxr-xr-x 1 root root 2165 Apr 1 2014 /usr/bin/masterha_master_monitor
-rwxr-xr-x 1 root root 2373 Apr 1 2014 /usr/bin/masterha_master_switch
-rwxr-xr-x 1 root root 5171 Apr 1 2014 /usr/bin/masterha_secondary_check
-rwxr-xr-x 1 root root 1739 Apr 1 2014 /usr/bin/masterha_stop
[root@server03 ~]# cp -arp /usr/bin/masterha_* /usr/local/bin/
[root@server03 bin]# cd /usr/bin/
[root@server03 bin]# find ./ -name apply_diff_relay_logs
./apply_diff_relay_logs
[root@server03 bin]# find ./ -name filter_mysqlbinlog
./filter_mysqlbinlog
[root@server03 bin]# find ./ -name purge_relay_logs
./purge_relay_logs
[root@server03 bin]# find ./ -name save_binary_logs
./save_binary_logs
cp /usr/bin/save_binary_logs /usr/local/bin/
cp /usr/bin/purge_relay_logs /usr/local/bin/
cp /usr/bin/filter_mysqlbinlog /usr/local/bin/
cp /usr/bin/apply_diff_relay_logs /usr/local/bin/
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
[root@server03 bin]# cd /usr/local/bin/
You have new mail in /var/spool/mail/root
[root@server03 bin]# ls
apply_diff_relay_logs filter_mysqlbinlog masterha_check_ssh masterha_conf_host masterha_master_monitor masterha_secondary_check save_binary_logs
masterha_check_repl masterha_check_status masterha_manager masterha_master_switch masterha_stop purge_relay_logs
- 1.
- 2.
- 3.
- 4.
- 5.
MHA配置文件如下
[root@server03 ~]# mkdir -p /etc/masterha
[root@server03 ~]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1.log
master_binlog_dir=/data/mysql/logs/bin-log
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=repmha
report_script=/usr/local/bin/send_report
secondary_check_script=/usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.2.128 --master_port=3306
shutdown_script=""
ssh_port=10280
ssh_user=root
user=monitor
[server1]
hostname=192.168.2.128
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.2.129
port=3306
[server3]
hostname=192.168.2.130
port=3306
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
1.2配置文件参数介绍:
[root@192.168.2.131 ~]# cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1.log //设置manager的工作目录
manager_log=/var/log/masterha/app1/manager.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=123456 //设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
user=root //设置监控用户root
ping_interval=1 //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp //设置远端mysql在发生切换时binlog的保存位置
repl_password=123456 //设置复制用户的密码
repl_user=repl //设置复制环境中的复制用户名
report_script=/usr/local/bin/send_report //设置发生切换后发送的报警的脚本
secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.2.128 --master_port=3306
//一旦MHA到server02的监控之间出现问题,MHA Manager将会尝试从server03登录到server02
shutdown_script="" //设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用)
ssh_user=root //设置ssh的登录用户名
ssh_port=12570 //设置ssh的登录端口。默认是22
[server1]
hostname=192.168.2.128
port=3306
[server2]
hostname=192.168.2.129
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=192.168.2.130
port=3306
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
注意:
##master_ip_failover_script= /usr/local/bin/master_ip_failover
MHA的Failover两种方式:一种是虚拟IP地址,一种是全局配置文件。MHA并没有限定使用哪一种方式,而是让用户自己选择,虚拟IP地址的方式会牵扯到其它的软件,比如keepalive软件,而且还要修改脚本
master_ip_failover。
所以先暂时注释master_ip_failover_script= /usr/local/bin/master_ip_failover这个选项。后面引入keepalived后和修改该脚本以后再开启该选项
二、设置relay log的清除方式(在每个slave节点上):
2.1三个节点服务器本地hosts解析
在slave master03 192.168.2.129操作:
[root@server03 ~]# mysql -uroot -p123456 -e "set global relay_log_purge=0"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@server03 ~]#
在slave master04 192.168.2.130操作:
[root@server03 ~]# mysql -uroot -p123456 -e "set global relay_log_purge=0"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@server03 ~]#
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
注意:
MHA在发生切换的过程中,从库的恢复过程中依赖于relay log的相关信息,所以这里要将relay log的自动清除设置为OFF,采用手动清除relay log的方式。在默认情况下,从服务器上的中继日志会在SQL线程执行完毕后被自动删除。但是在MHA环境中,这些中继日志在恢复其他从服务器时可能会被用到,因此需要禁用中继日志的自动删除功能。定期清除中继日志需要考虑到复制延时的问题。在ext3的文件系统下,删除大的文件需要一定的时间,会导致严重的复制延时。为了避免复制延时,需要暂时为中继日志创建硬链接,因为在linux系统中通过硬链接删除大文件速度会很快。(在mysql数据库中,删除大表时,通常也采用建立硬链接的方式)
2.2设置定期清理relay脚本(两台slave服务器):
在slave sever03 192.168.2.129操作:
[root@server03 ~]# cat /data/scripts/purge_relay_log.sh
#!/bin/bash
user=root
passwd=123456
port=3306
log_dir='/data/masterha/log'
work_dir='/data'
purge='/usr/local/bin/purge_relay_logs'
if [ ! -d $log_dir ]
then
mkdir $log_dir -p
fi
$purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
在slave server04 192.168.2.130操作跟上面是一样的,这里不演示了。
脚本中参数介绍:
--user mysql //用户名
--password mysql //密码
--port //端口号
--workdir //指定创建relay log的硬链接的位置,默认是/var/tmp,由于系统不同分区创建硬链接文件会失败,故需要执行硬链接具体位置,成功执行脚本后,硬链接的中继日志文件被删除
--disable_relay_log_purge //默认情况下,如果relay_log_purge=1,脚本会什么都不清理,自动退出,通过设定这个参数,当relay_log_purge=1的情况下会将relay_log_purge设置为0。清理relay log之后,最后将参数设置为OFF。
- 1.
- 2.
- 3.
- 4.
- 5.
purge_relay_logs脚本删除中继日志不会阻塞SQL线程。下面我们手动执行看看什么情况:
[root@server03 ~]# purge_relay_logs --user=root --password=123456 --port=3306 -disable_relay_log_purge --workdir=/data/
2018-07-01 11:53:16: purge_relay_logs script started.
Found relay_log.info: /data/mysql/relay-log.info
Opening /data/mysql/logs/relay-log/relay-bin.000001 ..
Opening /data/mysql/logs/relay-log/relay-bin.000002 ..
Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
2018-07-01 11:53:20: All relay log purging operations succeeded.
[root@server03 ~]#
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
三.主从failover脚本
[root@server04 ~]# cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
#Copyright (C) 2011 DeNA Co.,Ltd.
#This program is free software; you can redistribute it and/or modify
#t under the terms of the GNU General Public License as published by
#the Free Software Foundation; either version 2 of the License, or
#(at your option) any later version.
#This program is distributed in the hope that it will be useful,
#but WITHOUT ANY WARRANTY; without even the implied warranty of
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
#GNU General Public License for more details.
#You should have received a copy of the GNU General Public License
#along with this program; if not, write to the Free Software
#Foundation, Inc.,
#51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
##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;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
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,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
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 {
#updating global catalog, etc
$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 {
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, $new_master_password, 1 );
##Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print "Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
##Creating an app user on the new master
print "Creating app user on the new master..\n";
FIXME_xxx_create_user( $new_master_handler->{dbh} );
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
##Update master ip on the catalog database, etc FIXME_xxx;
$exit_code = 0;
};
if ($@) {
warn $@;
#If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
#do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
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";
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
脚本添加执行权限:
[root@server04 ~]# chmod +x /usr/local/bin/master_ip_failover
**提示:
由于篇幅有限。本博文示到此处,SSH免密码登录以及测试数据库同步会写到下篇博文,请继续关注