MyCat配置MySQL讀寫分離

前言：

Mycat讀寫分離和自動切換機制，需要mysql的主從複制機制配合，即讀從從機（slave）中讀取，寫向主機（master）中寫入！

是以我們需要先完成mysql的主從備份

一 mysql主從備份

因為我之前以及寫過一篇文章了，大家可以看完寫的部落格，或者，在網上找一些其它配置資料

點選跳轉

二 安裝mycat

第一步：mycat需要jdk環境，請安裝jdk

第一步： 我這裡在master中安裝mycat，即直接下載下傳mycat安裝包，解壓即可

mycat-1.6下載下傳

tar -zxvf mycat-1.6

其一級目錄如下

• mycat

  • bin

    mycay的執行檔案目錄

  • catlet

  • conf

    mycat的配置檔案其中(server.xml，rule.xml以及schema.xml最為重要)

  • lib

    mycat自身需要的依賴

  • logs

    mycat的日子檔案。發生錯誤，可以到這裡檢視

  • version.txt

    mycat的版本資訊

三 conf配置檔案

使用mycat隻需要配置相關檔案即可，其中以server.xml，rule.xml以及schema.xml最為重要、

• server.xml

  配置使用邏輯庫和邏輯表的使用者情況

• rule.xml

  配置分庫分表的規則

• schema.xml

  配置邏輯庫與邏輯表，以及實際庫和實際表

這裡說到了邏輯庫和邏輯表是說，mycat對外提供一個資料庫和一個表，你可以向操作MySQL一樣操作它，不用關心其中真實的資料在那個分庫那個分表。如下

3.1 在schema.xml中配置邏輯庫與邏輯表

schema.xml中

• 定義邏輯庫

定義邏輯庫，名字為TESTDB,不檢查輸入的sql語句的規範,每條sql語句預設加上limit 100,如果你加入limit則該sqlMaxLimit失效
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
           

• 定義邏輯表

#定義邏輯庫中的邏輯表travelrecord,其真實資料在dn1,dn2,dn3的MySQL中（後面定義的），其mycat的資料分布規則為auto-sharding-long（預設）改為crc32slot這樣會根據算法将寫入的資料也會分布到不同的資料庫中,這個規則需要在rule.xml中配置
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="crc32slot" />

#定義dataNode，即真實的MySQL資料的位址name為代表,其真實位址為localhost1（後面有定義）,資料庫為db1
 <dataNode name="dn1" dataHost="localhost1" database="db1" />
 <dataNode name="dn2" dataHost="localhost1" database="db2" />
 <dataNode name="dn3" dataHost="localhost1" database="db3" />

#定義dataHost，即真實的MySQL資料的IP主機位址dbType(資料庫類型mysql)，dbDriver驅動（mysql,因為mycat本身就是為MySQL做的，是以你寫native就是MySQL，其它資料庫則需要你寫完整的dbDriver），balance為平衡政策，一般用3(balance="3"：表示寫請求隻發給節點，讀請求隻發給讀節點這裡要改，否則後面讀寫無效)
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
          <heartbeat>select user()</heartbeat>
          <!-- 寫入資料向那個真實的資料庫寫入，一般是MySQL的主-->
          <writeHost host="hostM1" url="localhost:3306" user="root"
                     password="123456">
              <!--讀資料向那個真實的資料庫讀，一般是MySQL的從-->
             <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
          </writeHost>
 </dataHost>
           

使用crc32slot隻會将資料配置設定到兩個資料庫中，再配置檔案rule.xml中有，但是我們配置設定到了3個資料庫(db1,db2,db3後面會配置)，是以我們需要修改配置，改為3

如果你這裡忘記配置，并且以及開始mycat，那麼你再修改為3時，必須将conf/ruledata目錄（這個目錄會在你使用mycat插入資料的時候生成）删除，再重新啟動mycat，否則沒有效果，因為分片規則以及生成，隻有将其删除運作再生成！

下面這張圖就是我後面使用mycat插入資料後，ruledata/crc32slot_MYCATTEST.properties 的内容

一共分三片，如果你沒有該為3，這裡就隻有0 和1

• 0 = 0- 34132

  表示_slot為0-34132的在資料庫0中

• 1 = 34133 - 68266

  表示_slot為34133 - 68266的在資料庫1中

• 2 = 68266 - 102399

  表示_slot為 68266 - 102399的在資料庫2中

其中_slot為你建立表以後，mycat會自動在每張表加一個_slot字段

根據自身的MySQL的主從的資訊配置如下（删掉了注釋以及不需要的部分）

