01，主从复制架构演变介绍

1.基本结构

（1）一主一从
（2）一主多从
（3）多级主从
（4）双主
（5）循环复制

2.高级应用架构演变

高性能架构

读写分离架构(读性能较高)
代码级别
MySQL proxy (Atlas,mysql router,proxySQL(percona),maxscale)、
amoeba(taobao)
xx-dbproxy等。
分布式架构(读写性能都提高):
分库分表——cobar--->TDDL,DRDS
Mycat--->DBLE自主研发等。
NewSQL-->TiDB

高可用架构

单活:MMM架构——mysql-mmm（google）
单活:MHA架构——mysql-master-ha（日本DeNa）,T-MHA
多活:MGR ——5.7 新特性 MySQL Group replication(5.7.17) --->Innodb Cluster  
多活:MariaDB Galera Cluster架构,(PXC)Percona XtraDB Cluster、MySQL Cluster(Oracle rac)架构

02，高可用架构

1.架构工作原理

主库宕机处理过程
1. 监控节点 (通过配置文件获取所有节点信息)
   系统,网络,SSH连接性
   主从状态,重点是主库

2. 选主
(1) 如果判断从库(position或者GTID),数据有差异,最接近于Master的slave,成为备选主
(2) 如果判断从库(position或者GTID),数据一致,按照配置文件顺序,选主.
(3) 如果设定有权重(candidate_master=1),按照权重强制指定备选主.
    1. 默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重,也会失效.
    2. 如果check_repl_delay=0的化,即使落后很多日志,也强制选择其为备选主
3. 数据补偿
(1) 当SSH能连接,从库对比主库GTID 或者position号,立即将二进制日志保存至各个从节点并且应用(save_binary_logs )
(2) 当SSH不能连接, 对比从库之间的relaylog的差异(apply_diff_relay_logs) 
4. Failover
将备选主进行身份切换,对外提供服务
其余从库和新主库确认新的主从关系
5. 应用透明(VIP)
6. 故障切换通知(send_reprt)
7. 二次数据补偿(binlog_server)
8. 自愈自治(待开发...)

2.架构介绍

1主2从，master：db01   slave：db02   db03 ）：
MHA 高可用方案软件构成
Manager软件：选择一个从节点安装
Node软件：所有节点都要安装

3.MHA软件构成

Manager工具包主要包括以下几个工具：
masterha_manger             启动MHA 
masterha_check_ssh      检查MHA的SSH配置状况 
masterha_check_repl         检查MySQL复制状况 
masterha_master_monitor     检测master是否宕机 
masterha_check_status       检测当前MHA运行状态 
masterha_master_switch  控制故障转移（自动或者手动）
masterha_conf_host      添加或删除配置的server信息

Node工具包主要包括以下几个工具：
这些工具通常由MHA Manager的脚本触发，无需人为操作
save_binary_logs            保存和复制master的二进制日志 
apply_diff_relay_logs       识别差异的中继日志事件并将其差异的事件应用于其他的
purge_relay_logs            清除中继日志（不会阻塞SQL线程）

4.MHA环境搭建

①规划

主库: 51    node 
从库: 
52      node
53      node    manager

②环境准备（1主2从GTID,过程省略）

③配置关键程序软连接

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

④.配置各节点互信

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 
各节点验证
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

⑦状态检查

# 互信监测
[root@db03 ~ 16:26:56]# masterha_check_ssh  --conf=/etc/mha/app1.cnf 
Sun Mar  7 16:27:24 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Mar  7 16:27:24 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sun Mar  7 16:27:24 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sun Mar  7 16:27:24 2021 - [info] Starting SSH connection tests..
Sun Mar  7 16:27:25 2021 - [debug] 
Sun Mar  7 16:27:24 2021 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22)..
Sun Mar  7 16:27:24 2021 - [debug]   ok.
Sun Mar  7 16:27:24 2021 - [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)..
Sun Mar  7 16:27:25 2021 - [debug]   ok.
Sun Mar  7 16:27:25 2021 - [debug] 
Sun Mar  7 16:27:24 2021 - [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)..
Sun Mar  7 16:27:25 2021 - [debug]   ok.
Sun Mar  7 16:27:25 2021 - [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)..
Sun Mar  7 16:27:25 2021 - [debug]   ok.
Sun Mar  7 16:27:26 2021 - [debug] 
Sun Mar  7 16:27:25 2021 - [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)..
Sun Mar  7 16:27:26 2021 - [debug]   ok.
Sun Mar  7 16:27:26 2021 - [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)..
Sun Mar  7 16:27:26 2021 - [debug]   ok.
Sun Mar  7 16:27:26 2021 - [info] All SSH connection tests passed successfully.

⑧主从状态检查

