MySQL高可用架构--MHA

MySQL高可用架构--MHA

主从复制架构演变

6.1 普通架构（读多写少）

6.1.1 1主1从，1主多从

6.1.2 多级主从

6.1.3 双主结构 (MMM (过时了)，Mycat，DBLE)

6.1.4 环装复制

6.2 高可用架构

6.2.1 MHA 高可用环境 (重点讲解)

6.2.2 组复制 (MGR PXC G cluster) ，针对数据一致性的解决方案 (自己了解)

6.2.3 Innodb Cluster ,NDB Cluster

6.3 读写分离

atlas

6.4 分布式

Mycat

DRDS

1. 铺垫

1、监控

2、选主

3、数据补偿

4、FAILOVER(故障切换)

5、应用透明(vip)

6、故障提醒

7、架构前提

(1) 三个物理节点

(2) 1主2从结构

(必须是1主2从以上，所有数据库必须独立存在，master: db01 slave: db02 db03 )

注: 淘宝有能力做二次开发，TMHA架构，只需要两个节点

2. MHA 高可用软件构成

Manager软件: 选择任意一个节点安装，只需要装一个

1、监控问题? 主机、mysql实例

2、处理问题，需要人为

3、数据补偿---->GTID

Node软件: 所有节点都要安装

3. MHA manager的工作过程

(1) Manger要能够监控到所有node节点 (通信、MySOL实例状态)，需要有一个专门连接node节点的数

据库.

(2) 主库岩机

(2.1)选择一个新的主库做为代替原主库工作(原则上是选择最接近主库数据状态的从库)

>show slave status \G

Retrieved Gtid Set: 8c49d7ec-7e78-11e8-9638-000c29ca725d:1-3

(2.2) 如果，主库的SSH是可以通信的，从库会立即保存所有缺失的事务

mysqlbinlog -------------> /usr/bin/mysqlbinlog--->ln -s

scp 从节点---------------->各个节点之间建立互信关系

(2.3) 如果SSH不到主库，计算S1和S2之间的relay-log的差异，恢复到S2中

(3) S2使用 Change master to s1 重新构建主从

(4) MHA提供了额外的开发接口，和调用方式。

VIP

Mail

binlogserver

(5) 这个切换过程完成之后，Manager会立即退出，需要管理员，恢复所有节点正常后，再次启动才可以

继续工作.

4. MHA软件构成

Manager工具包主要包括以下几个工具:

masterha_check_ssh 检查MHA的SSH配置状况

masterha_check_repl 检查MySQL复制状况

masterha_check_status 检测当前MHA运行状态

masterha_manger 启动MHA

masterha_master_monitor 检测master是否宕机

masterha_master_switch 控制故障转移(自动或者手动)

masterha_conf_host 添加或删除配置的server信息

Node工具包(这些工具通常由MHA Manager的脚本触发，无需人为操作)主要包括以下几个工具:

save_binary_logs 保存和复制master的二进制日志

apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的

slave filter_mysglbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)

purge_relay_logs 清除中继日志(不会阻塞SQL线程)

5. MHA搭建过程

1. 准备环境

略 1主2从GTID

2. 设置从库relay的自动删除功能，从库设置只读

>set global relay_log purge = 0; //临时关闭relaylog(建议三个节点都做)

//从库设为只读，slave: db02和db03

set global read_only=1;

//永久设置需要加入配置文件

vim /etc/my.cnf

relay_log_purge = 0 //永久，在配置文件，建议在三个节点都做

set global read only=1; //只在两个从库中设置

3. 配置关键程序软连接（必须要设置，所有节点都要做）

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

In -s /application/mysql/bin/mysql /usr/bin/mysql

4. 配置各节点互信(ssh下发公钥到各节点)

db01:

# rm -rf /root/.ssh

# ssh -keygen

# cd /root/.ssh

# mv id_rsa.pub authorized_keys

# scp /root/.ssh 10.0.0.52:/root

# scp /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

2. 安装软件

1. 下载mha软件

mha官网: https://code.google.com/archive/p/mysql-master-ha/

github下载地址: https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

2. 上传MHA.zip到所有节点的/server/tools中，并解压

