投稿
  1. 云编程开发首页
  2. 数据库

Mysql MHA部署-04MHA配置

smartdo • • 数据库 • 阅读 151


Mysql MHA部署-04MHA配置



一 Mysql MHA部署-01介绍

二 Mysql MHA部署-02主从复制

三 Mysql MHA部署-03MHA软件安装


四 Mysql MHA部署-04MHA配置

五 Mysql MHA部署-05故障转移

六 Mysql MHA部署-06手动切换

七 Mysql MHA部署-07常见问题


架构说明：


参考：



http://www.zhaibibei.cn/mysql/mha/

一：MySQL MHA部署前准备


1.1 hosts文件
首先我们在四台服务器上添加其他服务器的hosts信息
[root@rac1 mha]# vim /etc/hosts
192.168.2.222 rac4
192.168.2.187 rac1
192.168.2.188 rac2
192.168.2.223 rac3


1.2 配置免密码登录
接下来我们需要配置各服务请求间的免密码登录
ssh-kengen 命令后请一路下一步

—192.168.2.187 rac1
ssh-keygen -t rsa
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.223
ssh-copy-id 192.168.2.222
ssh-copy-id rac2
ssh-copy-id rac3
ssh-copy-id rac4

—192.168.2.188 rac2
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.223
ssh-copy-id 192.168.2.222
ssh-copy-id rac1
ssh-copy-id rac3
ssh-copy-id rac4

—192.168.2.223 rac3
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.222
ssh-copy-id rac1
ssh-copy-id rac2
ssh-copy-id rac4

—192.168.2.222 rac4
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.223
ssh-copy-id rac1
ssh-copy-id rac2
ssh-copy-id rac3

1.3 创建监控账户
接下来我们创建用于MHA监控的数据库账户
主库 187：
master数据库
mysql> grant all privileges on *.* to ‘monitor’@’192.168.2.187’ identified by ‘123456’;
mysql> grant all privileges on *.* to ‘monitor’@’192.168.2.188’ identified by ‘123456’;
mysql> grant all privileges on *.* to ‘monitor’@’192.168.2.222’ identified by ‘123456’;
mysql> grant all privileges on *.* to ‘monitor’@’192.168.2.223’ identified by ‘123456’;
mysql> grant all privileges on *.* to ‘monitor’@’127.0.0.1’ identified by ‘123456’;
mysql> flush privileges;
mysql> select host,user from mysql.user;
+—————+—————+
| host | user |
+—————+—————+
| 127.0.0.1 | monitor |
| 192.168.2.187 | monitor |
| 192.168.2.187 | repl |
| 192.168.2.188 | monitor |
| 192.168.2.188 | repl |
| 192.168.2.222 | monitor |
| 192.168.2.223 | monitor |
| 192.168.2.223 | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+—————+—————+
11 rows in set (0.00 sec)

1.4 关闭防火墙
如果防火墙开始，需要开通服务器间3306端口的通信


1.5 关闭relay日志自动清理
由于默认情况下从库的relay logs会在SQL线程执行完毕后被自动删除，但是对于MHA场景下，对于某些滞后从库的恢复依赖于其他从库的relay log，因此采取禁用自动删除功能以及定期清理的办法
使用purge_relay_logs脚本这个后面说明
主从库3台
relay_log_purge=0


1.6 从库开启只读
从库需要设置只读模式
2台从库
read_only=1


1.7 master节点绑定VIP
刚开始需要手动将VIP绑定至master节点
[root@rac1 mysql]# ifconfig |grep 192
inet 192.168.2.187 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255
[root@rac1 mysql]# /sbin/ifconfig enp0s3:2 192.168.2.189 netmask 255.255.255.0 up
[root@rac1 mysql]# ifconfig |grep 192
inet 192.168.2.187 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.2.189 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255


1.8 准备自定义脚本
这里我们通过网盘下载，放到/etc/mha/script下面并赋予可执行权限
链接: https://pan.baidu.com/s/18H12bTIYHi2H0dXs2DI7NA 提取码: mwkq
管理节点：192.168.2.222 RAC4
[root@rac4 ~]# mkdir /etc/mha/script -p
[root@rac4 ~]# cp /package/mysql/MHA/script/* /etc/mha/script/
[root@rac4 ~]# ll -rth /etc/mha/script/
total 24K
-rw-r–r– 1 root root 2.4K Mar 14 19:56 send_report开发云主机域名
-rw-r–r– 1 root root 11K Mar 14 19:56 master_ip_online_change
-rw-r–r– 1 root root 4.3K Mar 14 19:56 master_ip_failover
接下来我们修改相关的文件

—master_ip_failover
该文件只需要修改
my $vip = ‘192.168.2.189’;
my $key = “0”;
my $ssh_start_vip = “/sbin/ifconfig enp0s3:$key $vip/24”;
my $ssh_stop_vip = “/sbin/ifconfig enp0s3:$key down”;
my $ssh_send_garp = “/sbin/arping -U $vip -I enp0s3 -c 1”;

