mariadb10.5 gtid基础上的MHA

mariadb10.5 gtid基础上的MHA

参考资料：
https://www.cnblogs.com/bigdevilking/p/9477699.html
https://www.jianshu.com/p/6bcf1b14f9f9
https://blog.csdn.net/xxj123go/article/details/72828920

 

# 1、安装依赖包

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

ln -s /usr/local/mysql/bin/mysql /usr/local/bin/
# 设置时间同步
cat>>/var/spool/cron/root<<"EOF"
*/10 * * * * /usr/sbin/ntpdate asia.pool.ntp.org >/dev/null 
EOF

/usr/sbin/ntpdate asia.pool.ntp.org
# 每个节点执行
cat >>/etc/hosts <<EOF
192.168.142.236  db236
192.168.142.237  db237
192.168.142.238  db238
EOF

# 2、配置ssh互信(现在的架构是：
# 192.168.142.236 从库1
# 192.168.142.237 主库
# 192.168.142.238 从库2 # 这个作为管理节点
# )

复制代码
# manager生成密钥对 , 把公钥发到下面
[root@db238 opt]# ssh-keygen -t rsa # 三台机器都要操作，一路回车
[root@db238 opt]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized.keys
[root@db238 opt]# rm -rf  ~/.ssh/kown_hosts
[root@db238 opt]# chmod  600 ~/.ssh/authorized.keys

# 只要在一个节点执行即可，这里在 192.168.142.238上执行
[root@db238 opt]# ssh 192.168.142.236 cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys
[root@db238 opt]# ssh 192.168.142.237 cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys

# 分发整合后的文件到其它节点
scp ~/.ssh/authorized_keys root@192.168.142.236:~/.ssh/
scp ~/.ssh/authorized_keys root@192.168.142.237:~/.ssh/

# 测试时，第一次，需要输入密码，之后就不需要输入密码了。
ssh root@192.168.142.236
ssh root@192.168.142.237
ssh root@192.168.142.238
复制代码

# 3、下载MHA组件

复制代码
cd /opt/
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

wget https://raw.githubusercontent.com/linyue515/mysql-master-ha/master/mha4mysql-node-0.57-0.el7.noarch.rpm 
wget https://raw.githubusercontent.com/linyue515/mysql-master-ha/master/mha4mysql-manager-0.57-0.el7.noarch.rpm


# 只在manager节点上安装
# 在manager节点和node节点中都需要安装mha node
# 这里建议都安装一下，反正没坏处
scp /opt/mha4mysql-* root@192.168.142.236:/opt/
scp /opt/mha4mysql-* root@192.168.142.237:/opt/

yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
复制代码

# 4、MHA manager配置

复制代码
mkdir -p /etc/masterha
mkdir -p /var/log/masterha
cat > /etc/masterha/app1.conf<<"EOF"
[server default]
#mha manager 工作目录
manager_workdir = /var/log/masterha/app1
manager_log = /var/log/masterha/app1/app1.log 
remote_workdir = /var/log/masterha/app1

[server1]
hostname=192.168.142.236
master_binlog_dir = /data/mysql/logs/
candidate_master = 1
check_repl_delay = 0       

[server2]
hostname=192.168.142.237
master_binlog_dir=/data/mysql/logs/
candidate_master=1
check_repl_delay=0

[server3]
hostname=192.168.142.238
master_binlog_dir=/data/mysql/logs/ 
candidate_master=0
check_repl_delay=0
EOF

# 需要提前创建用户： grant all privileges on *.* to 'mhauser'@'%' identified by 'mhauserpwd';
# 全局配置文件
cat > /etc/masterha/masterha_defaults.conf<<"EOF"
[server default]
#MySQL的用户和密码
user=mhauser
password=mhauserpwd

#系统ssh用户
ssh_user=root
ssh_port=22
log_level=info

#复制用户
repl_user=repl
repl_password=Oi8qcQXMnBvh2vrU

#监控
ping_type=select
ping_interval=3 
#shutdown_script=""

#切换调用的脚本
master_ip_failover_script=/etc/masterha/master_ip_failover
master_ip_online_change_script=/etc/masterha/master_ip_online_change
#report_script= /etc/masterha/app1/send_report    #master failover时执行

secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.142.236 -s 192.168.142.237 -s 192.168.142.238

EOF
复制代码
复制代码
# vip配置
cat >/etc/masterha/master_ip_failover<<"EOF"
#!/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 
); 
 
