前言
-
之前做主从复制和读写分离中，只有一个master
-
多个mysql形成一个中心化的群集
-
一个master对应多个slave
-
网页中的master，做一个代理功能，管理请求，把任务下发下去
一旦master挂了，群龙无首，就没有管理者，MHA 可以解决这个问题；解决这个问题还有一个是MMM架构，做两台master，每台master都与下面的从服务器相连
一、MHA概述
1.1 简介
- 主要是为了解决msater只有一台，若master出现故障时就使用MHA解决单点故障问题
- 一套优秀的MySQL高可用环境下故障切换和主从复制的软件
- MySQL故障过程中，MHA能做到0-30秒内自动完成故障切换
1. 2 MHA的组成(性能、组件)
- MHA Manager (管理节点)
- 可以借助于node组件，实现健康检查(ssh、主从复制)、控制故障切换
- 部署在所有MySQL服务器内；主要作用
- 定时汇报给主manager节点，mysql服务的状态
- 监控本地mysql服务功能
主从复制架构:主从复制(一主多从) ; MHA架构(多主多从) ; MMM架构(双主多从)
1.3 特点
- 自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
- 自动故障切换过程的本质
- 故障切换（本质VIP漂移）
- 自动（使用脚本触发自动）
- 使用半同步复制，可以大大降低数据丢失的风险
MHA为了解决的是故障切换，数据尽可能的保存，以及所有节点日志的一致性
二.搭建Mysql+MHA
实验思路
1）MHA 架构
数据库安装
- 一主两从
- MHA搭建
2）故障模拟
- 模拟主库失效
- 备选主库成为主库
- 原故障主库恢复重新加入到MHA 成为从库？
三 实验准备：
1）案例环境- 项目
- 服务器centos7.4（64位）manager/192.168.10.144
- 管理节点，安装manager组件（也需要安装node组件）
- 服务器centos7.4（64位）master/192.168.10.130
- master节点，安装node组件
- 服务器centos7.4（64位）slave1/192.168.10.142
- 项目slave节点，安装node组件
- 服务器centos7.4（64位）slave1/192.168.10.146
- 项目slave节点，安装node组件
这里操作系统是centos7，所以下载MHA版本为0.57版本
一旦master挂了，主备会顶上去干活
[root@lamp ~]# hostnamectl set-hostname manager
[root@lamp ~]# su
[root@manager ~]#
[root@nginx ~]# hostnamectl set-hostname master
[root@nginx ~]# su
[root@master ~]#
[root@localhost ~]# hostnamectl set-hostname slave1
[root@localhost ~]# su
[root@slave1 ~]#
[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# su
[root@slave2 ~]#
2）案例实现思路
安装mysql数据库
- 配置mysql一主两从
- 安装MHA软件
- 配置ssh免密码认证
- 配置mysql MHA高可用
- 模拟master故障切换
3)、配置主从复制
详见前两篇篇文章，更改点是需要注视掉从服务器配置文件/etc/my.cnf内的character_set_server=utf8
4）所有数据库提权
grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123';
grant all privileges on *.* to 'mha'@'192.168.10.%' identified by 'manager';
grant all privileges on *.* to 'mha'@'master' identified by 'manager';
grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
set global read_only=1; ##不会对超级管理员super生效，普通用户
flush privileges;
5）测试：验证从服务器只读功能
msater端创建数据表ss
use zyt
create table ss(id int);
只读功能验证
在slave1服务器上
mysql -umyslave -p123 -h 192.168.10.130 -P 3306
四 安装MHA软件
①所有服务器上都安装MHA以来的环境，首先安装epel源
yum install epel-release --nogpgcheck -y ##安装epel源，并且不进行gph检查
yum install -y perl-DBD-MySQL \ ##perl针对于mysql数据库
perl-Config-Tiny \ ###从配置文件中提拿其中的值
perl-Log-Dispatch \ ##log-日志
perl-Parallel-ForkManager \ ##子进程的函数，多线程管理模式
perl-ExtUtils-CBuilder \ ###扩展工具
perl-ExtUtils-MakeMaker \ ###扩展工具
perl-CPAN ##cpan perl中的数据库
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
MHA 软件包对于每个操作系统版本不一样，这里Centos7.4必须选择0.57版本
在所有服务器上必须先安装node组件，最后在MHA-manager节点上安装manager组件
因为manager依赖node组件
1)所有服务器同步阿里云时间
[root@master bin]# ntpdate ntp.aliyun.com
20 Jul 19:34:27 ntpdate[60181]: adjust time server 203.107.6.88 offset 0.002699 sec
[root@master bin]# crontab -e
no crontab for root - using an empty one
crontab: installing new crontab
[root@master bin]#
[root@master bin]# crontab -l
*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com
2）所有服务器安装node组件
当前目录上传node压缩包、上传manager压缩包
tar zxvf mha4mysql-node-0.57.tar.gz -C /root
cd mha4mysql-node-0.57
perl Makefile.PL
make && make install
3）在MHA节点上安装manager组件
必须先安装node才能安装manager组件
tar xf mha4mysql-manager-0.57.tar.gz -C /root
cd mha4mysql-manager-0.57
perl Makefile.PL
make && make install
4） 检测manager和node
- manager 安装后在/usr/local/bin目录下会生成几个工具，主要包括以下：
masterha-check_ssh 检查MHA的SSH配置状况（免交互）
masterha_check_repl 检查MySQL复制状况
masterha_manager 启动manager的脚本
masterha_check_status 检查当前MHA运行状态
masterha_master_monitor 检查master是否宕机
masterha_master_switch 控制故障转移(自动或者手动）
masterha_conf_host 添加或删除配置的server信息
masterha_stop 关闭manager
- node 安装后也会在/usr/local/bin目录下生成几个脚本，需要进行检查，主要包括以下
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志时间，并将其差异的事件应用于其他的slave；filter：过滤
filter_mysqlbinlog #去除不必要的ROLLBACK回滚事件（MHA 已不适用这个工具）
purge_relay_logs #清除中继日志（不会阻塞 SQL线程）
5)配置SSH无密码认证
①在manager上配置到所有数据库节点的无密码认证
在manager下配置，然后发给下面的三台mysql
ssh-keygen -t rsa
ssh-copy-id 192.168.10.130
ssh-copy-id 192.168.10.142
ssh-copy-id 192.168.10.146
ssh-copy-id 192.168.10.144
[root@manager mha4mysql-node-0.57]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
//因为是无密码验证，此处直接敲回车，下面也是一路回车
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:dOoBHzuKW27T8fxKO19ho6w5aehH4XDjUDPnODKcrLs root@master
The key's randomart image is:
+---[RSA 2048]----+
| |
| + . |
| .ooo.* |
| +O=* . |
| .SO + + |
| ..o.o+. o o |
| . oo.*..o . |
| ++ o.O+ . |
| ..E+.o=*o |
+----[SHA256]-----+
[root@manager mha4mysql-node-0.57]#
[root@manager mha4mysql-manager-0.57]# ssh-copy-id 192.168.247.160
//依次炮制192.168.247.161和192.168.247.154
Now try logging into the machine, with: "ssh '192.168.247.160'"
and check to make sure that only the key(s) you wanted were added.
以下依次类推。
7.5.5配置mysql MHA高可用
①在manager节点上复制相关脚本到/usr/local/bin目录下，会复制四个执行文件
[root@manager ~]# cd mha4mysql-manager-0.57/
[root@manager mha4mysql-manager-0.57]# ls
AUTHORS blib debian lib Makefile.PL META.yml README samples tests
bin COPYING inc Makefile MANIFEST pm_to_blib rpm t
[root@manager mha4mysql-manager-0.57]# cd samples/
[root@manager samples]# ls
conf scripts
[root@manager samples]# ls scripts/
master_ip_failover master_ip_online_change power_manager send_report
[root@manager samples]# cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@manager samples]# cd /usr/local/
[root@manager local]# ls
bin boost_1_59_0 etc games include lib lib64 libexec sbin share src tomcat9
[root@manager local]# cd bin/
[root@manager bin]# ls
apply_diff_relay_logs masterha_check_status masterha_master_switch save_binary_logs
filter_mysqlbinlog masterha_conf_host masterha_secondary_check scripts
masterha_check_repl masterha_manager masterha_stop
masterha_check_ssh masterha_master_monitor purge_relay_logs
[root@manager bin]# cd scripts/
[root@manager scripts]# ls -l
total 32
-rwxr-xr-x. 1 mysql mysql 3648 May 31 2015 master_ip_failover //自动切换时VIP管理的脚本
-rwxr-xr-x. 1 mysql mysql 9870 May 31 2015 master_ip_online_change //在线切换时vip的管理
-rwxr-xr-x. 1 mysql mysql 11867 May 31 2015 power_manager //故障发生后关闭主机的脚本
-rwxr-xr-x. 1 mysql mysql 1360 May 31 2015 send_report //因故障切换后发送报警的脚本
②复制上述的自动切换时vip的管理脚本到/usr/local/bin目录中并修改，这里使用脚本管理vip
[root@manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
[root@manager scripts]# vim /usr/local/bin/master_ip_failover
③修改/usr/local/bin/master_ip_failover脚本,内容如下:(删除原有内容，直接复制)
：% s /#//
#!/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.247.100'; //设置的vip漂移地址，类似vvip
my $brdc = '192.168.247.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;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
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 \"`;
}
# 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 --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";
}
④创建MHA软件目录并拷贝配置文件
[root@manager scripts]# mkdir /etc/masterha
[root@manager scripts]# cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
[root@manager scripts]# vim /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log //manager日志
manager_workdir=/var/log/masterha/app1.log //manager工作目录
master_binlog_dir=/usr/local/mysql/data //master保存binlog的位置，这里的路径要与master里配置的binlog的相同
master_ip_failover_script=/usr/local/bin/master_ip_failover //设置自动failover时候的切换脚本。也就是上边的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change //设置手动切换时候的切换脚本
password=manager //这个密码是前文中创建监控用户的那个密码
ping_interval=1 //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp //设置远程mysql时在发生切换时的日志binlog的保存位置
repl_password=123123 //设置复制同步用户密码
repl_user=myslave //设置复制同步用户的用户
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.247.161 -s 192.168.247.154 //设置发生切换后发生报警的脚本，后面跟两个从服务器地址
shutdown_script="" //设置故障发生关闭故障脚本主机，此处没有指定脚本，代表不关闭
ssh_user=root //设置ssh的登录用户名
user=mha //设置监控用户
#[server1]
#hostname=192.168.10.130
#port=3306
[server2]
candidate_master=1 //设置为候选master，如果设置该参数以后，MHA发送主从切换以后将会从此从库升级为主库，即使这个主库不是集群中事件最全的
hostname=192.168.10.142
check_repl_delay=0 //检查repl延迟为0，支持：默认情况下如果一个slave落后master 100M的relay logs话，MHA将不会选择该slave作为一个新的master，
port=3306
[server3]
hostname=192.168.10.146
port=3306
6）测试ssh免密码认证：masterha_check_ssh -conf=/etc/masterha/app1.cnf
如果正常最后会输出successfully，如下所示
root@manager scripts]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Fri Jan 10 10:26:03 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jan 10 10:26:03 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Jan 10 10:26:03 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Jan 10 10:26:03 2020 - [info] Starting SSH connection tests..
Fri Jan 10 10:26:04 2020 - [debug]
Fri Jan 10 10:26:03 2020 - [debug] Connecting via SSH from root@192.168.247.161(192.168.247.161:22) to root@192.168.247.154(192.168.247.154:22)..
Fri Jan 10 10:26:04 2020 - [debug] ok.
- 检查复制集：masterha_check_repl -conf=/etc/masterha/app1.cnf
[root@manager scripts]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Fri Jan 10 10:26:46 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jan 10 10:26:46 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Jan 10 10:26:46 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Jan 10 10:26:46 2020 - [info] MHA::MasterMonitor version 0.57.
Creating directory /var/log/masterha/app1.. done.
Fri Jan 10 10:26:48 2020 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.247.160:3306 from which slave 192.168.247.154(192.168.247.154:3306) replicates is not defined in the configuration file!
Fri Jan 10 10:26:48 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329.
Fri Jan 10 10:26:48 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Jan 10 10:26:48 2020 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
- 出现错误
真正问题不是出在mysql的配置文件上面，问题是出现在manager管理节点上面，管理节点的配置文件有两个，一个对于虚拟ip的配置文件/usr/local/bin/master_ip_failover用于故障转移 ，还有一个是/etc/masterha/app1.cnf
把/etc/masterha/app1.cnf中的server 1 取消注释
要注意配置授权和ssh免密钥对验证是否配置完全
[root@manager scripts]# masterha_check_repl -conf=/etc/masterha/app1.cnf
。。。。。。
MySQL Replication Health is OK.
⑦在master上手动开启VIP，虚拟ip
第一次配置，需要在master上手动配置master的虚拟IP
不要重启网卡，因为是通过命令设置，不是通过配置文件永久修改
[root@master ~]# /sbin/ifconfig ens33:1 192.168.10.130/24
[root@master ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.247.160 netmask 255.255.255.0 broadcast 192.168.247.255
inet6 fe80::e2c1:c26d:afa1:a4ad prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:52:4d:89 txqueuelen 1000 (Ethernet)
RX packets 503726 bytes 98211279 (93.6 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 620773 bytes 84707696 (80.7 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.247.100 netmask 255.255.255.0 broadcast 192.168.247.255
ether 00:0c:29:52:4d:89 txqueuelen 1000 (Ethernet)
五 启动MHA
①详解
①nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
ohup //选项
masterha_manager //开启
–conf=/etc/masterha/app1.cnf //指定配置文件
–remove_dead_master_conf //当master服务器失效时，发生主从切换后，会把旧的master的ip从主配置文件删除
–ignore_last_failover //忽略故障转移，忽略掉总是宕机不够可靠的服务器
在缺省情况下，如果MHA检测到连续发生宕机，且两次宕机间隔不足8小时的话，则不会进行Failover，之所以这样限制是为了避免ping-pong效应。该参数代表忽略桑次MHA出发切换产生的文件，默认情况下，MHA发生切换后会在日志记录下，也就是上面设置的日志app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件；为了方便，这里设为为–ignore_last_failover
< /dev/null > //生成的所有信息会导到null下或者/var/log/masterha/app1/manager.log日志文件中
2>&1 & //把2错误性的输出从定向为标准性输出，开启后台运行
② 查看MHA状态
可以看到当前的master是master服务器节点
[root@manager scripts]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:35738) is running(0:PING_OK), master:192.168.10.144
③查看MHA日志
[root@manager scripts]# cat /var/log/masterha/app1/manager.log
。。。。。
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.247.100===
。。。。。
/usr/local/bin/masterha_secondary_check -s 192.168.247.161 -s 192.168.247.154
Fri Jan 10 11:11:01 2020 - [info] Starting ping health check on 192.168.247.160(192.168.247.160:3306)..
Fri Jan 10 11:11:01 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
④查看master的VIP地址192.168.247.100是否存在
这个地址不会因为manager节点停止MHA服务而消失
[root@master ~]# ifconfig
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.10.130 netmask 255.255.255.0 broadcast 192.168.10.255
六 模拟master故障切换进行测试
①先启用监控观察日志去记录
[root@manager scripts]# tailf /var/log/masterha/app1/manager.log
②此时在master服务器当中pkill掉
[root@master ~]# pkill -9 mysql
③此时vip不再master身上了
④查看设置过candidate_master=1的slave1
[root@slave1 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.10.142 netmask 255.255.255.0 broadcast 192.168.10.255
inet6 fe80::1259:c72a:d63c:9f07 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:6b:25:36 txqueuelen 1000 (Ethernet)
RX packets 592080 bytes 148453591 (141.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 715917 bytes 97034436 (92.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.247.100 netmask 255.255.255.0 broadcast 192.168.247.255
ether 00:0c:29:6b:25:36 txqueuelen 1000 (Ethernet)
发现vip跑到了slave上面
⑤ MHA也有反应了
----- Failover Report -----
app1: MySQL Master failover 192.168.10.130(192.168.10.160:3306) to 192.168.10.161(192.168.10.142:3306) succeeded
Master 192.168.247.130(192.168.247.130:3306) is down!
Check MHA Manager logs at manager:/var/log/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.247.160(192.168.247.160:3306)
The latest slave 192.168.247.161(192.168.247.161:3306) has all relay logs for recovery.
Selected 192.168.247.161(192.168.247.161:3306) as a new master.
192.168.247.161(192.168.247.161:3306): OK: Applying all logs succeeded.
192.168.247.161(192.168.247.161:3306): OK: Activated master IP address.
192.168.247.154(192.168.247.154:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.247.154(192.168.247.154:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.247.161(192.168.247.161:3306)
192.168.247.161(192.168.247.161:3306): Resetting slave info succeeded.
Master failover to 192.168.247.161(192.168.247.161:3306) completed successfully.
⑥此时重开一个manager远程当作客户端，yum安装mysql客户端
连接成功
Last login: Fri Jan 10 09:10:46 2020 from 192.168.247.1
[root@manager ~]#
[root@manager ~]# yum install -y mysql
[root@manager ~]# mysql -h 192.168.247.100 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.247.206' (using password: YES)
发现无法登陆，没有权限
去到当前的主服务器下设置权限，允许root在所有终端登陆
[root@slave1 ~]# mysql -uroot -p
Enter password:
mysql> grant all on *.* to 'root'@'%' identified by '123';
此时再次在manager中登陆，指定的数据库IP是vip
[root@manager ~]# mysql -h 192.168.10.1144 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.247.206' (using password: YES)
[root@manager ~]# mysql -h 192.168.247.100 -p
Enter password:
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
MySQL [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> use school;
Database changed
MySQL [school]> create table info (id int);
Query OK, 0 rows affected (0.03 sec)
MySQL [school]> desc info;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
MySQL [school]>
此时查看现在的slave1（现在已经成为master）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc info;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql>
查看slave2，已被同步，之前设置的是slave同步的是master：192.168.10.142，现在同步的是slave1：192.168.247.161
[root@slave2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.161
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 714
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 878
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
七：切换备选主库的算法
-
一般判断从库的是从（postion/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选主。
-
数据一致的情况下，按照配置文件顺序，选择备选主库
-
设定有权重（candidate_master=1），按照权重强制指定备选主
默认情况下，如果一个slave落后master 100M 的relay logs的话，即使有权重，也会失效
如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主
八：安装过程中可能出现的故障
故障修复步骤:
8.1 修复db
/etc/init. d/mysqld start
8.2.修复主从
change master to master_ host=' 192. 168. 195. 130' , master_ port-3306, master_ auto_ position= 1, master. user=' mha',
start slave;
8.3.修改配置文件(再把这个记录添加进去，因为它检测掉失效时候会自动消失)
vi /etc/mas terha/appl. cnf
[ server1]
hostname- 192. 168. 195. 129
port=3306
8.4.启动manager (在manager那台机器上)
nohup masterha manager -”conf-/etc/masterha/app1. cnf -- remove. dead_ master. conf --ignore_ last_ failover < /c
dos2unix /usr/ local/bin/master_ ip. failover 解决中英字不兼容报错的问题
1299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