—master_ip_online_change
该文件也是需要修改
my $vip = ‘192.168.2.189’;
my $key = “0”;
my $ssh_start_vip = “/sbin/ifconfig enp0s3:$key $vip/24”;
my $ssh_stop_vip = “/sbin/ifconfig enp0s3:$key down”;
my $ssh_send_garp = “/sbin/arping -U $vip -I enp0s3 -c 1”;

—send_report
该文件需要修改邮件配置，这个后面做介绍


二 MySQL MHA部署指南


2.1 管理节点配置
[root@rac4 script]# vim /etc/mha/mha.conf
[server default]
# mysql user and password
user=monitor
password=123456
ssh_user=root
# working directory on the manager
manager_workdir=/etc/mha/manager
manager_log=/etc/mha/manager/mha.log
ping_interval=1
# working directory on MySQL servers
remote_workdir=/etc/mha/node
# replication user
repl_user=repl
repl_password=rpl
master_binlog_dir=/datalog/mysql/binlog
## customized script
master_ip_failover_script=/etc/mha/script/master_ip_failover
master_ip_online_change_script=/etc/mha/script/master_ip_online_change
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.2.188 -s 192.168.2.223 –user=root –master_host=192.168.2.187 –master_ip=192.168.2.187 –master_port=3306
[server1]
hostname=rac1
candidate_master=1
[server2]
hostname=rac2
candidate_master=1
[server3]
hostname=rac3
no_master=1
接下来我们来解释每个参数的意义：
manager_workdir 管理节点工作目录，mha运行时会有状态文件生成
masnager_log 管理节点生成的日志
ping_interval 管理节点检测主库状态的间隔，默认为3s
remote_workdir MHA node工作目录，不存在会自动建立，如果不允许创建，MHA Node自动异常退出
master_binlog_dir 指定主库的二进制日志位置，防止管理节点无法连接主库获取日志位置
master_ip_failover_script 发生切换时VIP漂移脚本
master_ip_online_change_script 用于手动切换时VIP的漂移
secondary_check_script 若管理节点无法连接主库，则调用该脚本从从库检测主库状态
candidate_master 指定如果主库发生故障，优先提升该节点为主库
no_master 指定该节点不会成为主库

2.2 测试连接

2.2.1 测试ssh是否设置正确
管理节点

[root@rac4 script]# masterha_check_ssh –conf=/etc/mha/mha.conf
Sat Mar 14 20:10:42 2020 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Mar 14 20:10:42 2020 – [info] Reading application default configuration from /etc/mha/mha.conf..
Sat Mar 14 20:10:42 2020 – [info] Reading server configuration from /etc/mha/mha.conf..
Sat Mar 14 20:10:42 2020 – [info] Starting SSH connection tests..
Sat Mar 14 20:10:44 2020 – [debug]
Sat Mar 14 20:10:42 2020 – [debug] Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac2(192.168.2.188:22)..
Sat Mar 14 20:10:43 2020 – [debug] ok.
Sat Mar 14 20:10:43 2020 – [debug] Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac3(192.168.2.223:22)..
Sat Mar 14 20:10:44 2020 – [debug] ok.
Sat Mar 14 20:10:45 2020 – [debug]
Sat Mar 14 20:10:43 2020 – [debug] Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac1(192.168.2.187:22)..
Sat Mar 14 20:10:44 2020 – [debug] ok.
Sat Mar 14 20:10:44 2020 – [debug] Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac2(192.168.2.188:22)..
Sat Mar 14 20:10:45 2020 – [debug] ok.
Sat Mar 14 20:10:45 2020 – [debug]
Sat Mar 14 20:10:43 2020 – [debug] Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac1(192.168.2.187:22)..
Sat Mar 14 20:10:44 2020 – [debug] ok.
Sat Mar 14 20:10:44 2020 – [debug] Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac3(192.168.2.223:22)..
Sat Mar 14 20:10:44 2020 – [debug] ok.
Sat Mar 14 20:10:45 2020 – [info] All SSH connection tests passed successfully.
测试通过会有成功字样打印出来


2.2.2 测试复制是否正常
管理节点222(rac4)

