天天看點
傳回

11.MySQL資料分片

11.MySQL資料分片

11.MySQL資料分片

  • ​​一、資料分片概述​​
  • ​​1.1、相關概念​​
  • ​​1.1.1、分庫/分表​​
  • ​​1.1.2、水準分割​​
  • ​​1.1.3、垂直分割​​
  • ​​1.2、MyCAT介紹​​
  • ​​1.2.1、軟體介紹​​
  • ​​1.2.2、分片規則​​
  • ​​1.2.3、工作過程​​
  • ​​二、部署MyCAT服務​​
  • ​​2.1、拓撲結構​​
  • ​​2.2、部署MyCAT服務​​
  • ​​2.2.1、建立資料庫​​
  • ​​2.2.2、在分片伺服器上安裝JDK​​
  • ​​2.2.3、安裝mycat軟體​​
  • ​​2.2.4、重要的配置檔案​​
  • ​​2.3、修改配置檔案​​
  • ​​2.3.1、建立連接配接使用者​​
  • ​​2.3.2、配置資料分片​​
  • ​​2.3.3、檢視配置完成後的結果​​
  • ​​三、測試配置​​
  • ​​3.1、分片規則​​
  • ​​3.1.1、枚舉分片規則 sharding-by-intfile​​
  • ​​3.1.2、求模分片規則mod-long​​
  • ​​四、添加新庫、新表​​
  • ​​4.1、添加新庫​​
  • ​​4.2、添加新表​​
  • ​​4.3、重新開機服務​​
  • ​​4.4、用戶端登入檢視​​

一、資料分片概述

1.1、相關概念

1.1.1、分庫/分表

将存放在一台資料庫伺服器中的資料，按照特定的方式進行拆分，分散存放到多台資料庫伺服器中，以達到分散單台伺服器負載的效果。

1.1.2、水準分割

  • 橫向切分

    按照表中指定字段的分片規則，将表記錄按行切分，分散存儲到多個資料庫中。

1.1.3、垂直分割

  • 縱向切分

    将單個資料庫的多個表按業務類型分類，分散存儲到不同的資料庫。

1.2、MyCAT介紹

1.2.1、軟體介紹

MyCAT是基于java的分布式資料庫系統中間件，為高并發環境的分布式存儲提供解決方案

  • 适合資料大量寫入的存儲需求，不适合大量查詢的環境
  • 支援MySQL、Oracle、Sqlserver、Mongodb等
  • 提供資料讀寫分離服務
  • 提供資料分片服務
  • 基于阿裡巴巴Cobar進行研發的開源軟體

1.2.2、分片規則

MyCAT支援提供10種分片規則

  1. 枚舉法

    sharding-by-intfile

  2. 固定分片

    rule1

  3. 範圍約定

    auto-sharding-long

  4. 求模法

    mod-long

  5. 日期列分區法

    sharding-by-date

  6. 通配取模

    sharding-by-pattern

  7. ASCII碼求模通配

    sharding-by-prefixpattern

  8. 程式設計指定

    sharding-by-substring

  9. 字元串拆分hash解析

    sharding-by-stringhash

  10. 一緻性hash

    sharding-by-murmur

1.2.3、工作過程

當MyCAT收到一個SQL指令時

  1. 解析SQL指令涉及到的表
  2. 然後看對表的配置，如果有分片規則，則擷取SQL指令裡分片字段的值，并比對分片函數，擷取分片清單
  3. 然後将SQL指令發往對應的資料庫伺服器去執行
  4. 最後收集和處理所有分片結構資料，并傳回到用戶端

二、部署MyCAT服務

2.1、拓撲結構

11.MySQL資料分片

2.2、部署MyCAT服務

2.2.1、建立資料庫

  1. 分别在資料庫1、2、3上建立資料庫db1、db2、db3 
--資料庫1
mysql> create database db1;
--資料庫2
mysql> create database db2;
--資料庫3
mysql> create database db3;

2.2.2、在分片伺服器上安裝JDK 

