MySQL 分庫(kù)分表

[toc]

分庫(kù) 分表

• 將存放在一個(gè)數(shù)據(jù)庫(kù)( 主機(jī) )中的數(shù)據(jù),按照特定方式進(jìn)行拆分,分散存放到多個(gè)數(shù)據(jù)庫(kù) ( 主機(jī) )中,以達(dá)到分散單臺(tái)設(shè)備負(fù)載的效果

目的

• 解決單數(shù)據(jù)庫(kù)服務(wù)器的訪問(wèn)壓力和存儲(chǔ)壓力
• 解決單表過(guò)大的問(wèn)題

水平分割

橫向切分

• 按照表中某個(gè)字段的某種規(guī)則,把表中的許多記錄按行切分,分散到多個(gè)數(shù)據(jù)庫(kù)中.

1550144306328

垂直分割

縱向切分

1. 將單個(gè)表,拆分成多個(gè)表,并分散到不同的數(shù)據(jù)庫(kù).
2. 將單個(gè)數(shù)據(jù)庫(kù)的多個(gè)表進(jìn)行分類(lèi),按業(yè)務(wù)類(lèi)別分散到不同的數(shù)據(jù)庫(kù)上

1550144227127

mycat 軟件

mycat 介紹

mycat 是基于 Java 的分布式數(shù)據(jù)庫(kù)系統(tǒng)中間層,為高并發(fā)環(huán)境的分布式訪問(wèn)提供解決方案.

• 支持 JDBC 形式連接
• 支持 MySQL Oracle Sqlserver Mongodb 等
• 提供數(shù)據(jù)讀寫(xiě)分離服務(wù)
• 可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器的高可用
• 提供數(shù)據(jù)分片服務(wù)
• 基于阿里巴巴 Cobar 進(jìn)行研發(fā)的開(kāi)源軟件
• 適合數(shù)據(jù)大量寫(xiě)入數(shù)據(jù)的存儲(chǔ)需求.( 缺點(diǎn)因?yàn)榉謳?kù)分表導(dǎo)致查詢(xún)效率變慢 )

分片規(guī)則

mycat 支持提供10中分片規(guī)則

工作過(guò)程

1550146505485

當(dāng)mycat 收到一個(gè)SQL查詢(xún)時(shí)

1. 先解析這個(gè)SQL查找涉及到的表
2. 然后看此表的定義,如果有分片規(guī)則,則獲取SQL里分片字段的值,并匹配分片函數(shù),獲得分片列表
3. 然后將SQL發(fā)往這些分片去執(zhí)行
4. 最后收集和處理所有分片結(jié)果數(shù)據(jù),并返回到客戶(hù)端

配置 mycat

環(huán)境部署

拓?fù)浣Y(jié)構(gòu)

1550147364869

IP規(guī)劃

配置 mycat

安裝

安裝 JDK

yum install java-1.8.0-openjdk

rpm -qa|grep -i jdk
java-1.8.0-openjdk-headless-1.8.0.191.b12-1.el7_6.x86_64
java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64

java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

安裝 mycat

wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

tar -xf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local

ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt

配置

目錄結(jié)構(gòu)說(shuō)明

重要配置文件說(shuō)明

配置標(biāo)簽說(shuō)明

修改配置文件注意

1550162799660

1550162840398

配置步驟

1. 定義連接 mycat 服務(wù)的 用戶(hù) 和 密碼 及 虛擬數(shù)據(jù)庫(kù)名稱(chēng).

vim conf/server.xml   
.. ..
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
        </user>

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

2. 對(duì)哪些表做數(shù)據(jù)分片及使用的分片規(guī)則

   邏輯表名 使用的分片規(guī)則 存儲(chǔ)到哪個(gè)數(shù)據(jù)庫(kù)服務(wù)器 dn1 dn2

   指定dn1 存儲(chǔ)數(shù)據(jù)庫(kù)庫(kù)名 db1

   指定dn2 存儲(chǔ)數(shù)據(jù)庫(kù)庫(kù)名 db2

   指定dn1 對(duì)應(yīng)的數(shù)據(jù)庫(kù)服務(wù)器ip 地址

   指定dn2 對(duì)應(yīng)的數(shù)據(jù)庫(kù)服務(wù)器ip 地址

vim conf/schema.xml
.. ..
    <!-- 配置 去掉 所有 dn3 節(jié)點(diǎn) 因?yàn)闇y(cè)試環(huán)境只有兩個(gè) datanode 此修改啟動(dòng)時(shí)會(huì)報(bào)錯(cuò),見(jiàn)排錯(cuò)-->
    <table name="travelrecord" dataNode="dn1,dn2" rule="auto-sharding-long" />
    <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2" />
    <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2" />

