1500字范文 > DBLE分库分表中间件

DBLE分库分表中间件

时间：2021-03-21 10:49:14

相关推荐

DBLE分库分表中间件

1.dble和mycat分库分表中间件

1.1分库分表中间件原理

1.分析SQL语句2.根据SQL语义，将SQL拆分成多个，发送至数据节点3.将多个数据节点的结果聚集，返回客户端

1.2dble

1.高性能、高可用的MySQL分库分表中间件2.上号爱可生公司开发3.完全开源4.基于开源项目MyCat

1.3dble对MyCat做的增强

1.缺陷修复2.实现改进，插入语句语法增强，用户连接欸书增强3.功能增强，提升性能4.功能裁剪，去掉不用的算法

1.4dble的基本概念

1.schema：虚拟数据库（不同于传统的schema）2.shardingTable：虚拟表（被拆分的表）3.shardingNode：虚拟节点4.dbGroup：实际的Mysql集群5.database：实际的database

1.5dble表的类型

1.全局表：每个节点上有保存表的完整数据2.拆分表：被拆分的表，存入不同节点3.非拆分表：不拆分的表，存在单一节点

1.6dble节

1.dble是一个高性能、易用的分库分表中间件2.dble基于MyCat，并做了改进3.dble在功能上水平分表为主

2.dble最简运行环境

1.mysql A 和 mysql B 是两个独立的数据库互相不知道对方的存在

2.1安装两个mysql

5.7版本；

2.2安装java

1.8.x版本；

2.3安装dble

# 官网/# github 源码位置/actiontech/dble# 下载版本页面/actiontech/dble/releases# 3.21.10.3 版本/actiontech/dble/releases/download/3.21.10.3%2Ftag/dble-3.21.10.3-0331093302-java1.8.0_151-linux.tar.gz# 百度网盘链接：/s/1Pmw_KOzK9wTmnh9sP42tZA 提取码：4545 # lunix 下载wget /actiontech/dble/releases/download/3.21.10.3%2Ftag/dble-3.21.10.3-0331093302-java1.8.0_151-linux.tar.gz# 解压dao /var/lib 目录下tar -xvf dble-3.21.10.3-0331093302-java1.8.0_151-linux.tar.gz -C '/var/lib/'

2.4dble配置文件

# 进入dble配置目录cd /var/lib/dble/conf ll[root@localhost conf]# ll总用量 76-rw-rw-r--. 1 2000 2000 0 3月 31 17:32 f-rw-rw-r--. 1 2000 2000 5399 3月 31 17:32 f-rw-rw-r--. 1 2000 2000 304 3月 31 17:32 cacheservice.properties-rw-rw-r--. 1 2000 2000 720 3月 31 17:32 f-rw-rw-r--. 1 2000 2000 1203 3月 31 17:32 dbseq.sql-rw-rw-r--. 1 2000 2000 1553 3月 31 17:32 db_template.xml-rw-rw-r--. 1 2000 2000 631 3月 31 17:32 ehcache.xml-rw-rw-r--. 1 2000 2000 4648 3月 31 17:32 log4j2.xml-rw-rw-r--. 1 2000 2000 15 3月 31 17:32 partition-enum.txt-rw-rw-r--. 1 2000 2000 88 3月 31 17:32 partition-number-range.txt-rw-rw-r--. 1 2000 2000 108 3月 31 17:32 partition-pattern.txt-rw-rw-r--. 1 2000 2000 607 3月 31 17:32 sequence_conf.properties-rw-rw-r--. 1 2000 2000 267 3月 31 17:32 sequence_db_conf.properties-rw-rw-r--. 1 2000 2000 6810 3月 31 17:32 sharding_template.xml-rw-rw-r--. 1 2000 2000 6411 3月 31 17:32 template_table.sql-rw-rw-r--. 1 2000 2000 1809 3月 31 17:32 user_template.xml

2.5dble集群配置文件

