MHA高可用mysql主从服务器搭建

原创已于 2022-08-07 00:11:59 修改 · 568 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#服务器 #mysql #ssh

于 2022-08-06 23:44:14 首次发布

运维专栏收录该内容

5 篇文章

订阅专栏

参考：MHA高可用架构 - 知乎 (zhihu.com)

一、环境准备

1、准备两台服务器host1、host2

2、秘钥互信

host1#: cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
host1#: ssh host2 cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys
host1#: scp ~/.ssh/authorized_keys host2:~/.ssh/

3、安装基础依赖包，所有机器都要安装

yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN perl-DBD-MySQL perlConfig-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

二、安装MHA组件

1、安装mha4mysql-node，所有机器

# tar xf mha4mysql-node-0.58.tar.gz
# cd mha4mysql-node-0.58
# perl Makefile.PL
# make && make install

2、安装mha4mysql-manager，只在主服务器上安装

# tar -zxxf mha4mysql-manager-0.58.tar.gz
# # cd mha4mysql-manager-0.58
# ls
AUTHORS blib debian lib Makefile.PL META.yml README samples tests
bin COPYING inc Makefile MANIFEST pm_to_blib rpm t
# perl Makefile.PL
# make && make install

三、安装mysql

# cd /usl/local/src/mha
# useradd -s /sbin/nologin -M mysql
# tar -zxxf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql-5.7
# ln -s /usr/local/src/mha/mysql-5.7 /usr/local/mysql-5.7
# ln -s /usr/local/src/mha/mysql-5.7 /usr/local/mysql
# echo 'export PATH=$PATH:/usr/local/mysql-5.7/bin' >> /etc/profile
# source /etc/profile
# mysql -V
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
# cd /usr/local/mysql-5.7
# cp support-files/mysql.server /etc/init.d/mysqld
# sed -i 's@/etc/my.cnf@/usr/local/mysql-5.7/my.cnf@g'
/etc/init.d/mysqld
# sed -i 's@/usr/local/mysql/data@/opt/mysql_data@g'
/etc/init.d/mysqld
# chkconfig mysqld on
# mkdir -p /opt/mysql_data
# chown -R mysql.mysql /usr/local/src/mha/mysql-5.7
# chown -R mysql.mysql /opt/mysql_data
# ln -s /usr/local/src/mha/mysql-5.7/bin/mysqlbinlog
/usr/local/bin/mysqlbinlog
# ln -s /usr/local/src/mha/mysql-5.7/bin/mysql /usr/local/bin/mysql

四、建立主从

1、主库：my.cnf 配置文件

[mysql]
default-character-set=utf8
socket = /tmp/mysql.sock
[mysqld]
socket = /tmp/mysql.sock
character-set-server=utf8
basedir=/usr/local/mysql-5.7
datadir=/opt/mysql_data
port=3306
pid-file=/opt/mysql_data/mysqld.pid
# 四台node不可重复
server-id=101
log-bin=master-bin
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
log_slave_updates = 1
binlog-do-db = test
binlog-ignore-db = mysql,performance_schema,sys,information_schema
replicate-do-db=test
replicate-ignore-db=mysql,performance_schema,sys,information_schema

2、从库：

[client]
socket = /tmp/mysql.sock
port=3306
[mysql]
default-character-set=utf8
socket = /tmp/mysql.sock
[mysqld]
socket = /tmp/mysql.sock
character-set-server=utf8
basedir=/usr/local/mysql-5.7
datadir=/opt/mysql_data
port=3306
pid-file=/opt/mysql_data/mysqld.pid
# 四台node不可重复
server-id=102
log-bin=master-bin
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
log_slave_updates = 1
binlog-do-db = test
binlog-ignore-db = mysql,performance_schema,sys,information_schema
replicate-do-db=test
replicate-ignore-db=mysql,performance_schema,sys,information_schema

五、初始化 MySQL

主从分别执行

# mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7 --
datadir=/opt/mysql_data

启动mysql：

# systemctl start mysqld

登陆MySQL 并修改密码

# mysql -uroot -p

所有mysql增加主从用户，防止主服务器挂掉

mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;

从服务器上

mysql> change master to
master_host='$host1',master_user='repl',master_password='123456',master_log_file='m
ysql-bin.000002',master_log_pos=154;

