Mysql主從復(fù)制，讀寫分離，分表分庫策略與實(shí)踐的示例分析

本篇文章給大家分享的是有關(guān)Mysql主從復(fù)制，讀寫分離，分表分庫策略與實(shí)踐的示例分析，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

一、MySQL擴(kuò)展具體的實(shí)現(xiàn)方式

隨著業(yè)務(wù)規(guī)模的不斷擴(kuò)大，需要選擇合適的方案去應(yīng)對(duì)數(shù)據(jù)規(guī)模的增長，以應(yīng)對(duì)逐漸增長的訪問壓力和數(shù)據(jù)量。

關(guān)于數(shù)據(jù)庫的擴(kuò)展主要包括：業(yè)務(wù)拆分、主從復(fù)制、讀寫分離、數(shù)據(jù)庫分庫與分表等。這篇文章主要講述數(shù)據(jù)庫分庫與分表

（1）業(yè)務(wù)拆分

在 大型網(wǎng)站應(yīng)用之海量數(shù)據(jù)和高并發(fā)解決方案總結(jié)一二 一篇文章中也具體講述了為什么要對(duì)業(yè)務(wù)進(jìn)行拆分。

業(yè)務(wù)起步初始，為了加快應(yīng)用上線和快速迭代，很多應(yīng)用都采用集中式的架構(gòu)。隨著業(yè)務(wù)系統(tǒng)的擴(kuò)大，系統(tǒng)變得越來越復(fù)雜，越來越難以維護(hù)，開發(fā)效率變得越來越低，并且對(duì)資源的消耗也變得越來越大，通過硬件提高系統(tǒng)性能的方式帶來的成本也越來越高。

因此，在選型初期，一個(gè)優(yōu)良的架構(gòu)設(shè)計(jì)是后期系統(tǒng)進(jìn)行擴(kuò)展的重要保障。

例如：電商平臺(tái)，包含了用戶、商品、評(píng)價(jià)、訂單等幾大模塊，最簡單的做法就是在一個(gè)數(shù)據(jù)庫中分別創(chuàng)建users、shops、comment、order四張表。

但是，隨著業(yè)務(wù)規(guī)模的增大，訪問量的增大，我們不得不對(duì)業(yè)務(wù)進(jìn)行拆分。每一個(gè)模塊都使用單獨(dú)的數(shù)據(jù)庫來進(jìn)行存儲(chǔ)，不同的業(yè)務(wù)訪問不同的數(shù)據(jù)庫，將原本對(duì)一個(gè)數(shù)據(jù)庫的依賴拆分為對(duì)4個(gè)數(shù)據(jù)庫的依賴，這樣的話就變成了4個(gè)數(shù)據(jù)庫同時(shí)承擔(dān)壓力，系統(tǒng)的吞吐量自然就提高了。

（2）主從復(fù)制

一般是主寫從讀，一主多從

1、 MySQL5.6 數(shù)據(jù)庫主從（Master/Slave）同步安裝與配置詳解

2、 MySQL主從復(fù)制的常見拓?fù)?、原理分析以及如何提高主從?fù)制的效率總結(jié)

3、 使用mysqlreplicate命令快速搭建 Mysql 主從復(fù)制

上述三篇文章中，講述了如何配置主從數(shù)據(jù)庫，以及如何實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離，這里不再贅述，有需要的選擇性點(diǎn)擊查看。

上圖是網(wǎng)上的一張關(guān)于MySQL的Master和Slave之間數(shù)據(jù)同步的過程圖。

主要講述了MySQL主從復(fù)制的原理：數(shù)據(jù)復(fù)制的實(shí)際就是Slave從Master獲取Binary log文件，然后再本地鏡像的執(zhí)行日志中記錄的操作。由于主從復(fù)制的過程是異步的，因此Slave和Master之間的數(shù)據(jù)有可能存在延遲的現(xiàn)象，此時(shí)只能保證數(shù)據(jù)最終的一致性。

（3）數(shù)據(jù)庫分庫與分表

我們知道每臺(tái)機(jī)器無論配置多么好它都有自身的物理上限，所以當(dāng)我們應(yīng)用已經(jīng)能觸及或遠(yuǎn)遠(yuǎn)超出單臺(tái)機(jī)器的某個(gè)上限的時(shí)候，我們惟有尋找別的機(jī)器的幫助或者繼續(xù)升級(jí)的我們的硬件，但常見的方案還是通過添加更多的機(jī)器來共同承擔(dān)壓力。

我們還得考慮當(dāng)我們的業(yè)務(wù)邏輯不斷增長，我們的機(jī)器能不能通過線性增長就能滿足需求？因此，使用數(shù)據(jù)庫的分庫分表，能夠立竿見影的提升系統(tǒng)的性能，關(guān)于為什么要使用數(shù)據(jù)庫的分庫分表的其他原因這里不再贅述，主要講具體的實(shí)現(xiàn)策略。請看下邊章節(jié)。