# dble 集群配置文件 fvi f# Copyright (C) - ActionTech.# License: /licenses/gpl.html GPL version 2 or higher.#clusterEnable=false# cluster ucore/zkclusterMode=zk# zk: clusterIP=10.186.19.aa:2281,10.186.60.bb:2281clusterIP=10.186.61.132:2181# zk not need cluster.portclusterPort=5700rootPath=/dble#cluster namespace, please use the same one in one clusterclusterId=cluster-1# if HA need sync by cluster, only useful when useOuterHa=trueneedSyncHa=false# unit is millisecondshowBinlogStatusTimeout=60000sequenceHandlerType=2# valid for sequenceHandlerType=2 or 3#sequenceStartTime=-11-04 09:42:54# valid for sequenceHandlerType=3 and clusterMode is zk, default true#sequenceInstanceByZk=true# 复制配置文件去掉templatecp f f

2.f

# 和java相关配置，复制文件去掉templatecp f f

2.7db_template.xml

# 实际数据分片配置文件修改数据库实际地址cp db_template.xml db.xml # 核心配置文件<?xml version="1.0"?><dble:db xmlns:dble="http://dble.cloud/" version="4.0"><dbGroup name="dbGroup1" rwSplitMode="2" delayThreshold="100"><heartbeat>show slave status</heartbeat><dbInstance name="instanceM1" url="ip1:3306" user="your_user" password="your_psw" maxCon="1000" minCon="10"primary="true" readWeight="1" id="xx1"><property name="testOnCreate">true</property></dbInstance></dbGroup><dbGroup name="dbGroup2" rwSplitMode="0" delayThreshold="100" disableHA="true"><heartbeat errorRetryCount="1" timeout="10">show slave status</heartbeat><dbInstance name="instanceM2" url="ip2:3306" user="your_user" password="your_psw" maxCon="1000" minCon="10"primary="true"><property name="testOnCreate">true</property></dbInstance></dbGroup></dble:db>

2.7user_template.xml

cp user_template.xml user.xml<dble:user xmlns:dble="http://dble.cloud/" version="4.0"><managerUser name="man1" password="654321" maxCon="100"/><managerUser name="user" usingDecrypt="true" whiteIPs="127.0.0.1,0:0:0:0:0:0:0:1" readOnly="true"password="AqEkFEuIFAX6g2TJQnp4cJ2r7Yc0Z4/KBsZqKhT8qSz18Aj91e8lxO49BKQElC6OFfW4c38pCYa8QGFTub7pnw=="/><shardingUser name="root" password="123456" schemas="testdb" readOnly="false" blacklist="blacklist1" maxCon="20"/><shardingUser name="root2" password="123456" schemas="testdb,testdb2" maxCon="20" tenant="tenant1"><privileges check="true"><schema name="testdb" dml="0110"><table name="tb_global1" dml="0000"/><table name="tb_global2" dml="1111"/></schema></privileges></shardingUser><blacklist name="blacklist1"><property name="selelctAllow">true</property></blacklist></dble:user>

2.8sharding_template.xml

