关于MHA
1.master HA，对主节点进行监控，可实现自动故障转 移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有 三台数据库服务器，一主二从，
即一台充当master，一台充当备用master，另外一台充当从库，如果财大气粗，也
可以用一台专门的服务器来当MHA监控管理服务器
2.MHA工作原理
1 从宕机崩溃的master保存二进制日志事件（binlog events）
2 识别含有最新更新的slave
3 应用差异的中继日志（relay log）到其他的slave
4 应用从master保存的二进制日志事件（binlog events）
5 提升一个slave为新的master
6 使其他的slave连接新的master进行复制
注意：MHA需要基于ssh，key验证登入方法
- MHA软件由两部分组成，Manager工具包和Node工具包，具体的说明如下。
1.Manager工具包主要包括以下几个工具：
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_manger 启动MHA
masterha_check_status 检测当前MHA运行状态
masterha_master_monitor 检测master是否宕机
masterha_master_switch 控制故障转移（自动或者手动）
masterha_conf_host 添加或删除配置的server信息
2.Node工具包（这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs 清除中继日志（不会阻塞SQL线程）
环境准备
| 主机名称 | ip地址 | 作用 |
|---|---|---|
| mysql1 | 10.2.17.60 | slave&MHAmanager |
| mysql2 | 10.2.17.61 | master |
| mysql3 | 10.2.17.62 | slave 备选主 |
这里MySQL采用rpm包安装 版本为5.7.27
依赖环境
yum -y install make gcc-c++ cmake bison-devel ncurses-devel libaio libaio-devel net-tools
mysql2配置文件修改（主库）
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
slow-query-log = On
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
server-id = 1
log-bin = master-log
binlog_format = row
binlog_cache_size = 2M
max_binlog_size = 1000M
log-bin = master-log
#log-bin = /var/lib/mysql
binlog_format = row
binlog_cache_size = 2M
max_binlog_size = 1000M
relay-log = relay-log
skip_name_resolve
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 4
#innodb_additional_mem_pool_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16M
innodb_open_files = 4000
innodb_read_io_threads = 24
innodb_write_io_threads = 24
innodb_thread_concurrency=24
innodb_io_capacity = 2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_max_dirty_pages_pct = 85
innodb_flush_log_at_trx_commit = 2
transaction-isolation = READ-COMMITTED
sync_binlog=20
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables
log-error=/var/log/mysqld.log
mysql1和mysql3（从库）做相应修改 注意：server-id 不相等
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
slow-query-log = On
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables
server-id = 5
relay-log = relay-log
log-bin = master-log
read_only = ON
relay_log_purge = 0
skip_name_resolve
log_slave_updates= 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 4
#innodb_additional_mem_pool_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16M
innodb_open_files = 4000
innodb_read_io_threads = 24
innodb_write_io_threads = 24
innodb_thread_concurrency=24
innodb_io_capacity = 2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_max_dirty_pages_pct = 85
innodb_flush_log_at_trx_commit = 2
transaction-isolation = READ-COMMITTED
sync_binlog=20
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3台 主机做免密登录 （不写了） 做好之后修改配置文件/etc/hosts
vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.2.17.62 mysql3
10.2.17.61 mysql2
10.2.17.60 mysql1
启动3台数据库 做主从
systemctl restart mysqld
创建数据库账号 授权 用于主从复制
mysql> alter user root@localhost identified by '123.com'; #修改密码
mysql> create user 'mysync'@'10.2.17.61' identified by '123.com';
mysql> create user 'mysync'@'10.2.17.62' identified by '123.com';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'mysync'@'10.2.17.60';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'mysync'@'10.2.17.61';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'mysync'@'10.2.17.62';
查看主库状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-log.000044 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库操作
stop slave;
change master to master_host='10.2.17.61', master_port=3306, master_user='mysync',master_password='123.com',master_log_file='master-log.000044', master_log_pos=154;
start slave;
查看主从状态
show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 10.2.17.61
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000044
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-log.000044
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从同步没问题 俩台从库都进行复制
安装MHA 3台全部安装基本环境
yum -y install epel-release
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
yum -y install 'perl(Module::Install)'
node节点操作 安装（10.2.17.60.61 10.2.17.60.62）
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
manager节点安装（10.2.17.60）
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
manager节点操作
创建相关目录
用于存放配置文件 和 日志
mkdir -p /data/mha/
mkdir -p /usr/local/mha
mha配置文件
cat /data/mha/app1.cnf
[server default]
user=root
password=123.com
manager_workdir=/usr/local/mha/
manager_log=/usr/local/mha/manager.log
master_ip_failover_script=/data/mha/master_ip_failover
ssh_user=root
repl_user=mysync
repl_password=123.com
ping_interval=1
master_binlog_dir=/var/lib/mysql/
secondary_check_script =masterha_secondary_check -s 10.2.17.60 -s 10.2.17.62
[server1]
hostname=10.2.17.61
port=3306
master_binlog_dir=/var/lib/mysql/
[server2]
hostname=10.2.17.62
port=3306
master_binlog_dir=/var/lib/mysql/
candidate_master=1
[server3]
hostname=10.2.17.60
port=3306
脚本转移文件
mha自带的脚本转移工具 做部分修改就可直接使用
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it 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
);
#以下四行为修改部分 建立虚拟IP进行故障迁移
my $vip = '10.2.17.63/23';
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,
'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 {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
&stop_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 $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
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 {
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";
}
mha 常用命令(注意：这里的配置文件和目录 按自己需求配置)
1、查看ssh登陆是否成功
masterha_check_ssh --conf=/data/mha/app1.cnf
2、查看复制是否建立好
masterha_check_repl --conf=/data/mha/app1.cnf
3、启动mha
nohup masterha_manager --conf=/data/mha/app1.cnf >/usr/local/mha/manager.log < /dev/null 2>&1 &
当有slave节点宕掉的情况是启动不了的，加上–ignore_fail_on_start即使有节点宕掉也能启动mha
nohup masterha_manager --conf=/data/mha/app1.cnf --ignore_fail_on_start > /usr/local/mha/manager.log < /dev/null 2>&1 &
4、检查启动的状态
masterha_check_status --conf=/data/mha/app1.cnf
5、停止mha
masterha_stop --conf=/data/mha/app1.cnf
6、failover后下次重启
每次failover切换后会在管理目录生成文件app1.failover.complete ，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉。
rm -rf /usr/local/mha/app1.failover.complete
也可以加上参数–ignore_last_failover
扫一扫
1622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