二、分表實(shí)現(xiàn)策略

關(guān)鍵字：用戶ID、表容量

對(duì)于大部分?jǐn)?shù)據(jù)庫的設(shè)計(jì)和業(yè)務(wù)的操作基本都與用戶的ID相關(guān)，因此使用用戶ID是最常用的分庫的路由策略。用戶的ID可以作為貫穿整個(gè)系統(tǒng)用的重要字段。因此，使用用戶的ID我們不僅可以方便我們的查詢，還可以將數(shù)據(jù)平均的分配到不同的數(shù)據(jù)庫中。（當(dāng)然，還可以根據(jù)類別等進(jìn)行分表操作，分表的路由策略還有很多方式）

接著上述電商平臺(tái)假設(shè)，訂單表order存放用戶的訂單數(shù)據(jù)，sql腳本如下（只是為了演示，省略部分細(xì)節(jié)）：

CREATE TABLE `order` (
  `order_id` bigint(32) primary key auto_increment,
  `user_id` bigint(32),
   ...
)

當(dāng)數(shù)據(jù)比較大的時(shí)候，對(duì)數(shù)據(jù)進(jìn)行分表操作，首先要確定需要將數(shù)據(jù)平均分配到多少張表中，也就是：表容量。

這里假設(shè)有100張表進(jìn)行存儲(chǔ)，則我們在進(jìn)行存儲(chǔ)數(shù)據(jù)的時(shí)候，首先對(duì)用戶ID進(jìn)行取模操作，根據(jù) user_id%100 獲取對(duì)應(yīng)的表進(jìn)行存儲(chǔ)查詢操作，示意圖如下：

例如，user_id = 101 那么，我們在獲取值的時(shí)候的操作，可以通過下邊的sql語句：

select * from order_1 where user_id= 101

其中，order_1是根據(jù) 101%100 計(jì)算所得，表示分表之后的第一章order表。

注意：

在實(shí)際的開發(fā)中，如果你使用MyBatis做持久層的話，MyBatis已經(jīng)提供了很好得支持?jǐn)?shù)據(jù)庫分表的功能，例如上述sql用MyBatis實(shí)現(xiàn)的話應(yīng)該是：

接口定義：

/**
  * 獲取用戶相關(guān)的訂單詳細(xì)信息
  * @param tableNum 具體某一個(gè)表的編號(hào)
  * @param userId 用戶ID
  * @return 訂單列表
  */
public List<Order> getOrder(@Param("tableNum") int tableNum,@Param("userId") int userId);

xml配置映射文件：

<select id="getOrder" resultMap="BaseResultMap">
    select * from order_${tableNum}
    where user_id = #{userId}
  </select>

其中${tableNum} 含義是直接讓參數(shù)加入到sql中，這是MyBatis支持的特性。

注意：

另外，在實(shí)際的開發(fā)中，我們的用戶ID更多的可能是通過UUID生成的，這樣的話，我們可以首先將UUID進(jìn)行hash獲取到整數(shù)值，然后在進(jìn)行取模操作。

三、分庫實(shí)現(xiàn)策略

數(shù)據(jù)庫分表能夠解決單表數(shù)據(jù)量很大的時(shí)候數(shù)據(jù)查詢的效率問題，但是無法給數(shù)據(jù)庫的并發(fā)操作帶來效率上的提高，因?yàn)榉直淼膶?shí)質(zhì)還是在一個(gè)數(shù)據(jù)庫上進(jìn)行的操作，很容易受數(shù)據(jù)庫IO性能的限制。

因此，如何將數(shù)據(jù)庫IO性能的問題平均分配出來，很顯然將數(shù)據(jù)進(jìn)行分庫操作可以很好地解決單臺(tái)數(shù)據(jù)庫的性能問題。

分庫策略與分表策略的實(shí)現(xiàn)很相似，最簡單的都是可以通過取模的方式進(jìn)行路由。

還是上例，將用戶ID進(jìn)行取模操作，這樣的話獲取到具體的某一個(gè)數(shù)據(jù)庫，同樣關(guān)鍵字有：

用戶ID、庫容量

路由的示意圖如下：

上圖中庫容量為100。

同樣，如果用戶ID為UUID請先hash然后在進(jìn)行取模。

四、分庫與分表實(shí)現(xiàn)策略

上述的配置中，數(shù)據(jù)庫分表可以解決單表海量數(shù)據(jù)的查詢性能問題，分庫可以解決單臺(tái)數(shù)據(jù)庫的并發(fā)訪問壓力問題。

