MySQL Mycat 分布式架構(gòu)

參考：

創(chuàng)新互聯(lián)公司是一家專注于做網(wǎng)站、網(wǎng)站制作與策劃設(shè)計(jì),玉龍網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:玉龍等地區(qū)。玉龍做網(wǎng)站價(jià)格咨詢:13518219792

圖中是兩組分片，紅色我們稱為shard1，藍(lán)色我們稱為shard2

51 52是服務(wù)器

兩個(gè)3307互為主從(雙主)，3309是本地3307的從庫

說明：沒有明確說明是只在某一個(gè)節(jié)點(diǎn)上做的，就是兩個(gè)節(jié)點(diǎn)都做

兩臺(tái)虛擬機(jī) db01 db02

每臺(tái)創(chuàng)建四個(gè)mysql實(shí)例：3307 3308 3309 3310

mysql軟件我們之前已完成二進(jìn)制安裝，直接初始化即可

我們server-id規(guī)劃為：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

兩個(gè)分片，每個(gè)分片四個(gè)mysql節(jié)點(diǎn)

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

這個(gè)復(fù)制用戶在誰上建都行

注：如果中間出現(xiàn)錯(cuò)誤，在每個(gè)節(jié)點(diǎn)進(jìn)行執(zhí)行以下命令

常見方案：

360 Atlas-Sharding 360

Alibaba cobar 阿里

Mycat 開源

TDDL 淘寶

Heisenberg 百度

Oceanus 58同城

Vitess 谷歌

OneProxy

DRDS 阿里云

我們裝的是openjdk，不是官方的那個(gè)

Mycat-server-xxxxx.linux.tar.gz

配置環(huán)境變量

我們mycat的命令也是在bin目錄下

啟動(dòng)

8066就是對(duì)外提供服務(wù)的端口，9066是管理端口

連接mycat：

默認(rèn)123456

db01:

我們一般先把原schema.xml備份，然后自己新寫一個(gè)：

xml和html看起來差不多，xml是從下往上調(diào)用的

前三行我們不用看，直接從第四行schema開始看起：

定義了schema，然后以/schema結(jié)尾

為什么要用邏輯庫？

業(yè)務(wù)透明化

此配置文件就是實(shí)現(xiàn)讀寫分離的配置

重啟mycat

讀寫分離測試

總結(jié)：

以上案例實(shí)現(xiàn)了1主1從的讀寫分離功能，寫操作落到主庫，讀操作落到從庫.如果主庫宕機(jī)，從庫不能在繼續(xù)提供服務(wù)了。

我們推薦這種架構(gòu)

一寫三讀，

不設(shè)置雙寫的原因是：性能沒提升多少，反而引起主鍵沖突的情況

配置文件：

之后重啟：mycat restart

真正的 writehost：負(fù)責(zé)寫操作的writehost

standby writeHost ：和readhost一樣，只提供讀服務(wù)

我們此處寫了兩個(gè)writehost，默認(rèn)使用第一個(gè)

當(dāng)寫節(jié)點(diǎn)宕機(jī)后，后面跟的readhost也不提供服務(wù)，這時(shí)候standby的writehost就提供寫服務(wù)，

后面跟的readhost提供讀服務(wù)

測試：

讀寫分離測試

對(duì)db01 3307節(jié)點(diǎn)進(jìn)行關(guān)閉和啟動(dòng),測試讀寫操作

結(jié)果應(yīng)為另一臺(tái)(52)的3307(17)是寫，3309(19)是讀

一旦7號(hào)節(jié)點(diǎn)恢復(fù)，此時(shí)因?yàn)?落后了，寫節(jié)點(diǎn)仍是17

balance屬性

負(fù)載均衡類型，目前的取值有3種：

writeType屬性

負(fù)載均衡類型，目前的取值有2種：

switchType屬性

-1 表示不自動(dòng)切換

1 默認(rèn)值，自動(dòng)切換

2 基于MySQL主從同步的狀態(tài)決定是否切換 ，心跳語句為 show slave status

datahost其他配置

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

maxCon="1000"：最大的并發(fā)連接數(shù)

minCon="10" ：mycat在啟動(dòng)之后，會(huì)在后端節(jié)點(diǎn)上自動(dòng)開啟的連接線程，長連接，好處是連接速度快，弊端是占內(nèi)存

tempReadHostAvailable="1"

這個(gè)一主一從時(shí)（1個(gè)writehost，1個(gè)readhost時(shí)），可以開啟這個(gè)參數(shù)，如果2個(gè)writehost，2個(gè)readhost時(shí)

heartbeatselect user()/heartbeat 監(jiān)測心跳

其他參數(shù)sqlMaxLimit自動(dòng)分頁,必須在啟用分表的情況下才生效

創(chuàng)建測試庫和表:

我們重啟mycat后連接到8066

發(fā)現(xiàn)跟一個(gè)庫一樣，實(shí)際上已經(jīng)分到不同的物理硬件上了

分片：對(duì)一個(gè)"bigtable"，比如說t3表

熱點(diǎn)數(shù)據(jù)表 核心表

(1)行數(shù)非常多，800w下坡

(2)訪問非常頻繁

分片的目的：

（1）將大數(shù)據(jù)量進(jìn)行分布存儲(chǔ)

（2）提供均衡的訪問路由

分片策略：

范圍 range 800w 1-400w 400w01-800w 不適用于業(yè)務(wù)訪問不均勻的情況

取模 mod (取余數(shù)) 和節(jié)點(diǎn)的數(shù)量進(jìn)行取模

枚舉 按枚舉的種類分，如移動(dòng)項(xiàng)目按省份分

哈希 hash

時(shí)間 流水