[root@rac4 script]# masterha_check_repl –conf=/etc/mha/mha.conf
Sat Mar 14 20:14:42 2020 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Mar 14 20:14:42 2020 – [info] Reading application default configuration from /etc/mha/mha.conf..
Sat Mar 14 20:14:42 2020 – [info] Reading server configuration from /etc/mha/mha.conf..
Sat Mar 14 20:14:42 2020 – [info] MHA::MasterMonitor version 0.56.
Sat Mar 14 20:14:43 2020 – [info] GTID failover mode = 1
Sat Mar 14 20:14:43 2020 – [info] Dead Servers:
Sat Mar 14 20:14:43 2020 – [info] Alive Servers:
Sat Mar 14 20:14:43 2020 – [info] rac1(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 – [info] rac2(192.168.2.188:3306)
Sat Mar 14 20:14:43 2020 – [info] rac3(192.168.2.223:3306)
Sat Mar 14 20:14:43 2020 – [info] Alive Slaves:
Sat Mar 14 20:14:43 2020 – [info] rac2(192.168.2.188:3306) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sat Mar 14 20:14:43 2020 – [info] GTID ON
Sat Mar 14 20:14:43 2020 – [info] Replicating from 192.168.2.187(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 – [info] Primary candidate for the new Master (candidate_master is set)
Sat Mar 14 20:14:43 2020 – [info] rac3(192.168.2.223:3306) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sat Mar 14 20:14:43 2020 – [info] GTID ON
Sat Mar 14 20:14:43 2020 – [info] Replicating from 192.168.2.187(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 – [info] Not candidate for the new Master (no_master is set)
Sat Mar 14 20:14:43 2020 – [info] Current Alive Master: rac1(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 – [info] Checking slave configurations..
Sat Mar 14 20:14:43 2020 – [info] Checking replication filtering settings..
Sat Mar 14 20:14:43 2020 – [info] binlog_do_db= , binlog_ignore_db=
Sat Mar 14 20:14:43 2020 – [info] Replication filtering check ok.
Sat Mar 14 20:14:43 2020 – [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Mar 14 20:14:43 2020 – [info] Checking SSH publickey authentication settings on the current master..
Sat Mar 14 20:14:43 2020 – [info] HealthCheck: SSH to rac1 is reachable.
Sat Mar 14 20:14:43 2020 – [info]
rac1(192.168.2.187:3306) (current master)
+–rac2(192.168.2.188:3306)
+–rac3(192.168.2.223:3306)
Sat Mar 14 20:14:43 2020 – [info] Checking replication health on rac2..
Sat Mar 14 20:14:43 2020 – [info] ok.
Sat Mar 14 20:14:43 2020 – [info] Checking replication health on rac3..
Sat Mar 14 20:14:43 2020 – [info] ok.
Sat Mar 14 20:14:43 2020 – [info] Checking master_ip_failover_script status:
Sat Mar 14 20:14:43 2020 – [info] /etc/mha/script/master_ip_failover –command=status –ssh_user=root –orig_master_host=rac1 –orig_master_ip=192.168.2.187 –orig_master_port=3306
Sat Mar 14 20:14:43 2020 – [info] OK.
Sat Mar 14 20:14:43 2020 – [warning] shutdown_script is not defined.
Sat Mar 14 20:14:43 2020 – [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
测试通过会有成功字样打印出来
通过输出我们可以看到相关架构信息

3 启动MHA
首先我们可以检测MHA是否已启动
管理节点

[root@rac4 script]# masterha_check_status –conf=/etc/mha/mha.conf
mha is stopped(2:NOT_RUNNING).
我们在manager上执行如下命令开启MHA

管理节点

[root@rac4 script]# nohup masterha_manager –conf=/etc/mha/mha.conf –remove_dead_master_conf &
–remove_dead_master_conf意思为当发生切换后，老的主库信息会从配置文件删除
[1] 4360
[root@rac4 script]# nohup: ignoring input and appending output to ‘nohup.out’


[root@rac4 script]# masterha_check_status –conf=/etc/mha/mha.conf
mha (pid:4360) is running(0:PING_OK), master:rac1


4停止MHA
[root@rac4 script]# masterha_stop -conf=/etc/mha/mha.conf
Stopped mha successfully.
[1]+ Exit 1 nohup masterha_manager –conf=/etc/mha/mha.conf –remove_dead_master_conf
[root@rac4 script]# masterha_check_status –conf=/etc/mha/mha.conf
mha is stopped(2:NOT_RUNNING).


5设置relay log清理计划
前面我们说到我们已经取消了relay log的自动清理，这里需要在主从库3台上设置定时任务手动清理
这里首先需要在root用户环境变量中增加mysql相关路径
主从库三台
export MYSQL_HOME=/usr/local/mysql
export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH
0 4 1 * * /usr/bin/purge_relay_logs –user=monitor –password=123456 -disable_relay_log_purge –workdir=/tmp/ >> /tmp/purge_relay_logs.log 2>&1

欢迎关注我的微信公众号”IT小Chen”，共同学习，共同成长！！！





相关推荐: mysql 查询语句可以使用的函数总结

mysql 查询语句有时候需要用到函数，但是函数比子查询的效率要高，有时候函数还是很方便的，下面简单总结sql函数具体用法。1，对数字处理函数avg（）count（）max（）min（）sum（）开发云主机域名2，对字符串的处理合并字符串函数：concat(s…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
smartdosmartdo
0 0
上一篇 06/05 17:49
下一篇 06/05 17:49

相关推荐