mysql架构怎么设置,mysql怎么设计

Mysql - 架构及常用组件功能

mysql底层架构分为:

创新互联是一家专注于成都做网站、成都网站建设与策划设计,金东网站建设哪家好?创新互联做网站,专注于网站建设十多年,网设计领域的专业建站公司;建站业务涵盖:金东等地区。金东做网站价格咨询:13518219792

1、client(客户端)

2、server(服务端)

client: 主要有各种plugin、jdbc等

server: 包含了连接器、查询缓存、分析器、优化器、执行器、存储引擎

连接器 的主要作用是与 客户端 建立联系,管理客户端的连接、会话、权限验证等。

查询缓存 的作用是，在sql通过连接器之后到达服务端之后,如果sql是sel开头的语句，那么先在 查询缓存 中获取命中结果，如果有命中结果则直接返回结果。没有结果那么sql会通往 分析器 。

分析器 拿到sql后,会对sql进行词法、语法分析，同时创建sql Id,如果sql有错误,那么将会终止sql行为，将异常返回客户端。

优化器 的作用主要是对通过 分析器 的sql进行优化,比如进行 索引选择 、 重写查询 等,同时会创建 sql执行计划 ,可以通过 explain 指令进行查看。

执行器 拿到了经过优化器的sql,将会操作 存储引擎 ,通过调用 存储引擎 提供的读写接口,得到返回结果。

存储引擎 是sql的最终执行者,它对外提供了读写接口,本身主要作用为执行sql、存储数据、获取数据等， 存储引擎 的设计是插件形式实现的,常见了有 InnoDB 、 MyISAM 等。

未完待续......

MySQL Mycat 分布式架构

参考：

图中是两组分片，红色我们称为shard1，蓝色我们称为shard2

51 52是服务器

两个3307互为主从(双主)，3309是本地3307的从库

说明：没有明确说明是只在某一个节点上做的，就是两个节点都做

两台虚拟机 db01 db02

每台创建四个mysql实例：3307 3308 3309 3310

mysql软件我们之前已完成二进制安装，直接初始化即可

我们server-id规划为：db01上是7/8/9/10,db02上是17/18/19/20

"箭头指向谁是主库"

10.0.0.51:3307 ----- 10.0.0.52:3307

10.0.0.51:3309 ------ 10.0.0.51:3307

10.0.0.52:3309 ------ 10.0.0.52:3307

两个分片，每个分片四个mysql节点

shard1：

Master：10.0.0.51:3307

slave1：10.0.0.51:3309

Standby Master：10.0.0.52:3307

slave2：10.0.0.52:3309

shard2：

Master：10.0.0.52:3308

slave1：10.0.0.52:3310

Standby Master：10.0.0.51:3308

slave2：10.0.0.51:3310

shard1

10.0.0.51:3307 ----- 10.0.0.52:3307

db02

db01

db02

10.0.0.51:3309 ------ 10.0.0.51:3307

db01

10.0.0.52:3309 ------ 10.0.0.52:3307

db02

shard2

10.0.0.52:3308 ----- 10.0.0.51:3308

db01

db02

db01

10.0.0.52:3310 ----- 10.0.0.52:3308

db02

10.0.0.51:3310 ----- 10.0.0.51:3308

db01

这个复制用户在谁上建都行

注：如果中间出现错误，在每个节点进行执行以下命令

常见方案：

360 Atlas-Sharding 360

Alibaba cobar 阿里

Mycat 开源

TDDL 淘宝

Heisenberg 百度

Oceanus 58同城

Vitess 谷歌

OneProxy

DRDS 阿里云

我们装的是openjdk，不是官方的那个

Mycat-server-xxxxx.linux.tar.gz

配置环境变量

我们mycat的命令也是在bin目录下

启动

8066就是对外提供服务的端口，9066是管理端口

连接mycat：

默认123456

db01:

我们一般先把原schema.xml备份，然后自己新写一个：

xml和html看起来差不多，xml是从下往上调用的

前三行我们不用看，直接从第四行schema开始看起：

定义了schema，然后以/schema结尾

为什么要用逻辑库？

业务透明化

此配置文件就是实现读写分离的配置

重启mycat

读写分离测试

总结：

以上案例实现了1主1从的读写分离功能，写操作落到主库，读操作落到从库.如果主库宕机，从库不能在继续提供服务了。