cp sharding_template.xml sharding.xml # 设置数据分片，虚拟数据库，虚拟数据表<?xml version="1.0"?><dble:sharding xmlns:dble="http://dble.cloud/" version="4.0"><schema name="testdb" sqlMaxLimit="100"><shardingTable name="tb_enum_sharding" shardingNode="dn1,dn2" sqlMaxLimit="200" function="func_enum" shardingColumn="code"/><shardingTable name="tb_range_sharding" shardingNode="dn1,dn2,dn3" function="func_range" shardingColumn="id"/><shardingTable name="tb_hash_sharding" shardingNode="dn1,dn2" function="func_common_hash" shardingColumn="id"/><shardingTable name="tb_hash_sharding_er1" shardingNode="dn1,dn2" function="func_common_hash" shardingColumn="id"/><shardingTable name="tb_hash_sharding_er2" shardingNode="dn1,dn2" function="func_common_hash" shardingColumn="id2"/><shardingTable name="tb_hash_sharding_er3" shardingNode="dn1,dn2" function="func_common_hash" shardingColumn="id" incrementColumn="id2"/><shardingTable name="tb_uneven_hash" shardingNode="dn1,dn2,dn3" function="func_uneven_hash" shardingColumn="id"/><shardingTable name="tb_mod" shardingNode="dn1,dn2,dn3,dn4" function="func_mod" shardingColumn="id" sqlRequiredSharding="true"/><shardingTable name="tb_jump_hash" shardingNode="dn1,dn2" function="func_jumpHash" shardingColumn="code"/><shardingTable name="tb_hash_string" shardingNode="dn1,dn2,dn3,dn4" function="func_hashString" shardingColumn="code"/><shardingTable name="tb_date" shardingNode="dn1,dn2,dn3,dn4" function="func_date" shardingColumn="create_date"/><shardingTable name="tb_pattern" shardingNode="dn1,dn2" function="func_pattern" shardingColumn="id"/><globalTable name="tb_global1" shardingNode="dn1,dn2" sqlMaxLimit="103" /><globalTable name="tb_global2" shardingNode="dn1,dn2,dn3,dn4" cron="0 0 0 * * ?" checkClass="CHECKSUM"/><singleTable name="tb_single" shardingNode="dn6" sqlMaxLimit="105"/><shardingTable name="tb_parent" shardingNode="dn1,dn2" function="func_common_hash" shardingColumn="id"><childTable name="tb_child1" joinColumn="child1_id" parentColumn="id" sqlMaxLimit="201"><childTable name="tb_grandson1" joinColumn="grandson1_id" parentColumn="child1_id"/><childTable name="tb_grandson2" joinColumn="grandson2_id" parentColumn="child1_id2"/></childTable><childTable name="tb_child2" joinColumn="child2_id" parentColumn="id"/><childTable name="tb_child3" joinColumn="child3_id" parentColumn="id2"/></shardingTable></schema><schema name="testdb2" shardingNode="dn5"/><shardingNode name="dn1" dbGroup="dbGroup1" database="db_1"/><shardingNode name="dn2" dbGroup="dbGroup2" database="db_2"/><shardingNode name="dn3" dbGroup="dbGroup1" database="db_3"/><shardingNode name="dn4" dbGroup="dbGroup2" database="db_4"/><shardingNode name="dn5" dbGroup="dbGroup1" database="db_5"/><shardingNode name="dn6" dbGroup="dbGroup2" database="db_6"/><function name="func_enum" class="Enum"><property name="mapFile">partition-enum.txt</property><property name="defaultNode">0</property><property name="type">0</property></function><function name="func_range" class="NumberRange"><property name="mapFile">partition-number-range.txt</property><property name="defaultNode">0</property></function><function name="func_common_hash" class="Hash"><property name="partitionCount">2</property><property name="partitionLength">512</property></function><function name="func_uneven_hash" class="Hash"><property name="partitionCount">2,1</property><property name="partitionLength">256,512</property></function><function name="func_mod" class="Hash"><property name="partitionCount">4</property><property name="partitionLength">1</property></function><function name="func_jumpHash" class="jumpStringHash"><property name="partitionCount">2</property><property name="hashSlice">0:2</property></function><function name="func_hashString" class="StringHash"><property name="partitionCount">4</property><property name="partitionLength">256</property><property name="hashSlice">0:2</property></function><function name="func_date" class="Date"><property name="dateFormat">yyyy-MM-dd</property><property name="sBeginDate">-01-01</property><property name="sEndDate">-01-31</property> <property name="sPartionDay">10</property><property name="defaultNode">0</property></function><function name="func_pattern" class="PatternRange"><property name="mapFile">partition-pattern.txt</property><property name="patternValue">1024</property><property name="defaultNode">0</property></function></dble:sharding>

2.9template_table.sql

