Mha+Keepalived高可用搭建
MHA工作原理
主库实例挂掉但是ssh还能连接
1、监控到主库宕机，选择一个新的主，被选择的新主会取消从库的角色（ reset slave）
选择标准：
一是根据其他从库的binlog日志的位置选择最新的从库作为新的主库
二是如果设置了半同步从库，直接选择半同从库作为新的主库
2、从库通过MHA自带的脚本程序，通过ssh向主库索取缺失部分的binlog
3、其他从库与新的主库从新构建主从，继续提供服务
4、如果由vip机制，将VIP从原来的主库漂移到新的主库，让应用无感知
主节点服务器宕机（ssh已经连接不上了）
1、监控到主机宕机后，尝试ssh连接，连接失败
2、通过上边所讲的选择标准选择新的主库
3、计算从库之间的relay-log的差异，补偿到新的其他从库
4、其他从库从新与新主构建主从关系，继续提供服务
5、如果由VIP机制，将VIP从原主漂移到新主，让应用无感知
6、如果有binlog server 机制，会继续将binlog server中缺失的事物，补偿到新的主库
在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用
缺点
需要编写脚本或利用第三方工具来实现VIP的配置
MHA启动后只只监控主服务器是否可用，没办法监控从服务器
需要基于SSH免认证登陆配置，存在一定的安全隐患
没有提供从服务器的读负载均衡功能
隐患：
在MHA自动故障切换的过程中，MHA试图从宕掉的主服务器上保存二进制日志，最大程度保证数据的不丢失，但这并不总是可行的。
例如，如果主服务器硬件故障或无法通过SSH访问，MHA没有办法保存二进制日志，只能进行故障转移而丢失了最新数据。
拓：MySQL服务挂了，但是可以从服务器拷贝二进制。但如果硬件宕机或者SSH不能连接，不能获取到最新的binlog日志，如果复制出现延迟，会丢失数据。
使用MySQL的半同步复制，可以大大降低数据丢失的风险。MHA可以和半同步复制结合起来。如果只有一个Slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有Slave服务器上，保持数据一致性。
最新版0.56版本，增加了支持GTID的功能，建议在MySQL5.6及之后版本使用。
Manager工具包情况如下：
- masterha_check_ssh:检查MHA的SSH配置情况。
- masterha_check_repl:检查MySQL复制状况。
- masterha_manager:启动MHA。
- masterha_check_status:检测当前MHA运行状态。
- masterha_master_monitor:检测Master是否宕机。
- masterha_master_switch:控制故障转移（自动或手动）。
- masterha_conf_host:添加或删除配置的server信息。
备注：
尽可能的减少因为主库硬件损坏宕机造成的数据丢失，可以配置版同步。
为了保证数据一致性，MySQL复制中，常常会在Master上使用sync_binlog参数保证binlog持久化，保证数据一致性。但这种方式对磁盘I/O会造成10~20%的影响。但是还有另外一个思路，就是使用MySQL半同步复制来保证数据一致性，MySQL半同步复制是在从服务器的内存中处理数据并进行发聩，虽然也会造成性能影响，但是相对于对Master造成的磁盘I/O的影响来说，反而是个更好的方法。据《高性能MySQL》 第三版中10.9的测试，写入远程的内存（一台从库的反馈）比写入本地的磁盘（写入并刷新）要更快。使用半同步复制相比主在主库上进行强持久化的性能有两倍的改善。
为了避免在更换主节点时同步日志出错(pos模式下更换节点,日志同步会出错),我们开启GTID模式
环境
| 角色 | ip | 服务 |
|---|---|---|
| master | db60 | mysql+keepalived+mhanode |
| slave1 | db61 | mysql+keepalived+mhanode |
| slave2 | db62 | mysql+keepalived+mhanode |
| monitor | db63 | mhamanager |
host文件
192.168.38.63 db63
192.168.38.60 db60
192.168.38.61 db61
192.168.38.62 db62
安装mysql
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm
yum install mysql-community-server -y
systemctl enable mysqld
systemctl daemon-reload
systemctl start mysqld
systemctl status mysqld
grep ‘temporary password’ /var/log/mysqld.log
mysql -uroot -p
mysql配置文件
db60：/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
server-id=1
log-bin=master-log
relay-log=relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
max_connections = 5000#
symbolic-links=0
gtid_mode=ON
enforce-gtid-consistency=true
db61：/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
server-id=2
log-bin=master-log
relay-log=relay-log
relay_log_purge=0
read_only=1
skip_name_resolve=1
innodb_file_per_table=1
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
gtid_mode=ON
enforce-gtid-consistency=true
db62：/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
server-id=3
log-bin=master-log
relay-log=relay-log
relay_log_purge=0
read_only=1
skip_name_resolve=1
innodb_file_per_table=1
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
gtid_mode=ON
enforce-gtid-consistency=true
mysql root权限：
GRANT ALL PRIVILEGES ON . TO ‘root’@‘192.168.38.%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
配置mysql主从：
#三个节点执行：
grant replication slave,replication client on . to ‘repluser’@‘192.168.38.%’ identified by ‘123456’ ;
flush privileges;
#授权MHA管理用户-mhaadmin
grant all on . to ‘mhaadmin’@‘192.168.38.%’ identified by ‘mhapass’ ;
#两个slave执行：
change master to master_host=‘192.168.38.60’,master_user=‘repluser’,master_password=‘123456’,master_log_file=‘master-log.000005’,master_log_pos=154;
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
set global read_only=1; #查看slave IO和slave sql是否都正常
#查看主从复制情况
show grants for ‘repluser’@‘192.168.38%’;
配置半同步复制：
在主库安装semisync_master.so和semisync_slave.so插件
master库执行：
INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
show status like ‘Rpl_semi_sync_master_status’;
show status like ‘Rpl_semi_sync_slave_status’;
slave库执行：
INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
show status like ‘Rpl_semi_sync_slave_status’;
show status like ‘Rpl_semi_sync_slave_status’;
配置keepalived
yum install keepalived -y
db60：
#cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server test@hulu.com
smtp_connect_timeout 30
router_id mysql
vrrp_mcast_group4 224.0.88.88 #组播地址
}
vrrp_script chk_mysqld {
script “/etc/keepalived/check_mysql.sh” #测试脚本
interval 1
weight -5
fall 2
}
vrrp_instance mysql{
state BACKUP
interface ens33 #心跳,网络监控端口
virtual_router_id 51
priority 100 #权重,manager上要略低于100
advert_int 1
nopreempt #不抢占模式，从节点上不必配置此项
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_mysqld
}
virtual_ipaddress {
192.168.38.100/24 dev ens33 #VIP地址
}
}
db61：
db61 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server test@hulu.com
smtp_connect_timeout 30
router_id mysql
vrrp_mcast_group4 224.0.88.88 #组播地址
}
vrrp_script chk_mysqld {
script “/etc/keepalived/check_mysql.sh” #测试脚本
interval 1
weight -5
fall 2
}
vrrp_instance mysql{
state BACKUP
interface ens33 #心跳,网络监控端口
virtual_router_id 51
priority 90 #权重,manager上要略低于100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_mysqld
}
virtual_ipaddress {
192.168.38.100/24 dev ens33 #VIP地址
}
}
检查mysql服务脚本
#cat /etc/keepalived/check_mysql.sh
#!/bin/sh
stat=$(ps -C mysqld --no-header | wc -l)
if [ $stat -eq 0 ];then
systemctl restart keepalived
fi
ssh互信，三台机器都要执行
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.38.60
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.38.61
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.38.62
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.38.63
安装MHA软件
所有节点安装node
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
manager节点上安装mha管理软件
wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
yum install perl-Parallel-ForkManager -y
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
yum install mailx -y
配置MHA软件
manager节点
cd /usr/bin/
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/
cp /usr/bin/masterha_* /usr/local/bin/
mkdir -p /etc/masterha
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=/var/lib/mysql
#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=repluser
secondary_check_script=/usr/local/bin/masterha_secondary_check -s db60 -s db61 --user=root --master_host=db61 --master_ip=192.168.38.61 --master_port=3306
shutdown_script=""
ssh_port=22
ssh_user=root
[server1]
hostname=192.168.38.60
candidate_master=1
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.38.61
port=3306
[server3]
hostname=192.168.38.62
port=3306
在slave上操作：
mysql -uroot -p123456 -e “set global relay_log_purge=0”
主从failover脚本
cat /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
#Copyright © 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
);
my $vip = ‘192.168.38.100’;
my $ssh_start_vip = “systemctl start keepalived”;
my $ssh_stop_vip = "systemctl stop keepalived ";
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 {
print “\n\nIN SCRIPT TEST==== s s h s t o p v i p = = ssh_stop_vip== sshstopvip==ssh_start_vip===\n\n”;
if ( $command eq “stop” || KaTeX parse error: Expected '}', got '#' at position 30: …opssh" ) { #̲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 “Disabling the VIP on old master: $orig_master_host \n”;
&stop_vip();
#updating global catalog, etc
KaTeX parse error: Expected 'EOF', got '}' at position 20: …_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 “Enabling the VIP - $vip on the new master - $new_master_host \n”;
&start_vip();
KaTeX parse error: Expected 'EOF', got '}' at position 24: …e = 0; }̲; if (@) {
warn $@;
#If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “status” ) {
print “Checking the Status of the script… OK \n”;
#do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
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 {
“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”;
}
chmod +x /usr/local/bin/master_ip_failover
MHA相关测试
测试SSH免密码登录
masterha_check_ssh --conf=/etc/masterha/app1.cnf
测试MHA数据库同步
masterha_check_repl --conf=/etc/masterha/app1.cnf
chmod +x /usr/local/bin/masterha_check_repl
启动MHA监控服务
查看MHA Manager监控是否正常：
masterha_check_status --conf=/etc/masterha/app1.cnf
关闭MHA Manage监控
masterha_stop --conf=/etc/masterha/app1.cnf
启动mha数据监控
masterha_check_status --conf=/etc/masterha/app1.cnf
mkdir -p /var/log/masterha/app1/
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 &
ps -ef|grep perl
扫一扫
1113
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