有時(shí)候，我們需要同時(shí)考慮這兩個(gè)問題，因此，我們既需要對(duì)單表進(jìn)行分表操作，還需要進(jìn)行分庫操作，以便同時(shí)擴(kuò)展系統(tǒng)的并發(fā)處理能力和提升單表的查詢性能，就是我們使用到的分庫分表。

分庫分表的策略相對(duì)于前邊兩種復(fù)雜一些，一種常見的路由策略如下：

１、中間變量　＝ user_id%（庫數(shù)量*每個(gè)庫的表數(shù)量）;
２、庫序號(hào)　＝　取整（中間變量／每個(gè)庫的表數(shù)量）;
３、表序號(hào)?。健≈虚g變量％每個(gè)庫的表數(shù)量;

例如：數(shù)據(jù)庫有256 個(gè)，每一個(gè)庫中有1024個(gè)數(shù)據(jù)表，用戶的user_id＝262145，按照上述的路由策略，可得：

１、中間變量?。?nbsp;262145%（256*1024）= 1;
２、庫序號(hào)?。健∪≌?／1024）= 0;
３、表序號(hào)?。健?％1024 = 1;

這樣的話，對(duì)于user_id＝262145，將被路由到第０個(gè)數(shù)據(jù)庫的第１個(gè)表中。

示意圖如下：

五、分庫分表總結(jié)

關(guān)于分庫分表策略的選擇有很多種，上文中根據(jù)用戶ID應(yīng)該是比較簡單的一種。其他方式比如使用號(hào)段進(jìn)行分區(qū)或者直接使用hash進(jìn)行路由等。有興趣的可以自行查找學(xué)習(xí)。

關(guān)于上文中提到的，如果用戶的ID是通過UUID的方式生成的話，我們需要單獨(dú)的進(jìn)行一次hash操作，然后在進(jìn)行取模操作等，其實(shí)hash本身就是一種分庫分表的策略，使用hash進(jìn)行路由策略的時(shí)候，我們需要知道的是，也就是hash路由策略的優(yōu)缺點(diǎn)，優(yōu)點(diǎn)是：數(shù)據(jù)分布均勻；缺點(diǎn)是：數(shù)據(jù)遷移的時(shí)候麻煩，不能按照機(jī)器性能分?jǐn)倲?shù)據(jù)。

上述的分庫和分表操作，查詢性能和并發(fā)能力都得到了提高，但是還有一些需要注意的就是，例如：原本跨表的事物變成了分布式事物；由于記錄被切分到不同的數(shù)據(jù)庫和不同的數(shù)據(jù)表中，難以進(jìn)行多表關(guān)聯(lián)查詢，并且不能不指定路由字段對(duì)數(shù)據(jù)進(jìn)行查詢。分庫分表之后，如果我們需要對(duì)系統(tǒng)進(jìn)行進(jìn)一步的擴(kuò)陣容（路由策略變更），將變得非常不方便，需要我們重新進(jìn)行數(shù)據(jù)遷移。

最后需要指出的是，分庫分表目前有很多的中間件可供選擇，最常見的是使用淘寶的中間件Cobar。

GitHub地址： https://github.com/alibaba/cobara

文檔地址為： https://github.com/alibaba/cobar/wiki

關(guān)于淘寶的中間件Cobar本篇內(nèi)容不具體介紹，會(huì)在后邊的學(xué)習(xí)中在做介紹。

另外Spring也可以實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離操作，后邊的文章，會(huì)進(jìn)一步學(xué)習(xí)。

六、總結(jié)

上述中，我們學(xué)到了如何進(jìn)行數(shù)據(jù)庫的讀寫分離和分庫分表，那么，是不是可以實(shí)現(xiàn)一個(gè)可擴(kuò)展、高性能、高并發(fā)的網(wǎng)站那？很顯然還不可以!一個(gè)大型的網(wǎng)站使用到的技術(shù)遠(yuǎn)不止這些，可以說，這些都是其中的最基礎(chǔ)的一個(gè)環(huán)節(jié)，因?yàn)檫€有很多具體的細(xì)節(jié)我們沒有掌握到，比如：數(shù)據(jù)庫的集群控制，集群的負(fù)載均衡，災(zāi)難恢復(fù)，故障自動(dòng)切換，事務(wù)管理等等技術(shù)。因此，還有很多需要去學(xué)習(xí)去研究的地方。

總之：

路漫漫其修遠(yuǎn)兮，吾將上下而求索。

前方道路美好而光明，2017年新征程，不泄步！

Mycat實(shí)現(xiàn)主從復(fù)制，讀寫分離，以及分庫分表的實(shí)踐

Mycat是什么

一個(gè)徹底開源的，面向企業(yè)應(yīng)用開發(fā)的大數(shù)據(jù)庫集群

支持事務(wù)、ACID、可以替代MySQL的加強(qiáng)版數(shù)據(jù)庫