[root@mycat ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@mycat ~]# java -version
openjdk version "1.8.0_161"
OpenJDK Runtime Environment (build 1.8.0_161-b14)
OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)

2.2.3、安裝mycat軟體 

Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
#免安裝源碼包

[root@mycat ~]# tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# ls mycat/
bin  catlet  conf  lib  logs  version.txt
[root@mycat ~]# mv mycat/  /usr/local/
[root@mycat ~]# ls /usr/local/mycat/
bin      #mycat指令
catlet    #擴充功能
conf      #配置檔案
lib      #mycat使用的jar包
logs      #mycat啟動日志和運作日志
  • mycat使用指令 
[root@mycat ~]# /usr/local/mycat/bin/mycat help
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }

2.2.4、重要的配置檔案 

[root@mycat ~]# cd /usr/local/mycat/conf
[root@mycat conf]# ls *.xml
ehcache.xml  
log4j2.xml  
rule.xml      #分片規則
schema.xml    #配置資料分片存儲的表
server.xml    #設定連接配接賬号及邏輯庫

2.3、修改配置檔案

2.3.1、建立連接配接使用者

  • 使用預設配置

    暫時不用配置

[root@mycat conf]# pwd
/usr/local/mycat/conf
[root@mycat conf]# vim server.xml 
<user name="root">    #連接配接mycat服務的使用者名
  <property name="password">123456</property>    #使用者連接配接mycat使用者時使用的密碼
  <property name="schemas">TESTDB</property>    #邏輯庫
</user>

<user name="user">    #連接配接mycat服務的使用者名
  <property name="password">user</property>
  <property name="schemas">TESTDB</property>
  <property name="readOnly">true</property>  #隻讀權限，連接配接mycat服務後隻有讀記錄的權限,不寫這一行則是可讀可寫 
</user>

2.3.2、配置資料分片

  1. 先備份schema.xml 
[root@mycat conf]# cp schema.xml /root/
  1. 删除無用的注釋行 
[root@mycat conf]# vim schema.xml
:q!
[root@mycat conf]# sed -i '56,77d' schema.xml    #删除56到77行

[root@mycat conf]# sed -i '39,42d' schema.xml    #删除39到42行
  • 原始配置
    • 11.MySQL資料分片
  • 修改後的 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />      #對travelrecord表做分片存儲

                <!-- global table is auto cloned to all defined data nodes ,so can join
                        with any table whose sharding node is in the same data node -->
                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />      #對company表做分片存儲
                <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
                <!-- random sharding using mod sharind rule -->
                <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
                           rule="mod-long" />
                <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
                        needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
                        rule="mod-long" /> -->
                <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile" />
                <table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
                           rule="sharding-by-intfile">
                        <childTable name="orders" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id">
                                <childTable name="order_items" joinKey="order_id"
                                                        parentKey="id" />
                        </childTable>
                        <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
                                                parentKey="id" />
                </table>
                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
         #定義資料庫主機名及存儲資料的庫
        <dataNode name="dn1" dataHost="mysql53" database="db1" />
        <dataNode name="dn2" dataHost="mysql54" database="db2" />
        <dataNode name="dn3" dataHost="mysql55" database="db3" />
    #定義mysql53主機名對應的資料庫伺服器ip位址
        <dataHost name="mysql53" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.4.53:3306" user="adminabc"
                                   password="123456">
                </writeHost>
        </dataHost>
    #定義mysql54主機名對應的資料庫伺服器ip位址
        <dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.4.54:3306" user="adminabc"
                                   password="123456">
                </writeHost>
        </dataHost>
        #定義mysql55主機名對應的資料庫伺服器ip位址
        <dataHost name="mysql55" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.4.55:3306" user="adminabc"
                                   password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>
  • 使用者授權 
[root@db1 ~]# mysql -uroot -p123456  -e 'grant all on  *.* to adminabc@"%" identified by  "123456"'
[root@db2 ~]# mysql -uroot -p123456  -e 'grant all on  *.* to adminabc@"%" identified by  "123456"'
[root@db3 ~]# mysql -uroot -p123456  -e 'grant all on  *.* to adminabc@"%" identified by  "123456"'
  • 用戶端驗證登陸 