# 示例表格所需要的建表语句-- testdb schema for template only-- @since -04-26-- @author yanhuqing666-- 水平分表案例use testdb;drop table if exists tb_enum_sharding;create table if not exists tb_enum_sharding (id int not null,code int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_enum_sharding values(1,10000,'1'),(2,10010,'2'),(3,10000,'3'),(4,10010,'4');drop table if exists tb_range_sharding;create table if not exists tb_range_sharding (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_range_sharding values(1,'1'),(5000001,'5000001'),(10000001,'10000001');drop table if exists tb_hash_sharding;create table if not exists tb_hash_sharding (id int not null,id2 int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_hash_sharding values(1,1,'1'),(2,2,'2'),(513,513,'513');drop table if exists tb_hash_sharding_er1;create table if not exists tb_hash_sharding_er1 (id int not null,id2 int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_hash_sharding_er1 values(1,1,'1'),(2,2,'2'),(513,513,'513');drop table if exists tb_hash_sharding_er2;create table if not exists tb_hash_sharding_er2 (id int not null,id2 bigint not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_hash_sharding_er2 values(1,1,'1'),(2,2,'2'),(513,513,'513');drop table if exists tb_hash_sharding_er3;create table if not exists tb_hash_sharding_er3 (id int not null,id2 bigint not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_hash_sharding_er3(id,content) values(1,'1'),(2,'2'),(513,'513');drop table if exists tb_uneven_hash;create table if not exists tb_uneven_hash (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_uneven_hash values(1,'1'),(257,'257'),(513,'513');drop table if exists tb_mod;create table if not exists tb_mod (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_mod values(1,'1'),(2,'2'),(3,'3'),(4,'4');drop table if exists tb_jump_hash;create table if not exists tb_jump_hash (id int not null,code varchar(250) not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_jump_hash values(1,'1','1'),(2,'2','2'),(3,'3','3'),(4,'4','4');drop table if exists tb_hash_string;create table if not exists tb_hash_string (id int not null,code varchar(250) not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_hash_string values(1,'1','1'),(2,'2','2'),(3,'3','3'),(4,'4','4');drop table if exists tb_date;create table if not exists tb_date (id int not null,code varchar(250) not null,create_date date not null,primary key(id))engine=innodb charset=utf8;insert into tb_date values(1,'1','-01-01'),(2,'2','-01-11'),(3,'3','-01-21');drop table if exists tb_pattern;create table if not exists tb_pattern (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_pattern values(1,'1'),(11,'11');-- global tablesdrop table if exists tb_global1;create table if not exists tb_global1 (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_global1 values(1,'1'),(2,'2');drop table if exists tb_global2;create table if not exists tb_global2 (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_global2 values(1,'1'),(2,'2');drop table if exists tb_single;create table if not exists tb_single (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_single values(1,'1'),(2,'2');drop table if exists tb_parent;create table if not exists tb_parent (id int not null,id2 int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_parent values(1,1,'1'),(2,2,'2'),(513,513,'513');drop table if exists tb_child1;create table if not exists tb_child1 (id int not null,child1_id int not null,child1_id2 int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_child1 values(1,1,1,'1');insert into tb_child1 values(2,2,2,'2');insert into tb_child1 values(513,513,513,'513');drop table if exists tb_grandson1;create table if not exists tb_grandson1 (id int not null,grandson1_id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_grandson1 values(1,1,'1');insert into tb_grandson1 values(2,2,'2');insert into tb_grandson1 values(513,513,'513');drop table if exists tb_grandson2;create table if not exists tb_grandson2 (id int not null,grandson2_id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_grandson2 values(1,1,'1');insert into tb_grandson2 values(2,2,'2');insert into tb_grandson2 values(513,513,'513');drop table if exists tb_child2;create table if not exists tb_child2 (id int not null,child2_id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_child2 values(1,1,'1');insert into tb_child2 values(2,2,'2');insert into tb_child2 values(513,513,'513');drop table if exists tb_child3;create table if not exists tb_child3 (id int not null,child3_id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_child3 values(1,1,'1');insert into tb_child3 values(2,2,'2');insert into tb_child3 values(513,513,'513');use testdb2;drop table if exists tb_test1;create table if not exists tb_test1 (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_test1 values(1,'1'),(2,'2');drop table if exists tb_test2;create table if not exists tb_test2 (id int not null,content varchar(250) not null,primary key(id))engine=innodb charset=utf8;insert into tb_test2 values(1,'1'),(2,'2');

3.dble运行