一個(gè)可以視為MySQL集群的企業(yè)級(jí)數(shù)據(jù)庫，用來替代昂貴的Oracle集群

一個(gè)融合內(nèi)存緩存技術(shù)、NoSQL技術(shù)、HDFS大數(shù)據(jù)的新型SQL Server

結(jié)合傳統(tǒng)數(shù)據(jù)庫和新型分布式數(shù)據(jù)倉庫的新一代企業(yè)級(jí)數(shù)據(jù)庫產(chǎn)品

一個(gè)新穎的數(shù)據(jù)庫中間件產(chǎn)品

以上內(nèi)容來自 Mycat官網(wǎng)，簡單來說，Mycat就是一個(gè)數(shù)據(jù)庫中間件，對(duì)于我們開發(fā)來說，就像是一個(gè)代理，當(dāng)我們需要使用到多個(gè)數(shù)據(jù)庫和需要進(jìn)行分庫分表的時(shí)候，我們只需要在mycat里面配置好相關(guān)規(guī)則，程序無需做任何修改，只是需要將原本的數(shù)據(jù)源鏈接到mycat而已，當(dāng)然如果以前有多個(gè)數(shù)據(jù)源，需要將數(shù)據(jù)源切換為單個(gè)數(shù)據(jù)源，這樣有個(gè)好處就是當(dāng)我們的數(shù)據(jù)量已經(jīng)很大的時(shí)候，需要開始分庫分表或者做讀寫分離的時(shí)候，不用修改代碼（只需要改一下數(shù)據(jù)源的鏈接地址）

使用Mycat分表分庫實(shí)踐

haha,首先這不是一篇入門Mycat的博客但小編感覺又很入門的博客!這篇博客主要講解Mycat中數(shù)據(jù)分片的相關(guān)知識(shí)，同時(shí)小編將會(huì)在本機(jī)數(shù)據(jù)庫上進(jìn)行測試驗(yàn)證，圖文并茂展示出來。

數(shù)據(jù)庫分區(qū)分表，咋一聽非常地高大上，總有一種高高在上，望塵莫及的感覺，但小編想說的是，其實(shí)，作為一個(gè)開發(fā)人員，該來的總是會(huì)來，該學(xué)的東西你還是得學(xué)，區(qū)別只是時(shí)間先后順序的問題。

一、分區(qū)分表

分區(qū)就是把一個(gè)數(shù)據(jù)表的文件和索引分散存儲(chǔ)在不同的物理文件中。

mysql支持的分區(qū)類型包括Range、List、Hash、Key，其中Range比較常用：

RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇。

HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

分表是指在邏輯上將一個(gè)表拆分成多個(gè)邏輯表，在整體上看是一張表，分表有水平拆分和垂直拆分兩種,舉個(gè)例子，將一張大的存儲(chǔ)商戶信息的表按照商戶號(hào)的范圍進(jìn)行分表，將不同范圍的記錄分布到不同的表中。

二、Mycat 數(shù)據(jù)分片的種類

Mycat 的分片其實(shí)和分表差不多意思，就是當(dāng)數(shù)據(jù)庫過于龐大，尤其是寫入過于頻繁且很難由一臺(tái)主機(jī)支撐是，這時(shí)數(shù)據(jù)庫就會(huì)面臨瓶頸。我們將存放在同一個(gè)數(shù)據(jù)庫實(shí)例中的數(shù)據(jù)分散存放到多個(gè)數(shù)據(jù)庫實(shí)例（主機(jī)）上，進(jìn)行多臺(tái)設(shè)備存取以提高性能，在切分?jǐn)?shù)據(jù)的同時(shí)可以提高系統(tǒng)的整體性。

數(shù)據(jù)分片是指將數(shù)據(jù)全局地劃分為相關(guān)的邏輯片段，有水平切分、垂直切分、混合切分三種類型，下面主要講下Mycat的水平和垂直切分。有一點(diǎn)很重要，那就是Mycat是分布式的，因此分出來的數(shù)據(jù)片分布到不同的物理機(jī)上是正常的，靠網(wǎng)絡(luò)通信進(jìn)行協(xié)作。

水平切分

就是按照某個(gè)字段的某種規(guī)則分散到多個(gè)節(jié)點(diǎn)庫中，每個(gè)節(jié)點(diǎn)中包含一部分?jǐn)?shù)據(jù)?？梢詫?shù)據(jù)水平切分簡單理解為按照數(shù)據(jù)行進(jìn)行切分，就是將表中的某些行切分到一個(gè)節(jié)點(diǎn)，將另外某些行切分到其他節(jié)點(diǎn)，從分布式的整體來看它們是一個(gè)整體的表。

垂直切分

