Mysql MHA搭建

李有乾

已于 2023-02-20 18:36:49 修改

阅读量821

点赞数

分类专栏：笔记 DB 文章标签： mysql 数据库

于 2023-02-17 10:19:25 首次发布

本文链接：https://blog.csdn.net/xyjy11/article/details/129079260

版权

笔记同时被 2 个专栏收录

76 篇文章 2 订阅

订阅专栏

2 篇文章 0 订阅

订阅专栏

机器配置

安装Docker

Docker安装和启动Mysql8.0.26

问题1 MHA验证主从复制报错Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

问题2 MHA验证主从复制报错Error happened on checking configurations. Redundantt /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm line 201.

机器配置

192.168.146.107	mysql主	node1	虚拟IP为192.168.146.106
192.168.146.108	mysql从	node2
192.168.146.109	mysql从	node3 + manager

系统信息：

为107设置VIP :

安装Docker

安装需要的软件包：yum-utils、device-mapper-persistent-data和lvm2。

yum install -y yum-utils \
  device-mapper-persistent-data \
  lvm2

设置仓库

使用国内镜像，比如阿里云源镜像

yum-config-manager \
    --add-repo \
    https://download.docker.com/linux/centos/docker-ce.repo

安装docker engine，包括docker-ce、docker-ce-cli和containerd.io

yum install docker-ce docker-ce-cli containerd.io

启动docker

systemctl start docker

验证安装是否ok

docker run hello-world

以上操作在三台机器上都执行

Docker安装和启动Mysql8.0.26

拉取镜像

docker pull mysql:8.0.26

创建文件夹用于挂载，放置mysql配置文件和数据

cd /home; mkdir -p mysql && cd $_ && mkdir {conf,data,mysql-files,mysqld,log}
chmod 777 /home/mysql/mysqld

进入conf文件夹创建mysql的配置文件my.cnf ，内容如下

[mysqld]
server_id=306
port=3306
default_authentication_plugin=mysql_native_password
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
symbolic-links=0
explicit_defaults_for_timestamp=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog_cache_size=1M
binlog_format=mixed
binlog_expire_logs_seconds=259200
max_binlog_size=500M
slave_skip_errors=1062
log_slave_updates=1
lower_case_table_names=1
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema
sync_binlog=0
bind-address=0.0.0.0
skip-name-resolve
skip_ssl

auto_increment_offset=1
auto_increment_increment=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true

max_connections=1000
innodb-force-recovery=0

gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=TABLE
relay_log_purge=0
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
innodb_flush_log_at_trx_commit=0
relay_log_recovery=1

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

上述操作在三台机器都执行， 但要注意，三个my.cnf的server_id需要不同

三台服务器启动mysql

docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=ws-123456 \
-v /home/mysql/data:/var/lib/mysql:rw \
-v /home/mysql/mysql-files:/var/lib/mysql-files:rw \
-v /home/mysql/mysqld:/var/run/mysqld:rw \
-v /home/mysql/log:/var/log/mysql:rw \
-v /home/mysql/conf:/etc/mysql:rw \
--name mysql --privileged=true \
-d mysql:8.0.26

进入容器，创建用户

docker exec -it 6f94e362fe6d sh

执行如下sql

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'ws-123456';
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'repl'@'%';
flush privileges;

CREATE USER IF NOT EXISTS 'mha'@'%' IDENTIFIED BY 'ws-123456';
ALTER USER 'mha'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
GRANT all  ON *.* TO 'mha'@'%';
flush privileges;

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY 'ws-123456';
grant all on *.* to 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
flush privileges;

搭建Mysql一主二从

连接108和109两台从机，执行如下命令，表示107的mysql作为主，启动主从复制

change master to 
master_host='192.168.146.107',
master_port=3306, 
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;   

start slave;
show slave status;

测试主从复制是否成功

连接107节点，执行如下sql

CREATE DATABASE IF NOT EXISTS `test` default character set utf8 collate utf8_general_ci;

查看108和109是否已经同步

设置三台机器免密访问

在107节点依次执行如下命令

ssh-keygen -t rsa
ssh-copy-id 192.168.146.108
ssh-copy-id 192.168.146.109

同样在108和109节点依次执行如下命令

108：
ssh-keygen -t rsa
ssh-copy-id 192.168.146.107
ssh-copy-id 192.168.146.109

109：
ssh-keygen -t rsa
ssh-copy-id 192.168.146.107
ssh-copy-id 192.168.146.108

如下图所示

108：

109：

测试免密登录

在107执行如下

ssh 192.168.146.108

安装MHA

安装依赖

所有服务器都需要安装依赖包

yum -y install epel-release
yum -y install perl-DBD-MySQL perl-DBI

#安装epel-release执行报错Error: Unable to find a match epel，百度之后说是少源，执行下边命令后正常
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

yum -y install ncftp

#执行如果报错Error: Unable to find a match ncftp，执行一下yum -y update，然后再次安装，如果还报错再手动编译安装ncftp
cd /home
wget https://www.ncftp.com/downloads/ncftp/ncftp-3.2.6-src.tar.gz
tar xvf ncftp-3.2.6-src.tar.gz
cd ncftp-3.2.6
./configure && make && make install

安装mha

所有服务器都需要安装

cd /home
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

创建mha工作目录

所有服务器执行