mysql -h192.168.4.53 -uadminabc -p123456
 mysql -h192.168.4.54 -uadminabc -p123456
 mysql -h192.168.4.55 -uadminabc -p123456

  • 啟動服務

    檢視狀态

[root@mycat conf]# /usr/local/mycat/bin/mycat start 
Starting Mycat-server...
[root@mycat conf]# netstat -utnlp | grep 8066
[root@mycat mycat]# pwd
/usr/local/mycat
[root@mycat mycat]# ls logs/

2.3.3、檢視配置完成後的結果 

[root@client ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |      --定義的邏輯庫,實際上不存在。裡面的表也是假的
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company          |
| customer         |
| customer_addr    |
| employee         |
| goods            |
| hotnews          |
| orders           |
| order_items      |
| travelrecord     |
+------------------+
9 rows in set (0.00 sec)

三、測試配置

3.1、分片規則

3.1.1、枚舉分片規則 sharding-by-intfile

  • 檢視那張表使用sharding-by-intfile 
vim  schema.xml
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
       rule="sharding-by-intfile" />    #表使用的分片規則

  • 檢視分片字段名

    字段名必須在規則檔案定義的值裡選擇

vim /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile">    #分片規則
  <rule>
    <columns>sharding_id</columns>    #字段名
    <algorithm>hash-int</algorithm>    #算法
  </rule>
</tableRule>
  • 修改分片規則配置檔案，定義值清單 
vim /usr/local/mycat/conf/rule.xml
<function name="hash-int"
    class="io.mycat.route.function.PartitionByFileMap">    #算法
    <property name="mapFile">partition-hash-int.txt</property>  #配置檔案名
</function>      
vim /usr/local/mycat/conf/partition-hash-int.txt
10000=0    #0表示第一台資料庫伺服器，對應dn1
10010=1    #1表示第二台資料庫伺服器，對應dn2
10020=2    #3表示第三台資料庫伺服器，對應dn3
  • 重新開機mycat服務 
[root@mycat local]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@mycat local]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat local]# netstat -utnlp | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      4600/java
  • 用戶端連接配接mycat伺服器，建立employee并存儲資料 
mysql> create table employee( ID int primary key auto_increment ,
    -> sharding_id int , name char(15) , age int );

mysql> desc employee;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| sharding_id | int(11)  | YES  |     | NULL    |                |
| name        | char(15) | YES  |     | NULL    |                |
| age         | int(11)  | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

mysql>  insert into  employee(sharding_id , name , age )values(10000,"bob",19),(10000,"bob",19),(10000,"bob",19);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into  employee(sharding_id , name , age )values(10010,"bobA",19),(10010,"bobA",19),(10010,"bobA",19);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into  employee(sharding_id , name , age )values(10020,"bobF",19),(10020,"bobF",19),(10020,"bobF",19);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
  • 在三台資料庫伺服器上檢視employee表記錄 
[root@db1 ~]# mysql -uroot -p123456 -e 'select  * from  db1.employee'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  1 |       10000 | bob  |   19 |
|  2 |       10000 | bob  |   19 |
|  3 |       10000 | bob  |   19 |
+----+-------------+------+------+
[root@db2 ~]# mysql -uroot -p123456 -e 'select  * from  db2.employee'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  1 |       10010 | bobA |   19 |
|  2 |       10010 | bobA |   19 |
|  3 |       10010 | bobA |   19 |
+----+-------------+------+------+
[root@db3 ~]# mysql -uroot -p123456 -e 'select  * from  db3.employee'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+------+
| ID | sharding_id | name | age  |
+----+-------------+------+------+
|  1 |       10020 | bobF |   19 |
|  2 |       10020 | bobF |   19 |
|  3 |       10020 | bobF |   19 |