# 进入dble启动目录cd /var/lib/dble/bin# 启动/var/lib/dble/bin/dble start# 重启/var/lib/dble/bin/dble restartStarting dble-server...[root@localhost conf]# # 查看日志tail -f /var/lib/dble/logs/wrapper.log tail -f /var/lib/dble/logs/dble.log# 打开管理端接口 9066firewall-cmd --zone=public --add-port=9066/tcp --permanentfirewall-cmd --zone=public --add-port=8066/tcp --permanentfirewall-cmd --reload# 链接dble mysql客户端mysql -h 192.168.1.9 -uman1 -P9066 -p654321# 数据端口8066 数据节点用户名root/123456 mysql: [Warning] Using a password on the command line interface can be insecure.ERROR (HY000): Can't connect to MySQL server on '192.168.1.9' (113)[root@localhost ~]# mysql -h'192.168.1.9' -uman1 -P9006 -p654321mysql: [Warning] Using a password on the command line interface can be insecure.ERROR (HY000): Can't connect to MySQL server on '192.168.1.9' (113)[root@localhost ~]# mysql -h 192.168.1.9 -uman1 -P9006 -p654321mysql: [Warning] Using a password on the command line interface can be insecure.ERROR (HY000): Can't connect to MySQL server on '192.168.1.9' (113)[root@localhost ~]# mysql -h 192.168.1.9 -uman1 -P9066 -p654321mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.7.11-dble-3.21.10.3-7023a10895753d46ee8343cc337b1d5d66bf593c-0331093302 dble Server (ActionTech)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> # 创建数据库create database @@shardingnode='dn$1-6';# 登录dble服务端口mysql -h 192.168.1.9 -uroot -P8066 -p123456mysql> show databases;+----------+| DATABASE |+----------+| testdb |+----------+1 row in set (0.02 sec)mysql>

4.提高分库分表架构的可靠性

1.解决数据完整性，主从复制

5.使用dble进行读写分离

/1.使用dble进行读写分离2.dble分析sql将读发给从3.rwSplitMode=0 直接分发到主实例4.rwSplitMode=1 读操作必须在所有从实例中均衡5.rwSplitMode=2 读操作在所有实例中均衡6.rwSplitMode=3 读操作尽量在所有实例中均衡r7.rwSplitMode=3 读操作尽量在所有实例中均衡，有从走从，没有走主

6.配至两台主和一台从

略

7.配至读写分离

# 1.配置db.xml disabled="false" 改为false，true 是这个节点废弃了# rwSplitMode="3" 读操作尽量在所有实例中均衡，有从走从，没有走主<?xml version="1.0"?><dble:db xmlns:dble="http://dble.cloud/" version="4.0"><dbGroup name="dbGroup1" rwSplitMode="3" delayThreshold="100"><heartbeat>show slave status</heartbeat><dbInstance name="instanceM1" url="192.168.1.7:3306" user="root" password="admin123" maxCon="1000" minCon="10"primary="true" readWeight="1" id="xx1"><property name="testOnCreate">true</property></dbInstance><dbInstance name="instanceS1" url="192.168.1.10:3306" user="root" password="admin123" maxCon="1000" minCon="10" readWeight="2" disabled="false"<property name="testOnCreate">false</property></dbInstance></dbGroup><dbGroup name="dbGroup2" rwSplitMode="0" delayThreshold="100" disableHA="true"><heartbeat errorRetryCount="1" timeout="10">show slave status</heartbeat><dbInstance name="instanceM2" url="192.168.1.9:3306" user="root" password="admin123" maxCon="1000" minCon="10"primary="true"><property name="testOnCreate">true</property></dbInstance></dbGroup></dble:db># 配置好重启/var/lib/dble/bin/dble restart# 记录sql语句去实际节点执行命令SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'on';# 查看sql语句，去实际节点执行SELECT * from mysql.general_log ORDER BY envent_time desc;

8.分库分表之后性能反而下降，怎么办。

1.查询语句where 条件中尽可能带有拆分字段2.插入语句必须带有拆分字段，根据拆分字段具体往哪个分库插入3.若使用in子句，缩减in子句值的数量4.减少表的搜索遍历5.减少结果集。6.跨节点连表7.尽量使用拆分字段作为join条件8.尽量少使用跨界点排序，分页功能9.复杂的语句拆分成多条语句数据crud尽量带拆分字段，尽量减少数据节点之间的数据交换，尽量将连接键作为拆分字段

9.小结

1.分区表提升单节点表容量2.分库分表：提升数据库性能，使用数据方法更复杂数据丢失的可能行增加3.dble数据库中间件，水平分库、分表，读写分离