我们推荐这种架构

一写三读，

不设置双写的原因是：性能没提升多少，反而引起主键冲突的情况

配置文件：

之后重启：mycat restart

真正的 writehost：负责写操作的writehost

standby writeHost ：和readhost一样，只提供读服务

我们此处写了两个writehost，默认使用第一个

当写节点宕机后，后面跟的readhost也不提供服务，这时候standby的writehost就提供写服务，

后面跟的readhost提供读服务

测试：

读写分离测试

对db01 3307节点进行关闭和启动,测试读写操作

结果应为另一台(52)的3307(17)是写，3309(19)是读

一旦7号节点恢复，此时因为7落后了，写节点仍是17

balance属性

负载均衡类型，目前的取值有3种：

writeType属性

负载均衡类型，目前的取值有2种：

switchType属性

-1 表示不自动切换

1 默认值，自动切换

2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status

datahost其他配置

dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"

maxCon="1000"：最大的并发连接数

minCon="10" ：mycat在启动之后，会在后端节点上自动开启的连接线程，长连接，好处是连接速度快，弊端是占内存

tempReadHostAvailable="1"

这个一主一从时（1个writehost，1个readhost时），可以开启这个参数，如果2个writehost，2个readhost时

heartbeatselect user()/heartbeat 监测心跳

其他参数sqlMaxLimit自动分页,必须在启用分表的情况下才生效

创建测试库和表:

我们重启mycat后连接到8066

发现跟一个库一样，实际上已经分到不同的物理硬件上了

分片：对一个"bigtable"，比如说t3表

热点数据表 核心表

(1)行数非常多，800w下坡

(2)访问非常频繁

分片的目的：

（1）将大数据量进行分布存储

（2）提供均衡的访问路由

分片策略：

范围 range 800w 1-400w 400w01-800w 不适用于业务访问不均匀的情况

取模 mod (取余数) 和节点的数量进行取模

枚举 按枚举的种类分，如移动项目按省份分

哈希 hash

时间 流水

优化关联查询（否则join的表在不同分片上，效率会比单库还要低）

全局表

ER分片

案例：移动统一：先拆出边缘业务，再按地域分片，但对应用来说是统一的

vim rule.xml

tableRule name="auto-sharding-long"

rule

columnsid/columns

algorithmrang-long/algorithm

/rule

function name="rang-long"

class="io.mycat.route.function.AutoPartitionByLong"

property name="mapFile"autopartition-long.txt/property

/function

===================================

vim autopartition-long.txt

0-10=0

11-20=1

创建测试表：

mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

测试：

重启mycat

mycat restart

mysql -uroot -p123456 -h 127.0.0.1 -P 8066

insert into t3(id,name) values(1,'a');

insert into t3(id,name) values(2,'b');

insert into t3(id,name) values(3,'c');

insert into t3(id,name) values(4,'d');

insert into t3(id,name) values(11,'aa');

insert into t3(id,name) values(12,'bb');

insert into t3(id,name) values(13,'cc');

insert into t3(id,name) values(14,'dd');

取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点

vim schema.xml

table name="t4" dataNode="sh1,sh2" rule="mod-long" /

vim rule.xml

property name="count"2/property

准备测试环境

创建测试表：

mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

测试：

mysql -uroot -p123456 -h10.0.0.52 -P8066

use TESTDB

insert into t4(id,name) values(1,'a');

insert into t4(id,name) values(2,'b');

insert into t4(id,name) values(3,'c');

insert into t4(id,name) values(4,'d');

分别登录后端节点查询数据

mysql -S /data/3307/mysql.sock

use taobao

select * from t4;

mysql -S /data/3308/mysql.sock

use taobao

select * from t4;

t5 表

id name telnum

1 bj 1212

2 sh 22222

3 bj 3333

4 sh 44444

5 bj 5555

sharding-by-intfile

vim schema.xml

table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /

vim rule.xml

tableRule name="sharding-by-intfile"

rule columnsname/columns

algorithmhash-int/algorithm

/rule

/tableRule

function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"

property name="mapFile"partition-hash-int.txt/property

property name="type"1/property

property name="defaultNode"0/property

/function

partition-hash-int.txt 配置：

bj=0

sh=1

DEFAULT_NODE=1

columns 标识将要分片的表字段，algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称

准备测试环境

mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

mysql -uroot -p123456 -h10.0.0.51 -P8066

use TESTDB

insert into t5(id,name) values(1,'bj');

insert into t5(id,name) values(2,'sh');

insert into t5(id,name) values(3,'bj');

insert into t5(id,name) values(4,'sh');

insert into t5(id,name) values(5,'tj');

a b c d

join

t

select t1.name ,t.x from t1

join t

select t2.name ,t.x from t2

join t

select t3.name ,t.x from t3

join t

使用场景：

如果你的业务中有些数据类似于数据字典，比如配置文件的配置，

常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，

而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，

要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，

避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。

vim schema.xml

table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" /

后端数据准备

mysql -S /data/3307/mysql.sock

use taobao

create table t_area (id int not null primary key auto_increment,name varchar(20) not null);

mysql -S /data/3308/mysql.sock

use taobao

create table t_area (id int not null primary key auto_increment,name varchar(20) not null);

重启mycat

mycat restart

测试：

mysql -uroot -p123456 -h10.0.0.52 -P8066

use TESTDB

insert into t_area(id,name) values(1,'a');

insert into t_area(id,name) values(2,'b');

insert into t_area(id,name) values(3,'c');

insert into t_area(id,name) values(4,'d');

A

join

B

为了防止跨分片join，可以使用E-R模式

A join B

on a.xx=b.yy

join C

on A.id=C.id

table name="A" dataNode="sh1,sh2" rule="mod-long"

childTable name="B" joinKey="yy" parentKey="xx" /

/table

MySQL如何实现高可用？

1. 概述

我们在考虑MySQL数据库的高可用的架构时，主要要考虑如下几方面：

关于对高可用的分级在这里我们不做详细的讨论，这里只讨论常用高可用方案的优缺点以及高可用方案的选型。

2. 高可用方案

2.1. 主从或主主半同步复制

使用双节点数据库，搭建单向或者双向的半同步复制。在5.7以后的版本中，由于lossless replication、logical多线程复制等一些列新特性的引入，使得MySQL原生半同步复制更加可靠。

常见架构如下：

通常会和proxy、keepalived等第三方软件同时使用，即可以用来监控数据库的 健康 ，又可以执行一系列管理命令。如果主库发生故障，切换到备库后仍然可以继续使用数据库。

优点：

缺点：

2.2. 半同步复制优化

半同步复制机制是可靠的。如果半同步复制一直是生效的，那么便可以认为数据是一致的。但是由于网络波动等一些客观原因，导致半同步复制发生超时而切换为异步复制，那么这时便不能保证数据的一致性。所以尽可能的保证半同步复制，便可提高数据的一致性。

该方案同样使用双节点架构，但是在原有半同复制的基础上做了功能上的优化，使半同步复制的机制变得更加可靠。

可参考的优化方案如下：

半同步复制由于发生超时后，复制断开，当再次建立起复制时，同时建立两条通道，其中一条半同步复制通道从当前位置开始复制，保证从机知道当前主机执行的进度。另外一条异步复制通道开始追补从机落后的数据。当异步复制通道追赶到半同步复制的起始位置时，恢复半同步复制。

搭建两条半同步复制通道，其中连接文件服务器的半同步通道正常情况下不启用，当主从的半同步复制发生网络问题退化后，启动与文件服务器的半同步复制通道。当主从半同步复制恢复后，关闭与文件服务器的半同步复制通道。

优点：

缺点：

2.3. 高可用架构优化

将双节点数据库扩展到多节点数据库，或者多节点数据库集群。可以根据自己的需要选择一主两从、一主多从或者多主多从的集群。

由于半同步复制，存在接收到一个从机的成功应答即认为半同步复制成功的特性，所以多从半同步复制的可靠性要优于单从半同步复制的可靠性。并且多节点同时宕机的几率也要小于单节点宕机的几率，所以多节点架构在一定程度上可以认为高可用性是好于双节点架构。

但是由于数据库数量较多，所以需要数据库管理软件来保证数据库的可维护性。可以选择MMM、MHA或者各个版本的proxy等等。常见方案如下：

MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。

MHA Node运行在每台MySQL服务器上，主要作用是切换时处理二进制日志，确保切换尽量少丢数据。

MHA也可以扩展到如下的多节点集群：

优点：

缺点：