[root@db03 ~ 16:27:26]# masterha_check_repl  --conf=/etc/mha/app1.cnf
Sun Mar  7 16:30:51 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Mar  7 16:30:51 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sun Mar  7 16:30:51 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sun Mar  7 16:30:51 2021 - [info] MHA::MasterMonitor version 0.56.
Sun Mar  7 16:30:52 2021 - [info] GTID failover mode = 1
Sun Mar  7 16:30:52 2021 - [info] Dead Servers:
Sun Mar  7 16:30:52 2021 - [info] Alive Servers:
Sun Mar  7 16:30:52 2021 - [info]   10.0.0.51(10.0.0.51:3306)
Sun Mar  7 16:30:52 2021 - [info]   10.0.0.52(10.0.0.52:3306)
Sun Mar  7 16:30:52 2021 - [info]   10.0.0.53(10.0.0.53:3306)
Sun Mar  7 16:30:52 2021 - [info] Alive Slaves:
Sun Mar  7 16:30:52 2021 - [info]   10.0.0.52(10.0.0.52:3306)  Version=5.7.26-log (oldest major version between slaves) log-bin:enabled
Sun Mar  7 16:30:52 2021 - [info]     GTID ON
Sun Mar  7 16:30:52 2021 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
Sun Mar  7 16:30:52 2021 - [info]   10.0.0.53(10.0.0.53:3306)  Version=5.7.26-log (oldest major version between slaves) log-bin:enabled
Sun Mar  7 16:30:52 2021 - [info]     GTID ON
Sun Mar  7 16:30:52 2021 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
Sun Mar  7 16:30:52 2021 - [info] Current Alive Master: 10.0.0.51(10.0.0.51:3306)
Sun Mar  7 16:30:52 2021 - [info] Checking slave configurations..
Sun Mar  7 16:30:52 2021 - [info]  read_only=1 is not set on slave 10.0.0.52(10.0.0.52:3306).
Sun Mar  7 16:30:52 2021 - [info]  read_only=1 is not set on slave 10.0.0.53(10.0.0.53:3306).
Sun Mar  7 16:30:52 2021 - [info] Checking replication filtering settings..
Sun Mar  7 16:30:52 2021 - [info]  binlog_do_db= , binlog_ignore_db= 
Sun Mar  7 16:30:52 2021 - [info]  Replication filtering check ok.
Sun Mar  7 16:30:52 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Mar  7 16:30:52 2021 - [info] Checking SSH publickey authentication settings on the current master..
Sun Mar  7 16:30:53 2021 - [info] HealthCheck: SSH to 10.0.0.51 is reachable.
Sun Mar  7 16:30:53 2021 - [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)

Sun Mar  7 16:30:53 2021 - [info] Checking replication health on 10.0.0.52..
Sun Mar  7 16:30:53 2021 - [info]  ok.
Sun Mar  7 16:30:53 2021 - [info] Checking replication health on 10.0.0.53..
Sun Mar  7 16:30:53 2021 - [info]  ok.
Sun Mar  7 16:30:53 2021 - [warning] master_ip_failover_script is not defined.
Sun Mar  7 16:30:53 2021 - [warning] shutdown_script is not defined.
Sun Mar  7 16:30:53 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

⑧开启MHA(db03)

[root@db03 ~ 16:30:53]# 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 &
[1] 2423

⑨查看MHA状态

[root@db03 ~ 16:43:42]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2423) is running(0:PING_OK), master:10.0.0.51
[root@db03 ~ 16:44:28]#  mysql -umha -pmha -h 10.0.0.51 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 51    |
+---------------+-------+
[root@db03 ~ 16:48:58]#  mysql -umha -pmha -h 10.0.0.52 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 52    |
+---------------+-------+
[root@db03 ~ 16:49:30]#  mysql -umha -pmha -h 10.0.0.53 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 53    |
+---------------+-------+

5.MHA应用透明（vip功能）

[db03]
准备脚本

master_ip_failover_script=/usr/local/bin/master_ip_failover
注意：/usr/local/bin/master_ip_failover，必须事先准备好

修改脚本内容

vi  /usr/local/bin/master_ip_failover
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

yum install -y dos2unix
# 将脚本中的英文字符转换成中文字符
dos2unix /usr/local/bin/master_ip_failover
#给脚本加上执行权限
chmod +x /usr/local/bin/master_ip_failover  

修改manager配置文件

vi /etc/mha/app1.cnf
添加：
master_ip_failover_script=/usr/local/bin/master_ip_failover

[db01]
主库上，手工生成第一个vip地址

手工在主库上绑定vip，注意一定要和配置文件中的ethN一致，我的是eth0:1(1是key指定的值)
ifconfig eth0:1 10.0.0.55/24

db03:重启MHA

masterha_stop --conf=/etc/mha/app1.cnf
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 &

6.binlog server--数据的额外补偿（db03）

参数

binlogserver配置：
找一台额外的机器，必须要有5.6以上的版本，支持gtid并开启，我们直接用的第二个slave（db03）
vim /etc/mha/app1.cnf 
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog

创建必要目录

mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*
修改完成后，将主库binlog拉过来（从000001开始拉，之后的binlog会自动按顺序过来）

拉取主库binlog日志

cd /data/mysql/binlog     -----》必须进入到自己创建好的目录
mysqlbinlog  -R --host=10.0.0.51 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &
注意：
拉取日志的起点,需要按照目前从库的已经获取到的二进制日志点为起点

重启MHA

masterha_stop --conf=/etc/mha/app1.cnf
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 &

03，高可用架构故障模拟及处理

主库宕机，binlogserver 自动停掉，manager 也会自动停止。
处理思路：
1、重新获取新主库的binlog到binlogserver中
2、重新配置文件binlog server信息
3、最后再启动MHA

1.宕掉db01数据库

systemctl stop mysqld

2.恢复故障

①启动故障节点

systemctl start mysqld

②恢复1主2从

[root@db03 ~ 19:29:55]# grep "CHANGE MASTER TO" /var/log/mha/app1/manager
Sun Mar  7 19:26:13 2021 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

在db01上执行

db01 [(none)]>CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
Query OK, 0 rows affected, 2 warnings (0.09 sec)

db01 [(none)]>start slave;
Query OK, 0 rows affected (0.04 sec)

③恢复配置文件（db03）

vim /etc/mha/app1.cnf
[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

④恢复binlogserver

cd /data/mysql/binlog
rm -rf /data/mysql/binlog/*  # ？？？？
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &

⑤.启动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 &
[root@db03 ~ 19:53:00]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7543) is running(0:PING_OK), master:10.0.0.52