my $vip = '192.168.142.239/24'; 
my $key = '0'; 
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, 
); 
 
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 \"`; 
} 
sub stop_vip() { 
     return 0  unless  ($ssh_user); 
    `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"; 
}
EOF
复制代码
复制代码
# 准备master_ip_online_change脚本
cat >/etc/masterha/master_ip_online_change<<"EOF"
#!/usr/bin/env perl 
use strict; 
use warnings FATAL =>'all'; 
 
use Getopt::Long; 
 
my $vip = '192.168.142.239/24';  # Virtual IP 
my $key = "0"; 
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; 
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; 
my $exit_code = 0; 
 
my ( 
  $command,              $orig_master_is_new_slave, $orig_master_host, 
  $orig_master_ip,       $orig_master_port,         $orig_master_user, 
  $orig_master_password, $orig_master_ssh_user,     $new_master_host, 
  $new_master_ip,        $new_master_port,          $new_master_user, 
  $new_master_password,  $new_master_ssh_user, 
); 
GetOptions( 
  'command=s'                => \$command, 
  'orig_master_is_new_slave' => \$orig_master_is_new_slave, 
  'orig_master_host=s'       => \$orig_master_host, 
  'orig_master_ip=s'         => \$orig_master_ip, 
  'orig_master_port=i'       => \$orig_master_port, 
  'orig_master_user=s'       => \$orig_master_user, 
  'orig_master_password=s'   => \$orig_master_password, 
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user, 
  '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, 
  'new_master_ssh_user=s'    => \$new_master_ssh_user, 
); 
 
 
exit &main(); 
 
sub main { 
 
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; 
 
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 { 
            print "\n\n\n***************************************************************\n"; 
            print "Disabling the VIP - $vip on old master: $orig_master_host\n"; 
            print "***************************************************************\n\n\n\n"; 
&stop_vip(); 
            $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 "\n\n\n***************************************************************\n"; 
            print "Enabling the VIP - $vip on new master: $new_master_host \n"; 
            print "***************************************************************\n\n\n\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"; 
        `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`; 
        exit 0; 
} 
else { 
&usage(); 
        exit 1; 
} 
} 
 
# A simple system call that enable the VIP on the new master 
sub start_vip() { 
`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`; 
} 
# A simple system call that disable the VIP on the old_master 
sub stop_vip() { 
`ssh $orig_master_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=po 
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n"; 
 
}
EOF
复制代码
chmod +x /etc/masterha/master_ip_failover
chmod +x /etc/masterha/master_ip_online_change
# MHA集群管理
# masterha_check_ssh --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf   
# masterha_check_repl --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf 
# masterha_check_status --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf
## masterha_conf_host --command=add --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.cnf --hostname=10.69.213.55 --params="port=3306;master_binlog_dir="/gomeo2o/logs/mysql/";ssh_user=root;ssh_port=22;log_level=info"
## masterha_master_monitor --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf
# nohup masterha_manager --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf >/tmp/mha_manager.log 2>&1 &
# masterha_stop --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf
复制代码
--- 需要开启定期清除relay日志
SET GLOBAL relay_log_purge=0;
GRANT ALL PRIVILEGES ON *.*  TO 'purge_relay_u'@'127.0.0.1' IDENTIFIED BY '2123456';
FLUSH PRIVILEGES;


mkdir -p /var/log/masterha/mysql_relay_logs_bak
touch /var/log/masterha/purge_relay_logs.log
chown -R mysql.mysql /var/log/masterha/purge_relay_logs.log

# 将下面的添加至计划任务中
10 02 */3 * * /usr/bin/purge_relay_logs --user=purge_repay_u--password=2123456 --host=127.0.0.1  --port=3306 --workdir=/var/log/masterha/mysql_relay_logs_bak -disable_relay_log_purge >>/var/log/masterha/app1/purge_relay_logs.log &
复制代码

 

复制代码
##########################################################################################
# 1、masterha_check_ssh
[root@db238 masterha]# masterha_check_ssh --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf

# 2、
# 需要注释掉my.cnf中的default-character-set=utf8mb4
[root@db238 opt]# masterha_check_repl --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf 

# 3、启动mha服务
[root@db238 masterha]# nohup masterha_manager --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf >/tmp/mha_manager.log 2>&1 &

# 启动后，主服务器（节点一）会自动分配相应的虚拟ip，如果不分配就按照下面方式手动分配

ifconfig ens33:0 192.168.142.239/24

# 4、检查运行状态
[root@db238 ~]# masterha_check_status --global_conf=/etc/masterha/masterha_defaults.conf --conf=/etc/masterha/app1.conf
app1 (pid:14585) is running(0:PING_OK), master:192.168.142.237
[root@db238 ~]# 

# 5、检查切换的日志
[root@db238 opt]# tail -f /var/log/masterha/app1/app1.log 

#################################################
cat >/etc/masterha/init_vip.sh<<"EOF"
vip="192.168.142.239/32"
/sbin/ip addr add $vip dev ens33
EOF


cat >/etc/masterha/drop_vip.sh<<"EOF"
vip="192.168.142.239/32"
/sbin/ip addr del $vip dev ens33
EOF

chmod +x /etc/masterha/init_vip.sh
chmod +x /etc/masterha/drop_vip.sh

#################################################
复制代码

 

posted @   davie2020  阅读(219)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理 了，记录一下
点击右上角即可分享
微信分享提示