mkdir /home/mysql_mha

安装mha监控manager

正常来说应该安装在一个单独的节点，此处为了节约一个节点，就安装在192.168.146.109上

yum -y install epel-release
yum -y install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch perl-DBD-MySQL

#执行如果报错Error: Unable to find a match ，执行一下yum -y update，然后再次安装
wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

在manager管理机器上配置管理节点

mkdir /etc/mha

vim /etc/mha/mysql_mha.cnf
#内容如下
[server default]
#mha访问数据库的账号与密码
user=mha
password=ws-123456
#指定mha的工作目录
manager_workdir=/home/mysql_mha
#指定管理日志路径
manager_log=/home/mysql_mha/manager.log
#指定mha在远程节点上的工作目录
remote_workdir=/home/mysql_mha
#可以使用ssh登入的用户
ssh_user=root
ssh_port=22
#指定主从复制的mysq用户和密码
repl_user=repl
repl_password=ws-123456
#指定检测间隔时间
ping_interval=1
#指定master节点存放binlog的日志文件的目录
master_binlog_dir=/home/mysql/data
#指定一个脚本，该脚本实现了在主从切换之后，将虚拟ip漂移到新的master上
master_ip_failover_script=/usr/bin/master_ip_failover
#指定用于二次检查节点状态的节点
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.146.107 -s 192.168.146.108 -s 192.168.146.109
#配置集群中的节点
[server1]
hostname=192.168.146.107
#指定该节点可以参与master选举
candidate_master=1

[server2]
hostname=192.168.146.108
candidate_master=1

[server3]
hostname=192.168.146.109
##指定该节点不参与master选举 该节点也是监控节点，所以关闭master候选
no_master=1

编写配置文件中的/usr/bin/master_ip_failover脚本

vim /usr/bin/master_ip_failover

#内容如下
#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
    $command, $orig_master_host, $orig_master_ip,$ssh_user,
    $orig_master_port, $new_master_host, $new_master_ip,$new_master_port,
    $orig_master_ssh_port,$new_master_ssh_port,$new_master_user,$new_master_password
);

# 这里定义的虚拟IP可以根据实际情况进行修改
my $vip = '192.168.146.106/24';
my $key = '1';

# 这里的网卡名称 “ens160” 需要根据你机器的网卡名称进行修改
my $ssh_start_vip = "/sbin/ifconfig ens160:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens160:$key down";
my $ssh_Bcast_arp= "/sbin/arping -I ens160 -c 3 -A 192.168.146.106";

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,
    'orig_master_ssh_port=i' => \$orig_master_ssh_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
    'new_master_ssh_port' => \$new_master_ssh_port,
    'new_master_user' => \$new_master_user,
    'new_master_password' => \$new_master_password

);

exit &main();

sub main {
    $ssh_user = defined $ssh_user ? $ssh_user : 'root';
    print "\n\nIN SCRIPT TEST====$ssh_user|$ssh_stop_vip==$ssh_user|$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();
	    &start_arp();
            $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() {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub start_arp() {
    `ssh $ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
}
sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --ssh_user=user --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 a+x /usr/bin/master_ip_failover

验证ssh互信配置

masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf

验证mysql主从同步

masterha_check_repl --conf=/etc/mha/mysql_mha.cnf

报错如下

Redundantt /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm line 201.

解决方法见问题记录2，解决之后重新验证成功

启动MHA manager

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf >/dev/null 2>/dev/null &
ps -ef|grep mha
#检测状态
masterha_check_status --conf=/etc/mha/mysql_mha.cnf 

#关闭mha
masterha_stop --conf=/etc/mha/mysql_mha.cnf

查看mha监控日志

tail -f /home/mysql_mha/manager.log

测试主从切换

停止master

mha管理节点日志如下

1）判断master下线，准备启动故障转移

2）校验slave配置，移除VIP设置

3）从slave中选择一个master

4）获取新master节点的binlog位点，重新设置VIP

5）将剩余从节点关联到新的master，开始复制，将新master节点slave info 清除

6）本次故障转移的总结报告

注：

1、宕机的原master重新上线后需要手动执行change master to建立主从关系

2、MHA进行一次故障切换之后，进程会退出

搭建问题记录

问题1 MHA验证主从复制报错Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

原因是配置的mha用户需要有root权限（grant all）

问题2 MHA验证主从复制报错Error happened on checking configurations. Redundantt /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm line 201.

百度说是代码里解析mysql版本有问题，修改代码

vi /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm

#找到如下两个方法parse_mysql_version和parse_mysql_major_version，修改代码

#修改前：
#sub parse_mysql_version($) {
#  my $str = shift;
#  my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
#  return $result;
#}
#sub parse_mysql_major_version($) {
#  my $str = shift;
#  my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g );
#  return $result;
#}

#修改后：
sub parse_mysql_version($) {
  my $str = shift;
 ($str) =  $str =~ m/^[^-]*/g;
  my $result = sprintf( '%03d%03d%03d', $str =~ m/(\d+)/g );
  return $result;
}

sub parse_mysql_major_version($) {
  my $str = shift;
  $str =~ /(\d+)\.(\d+)/;
  my $strmajor = "$1.$2";
  my $result = sprintf( '%03d%03d', $strmajor =~ m/(\d+)/g );
  return $result;
}