一個(gè)數(shù)據(jù)庫由很多表構(gòu)成，每個(gè)表對(duì)應(yīng)不同的業(yè)務(wù)，垂直切分是指按照業(yè)務(wù)將表進(jìn)行分類并分不到不同的節(jié)點(diǎn)上。垂直拆分簡單明了，拆分規(guī)則明確，應(yīng)用程序模塊清晰、明確、容易整合，但是某個(gè)表的數(shù)據(jù)量達(dá)到一定程度后擴(kuò)展起來比較困難。

混合切分

為水平切分和垂直切分的結(jié)合。

三、Mycat 垂直切分、水平切分實(shí)戰(zhàn)

1、垂直切分

上面說到，垂直切分主要是根據(jù)具體業(yè)務(wù)來進(jìn)行拆分的，那么，我們可以想象這么一個(gè)場景，假設(shè)我們有一個(gè)非常大的電商系統(tǒng)，那么我們需要將訂單表、流水表、用戶表、用戶評(píng)論表等分別分不到不同的數(shù)據(jù)庫中來提高吞吐量，架構(gòu)圖大概如下：

由于小編是在一臺(tái)機(jī)器上測試，因此就只有host1這個(gè)節(jié)點(diǎn)，但不同的表還是依舊對(duì)應(yīng)不同的數(shù)據(jù)庫，只不過是所有數(shù)據(jù)庫屬于同一個(gè)數(shù)據(jù)庫實(shí)例（主機(jī)）而已，后期不同主機(jī)只需增加<dataHost>節(jié)點(diǎn)即可。

mycat配置文件如下：

server.xml

<user name="root">
    <property name="password">root</property>
    // 對(duì)應(yīng)四個(gè)邏輯庫
    <property name="schemas">order,trade,user,comment</property>
</user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 4個(gè)邏輯庫，對(duì)應(yīng)4個(gè)不同的分片節(jié)點(diǎn) -->
    <schema name="order" checkSQLschema="false" sqlMaxLimit="100" dataNode="database1" />
    <schema name="trade" checkSQLschema="false" sqlMaxLimit="100" dataNode="database2" />
    <schema name="user" checkSQLschema="false" sqlMaxLimit="100" dataNode="database3" />
    <schema name="comment" checkSQLschema="false" sqlMaxLimit="100" dataNode="database4" />
    <!-- 四個(gè)分片，對(duì)應(yīng)四個(gè)不同的數(shù)據(jù)庫 -->
    <dataNode name="database1" dataHost="localhost1" database="database1" />
    <dataNode name="database2" dataHost="localhost1" database="database2" />
    <dataNode name="database3" dataHost="localhost1" database="database3" />
    <dataNode name="database4" dataHost="localhost1" database="database4" />
    <!-- 實(shí)際物理主機(jī)，只有這一臺(tái) -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="localhost:3306" user="root"
                password="root">
        </writeHost>
    </dataHost>
</mycat:schema>

登陸本機(jī)mysql，創(chuàng)建order,trade,user,comment4個(gè)數(shù)據(jù)庫:

create database database1 character set utf8;
create database database2 character set utf8;
create database database3 character set utf8;
create database database4 character set utf8;

執(zhí)行bin目錄下的startup_nowrap.bat文件，如果輸出下面內(nèi)容，則說明已經(jīng)啟動(dòng)mycat成功，如果沒有，請檢查order,trade,user,comment4個(gè)數(shù)據(jù)庫是否已經(jīng)創(chuàng)建。

采用下面語句登陸Mycat服務(wù)器：

mysql -uroot -proot -P8066 -h227.0.0.1

在comment數(shù)據(jù)庫中創(chuàng)建Comment表，并插入一條數(shù)據(jù)

上圖1處新建一個(gè)Comment表，2處插入一條記錄，3處查看記錄插入到哪個(gè)數(shù)據(jù)節(jié)點(diǎn)中，即database4。

2、水平切分

server.xml

<user name="root">
    <property name="password">root</property>
    <property name="schemas">TESTDB</property>
</user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
        <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="localhost:3306" user="root"
       password="root">
    </writeHost>
    </dataHost>
</mycat:schema>

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            rang-long
        </rule>
    </tableRule>
    <function name="rang-long"
            class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
</mycat:rule>

conf目錄下的autopartition-long.txt

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

上面的配置創(chuàng)建了一個(gè)名為TESTDB的邏輯庫，并指定了需要切分的表<table>標(biāo)簽，表名為travelrecord,分區(qū)的策略采用rang-long算法，即根據(jù)id數(shù)據(jù)列值的范圍進(jìn)行切分，具體的規(guī)則在autopartition-long.txt文件中定義，即id在0-500*10000范圍內(nèi)的記錄存放在db1的travelrecord表中，id在500*10000 - 1000*10000范圍內(nèi)的記錄存放在db2數(shù)據(jù)庫的travelrecord表中，下面我們插入兩條數(shù)據(jù)，驗(yàn)證是否和分片規(guī)則一致。

