mysql mha环境构建

作者: 泥人冷风 | 来源:发表于2020-12-27 11:58 被阅读0次

mysql mha环境构建
Zabbix集群升级
13 MySQL MHA集群
MySQL MHA+GTID
mysql(十三)
MHA +MYSQL 高可用主从搭建方案
MySQL MHA搭建
8. MySQL高可用-MHA
SUSE12 MySQL高可用架构 MHA环境部署
MySQL MHA

1. 环境准备

1.1 系统软件列表

No	主机名	作用	IP
1	mha1	master	192.168.8.211
2	mha2	slave	192.168.8.212
3	mha3	slave	192.168.8.213
4	mha1	监控	192.168.8.211
5	mha1	监控用户	mha
6	*	复制用户	repli

1.2 hostname

hostnamectl set-hostname mha1 --static
hostnamectl set-hostname mha2 --static
hostnamectl set-hostname mha3 --static

1.3 网络设置

vi /etc/sysconfig/network-scripts/ifcfg-ens33
BOOTPROTO="static" #dhcp改为static 
ONBOOT="yes" #开机启用本配置
IPADDR=192.168.8.211 #静态IP 192.168.8.212 /192.168.8.213同
GATEWAY=192.168.8.2 #默认网关
NETMASK=255.255.255.0 #子网掩码
DNS1=114.114.114.114 #DNS 配置
DNS2=8.8.8.8 #DNS 配置

1.4 防火墙开放

firewall-cmd --zone=public --list-ports//查看所有开放端口状态
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --reload

2. mysql配置

2.1 mysql安装

yum install wget
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"
yum install -y  mysql-community-server

2.2 查看MySQL运行状态

systemctl status mysqld.service

2.3 查找初始密码并修改

grep "password" /var/log/mysqld.log
mysql -u root -p
Enter password:

mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=6;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

2.4 mysql 配置

mha1

vi /etc/my.cnf
[mysqld]
log_bin
server-id=1
gtid_mode=on
enforce_gtid_consistency=on

mha2

vi /etc/my.cnf
[mysqld]
log_bin
server-id=2
gtid_mode=on
enforce_gtid_consistency=on

mha3

vi /etc/my.cnf
[mysqld]
log_bin
server-id=3
gtid_mode=on
enforce_gtid_consistency=on

2.5 重启mysql

systemctl restart mysqld

2.6 mha1登录mysql并创建主从复制用用户(repli)

mysql -uroot -p
mysql>use mysql;
mysql>select user,host from user;
mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=6;
mysql> create user repli@'%' identified by '123456';
mysql> grant all privileges on *.* to repli@'%' identified by '123456';

2.7 然后在mha2和mha3机器上，做主从复制

mysql -uroot -p
mysql> change master to master_host='192.168.8.211',master_user='repli',master_password='123456',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;

2.8 mha1登录mysql并创建监控用用户(mha)

mysql -uroot -p
mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=6;
mysql> create user mha@'%' identified by '123456';
mysql> grant all privileges on *.* to mha@'%' identified by '123456';

3 免密码登录(*)

vi /etc/hosts

192.168.8.211 mha1
192.168.8.212 mha2
192.168.8.213 mha3

ssh-keygen -t rsa
//一路确认下去

.ssh]# ssh-copy-id -i id_rsa.pub root@mha1
.ssh]# ssh-copy-id -i id_rsa.pub root@mha2
.ssh]# ssh-copy-id -i id_rsa.pub root@mha3

4 安装MHA

4.1 安装epel源(*)

yum -y install epel-release
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm

4.2 安装node节点(*)

yum install -y perl-DBD-MySQL ncftp perl-DBI.x86

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

4.3 安装manager节点(mha1)

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager


wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm


rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

5 故障切换脚本（mha1）

vi /usr/local/bin/master_ip_failover
chmod 777 /usr/local/bin/master_ip_failover

