业余从事网站开发,wordpress简约企业主题,前端响应式网站,做网站自己上传电影要多大服务器Mysql的MHA高可用及故障切换 MHA主从复制的单点问题配置1. 主从复制2. MHA高可用安装MHA的组件配置无密码认证manager节点配置manager节点上测试启动连接 故障切换模拟恢复 MHA
master high availability 建立在主从复制基础之上的故障切换的软件系统。
主从复制的单点问题
… Mysql的MHA高可用及故障切换 MHA主从复制的单点问题配置1. 主从复制2. MHA高可用安装MHA的组件配置无密码认证manager节点配置manager节点上测试启动连接 故障切换模拟恢复 MHA
master high availability 建立在主从复制基础之上的故障切换的软件系统。
主从复制的单点问题
当主从复制当中主服务器发生故障会自动切换到一台从服务器然后把从服务器升格为主继续主从的架构 master开启二进制日志并允许从节点复制主节点的二进制日志的内容 通过vip地址当主容机之后vip会自动的飘移到从节点。 从节点升级为主服务器然后从宕机的master保存二进制日志将更新的内容同步到新主然后再同步到从节点。
配置
mysql1 192.168.246.6 主 mysql2 192.168.246.7 从1----主备 mysql3 192.168.246.10 从2 manager节点u3 192.168.246.9 MHA的组件 vip 192.168.246.100
1. 主从复制
ntpdate ntp.aliyun.com
#时间同步vim /etc/my.cnf#主
log-binmaster-bin
binlog_formatMIXED
log-slave-updatestrue
relay_log_recovery1
#启用从库崩溃或者重启时会自动尝试从日志当中恢复。#从1
server-id 2
log-binmaster-bin
relay-logrelay-log-bin
relay-log-indexslave-relay-bin.index
relay_log_recovery 1#从2
server-id 3
relay-logrelay-log-bin
relay-log-indexslave-relay-bin.index
relay_log_recovery 1#主从
mysql -u root -p123456#从数据库同步使用
CREATE USER myslave192.168.246.% IDENTIFIED WITH mysql_native_password BY 123456;
GRANT REPLICATION SLAVE ON *.* TO myslave192.168.246.%; #manager 使用
CREATE USER mha192.168.246.% IDENTIFIED WITH mysql_native_password BY manager;
GRANT ALL PRIVILEGES ON *.* TO mha192.168.246.% WITH GRANT OPTION;#防止从库通过主机名连接不上主库
CREATE USER mhamaster IDENTIFIED WITH mysql_native_password BY manager;
GRANT ALL PRIVILEGES ON *.* TO mhamaster;
CREATE USER mhaslave1 IDENTIFIED WITH mysql_native_password BY manager;
GRANT ALL PRIVILEGES ON *.* TO mhaslave1;
CREATE USER mhaslave2 IDENTIFIED WITH mysql_native_password BY manager;
GRANT ALL PRIVILEGES ON *.* TO mhaslave2;flush privileges;#主
show master status;#从1 从2
mysql -u root -p123456#同步建立
CHANGE master to master_host192.168.246.6,master_usermyslave,master_password123456,master_log_filemaster-bin.000001,master_log_pos157;start slave;show slave status\G;#从库必须全部设置为只读模式
set global read_only1;2. MHA高可用
安装MHA的组件
NODE 监控每台机器上mysql的状态传回给managerMANAGER管理节点控制mha的状态。
#每个服务器都需要安装MHA依赖的环境
apt install -y libdbd-mysql-perl \
libconfig-tiny-perl \
liblog-dispatch-perl \
libparallel-forkmanager-perl \
libextutils-cbuilder-perl \
libmodule-install-perl \
make#在MHA的manager节点上安装manager组件
cd /opt
tar -xf mha4mysql-manager-0.57.tar.gzcd mha4mysql-manager-0.57
perl Makefile.PL
make make installcd /opt
tar -xf mha4mysql-node-0.57.tar.gzcd mha4mysql-node-0.57
perl Makefile.PL
make make install#组件安装后在/usr/local/bin 下面会生成几个工具
masterha_check_ssh : 检查mha节点之间ssh的配置和通信
masterha_manager manager的启动脚本
masterha_check_status 检查mha的运行状态
masterha_stop关闭manager
masterha_master_switch: 控制故障转移的方式
ave_binary_logs 检查保存复制master节点的二进制日志
apply_diff_relay_logs 识别二进制日志当中的差异部分把差异的部分同步到slave配置无密码认证
#配置所有节点之间的MHA的ssh的通信
#manager节点
ssh-keygen -t rsa
回车
ssh-copy-id 192.168.246.6
ssh-copy-id 192.168.246.7
ssh-copy-id 192.168.246.10#主
ssh-keygen -t rsa
回车
ssh-copy-id 192.168.246.7
ssh-copy-id 192.168.246.10#从
ssh-keygen -t rsa
回车
ssh-copy-id 192.168.246.6
ssh-copy-id 192.168.246.10manager节点配置
rootu3:/usr/local/bin# cd /opt/mha4mysql-manager-0.57/samples/rootu3:/opt/mha4mysql-manager-0.57/samples# cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/binrootu3:/opt/mha4mysql-manager-0.57/samples# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/rootu3:/opt/mha4mysql-manager-0.57/samples# vim /usr/local/bin/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 192.168.233.100;
my $brdc 192.168.233.255;
my $ifdev ens33;
my $key 1;
my $ssh_start_vip /sbin/ifconfig ens33:$key $vip;
my $ssh_stop_vip /sbin/ifconfig ens33:$key down;
my $exit_code 0;
GetOptions(
commands \$command,
ssh_users \$ssh_user,
orig_master_hosts \$orig_master_host,
orig_master_ips \$orig_master_ip,
orig_master_porti \$orig_master_port,
new_master_hosts \$new_master_host,
new_master_ips \$new_master_ip,
new_master_porti \$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 \;
}
### A simple system call that disable the VIP on the old_master
sub stop_vip() {
ssh $ssh_user\$orig_master_host \ $ssh_stop_vip \;
}sub usage {
print
Usage: master_ip_failover --commandstart|stop|stopssh|status --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n;
}rootu3:/opt/mha4mysql-manager-0.57/samples# mkdir /etc/masterharootu3:/opt/mha4mysql-manager-0.57/samples# ls
conf scripts
rootu3:/opt/mha4mysql-manager-0.57/samples# cd conf/
rootu3:/opt/mha4mysql-manager-0.57/samples/conf# ls
app1.cnf masterha_default.cnfrootu3:/opt/mha4mysql-manager-0.57/samples/conf# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
rootu3:/opt/mha4mysql-manager-0.57/samples/conf# vim /etc/masterha/app1.cnf
#复制以下代码需要清除注释[server default]
manager_log/var/log/masterha/app1/manager.log
manager_workdir/var/log/masterha/app1
master_binlog_dir/usr/local/mysql/data
#指向到myql的默认位置master_ip_failover_script/usr/local/bin/master_ip_failover
#切换vip的脚本master_ip_online_change_script/usr/local/bin/master_ip_online_change
passwordmanager
ping_interval1
#每一秒检测一次主的状态remote_workdir/tmp
repl_password123456
repl_usermyslave
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.246.7 -s 192.168.246.10
#从对主监听
shutdown_script
ssh_userroot
usermha[server1]
hostname192.168.246.6
#主服务器port3306[server2]
candidate_master1
#声明server2备服务器check_repl_delay0
#立刻切换hostname192.168.246.7
#备用主服务器
port3306[server3]
hostname192.168.246.10
#从服务器2
port3306rootu3:/opt/mha4mysql-manager-0.57/samples/conf# vim /usr/local/share/perl/5.34.0/MHA/NodeUtil.pm#192行修改添加$str ~ /(\d)\.(\d)/;my $strmajor $1.$2;my $result sprintf( %03d%03d, $1, $2 ) if $str ~ m/(\d)\.(\d)/;#主需要手动开启虚拟IP
ifconfig ens33:1 192.168.246.100/24#主从设置软连接
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/manager节点上测试 #测试ssh无密码认证如果正常最后会输出为successfully masterha_check_ssh -conf/etc/masterha/app1.cnf 测试mysql主从连接情况最后出现is OK字样则正常。 masterha_check_repl -conf/etc/masterha/app1.cnf 启动连接
#manager节点上
nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /var/log/masterha/app1/manager.log 21
#nohup:执行记录把执行记录保存到指定的文件。#检查master节点
rootu3:~# masterha_check_status --conf/etc/masterha/app1.cnf
app1 (pid:4171) is running(0:PING_OK), master:192.168.246.6#客户端
apt -y install mariadb-server#用vip连接
mysql -h 192.168.246.100 -u root -p123456MySQL [(none)] create database test1;
Query OK, 1 row affected (0.139 sec)MySQL [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
--------------------
5 rows in set (0.004 sec)
故障切换模拟
#manager节点打开日志
tail -f /var/log/masterha/app1/manager.log#停止mha
master_stop --conf/etc/masterha/appl.cnf#主
systemctl stop mysqld
#从1
ip addr恢复
#manager节点
vim /etc/masterha/app1.cnf原来的主自动删除了 修改并添加如下图的内容
#原来的主
vim /etc/my.cnflog-binmaster-bin
binlog_formatMIXED
relay-logrelay-log-bin
relay-log-indexslave-relay-bin.index
relay_log_recovery1systemctl restart mysqld#从1
server-id 2
log-binmaster-bin
log-slave-updatestrue
relay-logrelay-log-bin
relay-log-indexslave-relay-bin.index
relay_log_recovery 1mysql -u root -p123456
show master status;原主与从2
CHANGE master to master_host192.168.246.7,master_usermyslave,master_password123456,master_log_filemaster-bin.000001,master_log_pos352;start slave;show slave status\G;#客户端
rootu4:~# mysql -h 192.168.246.100 -u root -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.30 MySQL Community Server - GPLCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type help; or \h for help. Type \c to clear the current input statement.MySQL [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
--------------------
5 rows in set (0.004 sec)
4 rows in set (0.013 sec)MySQL [(none)] create database test2;
Query OK, 1 row affected (0.139 sec)MySQL [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
--------------------
5 rows in set (0.004 sec)