創(chuàng)建db1,db2,db3數(shù)據(jù)庫

create database db1 character set utf8;
create database db2 character set utf8;
create database db3 character set utf8;

確實(shí)是這樣的，到此我們就完成了mycat數(shù)據(jù)庫的水平切分，這個(gè)例子只是演示按照id列值得范圍進(jìn)行切分，mycat還支持很多的分片算法，如取模、一致性哈希算法、按日期分片算法等等，大家可以看《分布式數(shù)據(jù)庫架構(gòu)及企業(yè)實(shí)戰(zhàn)——基于Mycat中間件》這本書深入學(xué)習(xí)。

為什么需要讀寫分離

至于為什么需要讀寫分離，在我之前的文章有介紹過了，相信看到這篇文章的人也知道為什么需要讀寫分離了，當(dāng)然如果你也需要了解一下，那么歡迎查看我之前的文章 SpringBoot Mybatis 讀寫分離配置,順便也可以了解一下怎么通過代碼進(jìn)行讀寫分離的

MySQL主從復(fù)制

主從復(fù)制是讀寫分離的關(guān)鍵，不管通過什么方式進(jìn)行讀寫分離，前提就是MySQL有主從復(fù)制，當(dāng)前雙機(jī)主從也行，但是關(guān)鍵的關(guān)鍵，是要能保證2個(gè)庫的數(shù)據(jù)能一致（出掉剛寫入主庫從庫還未能及時(shí)反應(yīng)過來的情況），如果2個(gè)庫的數(shù)據(jù)不一致，那么讀寫分離也有沒有任何意義了，具體MySQL怎么做主從復(fù)制可以查看我之前的文章 MySQL主從復(fù)制搭建，基于日志（binlog）

Mycat讀寫分離設(shè)置

配置Mycat用戶

Mycat的用戶就跟MySQL用戶是同一個(gè)意思，主要配置鏈接到Mycat的用戶名以及密碼，以及能使用的邏輯庫，用戶信息主要在server.xml中配置的，具體如下

<?xml version="1.0" encoding="UTF-8"?>  
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");  
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">  
<mycat:server xmlns:mycat="http://io.mycat/">  
    <system>
    <property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1為開啟mysql壓縮協(xié)議-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <property name="processors">1</property> 
    <property name="processorExecutor">32</property> 
     -->
        <!--默認(rèn)是65535 64K 用于sql解析時(shí)最大文本長度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!-- 
            <property name="mutiNodeLimitType">1</property> 0：開啟小數(shù)量級(jí)（默認(rèn)） ；1：開啟億級(jí)數(shù)據(jù)排序
            <property name="mutiNodePatchSize">100</property> 億級(jí)數(shù)量排序批量
            <property name="processors">32</property> <property name="processorExecutor">32</property> 
            <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
    </system>
    <user name="raye">
        <property name="password">rayewang</property>
        <property name="schemas">separate</property>
    </user>
        </host> 
</mycat:server>

其中<user name="raye">定義了一個(gè)名為raye的用戶，標(biāo)簽user中的<property name="password">rayewang</property>定義了用戶的密碼，<property name="schemas">separate</property>定義了用戶可以使用的邏輯庫

配置Mycat邏輯庫

Mycat的配置有很多，不過因?yàn)槲覀冎皇鞘褂肕ycat的讀寫分類的功能，所以用到的配置并不多，只需要配置一些基本的，當(dāng)然本文也只是會(huì)介紹到讀寫分離相關(guān)的配置，其他配置建議讀者自己查看一下文檔，或者通過其他方式了解，邏輯庫是在schema.xml中配置的

首先介紹Mycat邏輯庫中的一些配置標(biāo)簽

schema

schema 標(biāo)簽是用來定義邏輯庫的，schema有四個(gè)屬性dataNode,checkSQLschema,sqlMaxLimit,name

dataNode 標(biāo)簽屬性用于綁定邏輯庫到某個(gè)具體的 database 上，1.3 版本如果配置了 dataNode，則不可以配置分片表，1.4 可以配置默認(rèn)分片，只需要配置需要分片的表即可

name是定義當(dāng)前邏輯庫的名字的，方便server.xml中定義用戶時(shí)的引用

checkSQLschema當(dāng)該值設(shè)置為 true 時(shí)，如果我們執(zhí)行語句select from separate.users;則 MyCat 會(huì)把語句修改 為select from users;。即把表示 schema 的字符去掉，避免發(fā)送到后端數(shù)據(jù)庫執(zhí)行時(shí)報(bào)（ERROR 1146 (42S02): Table ‘separate.users’ doesn’t exist）。 不過，即使設(shè)置該值為 true ，如果語句所帶的是并非是 schema 指定的名字，例如：select * from db1.users; 那么 MyCat 并不會(huì)刪除 db1 這個(gè)字段，如果沒有定義該庫的話則會(huì)報(bào)錯(cuò)，所以在提供 SQL語句的最好是不帶這個(gè)字段。