vi /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
);
#############################添加内容部分#########################################
# $vip、$brdc、$ifdev(网卡)需要修改
my $vip = '192.168.8.66';
my $brdc = '192.168.8.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
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 \"`;
}
# 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";
}

6 MHA配置文件

6.1 MHA配置文件—mha.conf(mha1)

mkdir -p /var/log/masterha

mkdir -p /etc/masterha

vi /etc/masterha/mha.conf
##MHA配置
[server default]
# 监控用户
user=mha
# 监控用户密码
password=123456
# ssh登录用户
ssh_user=root
# 复制用户
repl_user=repli
# 复制用户密码
repl_password=123456
# 工作目录
manager_workdir=/etc/masterha
# 日志目录
manager_log=/var/log/masterha/manager.log
master_binlog_dir=/var/lib/mysql
remote_workdir=/data/log/masterha
secondary_check_script=masterha_secondary_check -s 192.168.8.211 -s 192.168.8.212 -s 192.168.8.213
ping_interval=3
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/script/masterha/send_master_failover_mail
[server1]
hostname=192.168.8.211
candidate_master=1

[server2]
hostname=192.168.8.212
candidate_master=1

[server3]
hostname=192.168.8.213
candidate_master=1

6.1 验证机制(mha1)

masterha_check_ssh --conf /etc/masterha/mha.conf
masterha_check_repl --conf /etc/masterha/mha.conf

6.2 MHA—启动(mha1)

nohup masterha_manager --conf=/etc/masterha/mha.conf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/manager.log 2>&1 &

6.3 MHA---观察状态

masterha_check_status --conf /etc/masterha/mha.conf

6.4 手动加个VIP 66(mha1)

ifconfig ens33:1 192.168.8.66/24
[root@mha1 .ssh]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f3:a6:8b brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.211/24 brd 192.168.8.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.8.66/24 brd 192.168.8.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fd15:4ba5:5a2b:1008:c794:c9e0:1526:a08b/64 scope global noprefixroute dynamic
       valid_lft 86373sec preferred_lft 14373sec
    inet6 fe80::a82a:9fcf:2247:7771/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

6.5 演示MHA的效果

[root@mha1 .ssh]#systemctl stop mysqld
[root@mha2 .ssh]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:36:d4:4e brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.212/24 brd 192.168.8.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.8.66/24 brd 192.168.8.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe36:d44e/64 scope link
       valid_lft forever preferred_lft forever

mysql mha环境构建
1. 环境准备 1.1 系统软件列表 No主机名作用IP1mha1master192.168.8.2112mha2...
Zabbix集群升级
一、###Zabbix集群构建： Mysql的MHA架构清单：主机名IP主/从MHA高可用软件读写分离软件db0...
13 MySQL MHA集群
MySQL MHA 集群 [toc] MHA 简介 MHA ( Master High Availability ...
MySQL MHA+GTID
MySQL环境准备主机名wanIPlanIP角色db0110.0.0.51172.16.1.51主库,MHA客户...
mysql(十三)
MySQL环境准备主机名wanIPlanIP角色db0110.0.0.51172.16.1.51主库,MHA客户...
MHA +MYSQL 高可用主从搭建方案
MHA +MYSQL 高可用主从搭建方案一、环境及软件版本服务器：centos 7 4台 mysql 版本...
MySQL MHA搭建
MySQL MHA 架构介绍：MHA由两部分组成MHA Manager（管理节点）和MHA Node（数据节点），...
8. MySQL高可用-MHA
9 MySQL高可用 9.1 MHA 9.1.1 MHA介绍 MHA会对主节点进行监控, 可实现自动故障转移至其他...
SUSE12 MySQL高可用架构 MHA环境部署
一、MHA介绍 MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架...
MySQL MHA
MySQL-day16 MHA切换优先级在数据量相同的情况下，MHA会往哪里切换？ MHA启动命令 MHA集群恢...