寫在前面
Mycat是基於阿里開源的Cobar產品而研發,Cobar的穩定性、可靠性、優秀的架構和效能以及眾多成熟的使用案例使得Mycat一開始就擁有一個很好的起點,站在巨人的肩膀上,我們能看到更遠。業界優秀的開源專案和創新思路被廣泛融入到Mycat的基因中,使得Mycat在很多方面都領先於目前其他一些同類的開源專案,甚至超越某些商業產品。——來自Mycat官網。
作為Mycat的核心開發者,怎能不來一波Mycat系列文章呢?
背景介紹
作為Mycat的核心開發者之一,今天,終於安排到Mycat系列文章了。在Mycat系列文章中,我們一起從一個利用Mycat實現分庫分表的案例作為入門程式。後續會持續更新Mycat原理、架構和底層原始碼解析的文章。希望Mycat系列文章能夠幫助小夥伴們徹底掌握Mycat。
那麼,今天,我們就先來一波使用Mycat實現MySQL分庫分表的文章。
注:案例中的MySQL伺服器是安裝在CentOS6.8伺服器上,Mycat Server是安裝在本機的Windows系統上,安裝在什麼環境上無所謂,這裡,我用的是VMWare虛擬機器,安裝的CentOS系統,開啟多個虛擬機器,電腦實在是吃力,所以將Mycat Server裝在了本機的Windows系統上。
方案規劃
IP | 埠 | 服務 | 使用者名稱 | 密碼 |
---|---|---|---|---|
192.168.81.131 | 3306 | MySQL資料庫 | root | root |
192.168.81.132 | 3306 | MySQL資料庫 | root | root |
192.168.81.133 | 3306 | MySQL資料庫 | root | root |
192.168.81.130 | 8066/9066 | Mycat Server | admin | admin123 |
如上表所示,在區域網的4臺主機中,131——133的主機各安裝有一臺MySQL例項,130主機,也就是本機安裝了Mycat Server。
現在假設系統的資料庫為messagedb,裡面只有2張表,一張表為訊息表:message,一張表示訊息來源的字典表:source,本案例實現的是按自然月分片的規則,因此上述3個mysql例項各自需要建立4個資料庫,即
資料庫例項 | 儲存的資料庫 |
---|---|
192.168.81.131:3306 | message202001、message202002、message202003、message202004 |
192.168.81.132:3306 | message202005、message202006、message202007、message202008 |
192.168.81.133:3306 | message202009、message202010、message202011、message202012 |
說明:如果是剛接觸Mycat的小夥伴對分片不太理解,簡單地說,對於Mycat,一個分片表示某一個MySQL例項上的某一個資料庫,即schema@host,於是當我們原先的一張大表需要分片的時候,mycat就會按照我們設定的規則,把這張大表中的資料分散到各個分片上,即所謂的分表分庫,因此我們需要在每個對應的分片上建立相同名稱的資料庫,相同結構的表。
環境準備
注意:這裡,我就省略了MySQL的安裝過程,小夥伴們可自行安裝MySQL。我後續也會在MySQL相關的專題中給大家分享企業級MySQL安裝、優化與部署過程。
建立資料庫並建表匯入資料
根據資料庫例項和儲存的資料庫對應關係表建立所有的資料庫,並在每個資料庫裡執行如下指令碼:
create table source (
id int(11) not null auto_increment primary key comment 'pk',
name varchar(10) default '' comment 'source name'
);
create table message (
id int(11) not null auto_increment primary key comment 'pk',
content varchar(255) default '' comment 'message content',
create_time date default null,
source_id int(11) not null,
foreign key(source_id) references source(id)
);
insert into `source`(`id`,`name`) values(1,'weibo');
insert into `source`(`id`,`name`) values(2,'weixin');
insert into `source`(`id`,`name`) values(3,'qq');
insert into `source`(`id`,`name`) values(4,'email');
insert into `source`(`id`,`name`) values(5,'sms');
在message表中,總共有4個欄位:
- id:主鍵
- content:訊息的內容
- create_time:建立時間,這也是mycat進行分片時的參考欄位
- source_id:source表的外來鍵
另外,我們在source表插入了5條記錄,用於測試。到這裡,後端資料庫的環境就搭建完成了。
安裝和配置Mycat
安裝Mycat
安裝Mycat的過程比較簡單,在這個地址就可以下載安裝包:https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE。下載完之後,就進行解壓到系統相應目錄,這裡就不細說了。
Mycat安裝包結構
安裝完之後,簡單地看一下mycat目錄結構:
啟動Mycat
WIndows下啟動需要以管理員身份開啟命令列視窗,cd 到Mycat的bin目錄下,或者將Mycat的 安裝目錄加入系統的環境變數path目錄裡,首先輸入命令mycat install進行mycat服務的安裝操作,然後 輸入命令mycat start 啟動Mycat Server。
Linux下進入Mycat的bin目錄直接輸入./mycat start 啟動Mycat Server。
Mycat提供了兩個埠,其中,9066埠是管理埠,提供檢視當前系統節點的情況,報告心跳狀態等相關係統監控的功能,8066是資料埠,相當於資料庫的訪問埠。我們可以使用mysql命令訪問這裡兩個埠
mysql -h[mycat_host] -u[mycat_user] -p[mycat_passwd] -P [8066|9066]
同時,我們也可以修改這兩個埠。
那麼mycat_user和mycat_passwd是如何配置呢,下面就需要介紹mycat中最主要的3個配置檔案:server.xml,schema.xml和rule.xml。
server.xml
該配置檔案是用於配置mycat的系統資訊,主要有兩個標籤:system和user。這裡的user就是上述訪問mycat服務的使用者,不是後端資料庫的使用者。如果我們使用預設的配置,server.xml大概是這樣的:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
</system>
<user name="admin">
<property name="password">admin123</property>
<property name="schemas">messagedb</property>
</user>
</mycat:server>
user標籤下schemas屬性表示該使用者可以訪問的資料庫,可以定義多個資料庫,用英文逗號隔開。schemas定義的資料庫,一定要配置在後面的schema.xml檔案對應的邏輯庫,否則會提示無法訪問。
schema.xml
schema配置檔案比較複雜,也是最關鍵的一個配置檔案,定義了mycat中的邏輯庫、邏輯表,和分片的相關資訊。配置如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="messagedb" checkSQLschema="false" sqlMaxLimit="100">
<table name="message" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" rule="sharding-by-month" />
<table name="source" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" />
</schema>
<dataNode name="dn1" dataHost="mysql-01" database="message202001" />
<dataNode name="dn2" dataHost="mysql-01" database="message202002" />
<dataNode name="dn3" dataHost="mysql-01" database="message202003" />
<dataNode name="dn4" dataHost="mysql-01" database="message202004" />
<dataNode name="dn5" dataHost="mysql-02" database="message202005" />
<dataNode name="dn6" dataHost="mysql-02" database="message202006" />
<dataNode name="dn7" dataHost="mysql-02" database="message202007" />
<dataNode name="dn8" dataHost="mysql-02" database="message202008" />
<dataNode name="dn9" dataHost="mysql-03" database="message202009" />
<dataNode name="dn10" dataHost="mysql-03" database="message202010" />
<dataNode name="dn11" dataHost="mysql-03" database="message202011" />
<dataNode name="dn12" dataHost="mysql-03" database="message202012" />
<dataHost name="mysql-01" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.81.131:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="mysql-02" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.81.132:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="mysql-03" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.81.133:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
這裡,有幾點要說明一下:
-
schema標籤定義邏輯庫,其下table子標籤定義邏輯表,datanode屬性定義該邏輯表需要分佈到哪幾個分片上,rule屬性表示使用何種分片規則,這裡我們選擇sharding-by-month,這個規則的名稱是自定義的,只要和後面的rule.xml對應起來即可
-
source表是一張全域性表,這裡需要使用type=”global”來定義,這樣mycat就可以幫我們在指定的分片上克隆相同的資料,這對join查詢是非常有好處的。
-
datanode標籤定義了分片,datahost是主機名,對應dataHost標籤的name屬性值,database定義該主機資料庫例項上的具體資料庫名。
-
dataHost標籤定義資料庫例項,其下heartbeart標籤表示心跳檢測所使用的方法,writeHost標籤定義寫資料的例項,另外還有readHost標籤可以定義讀資料的例項,這裡不考慮讀寫分離,僅使用寫例項,因此需要把balance屬性設定為0
-
出於規範和安全考慮,最好不使用資料庫的root使用者,而是另外再建立一個用於mycat訪問的使用者。
rule.xml
rule.xml中定義了很多分片的規則,具體規則的演算法可以參考官方權威指南,這裡我們直接使用預設的就可以了,其中按自然月的分片規則配置如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2020-01-01</property>
</function>
</mycat:rule>
- tableRule標籤定義分片規則的,其下columns標籤表示對資料庫表中的哪個欄位應用規則,algorithm指定實現演算法的名稱,對應的是function標籤中的name屬性值
- function標籤定義對應的實現類,以及引數,包括dateFormat(日期格式)和sBeginDate(起始日期)
說明:起始日期是用來計算資料所在的分片位置,例如2020年1月的message就會找到第1個分片,即dn1,2020年12月的message就會找到第12個分片,即dn12,但是如果出現了2018年1月的message,mycat就會去找第13個分片,但是配置檔案中又沒有對應的配置,那麼就會丟擲無法找到分片的錯誤。
綜上:server.xml定義了訪問mycat服務的使用者,以及該使用者授權的資料庫(邏輯庫),schema.xml定義了具體的邏輯庫,邏輯表,以及分片和資料庫例項的資訊,rule.xml分片規則和實現類
測試
到這裡已經完成了mycat的配置檔案,但先不急著往裡面灌資料,我們先訪問管理埠9066,看一下執行情況:
C:\Users\binghe>mysql -uadmin -padmin123 -P9066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show @@datanode;
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | mysql-01/message202001 | 0 | mysql | 0 | 4 | 1000 | 412 | 0 | 0 | 0 | -1 |
| dn10 | mysql-03/message202010 | 0 | mysql | 0 | 0 | 1000 | 11 | 0 | 0 | 0 | -1 |
| dn11 | mysql-03/message202011 | 0 | mysql | 0 | 8 | 1000 | 16 | 0 | 0 | 0 | -1 |
| dn12 | mysql-03/message202012 | 0 | mysql | 0 | 1 | 1000 | 412 | 0 | 0 | 0 | -1 |
| dn2 | mysql-01/message202002 | 0 | mysql | 0 | 2 | 1000 | 9 | 0 | 0 | 0 | -1 |
| dn3 | mysql-01/message202003 | 0 | mysql | 0 | 4 | 1000 | 11 | 0 | 0 | 0 | -1 |
| dn4 | mysql-01/message202004 | 0 | mysql | 0 | 2 | 1000 | 9 | 0 | 0 | 0 | -1 |
| dn5 | mysql-02/message202005 | 0 | mysql | 0 | 4 | 1000 | 413 | 0 | 0 | 0 | -1 |
| dn6 | mysql-02/message202006 | 0 | mysql | 0 | 2 | 1000 | 9 | 0 | 0 | 0 | -1 |
| dn7 | mysql-02/message202007 | 0 | mysql | 0 | 4 | 1000 | 11 | 0 | 0 | 0 | -1 |
| dn8 | mysql-02/message202008 | 0 | mysql | 0 | 2 | 1000 | 9 | 0 | 0 | 0 | -1 |
| dn9 | mysql-03/message202009 | 0 | mysql | 0 | 0 | 1000 | 11 | 0 | 0 | 0 | -1 |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
12 rows in set (0.00 sec)
mysql> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM2 | mysql | 192.168.81.132 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2020-03-04 14:22:59 | false |
| hostM1 | mysql | 192.168.81.131 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2020-03-04 14:22:59 | false |
| hostM3 | mysql | 192.168.81.133 | 3306 | 1 | 0 | idle | 0 | 2,1,1 | 2020-03-04 14:22:59 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.00 sec)
mysql>
如果看到各個節點都已經出現,並且心跳狀態RS_CODE=1,則表示後端資料庫連線正常。
現在我們用JDBC的方式批量插入1000萬資料:
package com.mycat.test;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Random;
import org.junit.Test;
/**
* 測試Mycat
* @author binghe
*/
public class TestMycat {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://127.0.0.1:8066/messagedb?useServerPrepStmts=false&rewriteBatchedStatements=true";
private static final String username = "admin";
private static final String password = "admin123";
@Test
public void test() throws SQLException {
Calendar calendar = Calendar.getInstance();
Random random = new Random();
calendar.set(2020, 0, 1, 0, 0, 0);
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName(driver);
connection = (Connection) DriverManager.getConnection(url, username, password);
connection.setAutoCommit(false);
String sql = "insert into message(`content`, `create_time`, `source_id`) values(?,?,?)";
ps = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 10000000; i++) {
ps.setString(1, System.currentTimeMillis() + "");
long randomtime = calendar.getTimeInMillis() + (random.nextInt(365) + 1) * 86400 * 1000l;
Date date = new Date(randomtime);
int source_id = random.nextInt(5) + 1;
ps.setDate(2, date);
ps.setInt(3, source_id);
ps.addBatch();
if (i != 0 && i % 10000 == 0) {
System.out.println("execute batch : " + i);
ps.executeBatch();
}
}
ps.executeBatch();
connection.commit();
System.out.println(System.currentTimeMillis() - start);
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (ps != null)
ps.close();
if (connection != null)
connection.close();
}
}
}
如果執行的時候報錯: Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage;,可以適當調大一下my.cnf下的max_binlog_cache_size引數。
驗證
最後我們來檢驗一下分片的結果,其中message表中的資料根據create_time的值按月進行了分片,而source表作為全域性表,則其資料出現在了每個分片上,下面貼出部分結果
mysql -h192.168.81.131 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202002.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020-02-01 | 2020-02-28 |
+------------------+------------------+
mysql -h192.168.81.132 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202005.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020-05-01 | 2020-05-31 |
+------------------+------------------+
mysql -h192.168.81.133 -uroot -proot -P3306 -e "select min(create_time),max(create_time) from message202009.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2020-09-01 | 2020-09-30 |
+------------------+------------------+
mysql -h192.168.81.131 -uroot -proot -P3306 -e "select * from message202001.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
mysql -h192.168.81.132 -uroot -proot -P3306 -e "select * from message202007.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
mysql -h192.168.81.133 -uroot -proot -P3306 -e "select * from message202011.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
總結
本文就mycat分片的特性進行一次實戰操作,完成了部署mycat-server以及後端mysql資料庫,並以按自然月為分片規則進行了相關的配置,最後做了一個小的測試來驗證分片功能的正確性。
好了,今天Mycat系列文章的入門案例就到這兒吧,大家有啥好的意見或建議都可以在文末留言,我是冰河,我們下期見!!
重磅福利
微信搜一搜【冰河技術】微信公眾號,關注這個有深度的程式設計師,每天閱讀超硬核技術乾貨,公眾號內回覆【PDF】有我準備的一線大廠面試資料和我原創的超硬核PDF技術文件,以及我為大家精心準備的多套簡歷模板(不斷更新中),希望大家都能找到心儀的工作,學習是一條時而鬱鬱寡歡,時而開懷大笑的路,加油。如果你通過努力成功進入到了心儀的公司,一定不要懈怠放鬆,職場成長和新技術學習一樣,不進則退。如果有幸我們江湖再見!
另外,我開源的各個PDF,後續我都會持續更新和維護,感謝大家長期以來對冰河的支援!!