1 <?xml version="1.0"?>
  2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3 <mycat:schema xmlns:mycat="http://io.mycat/">
  4 
  5     <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
  6         <!-- auto sharding by id (long) -->
  7         <table name="mycattest" dataNode="dn1,dn2,dn3" rule="crc32slot" />
  9     </schema>
 10 
 11     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
 12         /> -->
 13     <dataNode name="dn1" dataHost="localhost1" database="db1" />
 14     <dataNode name="dn2" dataHost="localhost1" database="db2" />
 15     <dataNode name="dn3" dataHost="localhost1" database="db3" />
 16 
 17     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
 18               writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
 19         <heartbeat>select user()</heartbeat>
 20         <!-- can have multi write hosts -->
 21         <writeHost host="hostM1" url="192.168.25.5:3306" user="root"
 22                    password="123456">
 23             <!-- can have multi read hosts -->
 24             <readHost host="hostS2" url="192.168.25.6:3306" user="root" password="123456" />
 25         </writeHost>
 26     </dataHost>
 27 </mycat:schema>```
           

3.2 在server.xml中配置使用者登陸mycat的資訊

主要檢視server.xml中的使用者配置資訊

# 登陸mycat的使用者配置
 <user name="root">
           使用者是root，密碼是123456，該使用者隻能操作TESTDB邏輯庫
          <property name="password">123456</property>
          <property name="schemas">TESTDB</property>
  
         這裡你可以設定該使用者對邏輯庫TESTDB的DML的權限設定
         <!-- 表級 DML 權限設定 -->
         <!--        
         <privileges check="false">
             <schema name="TESTDB" dml="0110" >
                  <table name="tb01" dml="0000"></table>
                  <table name="tb02" dml="1111"></table>
              </schema>
          </privileges>       
           -->
     </user>
  
    # 使用者名為user密碼為user，可以操作的邏輯庫是TESTDB，但是是隻讀的
      <user name="user">
          <property name="password">user</property>
          <property name="schemas">TESTDB</property>
          <property name="readOnly">true</property>
      </user> 
           

四 測試

4.1 根據之前的配置，我們需要修改MySQL主從中的使用者登陸權限，讓mycat可以登陸進去

• mysql_master 192.168.25.5

grant all privileges on . to root@’%’ identified by “123456”; #on 後面是* 點 *

再重新整理權限

FLUSH PRIVILEGES;

• mysql_slave 192.168.25.6

grant all privileges on . to root@’%’ identified by “123456”; #on 後面是* 點 *

再重新整理權限

FLUSH PRIVILEGES;

結果同上

4.2 測試讀寫分離

• 進入mycat安裝目錄的bin，啟動

cd /root/mycat/bin
           

• 登陸mycat

mysql -h192.168.25.5 -uroot -p123456 -P8066

-u為你在mycat中的server.xml中配置的使用者名，-p（小寫）為該使用者密碼，-h為mycat的安裝主機

-P （大寫） 為mycat的端口

9066為管理端口管理端口用于執行管理指令：

8066指令端口使用者執行增删改查等 SQL 語句：

如果出現 Unsupported statement 說明你使用的是9066端口操作（改為8066登陸），或者登陸的使用者在mycat中的server.xml中權限受到了限制

• 檢視邏輯資料庫

  

• 檢視邏輯表

  

• 為mysql_master配置資料庫

  因為在前面的schena.xml中配置了真實資料庫的名字，但是我們還有沒有建立，是以需要先建立出來，直接在master中寫sql，語句即可，因為以及配置了主從，slave也會執行

create database db1;
create database db2;
create database db3;
           

• 測試

  為已存在的邏輯表跟新你需要的完整資訊

mysql> use TESTDB;
mysql> CREATE TABLE `mycattest` (
  `id` int(255) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
mysql>desc mycattest
           

這裡我需要說明一下，為什麼這裡建立表結構，因為，表必須現在mycat中聲明配置其相關屬性（但是沒有結構），後面mycat會自動為你生成一個邏輯表(mycattest)，并會在你配置的主從資料庫(db1,db2,db3)中加入該表(mycattest),此時該表是沒有資料結構的，但是其分片分庫的規則都以完成，是以你隻需要完善其你需要的表結構即可！邏輯表必須先在schema.xml中聲明配置規則

• 檢視MySQL的主從資料結構

  随意選擇一個資料庫(db1,db2,db3),注意mycat會自動增加一個字段_slot，根據該字段，mycat會把資料分散到你定義的片中(db1,db2,db3)

  

• 測試寫

  使用mycat寫入5條資料

INSERT INTO mycattest(id,username,password)  VALUES('2','gxm','123456');
INSERT INTO mycattest(id,username,password)  VALUES('3','gxm','123456');
INSERT INTO mycattest(id,username,password)  VALUES('4','gxm','123456');
INSERT INTO mycattest(id,username,password)  VALUES('5','gxm','123456');
INSERT INTO mycattest(id,username,password)  VALUES('6','gxm','123456');
           

檢視master與slave是否都已插入資料,如果主從都已有資料，說明寫是分離的，因為隻有mycat向master中寫入資料，slave才會隻有相同的一份資料，因為如果同時向master與slave寫入

• 第一種報錯主鍵重複（如果不是，我這裡就應該報錯，id為主鍵）
• 第二種，資料會重複，master與slave每個都會會有10條資料（如果沒有主鍵的話）

但是現在這裡上述2中情況沒有出現說明，是單獨向master寫入，由slave同步master的結果

因為我們配置是rule是crc32slot配置了分片是以資料會根據_slot大緻均勻的分到到db1,db2,db3中

• 測試讀

  我向slave中寫入一條記錄

master不會同步slave，是以slave的資料會比master多一條’999’,‘slave’,‘99999’

此時我再使用mycat查詢資料如果沒有999号資料說明從master中讀取，有999号資料說明從slave中讀取

或者你可以使用9066端口登陸，檢視sql語句的執行狀況

show @@datasource;

關于使用9066或者8066端口的使用的一些常用指令，檢視mycat狀态等等，這片部落格感覺寫的挺好的！

點選跳轉