3.1.2、求模分片規則mod-long

  • 根據字段值與設定的數字求模結果存儲資料

    使用分片字段的值和指定數字的值，做取餘運算，根據運算結果存取資料。

  • 例如

    餘數為0對應dn1

    餘數為1對應dn2

    餘數為2對應dn3

  • 檢視使用求模分别規則的表 
[root@mycat local]# vim /usr/local/mycat/conf/schema.xml
<table name="hotnews" dataNode="dn1,dn2,dn3"
       rule="mod-long" />
  • 檢視分片字段名 
vim /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
    <rule>
         <columns>id</columns>
         <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
  • 設定分片字段求模的數字 
vim /usr/local/mycat/conf/rule.xml
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
     <!-- how many data nodes -->
     <property name="count">3</property>
</function>
  • 重新開機mycat服務 
[root@mycat ~]# /usr/local/mycat/bin/mycat stop 
Stopping Mycat-server...
Stopped Mycat-server.
[root@mycat ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -utnlp | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      4896/java
  • 用戶端連接配接mycat伺服器建表 并存儲資料 
[root@client ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456      
mysql> use TESTDB

mysql> create table hotnews( id int , title char(50) , comment char(200) ) ;

mysql> insert into hotnews(id , title , comment) values (9,"xxx","xxxx"),(9,"xxx","xxx"),(9,"xxx","xxxx");
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into hotnews(id , title , comment) values (10,"xxxA","xxxxA"),(10,"xxxA","xxxA"),(10,"xxxA","xxxxA");
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into hotnews(id , title , comment) values (11, "xxxB","xxxxA"),(11,"xxxB","xxxA"),(11,"xxxB","xxxxA");
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
  • 三台資料庫檢視資料 
[root@db1 ~]# mysql -uroot -p123456 -e 'select  * from  db1.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
| id   | title | comment |
+------+-------+---------+
|    9 | xxx   | xxxx    |
|    9 | xxx   | xxx     |
|    9 | xxx   | xxxx    |
+------+-------+---------+
[root@db2 ~]# mysql -uroot -p123456 -e 'select  * from  db2.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
| id   | title | comment |
+------+-------+---------+
|   10 | xxxA  | xxxxA   |
|   10 | xxxA  | xxxA    |
|   10 | xxxA  | xxxxA   |
+------+-------+---------+
[root@db3 ~]# mysql -uroot -p123456 -e 'select  * from  db3.hotnews'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+---------+
| id   | title | comment |
+------+-------+---------+
|   11 | xxxB  | xxxxA   |
|   11 | xxxB  | xxxA    |
|   11 | xxxB  | xxxxA   |

四、添加新庫、新表

4.1、添加新庫

  • 修改server.xml檔案

    添加GAMEDB庫

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml 
<user name="root">
      <property name="password">123456</property>
      <property name="schemas">TESTDB,GAMEDB</property>
</user>

<user name="user">
      <property name="password">user</property>
      <property name="schemas">TESTDB,GAMEDB</property>
      <property name="readOnly">true</property>
 </user>

4.2、添加新表

  • 修改schema.xml 
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="GAMEDB" checkSQLschema="false" sqlMaxLimit="100">
      <table name="company2" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
      <table name="hotnews2" rule="sharding-by-intfile" dataNode="dn1,dn2,dn3" />
</schema>

4.3、重新開機服務 

[root@mycat ~]# /usr/local/mycat/bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@mycat ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -utnlp | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      2126/java

4.4、用戶端登入檢視 

[root@client ~]# mysql -h192.168.4.56 -P8066 -uroot -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| GAMEDB   |
| TESTDB   |
+----------+
2 rows in set (0.00 sec)

mysql> use GAMEDB
mysql> show tables;
+------------------+
| Tables in GAMEDB |
+------------------+
| company2         |
| hotnews2         |
+------------------+
2 rows in set (0.00 sec)

mysql> create table company2(name char(20),addr char(50));
Query OK, 0 rows affected (0.01 sec)
mysql 資料庫 xml vim
上一篇: mysql不被人知的秘密
下一篇: 樹莓派與windows之間傳輸檔案

繼續閱讀