優(yōu)化關(guān)聯(lián)查詢（否則join的表在不同分片上，效率會(huì)比單庫還要低）

全局表

ER分片

案例：移動(dòng)統(tǒng)一：先拆出邊緣業(yè)務(wù)，再按地域分片，但對(duì)應(yīng)用來說是統(tǒng)一的

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

創(chuàng)建測試表：

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');

取余分片方式：分片鍵（一個(gè)列）與節(jié)點(diǎn)數(shù)量進(jìn)行取余，得到余數(shù)，將數(shù)據(jù)寫入對(duì)應(yīng)節(jié)點(diǎn)

vim schema.xml

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

vim rule.xml

property name="count"2/property

準(zhǔn)備測試環(huán)境

創(chuàng)建測試表：

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');

分別登錄后端節(jié)點(diǎn)查詢數(shù)據(jù)

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 標(biāo)識(shí)將要分片的表字段，algorithm 分片函數(shù)， 其中分片函數(shù)配置中，mapFile標(biāo)識(shí)配置文件名稱

準(zhǔn)備測試環(huán)境

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

使用場景：

如果你的業(yè)務(wù)中有些數(shù)據(jù)類似于數(shù)據(jù)字典，比如配置文件的配置，

常用業(yè)務(wù)的配置或者數(shù)據(jù)量不大很少變動(dòng)的表，這些表往往不是特別大，

而且大部分的業(yè)務(wù)場景都會(huì)用到，那么這種表適合于Mycat全局表，無須對(duì)數(shù)據(jù)進(jìn)行切分，

要在所有的分片上保存一份數(shù)據(jù)即可，Mycat 在Join操作中，業(yè)務(wù)表與全局表進(jìn)行Join聚合會(huì)優(yōu)先選擇相同分片內(nèi)的全局表join，

避免跨庫Join，在進(jìn)行數(shù)據(jù)插入操作時(shí)，mycat將把數(shù)據(jù)分發(fā)到全局表對(duì)應(yīng)的所有分片執(zhí)行，在進(jìn)行數(shù)據(jù)讀取時(shí)候?qū)?huì)隨機(jī)獲取一個(gè)節(jié)點(diǎn)讀取數(shù)據(jù)。

vim schema.xml

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

后端數(shù)據(jù)準(zhǔn)備

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數(shù)據(jù)庫的數(shù)據(jù)量很大，數(shù)據(jù)庫架構(gòu)該怎么去設(shè)計(jì)？

1.設(shè)置讀寫分離

2.進(jìn)行分庫分表，用中間件路由，分?jǐn)偭髁?/p>

3.可以先寫入mq，然后慢慢寫入

4.可以使用tidb，tps很高

mysql 更改表結(jié)構(gòu)

Online DDL 工具：pt-osc

對(duì)于 MySQL Online DDL 目前主流的有三種工具：

原生 Online DDL；

pt-osc(online-schema-change)，

gh-ost

本文主要講解 pt-online-schema-change 的使用以及三種工具的簡單對(duì)比。

一、原理及限制

1.1 原理

1.?創(chuàng)建一個(gè)與原表結(jié)構(gòu)相同的空表，表名是?_new?后綴；

2. 修改步驟 1 創(chuàng)建的空表的表結(jié)構(gòu)；

3. 在原表上加三個(gè)觸發(fā)器：delete/update/insert，用于 copy 數(shù)據(jù)過程中，將原表中要執(zhí)行的語句在新表中執(zhí)行；

4. 將原表數(shù)據(jù)以數(shù)據(jù)塊（chunk）的形式 copy 到新表；

5. rename 原表為 old 表，并把新表 rename 為原表名，然后刪除舊表；

6. 刪除觸發(fā)器。

Mysql - 架構(gòu)及常用組件功能

mysql底層架構(gòu)分為:

1、client(客戶端)

2、server(服務(wù)端)

client: 主要有各種plugin、jdbc等

server: 包含了連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器、存儲(chǔ)引擎

連接器 的主要作用是與 客戶端 建立聯(lián)系,管理客戶端的連接、會(huì)話、權(quán)限驗(yàn)證等。

查詢緩存 的作用是，在sql通過連接器之后到達(dá)服務(wù)端之后,如果sql是sel開頭的語句，那么先在 查詢緩存 中獲取命中結(jié)果，如果有命中結(jié)果則直接返回結(jié)果。沒有結(jié)果那么sql會(huì)通往 分析器 。

分析器 拿到sql后,會(huì)對(duì)sql進(jìn)行詞法、語法分析，同時(shí)創(chuàng)建sql Id,如果sql有錯(cuò)誤,那么將會(huì)終止sql行為，將異常返回客戶端。

優(yōu)化器 的作用主要是對(duì)通過 分析器 的sql進(jìn)行優(yōu)化,比如進(jìn)行 索引選擇 、 重寫查詢 等,同時(shí)會(huì)創(chuàng)建 sql執(zhí)行計(jì)劃 ,可以通過 explain 指令進(jìn)行查看。

執(zhí)行器 拿到了經(jīng)過優(yōu)化器的sql,將會(huì)操作 存儲(chǔ)引擎 ,通過調(diào)用 存儲(chǔ)引擎 提供的讀寫接口,得到返回結(jié)果。

存儲(chǔ)引擎 是sql的最終執(zhí)行者,它對(duì)外提供了讀寫接口,本身主要作用為執(zhí)行sql、存儲(chǔ)數(shù)據(jù)、獲取數(shù)據(jù)等， 存儲(chǔ)引擎 的設(shè)計(jì)是插件形式實(shí)現(xiàn)的,常見了有 InnoDB 、 MyISAM 等。

未完待續(xù)......