注意：上面的master_log_file和master_log_pos这2个参数，是和master中的参数要一致的。master如何查看这2 个参数呢？在master mysql命令行上运行show master status;

mysql> start slave; #启动复制
mysql> show slave status\G; #查看slave IO和slave sql是否都正常

如果出现下面这个，说明搭建基本OK：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

六、MHA 构建

我们这里演示的vip 是: vhost

MHA Manager 配置，在host1上：

# 创建MHA配置文件目录
# mkdir /etc/mha
# 创建MHA脚本目录
# mkdir /etc/mha/scripts
# 创建MHA日志目录
# mkdir /var/log/mha/
# 创建日志目录
# mkdir /var/log/mha/app1 -p
# 创建日志文件
# touch /var/log/mha/app1/manager.log

编辑配置文件

vim /etc/mha/app1.cn

[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
[server1]
hostname=host2
port=3306
candidate_master=1 #是否能提升为master

配置文件2：

vim /etc/masterha_default.cnf

[server default]
user=root #注释请去掉，避免报错
password=123456
repl_user=repl
repl_password=123456
ssh_user=root
ping_interval=1
master_binlog_dir=/opt/mysql_data
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/manager.log
master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
report_script="/etc/mha/scripts/send_report"
remote_workdir=/tmp
secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.232.101 -s 192.168.232.102 -s 192.168.232.103
shutdown_script=""

MHA主要配置文件说明

manager_workdir=/var/log/masterha/app1：设置manager的工作目录 manager_log=/var/log/masterha/app1/manager.log：设置manager的日志文件 master_binlog_dir=/opt/mysql_data：设置master 保存binlog的位置，以便MHA可以找到master的日志 master_ip_failover_script="/etc/mha/scripts/master_ip_failover"：设置自动failover时候的切换脚本 master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"：设置手动切换时候的切换脚本 user=root：设置监控mysql的用户 password=dayi123：设置监控mysql的用户，需要授权能够在manager节点远程登录 ping_interval=1：设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover remote_workdir=/tmp：设置远端mysql在发生切换时binlog的保存位置 repl_user=repl ：设置mysql中用于复制的用户密码 repl_password=replication：设置mysql中用于复制的用户 report_script=/usr/local/send_report：设置发生切换后发送的报警的脚本 shutdown_script=""：设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂, 这里没有使用） ssh_user=root //设置ssh的登录用户名 candidate_master=1：在节点下设置，设置当前节点为候选的master slave check_repl_delay=0 :在节点配置下设置，默认情况下如果一个slave落后master 100M的relay logs 的话，MHA将不会选择该slave作为一个新的master；这个选项对于对于设置了candidate_master=1的主机非常有用

七、脚本配置

1、自动 VIP 管理配置

# vim /etc/mha/scripts/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
);
my $vip = '{$vhost}';
my $key = '0';
my $ssh_start_vip = "ifconfig ens33:0 $vip netmask 255.255.255.0 up"; 
# 如果网卡类型不是ens33的，就要把文中
的ens33缓存自己的，因为老师的是ens33，所以写的是ens33
my $ssh_stop_vip = "/sbin/ifconfig ens33:$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" ) {
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";
}

最后给刚刚配置的三个脚本增加执行权限

# chmod +x /etc/mha/scripts/master_ip_failover

八、验证 MHA 相关操作

1 、通过 masterha_check_ssh 命令验证ssh 信任登录是否成功

# masterha_check_ssh --conf=/etc/mha/app1.cnf

2、通过 masterha_check_repl 命令验证 mysql 主从复制是否成功

# masterha_check_repl --conf=/etc/mha/app1.cnf

九、启动 MHA

初次使用的时候，需要先在master上添加vip，以后就不需要了。

ifconfig ens33:0 {$vhost} netmask 255.255.255.0 up #如果网卡类型不是ens33的，就要把文中
的ens33缓存自己的，因为老师的是ens33，所以写的是ens33

启动在master上

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 状态

# masterha_check_status --conf=/etc/mha/app1.cnf

MHA 的日志保存在/var/log/masterha/app1/manager.log 下

关闭 MHA

masterha_stop --conf=/etc/mha/app1.cnf

宕机测试

masterha_stop --conf=/etc/mha/app1.cnf

在从机host2上查看VIP

# ifconfig | grep 232