MySQL-MHA搭建笔记day15

最新推荐文章于 2024-05-02 17:32:35 发布

追梦娃

最新推荐文章于 2024-05-02 17:32:35 发布

阅读量156

点赞数

本文链接：https://blog.csdn.net/weixin_44922815/article/details/102805612

版权

一、环境三台服务器

ip db01 10.0.0.51
ip db02 10.0.0.52
ip db03 10.0.0.53
把MHA文件安装包分别传入三台服务器中并进行解压。

MHA搭建

配置关键程序软连接

10.0.0.51/52/53:

ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
 ln -s /application/mysql/bin/mysql /usr/bin/mysql

配置各节点互信(SSH协议)

在db01上创建秘钥:

rm -rf /root/.ssh
ssh-keygen #生成秘钥 一直回车
cd /root/.ssh
mv id_rsa.pub authorized_keys
scp -r /root/.ssh 10.0.0.52:/root 
scp -r /root/.ssh 10.0.0.53:/root

第二种方法在各节点都分发秘钥

cat /server/scripts/ssh.sh #!/bin/bash
rm -rf ~/.ssh/*
#make key pair
ssh-keygen -t dsa -f ~/.ssh/id_dsa -P '' #fenfa public key
for ip in 51 52 53
do
sshpass -p123456 ssh-copy-id -o StrictHostKeyChecking=no 10.0.0.$ip; done

各节点验证

db01:
ssh 10.0.0.51 date ssh 10.0.0.52 date ssh 10.0.0.53 date db02:
ssh 10.0.0.51 date ssh 10.0.0.52 date ssh 10.0.0.53 date db03:
ssh 10.0.0.51 date ssh 10.0.0.52 date ssh 10.0.0.53 date

二、安装软件

下载mha软件
mha官网:https://code.google.com/archive/p/mysql-master-ha/ github下载地址:https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

所有节点安装Node软件依赖包

yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

在db01主库中创建mha需要的用户

grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

Manager软件安装(db03)

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

三、配置文件准备(db03)

创建配置文件目录

mkdir -p /etc/mha

创建日志目录

mkdir -p /var/log/mha/app1

编辑mha配置文件

vim /etc/mha/app1.cnf
[server default] manager_log=/var/log/mha/app1/manager manager_workdir=/var/log/mha/app1 master_binlog_dir=/data/binlog user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306

四、状态检查

互信检查
masterha_check_ssh --conf=/etc/mha/app1.cnf

Fri Apr 19 16:39:34 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Apr 19 16:39:34 2019 from /etc/mha/app1.cnf..
Fri Apr 19 16:39:34 2019 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22).. Fri Apr 19 16:39:35 2019 - [debug] ok.
Fri Apr 19 16:39:36 2019 - [debug]
Fri Apr 19 16:39:35 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22).. Fri Apr 19 16:39:35 2019 - [debug] ok.
2019
- [info] Reading application default configuration - [info] Reading server configuration from
- [info] Starting SSH connection tests.. - [debug]
- [debug] Connecting via SSH from
Fri Apr 19 16:39:34
/etc/mha/app1.cnf..
Fri Apr 19 16:39:34
Fri Apr 19 16:39:35
Fri Apr 19 16:39:34
root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22).. Fri Apr 19 16:39:34 2019 - [debug] ok.
Fri Apr 19 16:39:35 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22).. Fri Apr 19 16:39:35 2019 - [debug] ok.
Fri Apr 19 16:39:37 2019 - [debug]
Fri Apr 19 16:39:35 2019 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22).. Fri Apr 19 16:39:35 2019 - [debug] ok.
Fri Apr 19 16:39:35 2019 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22).. Fri Apr 19 16:39:36 2019 - [debug] ok.
Fri Apr 19 16:39:37 2019 - [info] All SSH connection tests passed successfully.

主从状态检查

Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 - Version=5.7.20-log (oldest
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 - 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - Version=5.7.20-log (oldest
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 - 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 - 10.0.0.52(10.0.0.52:3306).
Fri Apr 19 16:40:51 2019 - 10.0.0.53(10.0.0.53:3306).
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 -
Fri Apr 19 16:40:51 2019 - Skipping all SSH and Node package Fri Apr 19 16:40:51 2019 - [info] settings on the current master.. Fri Apr 19 16:40:51 2019 - [info] reachable.
10.0.0.51(10.0.0.51:3306) 10.0.0.52(10.0.0.52:3306) 10.0.0.53(10.0.0.53:3306)
[info]
[info]
[info]
[info]
[info]
major version between slaves) log-bin:enabled
[info]
[info]
[info]
[info]
[info]
[info]
    GTID ON
    Replicating from
Current Alive Master:
Checking slave configurations.. read_only=1 is not set on slave
read_only=1 is not set on slave
Checking replication filtering settings.. binlog_do_db= , binlog_ignore_db= Replication filtering check ok.
GTID (with auto-pos) is supported. checking.
Checking SSH publickey authentication
HealthCheck: SSH to 10.0.0.51 is
[info]
[info]
[info]
[info]
Alive Slaves: 10.0.0.52(10.0.0.52:3306)
[info]
[info]
[info]
major version between slaves) log-bin:enabled
Fri Apr 19 16:40:51 2019 - [info] 10.0.0.51(10.0.0.51:3306) (current master)
+--10.0.0.52(10.0.0.52:3306) +--10.0.0.53(10.0.0.53:3306)
Fri Apr 19 16:40:51 2019 - [info] Checking
Fri Apr 19 16:40:51 2019 - [info] ok.
Fri Apr 19 16:40:51 2019 - [info] Checking
Fri Apr 19 16:40:51 2019 - [info] ok.
Fri Apr 19 16:40:51 2019 - [warning] master_ip_failover_script is not defined.
Fri Apr 19 16:40:51 2019 - [warning] shutdown_script is not defined. Fri Apr 19 16:40:51 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.

四、开启MHA（db03):

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

五、查看MHA状态(db03):

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:4719) is running(0:PING_OK), master:10.0.0.51

六、测试

把10.0.0.51主库down掉

/etc/init.d/mysqld stop

去10.0.0.52从库中查询状态

show slave status\G

显示为空
再去10.0.0.53从库中查询状态

show slave status\G

发现52变成了主库

这就说明mha作用生效了。

七、恢复

(1)修复主库

/etc/init.d/mysqld start

(2)修复主从

mysql -e "change master to master_host='10.0.0.52',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;"
mysql -e  "start slave;"
[root@db01 ~]# mysql -e  "show slave status \G"|grep Running:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(3) 恢复配置文件

[root@db03 /etc/mha]# cat app1.cnf 
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha
[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

(4)启动MHA

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

五、 Manager额外参数介绍

说明：

主库宕机谁来接管？

所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主。
从节点日志不一致，自动选择最接近于主库的从库
如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。
但是此节点日志量落后主库100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点。

(1) ping_interval=1

设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover

(2) candidate_master=1

设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave

(3)check_repl_delay=0

默认情况下如果一个slave落后master 100M的relay logs的话，

MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

追梦娃

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-MHA搭建笔记day15

一、环境三台服务器ip db01 10.0.0.51ip db02 10.0.0.52ip db03 10.0.0.53把MHA文件安装包分别传入三台服务器中并进行解压。MHA搭建配置关键程序软连接10.0.0.51/52/53:ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog ln -s...
复制链接

扫一扫