10.主从切换

10.1切换保业务还是保数据

1.将已经宕机的主数据库，切换到从数据库2.停止备库同步3.配置主库复制从库，身份切换

10.2可靠性数据优先策略

1.检查从库seconds_behind_master ,时间落后A库多少时间时间2.落后时间太长,主库开启只读readonly=true3.检查seconds_behind_master==04.等于0，从库关只读readonly=false5.从库停止复制主库，主库开始复制从库，身份切换6.可以保证数据五丢失，但是有几秒的时间两个数据库均不可写7.一开始未检查seconds_behind_master，不可用时间无法控制

10.3可用性优先策略

1.取消等待数据一致的过程2.主库只读、从库关只读3.从库停止复制主库，主库开始复制从库4.系统没有不可写的时间5.有可能造成数据不一致错误

10.4数据库切换了，业务怎么连接备库

1.业务预留接口，通知新的数据库切换至新地址2.使用微服务业务通知3.内部搭建（coreDNS）DNS服务其，通知域名连接4.vip漂移，使用keepalived进行vip漂移5.使用代理，负载均衡器代理HaProxy、内网穿透6.使用dble修改数据库配置文件，没方法探测数据库死活

10.5小结

1.业务切换至新地址：不影响性能，业务可能不支持2.使用内部DNS：方便，需要多余硬件资源3.vip漂移：自动漂移，需要多余的ip资源4.使用代理：自动更新，需要多余资源5.dble：客户无感知，需要被动通知

11.如何实现自动主从切换

11.1keepalived

1.常用的高可用组件2.可以检测节点状态3.自动执行切换脚本4.还有VIP漂移功能

11.2MHA（Master High Availability）

1.常用的MySQL高可用组件2.支持GTID方式3.从宕机崩溃的master抢救未传送的binlog4.等待从库执行中继日志，追赶Master5.在从库执行从主库Master抢救出的binlog6.从库和主库宕机前状态一样7.提升从库为主库8.提升从从库为从库，复制新的主库9.修复好数据库宕机，再重新将配置好的主从关系配置到MHA

11.3研高可用组件

1.完全自主控制2.研发代价高

11.4总结

1.keepalived可以自动切换身份，不完善2.MHA比较完善的MySQL自动身份切换工具3.有更高级的MySql管理需求，可以二次开发MHA或者自己的语言

12.MHA实战

12.1架构图

12.2github地址

# 主页地址/yoshinorim# 管理程序/yoshinorim/mha4mysql-manager# 预置到各个节点/yoshinorim/mha4mysql-node# 节点程序下载链接/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm# 管理程序下载链接/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

12.3安装mha4mysql-node

# 主从都要做rpm -ivh mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm# perl-DBD-MySQL 脚本语言yum install perl-DBD-MySQL -y# 依赖库yum install epel-release -y# 清理yum缓存yum clean all# 重新拉yum源yum makecache# 安装mha4mysql-node-0.58-0.el7rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

12.4安装mha4mysql-manager

# 上面不安装mha4mysql-node-0.58-0.el7的基础+yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

12.5让所有的服务器登录都免密

vim /etc/ssh/sshd_configPermitRootLogin yes# 允许root账户在远程登陆PubkeyAuthentication yes

12.6配置MHA本身

vim /etc/f# 检查主从之间免密连接sudo masterha_check_ssh --conf=/etc/fsuccessfull# 检查主备复制是否正常sudo masterha_check_repl --conf=/etc/fsuccessfull# 检查现在MHA工作状态sudo masterha_check_status --conf=/etc/f# 启动MHAsudo masterha_manager --conf=/etc/f &# 查看日志,不停查看主节点状态。手动停止主库。查看日志状态。tail -f /var/log/masterha/app1/app1.log# 进入从库查看从库状态show slave status\G;

12.7MHA与DBLE联动

1.配置MHA使用脚本通知dble

12.8高可用了。集群为什么还会挂

1.dble单点问题可能造成数据库系统无法使用2.使用zookeeper存储数据库信息、表信息、分片信息3.MHA只能配置一个dble，使用SLB/Haproxy高可用4.keepalived ip漂移

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。