sqlMaxLimit當(dāng)該值設(shè)置為某個(gè)數(shù)值時(shí)。每條執(zhí)行的 SQL 語句，如果沒有加上 limit 語句，MyCat 也會(huì)自動(dòng)的加上所對(duì)應(yīng)的值。例如設(shè)置值為 100，執(zhí)行select from users;的效果為和執(zhí)行select from users limit 100;相同。設(shè)置該值的話，MyCat 默認(rèn)會(huì)把查詢到的信息全部都展示出來，造成過多的輸出。所以，在正常使用中，還是建議加上一個(gè)值，用于減少過多的數(shù)據(jù)返回。當(dāng)然 SQL 語句中也顯式的指定 limit 的大小，不受該屬性的約束。需要注意的是，如果運(yùn)行的 schema 為非拆分庫的，那么該屬性不會(huì)生效。需要手動(dòng)添加 limit 語句。

schema標(biāo)簽中有標(biāo)簽table用于定義不同的表分片信息，不過我們只是做讀寫分離，并不會(huì)用到，所以這里就不多介紹了

dataNode

dataNodedataNode 標(biāo)簽定義了 MyCat 中的數(shù)據(jù)節(jié)點(diǎn)，也就是我們通常說所的數(shù)據(jù)分片。一個(gè) dataNode 標(biāo)簽就是一個(gè)獨(dú)立的數(shù)據(jù)分片,dataNode有3個(gè)屬性:name,dataHost,database。

name定義數(shù)據(jù)節(jié)點(diǎn)的名字，這個(gè)名字需要是唯一的，此名字是用于table標(biāo)簽和schema標(biāo)簽中引用的

dataHost該屬性用于定義該分片屬于哪個(gè)數(shù)據(jù)庫實(shí)例的，屬性值是引用 dataHost 標(biāo)簽上定義的 name 屬性

database該屬性用于定義該分片屬性哪個(gè)具體數(shù)據(jù)庫實(shí)例上的具體庫，因?yàn)檫@里使用兩個(gè)緯度來定義分片，就是：實(shí)例+具體的庫。因?yàn)槊總€(gè)庫上建立的表和表結(jié)構(gòu)是一樣的。所以這樣做就可以輕松的對(duì)表進(jìn)行水平拆分

dataHost

dataHost是定義真實(shí)的數(shù)據(jù)庫連接的標(biāo)簽，該標(biāo)簽在 mycat 邏輯庫中也是作為最底層的標(biāo)簽存在，直接定義了具體的數(shù)據(jù)庫實(shí)例、讀寫分離配置和心跳語句，dataHost有7個(gè)屬性：name,maxCon,minCon,balance,writeType,dbType,dbDriver,有2個(gè)標(biāo)簽heartbeat,writeHost,其中writeHost標(biāo)簽中又包含一個(gè)readHost標(biāo)簽

name唯一標(biāo)識(shí) dataHost 標(biāo)簽，供dataNode標(biāo)簽使用

maxCon指定每個(gè)讀寫實(shí)例連接池的最大連接。也就是說，標(biāo)簽內(nèi)嵌套的 writeHost、readHost 標(biāo)簽都會(huì)使用這個(gè)屬性的值來實(shí)例化出連接池的最大連接數(shù)

minCon指定每個(gè)讀寫實(shí)例連接池的最小連接，初始化連接池的大小

balance 讀取負(fù)載均衡類型

1. balance=”0”, 不開啟讀寫分離機(jī)制，所有讀操作都發(fā)送到當(dāng)前可用的 writeHost 上。

2. balance=”1”，全部的 readHost 與 stand by writeHost 參與 select 語句的負(fù)載均衡，簡單的說，當(dāng)雙主雙從模式(M1->S1，M2->S2，并且 M1 與 M2 互為主備)，正常情況下，M2,S1,S2 都參與 select 語句的負(fù)載均衡。

3. balance=”2”，所有讀操作都隨機(jī)的在 writeHost、readhost 上分發(fā)。

4. balance=”3”，所有讀請求隨機(jī)的分發(fā)到 wiriterHost 對(duì)應(yīng)的 readhost 執(zhí)行，writerHost 不負(fù)擔(dān)讀壓力

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

1. writeType=”0”, 所有寫操作發(fā)送到配置的第一個(gè) writeHost，第一個(gè)掛了切到還生存的第二個(gè)writeHost，重新啟動(dòng)后已切換后的為準(zhǔn)，切換記錄在配置文件中:dnindex.properties .

2. writeType=”1”，所有寫操作都隨機(jī)的發(fā)送到配置的 writeHost