.. ..
        <!--配置 節(jié)點(diǎn) dn1 主機(jī)名為 c1 數(shù)據(jù)存儲(chǔ)至 db1 -->
        <dataNode name="dn1" dataHost="c1" database="db1" />

        <!--配置 節(jié)點(diǎn) dn2 主機(jī)名為 c2 數(shù)據(jù)存儲(chǔ)至 db2 -->
        <dataNode name="dn2" dataHost="c2" database="db2" />

        <!--配置 主機(jī)名 c1 ip 端口 mycat 訪問(wèn) c1 使用 賬戶(hù) 密碼 -->    
        <dataHost name="c1" 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.1.102:3306" user="root"
                                   password="123456">                       
                </writeHost>
        </dataHost>

        <!--配置 主機(jī)名 c2 ip 端口 mycat 訪問(wèn) c2 使用 賬戶(hù) 密碼 -->
        <dataHost name="c2" 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.1.103:3306" user="root"
                                   password="123456">
                </writeHost>
        </dataHost>
.. ..

3. 修改數(shù)據(jù)庫(kù)服務(wù)器配置文件

• 添加對(duì)應(yīng)設(shè)置后重啟 mysqld 服務(wù)

vim /etc/my.cnf
[mysqld]
#表名不區(qū)分字母大小寫(xiě)
lower_case_table_names=1

• 添加 mycat 訪問(wèn) 數(shù)據(jù)庫(kù)授權(quán)用戶(hù) 和 對(duì)應(yīng)庫(kù)

#c1 db1 
mysql> create database db1;
mysql> grant all on *.* to root@'192.168.1.101' identified by "123456";
#c2 db2
mysql> create database db2;
mysql> grant all on *.* to root@'192.168.1.101' identified by "123456";

啟動(dòng)

usr/local/mycat/bin/mycat start
Starting Mycat-server...

netstat -nltp|grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      4708/java

1550162878162

測(cè)試

mysql -h192.168.1.101 -uroot -p123456 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
mysql> use TESTDB

#mycat 上定義的邏輯表
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company          |
| customer         |
| customer_addr    |
| employee         |
| goods            |
| hotnews          |
| orders           |
| order_items      |
| travelrecord     |
+------------------+
#邏輯表是假表,不存在
mysql> desc employee;
ERROR 1146 (HY000): Table 'db1.employee' doesn't exist

#查看 schema.xml employee表 配置,使用 sharding-by-intfile 表規(guī)則,此規(guī)則是 枚舉法分片
vim conf/schema.xml
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
#查看 sharding-by-intfile 表規(guī)則 引用 partition-hash-int.txt 規(guī)則
vim conf/rule.xml
<function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
</function>
#查看 partition-hash-int.txt 規(guī)則, 10000=0 放入庫(kù)dn1 10010=1 放入庫(kù)dn2 可以添加10020 =2
vim conf/partition-hash-int.txt
10000=0
10010=1
#10020=2

#創(chuàng)建 employee 表,必須有 id 和 sharding_id 字段.會(huì)在 dn1 dn2 兩個(gè)庫(kù) 同時(shí)建立 此表.
mysql> create table employee(
    -> id int not null primary key,
    -> name varchar(100),
    -> age int(2),
    -> sharding_id int not null
    -> );
    
#插入數(shù)據(jù)
mysql> insert into employee(id,name,age,sharding_id) values(1,"bob",21,10000),(2,"lucy",18,100010);

# dn1 上查看
mysql> select * from employee;
+----+------+------+-------------+
| id | name | age  | sharding_id |
+----+------+------+-------------+
|  1 | bob  |   21 |       10000 |
+----+------+------+-------------+
# dn2 上查看
mysql> select * from employee;
+----+------+------+-------------+
| id | name | age  | sharding_id |
+----+------+------+-------------+
|  2 | lucy |   18 |       10010 |
+----+------+------+-------------+

排錯(cuò)

錯(cuò)誤1

table [ TRAVELRECORD ] rule function [ rang-long ] partition size : 3 > table datanode size : 2, please make sure table datanode size = function partition size

解決方法

錯(cuò)誤說(shuō)明:

rang-long 算法默認(rèn)需要 3個(gè) dotanode,測(cè)試拓?fù)渲挥袃蓚€(gè)dotanode,所以需要修改 autopartition-long.txt文件

#查看 rule.xml 中配置,找到 rang-long 算法的函數(shù)對(duì)應(yīng)配置 autopartition-long.txt
<function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
</function>

編輯配置 注釋掉最后一個(gè) datanode

vim conf/autopartition-long.txt
#默認(rèn)是三個(gè)妹萨，我們需要?jiǎng)h除最后一個(gè)肚医，不然就會(huì)報(bào)錯(cuò)翁逞，說(shuō)節(jié)點(diǎn)少了
#K=1000條記錄，M=10000條記錄，那么下面三個(gè)配置就是0~500萬(wàn)的記錄會(huì)存在數(shù)據(jù)庫(kù)節(jié)點(diǎn)1的表中读慎，500萬(wàn)~1000萬(wàn)會(huì)存在節(jié)點(diǎn)2的表中
0-500M=0
500M-1000M=1
#1000M-1500M=2

錯(cuò)誤2

table [ HOTNEWS ] rule function [ mod-long ] partition size : 3 > table datanode size : 2, please make sure table datanode size = function partition size

解決方法

錯(cuò)誤說(shuō)明:

和錯(cuò)誤1類(lèi)似, mod-long 算法默認(rèn)需要 3個(gè) dotanode,測(cè)試拓?fù)渲挥袃蓚€(gè)dotanode,需要修改 rule.xml 文件,修改count數(shù)為2即可

        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>