3. 安装Node软件依赖包

#安装依赖包

#yum install perl-DBD-MySQL -y

4. 所有节点安装node软件

#rpm -ivh mha4mysql-node-0.57-0.el7.noarch .rpm

5. 在db01主库中创建mha需要的用户

>grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

6. Manager软件安装 (db03上最安全)

//安装manager依赖包

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

//安装manager包

[root@db03 tools]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

3. 配置文件准备(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/mysql/ //主库二进制日志位置

user=mha

password=mha

ping_interval=2 //ping的时间间隔

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

4. 状态检查(db03)

(1) 互信检查

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

(2) 主从状态

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

5. 开启MHA

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

6. 查看MHA状态

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

//查看各节点信息

mysgl -umha -pmha -h 10.0.0.51 -e "show variables like 'server_id'"

mysql -umha -pmha -h 10.0.0.52 -e "show variables like 'server_id'"

mysgl -umha -pmha -h 10.0.0.53 -e "show variables like 'server_id'"

6. MHA模拟故障

down掉主库，看是否切换

---

8. 恢复MHA状态

(1) 修好db01，并启动它

(2) 加入到主从复制中，以db02为主库

//找到manager文件，找到下面内容

#vim manager

CHANGE MASTER TO

MASTER_HOST='10.0.0.52',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1,

MASTER_USER='repl',

MASTER_PASSWORD='123';

//在db01上做上面的语句

>CHANGE MASTER TO

MASTER_HOST='10.0.0.52',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1,

MASTER_USER='repl',

MASTER_PASSWORD='123';

>start slave;

//查看db01的状态，并作为从库指向新主库db02

>show slave status \G;

(3) 将修好的节点信息加入配置文件

[server1]

hostname=10.0.0.51

port=3306

(4) 启动MHA

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

(5) 查看MHA状态

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

---

7. MHA工作原理

MHA组成：

MHA由node和manager组成；

node:

就是所有数据库都需要部署的， master是一个node，slave也是一个node; 相当于监控客户端；

manager:

Manager相当server，它会对node进行管理，配置检查，binlog和relay log的获取，执行切换任务等； 可以单独部署在server上(推荐)，单独部署可以管理多套主从架构。也可以部署在某一个slave上； 如果部署在slave上，那么该slave就无法 被升级为主库；

MHA切换主要涉及下面几个步骤：

1. MHA每隔3秒钟探测一次MASTER； （Hi, 你还活着吗？）

ping_interval 控制间隔时间；

ping_type 控制探测方式，SELECT（执行SELECT 1）和CONNECT（创建连接/断开连接）

2. 如果manager探测到MASTER 故障，无法访问，Manager会执行下面操作：

从其他node发起，检查主库是否能够SSH上去；

从其他node发起连接，检查MASTER实例是否能够登陆；

3. 如果所有Node均连接失败，则开始故障转移，步骤如下：

Phase 1 Configuration Check Phase..

检查数据库版本

检查是否启用GTID

检查从库是否存活

检查配置文件的candidate

Phase 2 Dead Master Shutdown Phase.

该阶段会调用master_ip_failover脚本；去关闭所有Node的IO Thread

调用shutdown_script 强制关闭MASTER实例，防止应用程序来连接；

Phase 3 Master Recovery Phase.. 　

Phase 3.1 Getting Latest Slaves Phase.

检查所有节点，从show slave status中对比获取最新的binlog/position

Phase 3.2 Saving Dead Master's Binlog Phase..

如果老的Master可以SSH，上去获取BINLOG，从position到END位置，获取这段

BINLOG(MASETER产生这段BINLOG，还未来得及发送给SLAVE）将这部分日志发送给

Manager节点(manager_workdir位置)；

如果故障Master无法SSH，则无法获取这段日志

Phase 3.3 Determining New Master Phase..

对比所有SLAVE，从最新SALVE中同步差异realylog给其他slave;最终确保所有SLAVE数据一

致

Phase 3.4 New Master Diff Log Generation Phase..

确认新master 是否为最新slave，如果不是，则从最新slave获取差异日志；

将manager上获取的BINLOG日志发送给new master；

Phase 3.5 Master Log Apply Phase..

对比新master的Exec_Master_Log_Pos和Read_Master_Log_Pos，判断恢复的位置；

在本地回放 3.3 Phase的差异日志；

获取新master的binlog和position；

Phase 4 Slaves Recovery Phase.. 　

Phase 4.1 Starting Parallel Slave Diff Log Generation Phase.

对每个SLAVE恢复：所有SLAVE和最新Slave做对比，如果position不一致，则生产差异日志

Phase 4.2 Starting Parallel Slave Log Apply Phase.

每个SLAVE 应用差异日志；

执行CHANGE MASTER 挂在到新Master

Phase 5 New master cleanup phase..

reset slave all;

----------------------------------------------------------------------

[老师讲的原理]

13.MHA 画图说明

13.1 MHA工作前提

(1) 1主2从的gtid复制环境

(2)所有节点互信

(3) manager配置文件中，需要添加所有节点信息

13.2 manager工作过程

(1)监控所有节点

系统层面:

网络是否ping通 ping 10.0.0.51 52 53

ssh的连通性 ssh 10.0.0.51 date 52 53

数据库层面:

数据库实例是否启动: mysqladmin -umha -pmha -h10.0.0.51 ping

主从状态 : show slave status \G

并把所有信息，记录到日志中

(2) 如果主库发生故障

1.db01 能够ssh上

2.db01 不能ssh上

(3) 选主

算法1 (优先级第二)

判断从库和主库的差距:

基于从库show slave status\G (gtid或者Position号)判断哪个从库更新

算法2 (优先级第三)

如果从库日志一样新，默认按照配置文件的书写顺序选择新主库

算法3 (优先级第一): 自定制配置，强制选择新主

(4) 数据补偿

1.ssh能连接上

1.1 查看从库gtid或position

show slave status G

1.2 查看主库的最后binlog文件的gtid或position

1.3 截取缺失部分的binlog日志。

1.4 scp到各个从节点，进行恢复

2.ssh连接不上

2.1 判断两个从库日志是否一致，

2.1.1 如果一致，则不需要数据补偿

2.1.2 如果不一致，通过apply_diff_relay_logs，进行补偿即可。

(5) Failover(masterha master switch)

1. 将新主库，解除从库身份

stop slave;

reset slave all;

2. 将剩余从库，change master to 到新主库

(6) 临终前操作

1. 清理故障主机配置信息 (masterha_conf_host)

2.manager自杀

---

8. Manager额外参数介绍

(1) ping_interval=1

#设置监控主库，发送ping包的时间间隔，尝试三次没有回应的时候自动进行failover

(2) candidate_master=1

#设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不

是集群中事件最新的slave

(3) check_repl_delay=0

#默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

9. MHA 的vip功能 (应用透明)

1. 参数:

master_ip_failover_script=/usr/local/bin/master_ip_failover

2. 注意:

/usr/local/bin/master_ip_failover，必须事先准备好此脚本

3. 将script.tar.gz 文件上传到/usr/local/bin，并解压

4. 修改脚本内容:

vim /usr/local/bin/master_ip_failover

my $vip ='10.0.0.55/24';

my $key ='0';

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";

my $ssh_stop_vip ="/sbin/ifconfig eth0:$key down";

5. 更改manager配置文件:

vim /etc/mha/app1.cnf

添加:

master_ip_failover_script=/usr/local/bin/master_ip_failover

6. 主库上，手工生成第一个vip地址

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

ifconfig eth0: 10.0.0.55/24

7. 重启mha

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

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

+++++++++++报错++++++++++

原因: master_ip_failover中有中文字符

处理方法:

yum install -y dos2unix

dos2unix master_ip_failover

再次启动MHA即可

---------------------------------------------------------------------

8. 切换测试:

停主库，看vip是否漂移

db02

/etc/init.d/mysqld stop

9. 处理故障(db02)

/etc/init.d/mysgld start

CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_PORT=3306，MASTER_AUTO_POSITION=1, MASTER_USER='repl',MASTER_PASSWORD='123';

start slave;

10. 修改manager配置文件，添加db02节点信息

vim /etc/mha/app1.cnf

[server2]

hostname=10.0..52

port=3306

11. 启动MHA

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

10. 邮件提醒 (微信提醒)

1. 参数:

report_script=/usr/local//bin/send

2. 准备邮件脚本

send_report

(1)准备发邮件的脚本(我们已经为大家准备好了script.tar.gz)

将以上时本解压到 /usr/local/bin

(2)将准备好的脚本添加到mha配置文件中，让其调用

3. 修改manager配置文件，调用邮件脚本

vim /etc/mha/appl.cnf

report_script=/usr/local/bin/send

(1) 停止MHA

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

(2) 开启MHA

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --

ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

(3) 关闭主库，看警告邮件

//故障修复:

1. 恢复故障节点

(1) 实例宕掉

/etc/init.d/mysqld start

(2) 主机损坏，有可能数据也损坏了

备份并恢复故障节点。

2.恢复主从环境

看日志文件:

CHANGE MASTER TO MASTER_HOST='10.0.0.52',MASTER_PORT-3306,

MASTER_AUTO_PSITION=1, MASTER_USER='repl',MASTER_PASSWORD='123';

start slave;

3.恢复manager

(1) 修好的故障节点配置信息，加入到配置文件

[server1]

hostname=10.0.0.51

port=3306

(2) 启动manager

(3) 开启MHA

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --

ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

11. binlog server (db03)

1. 参数:

binlogserver配置:

找一台额外的机器，必须要有5.6以上的版本，支持gtid并开启，我们直接用的第二个slave (db03)

vim /etc/mha/appl.cnf

[binlog1]

no_master=1

hostname=10.0.0.53

master_binlog_dir=/data/mysql/binlog

2. 创建必要目录

提前创建好，这个目录不能和原有的binlog一致

mkdir -p /data/mysql/binlog

chown -R mysql.mysql /data/*

修改完成后，将主库binlog拉过来(从000001开始拉，之后的binlog会自动按顺序过来)

3. 拉取主库binlog日志

cd /data/mysql/binlog --->必须进入到自己创建好的目录

mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &

注意:

binlog拉取和mha本身没啥关系，但是mha配置文件中加入了binlogserver，必须mha启动之前要去配置执

行，否则mha起不来

4. 重启MHA

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

nohup masterha_manager --conf=/etc/mha/appl.cnf --remove_dead_master_conf --

ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

5. 故障处理

主库岩机，binlogserver 自动停掉，manager 也会自动停止。

处理思路:

1. 重新获取新主库的binlog到binlogserver中

2. 重新配置文件binlog server信息

3. 最后再启动MHA

---

MySQL读写分离架构

1. 读写分离

1.1 理念:

主库只负责写操作

从库负责所有读操作

1.2 读写分离方案

(1) 直接在应用代码中加入判断

(2) 在应用端和数据库端中间，架设一个“拦截器” (“路由器”)，统称的为“中间件”

1.3 拦截器应当具备的功能

(1) 需要提供连接协议

(2) 需要具备SQL层的语法语义检查功能

(3) 需要具备监控后端节点功能《节点是否在线，节点的角色)

屏蔽故障节点

(4) 对于只读库，需要具备负载均衡的功能。

1.4 读写分离产品介绍

上课讲解:

altas

mycat

自己研究:

mysql router

proxySQL（perconna）

源码 Github: https://github.com/Qihoo360/Atlas

2. Atlas

1. Atlas软件介绍

Atlas (讲)

Atlas-sharding (介绍)

2. 软件功能

读写分离

从库负载均衡

自动分表

IP过滤

SQL语句黑白名单

DBA可平滑上下线DB

自动摘除宕机的DB

3. 软件使用

(1) 下载地址: https://github.com/Qihoo360/Atlas/releases

注意:

1、Atlas只能安装运行在64位的系统上

2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm, Centos 6.X安装Atlas-XX.el6.x86_64.rpm

3、后端mysql版本应大于5.1，建议使用Mysql 5.6以上

(2) 安装

rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

(3) 配置文件使用

cd /usr/local/mysql-proxy/conf/

mv test .cnf test .cnf.bak

vim test.cnf

[mysql-proxy]

admin-username = user

admin-password = pwd

proxy-backend-addresses = 10.0.0.55:3306

proxy-read-only-backend-addresses = 10.0.0.51:3306,10.0.0.53:3306

pwds = repl:3yb5jEku5h4=,mha:02jBXONX098=

daemon = true

keepalive = true

event-threads = 8

log-level = message

log-path = /usr/local/mysql-proxy/log

sgl-log=ON

proxy-address = 0.0.0.0:33060

admin-address =0.0.0.0:2345

charset=utf8

(4) 启动atlas

启动:

/usr/local/mysql-proxy/bin/mysql-proxyd test start

ps -ef | grep proxy

(5) Atlas功能测试

测试读操作:

mysql -umha -pmha -h 10.0.0.53 -P 33060

show variables like 'server_id'

//测试写操作:

mysql> begin;show variables like 'server_id';commit;

/usr/local/mysql-proxy/bin/encrypt 123 ---->制作加密密码

4. Atlas基本管理

连接管理接口:

mysql -uuser -ppwd -h127.0.0.1 -P2345

打印帮助:

mysql> select * from help;

查询后端所有节点信息:

mysql> SELECT * FROM backends;

动态添加删除节点:

REMOVE BACKEND 3;

动态添加节点:

ADD SLAVE 10.0.0.53:3306:

保存配置到配置文件

SAVE CONFIG;

5. 自动分表(了解)**

自动分表

使用Atlas的分表功能时，首先需要在配置文件test.cnf设置tables参数

tables参数设置格式: 数据库名.表名.分表字段.子表数量

比如:

你的数据库名叫school，表名叫stu，分表字段叫id，总共分为2张表

那么就写为school.stu.id.2，如果还有其他的分表，以逗号分隔即可。

用户需要手动建立2张子表 (stu_0,stu_1，注意子表序号是从0开始的)

所有的子表必须在DB的同一个database里。

当通过AtLas执行 (SELECT、DELETE、UPDATE、INSERT、REPLACE) 操作时，Atlas会根据分表结果 (id%2=k)，定位到相应的子表stu_k)。例如，执行select * from stu where id=3;，Atlas会自动从stu_1这张子表返回查询结果。但如果执行SQL语句 (Select * from stu;)时不带上id，则会提示执行stu 表不存在。

Atlas暂不支持自动建表和跨库分表的功能。

Atlas目前支持分表的语句有SELECT、DELETE、UPDATE、INSERT、REPLACE

什么是分表?

表达到800w行我们考虑采用分表的策略

基本分表策略

1、分区 (partition)

2、atlas 自动分表

tables参数设置格式: 数据库名.表名.分表字段.子表数量

分表的思路:

(1) 预估分表的个数，例如3个 (school库下的stu表)

(2) 准备分表，创建3个分表，并且和原表结构，命名规范是stu_0,_1,_2

create table stu_0 like stu;

create table stu_1 like stu;

create table stu_2 like stu;

(3) 配置文件中开启分表规则

tables = school.stu.id.3

(4) 测试分表的功能

------------------------------------------------------

操作:

mysql -umha -pmha -h 10.0.0.53 -P 33060

原始结构:

create database school;

use school;

create table stu (id int ,name varchar(20));

创建分表

create table stu_0 like stu;

create table stu_1 like stu;

create table stu_2 like stu;

开启分表规则:

vim /usr/local/mysql-proxy/conf/test.cnf

添加以下配置:

tables = school.stu.id.3

重启atlas:

/usr/local/mysql-proxy/bin/mysql-proxyd test restart

测试分表功能:

mysql -uapp -p123 -h 10.0.0.53 -P 33060

use school

insert into stu values(1,'zhang3');

insert into stu(id,name) values (11,'zhang3');

insert into stu(id,name) values (12,'zhang3');

insert into stu(id,name) values (13,"zhang3');

1.10 ip过滤 (白名单)

client-ips=192.168.1.2，192.168.2.0(网段)

1.11 SOL黑名单

Atlas会屏蔽不带where条件的delete和update操作，以及sleep函数。

1.12 atlas-sharding

(1) 不能跨分片访问数据

(2) 不能跨分片join

(3) 在添加新分片时，只能实在range分片的方式下才能添加节点