Zookeeper使用分布式算法保证集群数据的一致性，使用zookeeper可以有效的保证proxy的高可用性，可以较好的避免网络分区现象的产生。

优点：

缺点：

2.4. 共享存储

共享存储实现了数据库服务器和存储设备的解耦，不同数据库之间的数据同步不再依赖于MySQL的原生复制功能，而是通过磁盘数据同步的手段，来保证数据的一致性。

SAN的概念是允许存储设备和处理器（服务器）之间建立直接的高速网络（与LAN相比）连接，通过这种连接实现数据的集中式存储。常用架构如下：

使用共享存储时，MySQL服务器能够正常挂载文件系统并操作，如果主库发生宕机，备库可以挂载相同的文件系统，保证主库和备库使用相同的数据。

优点：

缺点：

DRBD是一种基于软件、基于网络的块复制存储解决方案，主要用于对服务器之间的磁盘、分区、逻辑卷等进行数据镜像，当用户将数据写入本地磁盘时，还会将数据发送到网络中另一台主机的磁盘上，这样的本地主机(主节点)与远程主机(备节点)的数据就可以保证实时同步。常用架构如下：

当本地主机出现问题，远程主机上还保留着一份相同的数据，可以继续使用，保证了数据的安全。

DRBD是linux内核模块实现的快级别的同步复制技术，可以与SAN达到相同的共享存储效果。

优点：

缺点：

2.5. 分布式协议

分布式协议可以很好解决数据一致性问题。比较常见的方案如下：

MySQL cluster是官方集群的部署方案，通过使用NDB存储引擎实时备份冗余数据，实现数据库的高可用性和数据一致性。

优点：

缺点：

基于Galera的MySQL高可用集群， 是多主数据同步的MySQL集群解决方案，使用简单，没有单点故障，可用性高。常见架构如下：

优点：

缺点：

Paxos 算法解决的问题是一个分布式系统如何就某个值（决议）达成一致。这个算法被认为是同类算法中最有效的。Paxos与MySQL相结合可以实现在分布式的MySQL数据的强一致性。常见架构如下：

优点：

缺点：

3. 总结

随着人们对数据一致性的要求不断的提高，越来越多的方法被尝试用来解决分布式数据一致性的问题，如MySQL自身的优化、MySQL集群架构的优化、Paxos、Raft、2PC算法的引入等等。

而使用分布式算法用来解决MySQL数据库数据一致性的问题的方法，也越来越被人们所接受，一系列成熟的产品如PhxSQL、MariaDB Galera Cluster、Percona XtraDB Cluster等越来越多的被大规模使用。

随着官方MySQL Group Replication的GA，使用分布式协议来解决数据一致性问题已经成为了主流的方向。期望越来越多优秀的解决方案被提出，MySQL高可用问题可以被更好的解决。

分布式解决方案 tidb

多主 多备 master lvs做vip 读写分离中间件

mysql主从架构如何保

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。前者使用起来比较简便。推荐使用。

1. check table 和 repair table

登陆mysql 终端：

mysql -uxxxxx -p dbname

check table tabTest;

如果出现的结果说Status是OK，则不用修复，如果有Error，可以用：

repair table tabTest;

进行修复，修复之后可以在用check table命令来进行检查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。

2. myisamchk, isamchk

其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：

myisamchk tablename.MYI

进行检测，如果需要修复的话，可以使用：

myisamchk -of tablename.MYI

关于myisamchk的详细参数说明，可以参见它的使用帮助。需要注意的时在进行修改时必须确保MySQL服务器没有访问这个数据表，保险的情况下是最好在进行检测时把MySQL服务器Shutdown掉。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

另外可以把下面的命令放在你的rc.local里面启动MySQL服务器前：

[ -x /tmp/mysql.sock ] /pathtochk/myisamchk -of /DATA_DIR/*/*.MYI

其中的/tmp/mysql.sock是MySQL监听的Sock文件位置，对于使用RPM安装的用户应该是/var/lib/mysql/mysql.sock，对于使用源码安装则是/tmp/mysql.sock可以根据自己的实际情况进行变更，而pathtochk则是myisamchk所在的位置，DATA_DIR是你的MySQL数据库存放的位置。

需要注意的时，如果你打算把这条命令放在你的rc.local里面，必须确认在执行这条指令时MySQL服务器必须没有启动！检测修复所有数据库(表)