dbType 指定后端連接的數(shù)據(jù)庫類型，目前支持二進(jìn)制的 mysql 協(xié)議，還有其他使用 JDBC 連接的數(shù)據(jù)庫。例如：mongodb、oracle、spark 等

dbDriver指定連接后端數(shù)據(jù)庫使用的 Driver，目前可選的值有 native 和 JDBC。使用 native 的話，因?yàn)檫@個(gè)值執(zhí)行的 是二進(jìn)制的 mysql 協(xié)議，所以可以使用 mysql 和 maridb。其他類型的數(shù)據(jù)庫則需要使用 JDBC 驅(qū)動(dòng)來支持。從 1.6 版本開始支持 postgresql 的 native 原始協(xié)議。 如果使用 JDBC 的話需要將符合 JDBC 4 標(biāo)準(zhǔn)的驅(qū)動(dòng) JAR 包放到 MYCAT\lib 目錄下，并檢查驅(qū)動(dòng) JAR 包中包括如下目錄結(jié)構(gòu)的文件：META-INF\services\java.sql.Driver。在這個(gè)文件內(nèi)寫上具體的 Driver 類名，例如： com.mysql.jdbc.Driver。

heartbeat這個(gè)標(biāo)簽內(nèi)指明用于和后端數(shù)據(jù)庫進(jìn)行心跳檢查的語句。例如,MYSQL 可以使用 select user()，Oracle 可以使用 select 1 from dual 等。 這個(gè)標(biāo)簽還有一個(gè) connectionInitSql 屬性，主要是當(dāng)使用 Oracla 數(shù)據(jù)庫時(shí)，需要執(zhí)行的初始化 SQL 語句就這個(gè)放到這里面來。例如：alter session set nlsdateformat=’yyyy-mm-dd hh34:mi:ss’

writeHost，readHost這兩個(gè)標(biāo)簽都指定后端數(shù)據(jù)庫的相關(guān)配置給 mycat，用于實(shí)例化后端連接池。唯一不同的是，writeHost 指定寫實(shí)例、readHost 指定讀實(shí)例，組著這些讀寫實(shí)例來滿足系統(tǒng)的要求。 在一個(gè) dataHost 內(nèi)可以定義多個(gè) writeHost 和 readHost。但是，如果 writeHost 指定的后端數(shù)據(jù)庫宕機(jī)，那么這個(gè) writeHost 綁定的所有 readHost 都將不可用。另一方面，由于這個(gè) writeHost 宕機(jī)系統(tǒng)會(huì)自動(dòng)的檢測到，并切換到備用的 writeHost 上去,這2個(gè)標(biāo)簽屬性都一致，擁有host,url,password,user,weight,usingDecrypt等屬性

host用于標(biāo)識(shí)不同實(shí)例，一般 writeHost 我們使用M1，readHost 我們用S1

url真實(shí)數(shù)據(jù)庫的實(shí)例的鏈接地址，如果是使用 native 的 dbDriver，則一般為 address:port 這種形式。用 JDBC 或其他的dbDriver，則需要特殊指定。當(dāng)使用 JDBC 時(shí)則可以這么寫：jdbc:mysql://localhost:3306/

user真實(shí)數(shù)據(jù)庫實(shí)例的鏈接用戶名

password真實(shí)數(shù)據(jù)庫實(shí)例的鏈接密碼

weight權(quán)重 配置在 readhost 中作為讀節(jié)點(diǎn)的權(quán)重,主要用于多臺(tái)讀取的數(shù)據(jù)庫實(shí)例機(jī)器配置不同的情況，可以根據(jù)權(quán)重調(diào)整訪問量

usingDecrypt是否對(duì)密碼加密默認(rèn) 0 否 如需要開啟配置 1，同時(shí)使用加密程序?qū)γ艽a加密

注意，readHost是在writeHost標(biāo)簽內(nèi)的，不是單獨(dú)的

以下是我的讀寫分離配置文件

<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="separate" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
    <dataNode name="dn1" dataHost="localhost1" database="test" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.1.126:3307" user="root"
                   password="123456">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.1.126:3308" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

前面已經(jīng)差不多都解釋清楚了，因?yàn)槲抑皇怯玫幕镜闹鲝膹?fù)制，所以我的將dataHost的balance設(shè)置成了3

啟動(dòng)mycat，然后用數(shù)據(jù)庫連接工具連接到mycat，可以測試是否配置成功，最簡單的就是通過修改從庫的數(shù)據(jù)，這樣方便查看到底是運(yùn)行到哪個(gè)庫上面了，另外由于我是基于docker啟動(dòng)的mycat，所以如果是直接在系統(tǒng)中運(yùn)行的mycat的，可以去看官方文檔，看看到底怎么啟動(dòng)mycat

以上就是Mysql主從復(fù)制，讀寫分離，分表分庫策略與實(shí)踐的示例分析，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。