|
近日嘗試了關(guān)于Mycat分表分庫的特性，這里做一些整理，作為入門的參考。
本文不對Mycat配置的每一項都進(jìn)行詳細(xì)解釋，這些內(nèi)容在其官方的權(quán)威指南中都有介紹，這里闡述部署mycat的實際操作過程，也算是對文檔沒有交代部分的一個補充吧。
一、方案規(guī)劃
部署模型如圖
其中，有兩臺虛擬機(jī)：192.168.1.21和192.168.1.22，前者用于部署Mycat-server服務(wù)和1個mysql實例，后者部署2個mysql實例，這里為了簡化部署模型，未考慮mysql的主從復(fù)制，3個實例均獨立。
現(xiàn)在假設(shè)系統(tǒng)的數(shù)據(jù)庫為messagedb，里面只有2張表，一張表為消息表：message，一張表示消息來源的字典表：source，本案實現(xiàn)的是按自然月分片的規(guī)則，因此上述3個mysql實例各自需要創(chuàng)建4個數(shù)據(jù)庫，即：
| host |
database |
host |
database |
| 192.168.1.21:3307 |
message201601 |
192.168.1.22:3307 |
message201605 |
|
message201602 |
|
message201606 |
|
message201603 |
|
message201607 |
|
message201604 |
|
message201608 |
|
|
192.168.1.22:3308 |
message201609 |
|
|
|
message201610 |
|
|
|
message201611 |
|
|
|
message201612 |
說明：如果是剛接觸Mycat的小伙伴對分片不太理解，簡單地說，對于Mycat，一個分片表示某一個MySQL實例上的某一個數(shù)據(jù)庫，即schema@host，于是當(dāng)我們原先的一張大表需要分片的時候，mycat就會按照我們設(shè)定的規(guī)則，把這張大表中的數(shù)據(jù)分散到各個分片上，即所謂的分表分庫，因此我們需要在每個對應(yīng)的分片上創(chuàng)建相同名稱的數(shù)據(jù)庫，相同結(jié)構(gòu)的表。
二、環(huán)境準(zhǔn)備
首先假設(shè)mysql的3個實例都已經(jīng)部署完成，然后將所有實例都啟動起來。如果需要了解mysql的安裝部署過程可以參考原文：http://www./2016/12/21/342/
現(xiàn)在開始創(chuàng)建數(shù)據(jù)庫了，因為都是相同的數(shù)據(jù)庫名和表結(jié)構(gòu)，所以可以用一段腳本來完成工作：
db_host=( "192.168.1.21" "192.168.1.22" "192.168.1.22" )
db_port=( "3307" "3307" "3308" )
mysql_user=root
mysql_passwd=root123
mysql_cmd="/application/mysql/bin/mysql -u$mysql_user -p$mysql_passwd -e"
for (( i=0;i<${#db_host[@]};i++ ))
do
db="-h${db_host[$i]} -P${db_port[$i]}";
for (( j=1;j<=4;j++ ))
do
n=$(( $i*4+$j ))
[ $n -lt 10 ] && db_name="message20160"$n || db_name="message2016"$n
$mysql_cmd "drop database if exists $db_name" $db >/dev/null 2>&1
echo "creating database "$db_name"..."
$mysql_cmd "create database $db_name;" $db >/dev/null 2>&1
$mysql_cmd "use $db_name;source /data/tb.sql;" $db 2>/dev/null
done
$mysql_cmd "show databases;" $db 2> /dev/null
done
其中tb.sql的內(nèi)容如下：
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：消息的內(nèi)容
- create_time：創(chuàng)建時間，這也是mycat進(jìn)行分片時的參考字段
- source_id：source表的外鍵
另外，我們在source表插入了5條記錄，用于測試。到這里，后端數(shù)據(jù)庫的環(huán)境就搭建完成了。
三、安裝和配置Mycat
安裝Mycat的過程比較簡單，在這個地址就可以下載安裝包：https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE。
下載完之后，就進(jìn)行解壓，創(chuàng)建軟鏈接，創(chuàng)建相應(yīng)的用戶，修改目錄權(quán)限等套路操作，這里就不細(xì)說了。
安裝完之后，簡單地看一下mycat目錄結(jié)構(gòu)：
# tree mycat -L 1
mycat
|-- bin
|-- catlet
|-- conf
|-- lib
|-- logs
`-- version.txt
我們可以通過bin目錄下的mycat可執(zhí)行文件啟動服務(wù)：bin/mycat start。啟動之后可以看一下，端口打開的情況：
# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1146/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2249/master
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 12842/java
tcp6 0 0 :::9066 :::* LISTEN 12842/java
tcp6 0 0 :::42346 :::* LISTEN 12842/java
tcp6 0 0 :::3307 :::* LISTEN 3323/mysqld
tcp6 0 0 :::22 :::* LISTEN 1146/sshd
tcp6 0 0 ::1:25 :::* LISTEN 2249/master
tcp6 0 0 :::34141 :::* LISTEN 12842/java
tcp6 0 0 :::1984 :::* LISTEN 12842/java
tcp6 0 0 :::8066 :::* LISTEN 12842/java
其中，9066端口是管理端口，提供查看當(dāng)前系統(tǒng)節(jié)點的情況，報告心跳狀態(tài)等相關(guān)系統(tǒng)監(jiān)控的功能，8066是數(shù)據(jù)端口，相當(dāng)于數(shù)據(jù)庫的訪問端口。我們可以使用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。
3.1 server.xml
該配置文件是用于配置mycat的系統(tǒng)信息，主要有兩個標(biāo)簽：system和user。這里的user就是上述訪問mycat服務(wù)的用戶，不是后端數(shù)據(jù)庫的用戶。如果我們使用默認(rèn)的配置，server.xml大概是這樣的：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://cat/">
<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標(biāo)簽下schemas屬性表示該用戶可以訪問的數(shù)據(jù)庫，可以定義多個數(shù)據(jù)庫，用英文逗號隔開。schemas定義的數(shù)據(jù)庫，一定要配置在后面的schema.xml文件對應(yīng)的邏輯庫，否則會提示無法訪問。
system標(biāo)簽暫時使用默認(rèn)的配置，不做過多的討論?？傊?，先讓你的貓先跑起來再考慮其他玩法。
3.2 schema.xml
schema配置文件比較復(fù)雜，也是最關(guān)鍵的一個配置文件，定義了mycat中的邏輯庫、邏輯表，和分片的相關(guān)信息。配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://cat/">
<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" />
<!-- 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="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="message201601" />
<dataNode name="dn2" dataHost="mysql-01" database="message201602" />
<dataNode name="dn3" dataHost="mysql-01" database="message201603" />
<dataNode name="dn4" dataHost="mysql-01" database="message201604" />
<dataNode name="dn5" dataHost="mysql-02" database="message201605" />
<dataNode name="dn6" dataHost="mysql-02" database="message201606" />
<dataNode name="dn7" dataHost="mysql-02" database="message201607" />
<dataNode name="dn8" dataHost="mysql-02" database="message201608" />
<dataNode name="dn9" dataHost="mysql-03" database="message201609" />
<dataNode name="dn10" dataHost="mysql-03" database="message201610" />
<dataNode name="dn11" dataHost="mysql-03" database="message201611" />
<dataNode name="dn12" dataHost="mysql-03" database="message201612" />
<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.1.21:3307" user="root"
password="root123">
</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.1.22:3307" user="root"
password="root123">
</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.1.22:3308" user="root"
password="root123">
</writeHost>
</dataHost>
</mycat:schema>
幾點要說明一下：
- schema標(biāo)簽定義邏輯庫，其下table子標(biāo)簽定義邏輯表，datanode屬性定義該邏輯表需要分布到哪幾個分片上，rule屬性表示使用何種分片規(guī)則，這里我們選擇sharding-by-month，這個規(guī)則的名稱是自定義的，只要和后面的rule.xml對應(yīng)起來即可
- source表是一張全局表，這里需要使用type=”global”來定義，這樣mycat就可以幫我們在指定的分片上克隆相同的數(shù)據(jù)，這對join查詢是非常有好處的。
- datanode標(biāo)簽定義了分片，datahost是主機(jī)名，對應(yīng)dataHost標(biāo)簽的name屬性值，database定義該主機(jī)數(shù)據(jù)庫實例上的具體數(shù)據(jù)庫名。
- dataHost標(biāo)簽定義數(shù)據(jù)庫實例，其下heartbeart標(biāo)簽表示心跳檢測所使用的方法，writeHost標(biāo)簽定義寫數(shù)據(jù)的實例，另外還有readHost標(biāo)簽可以定義讀數(shù)據(jù)的實例，這里不考慮讀寫分離，僅使用寫實例，因此需要把balance屬性設(shè)置為0
- 其他屬性可以自行查閱官方權(quán)威指南
- 最后，出于規(guī)范和安全考慮，最好不使用數(shù)據(jù)庫的root用戶，而是另外再創(chuàng)建一個用于mycat訪問的用戶。
3.3 rule.xml
rule.xml中定義了很多分片的規(guī)則，具體規(guī)則的算法可以參考官方權(quán)威指南，這里我們直接使用默認(rèn)的就可以了，其中按自然月的分片規(guī)則配置如下：
<mycat:rule xmlns:mycat="http://cat/">
...
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
...
<function name="partbymonth"
class="cat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2016-01-01</property>
</function>
...
</mycat:rule>
- tableRule標(biāo)簽定義分片規(guī)則的，其下columns標(biāo)簽表示對數(shù)據(jù)庫表中的哪個字段應(yīng)用規(guī)則，algorithm指定實現(xiàn)算法的名稱，對應(yīng)的是function標(biāo)簽中的name屬性值
- function標(biāo)簽定義對應(yīng)的實現(xiàn)類，以及參數(shù)，包括dateFormat（日期格式）和sBeginDate（起始日期）
說明：起始日期是用來計算數(shù)據(jù)所在的分片位置，例如2016年1月的message就會找到第1個分片，即dn1，2016年12月的message就會找到第12個分片，即dn12，但是如果出現(xiàn)了2017年1月的message，mycat就會去找第13個分片，但是配置文件中又沒有對應(yīng)的配置，那么就會拋出無法找到分片的錯誤。
最后再來總結(jié)一下配置文件的關(guān)系:
如圖所示，server.xml定義了訪問mycat服務(wù)的用戶，以及該用戶授權(quán)的數(shù)據(jù)庫（邏輯庫），schema.xml定義了具體的邏輯庫，邏輯表，以及分片和數(shù)據(jù)庫實例的信息，rule.xml分片規(guī)則和實現(xiàn)類。
四、測試
到這里已經(jīng)完成了mycat的配置文件，但先不急著往里面灌數(shù)據(jù)，我們先訪問管理端口9066，看一下運行情況：
mysql -h127.0.0.1 -uadmin -p -P 9066
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2016, 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/message201601 | 0 | mysql | 0 | 9 | 1000 | 12282 | 0 | 0 | 0 | -1 |
| dn10 | mysql-03/message201610 | 0 | mysql | 0 | 0 | 1000 | 2639 | 0 | 0 | 0 | -1 |
| dn11 | mysql-03/message201611 | 0 | mysql | 0 | 1 | 1000 | 2640 | 0 | 0 | 0 | -1 |
| dn12 | mysql-03/message201612 | 0 | mysql | 0 | 9 | 1000 | 12283 | 0 | 0 | 0 | -1 |
| dn2 | mysql-01/message201602 | 0 | mysql | 0 | 0 | 1000 | 2638 | 0 | 0 | 0 | -1 |
| dn3 | mysql-01/message201603 | 0 | mysql | 0 | 0 | 1000 | 2642 | 0 | 0 | 0 | -1 |
| dn4 | mysql-01/message201604 | 0 | mysql | 0 | 0 | 1000 | 2638 | 0 | 0 | 0 | -1 |
| dn5 | mysql-02/message201605 | 0 | mysql | 0 | 9 | 1000 | 12289 | 0 | 0 | 0 | -1 |
| dn6 | mysql-02/message201606 | 0 | mysql | 0 | 0 | 1000 | 2640 | 0 | 0 | 0 | -1 |
| dn7 | mysql-02/message201607 | 0 | mysql | 0 | 1 | 1000 | 2645 | 0 | 0 | 0 | -1 |
| dn8 | mysql-02/message201608 | 0 | mysql | 0 | 0 | 1000 | 2638 | 0 | 0 | 0 | -1 |
| dn9 | mysql-03/message201609 | 0 | mysql | 0 | 0 | 1000 | 2638 | 0 | 0 | 0 | -1 |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
12 rows in set (2.17 sec)
mysql> show @@heartbeat;
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM2 | mysql | 192.168.1.22 | 3307 | 1 | 0 | idle | 0 | 2,2,1 | 2016-12-21 20:03:02 | false |
| hostM1 | mysql | 192.168.1.21 | 3307 | 1 | 0 | idle | 0 | 0,0,0 | 2016-12-21 20:03:02 | false |
| hostM3 | mysql | 192.168.1.22 | 3308 | 1 | 0 | idle | 0 | 0,1,1 | 2016-12-21 20:03:02 | false |
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.21 sec)
如果看到各個節(jié)點都已經(jīng)出現(xiàn)，并且心跳狀態(tài)RS_CODE=1，則表示后端數(shù)據(jù)庫連接正常。
現(xiàn)在我們用JDBC的方式批量插入1000萬數(shù)據(jù)：
public class TestMyCat {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://192.168.1.21:8066/message?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(2016, 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;，可以適當(dāng)調(diào)大一下my.cnf下的max_binlog_cache_size參數(shù)。
最后我們來檢驗一下分片的結(jié)果，其中message表中的數(shù)據(jù)根據(jù)create_time的值按月進(jìn)行了分片，而source表作為全局表，則其數(shù)據(jù)出現(xiàn)在了每個分片上，下面貼出部分結(jié)果：
mysql -h192.168.1.21 -uroot -proot123 -P3307 -e "select min(create_time),max(create_time) from message201602.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2016-02-01 | 2016-02-29 |
+------------------+------------------+
mysql -h192.168.1.22 -uroot -proot123 -P3307 -e "select min(create_time),max(create_time) from message201605.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2016-05-01 | 2016-05-31 |
+------------------+------------------+
mysql -h192.168.1.22 -uroot -proot123 -P3308 -e "select min(create_time),max(create_time) from message201609.message;":
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2016-09-01 | 2016-09-30 |
+------------------+------------------+
mysql -h192.168.1.21 -uroot -proot123 -P3307 -e "select * from message201601.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
mysql -h192.168.1.22 -uroot -proot123 -P3307 -e "select * from message201607.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
mysql -h192.168.1.22 -uroot -proot123 -P3308 -e "select * from message201611.source"
+----+--------+
| id | name |
+----+--------+
| 1 | weibo |
| 2 | weixin |
| 3 | qq |
| 4 | email |
| 5 | sms |
+----+--------+
五、總結(jié)
本文就mycat分片的特性進(jìn)行一次實戰(zhàn)操作，完成了部署mycat-server以及后端mysql數(shù)據(jù)庫，并以按自然月為分片規(guī)則進(jìn)行了相關(guān)的配置，最后做了一個小的測試來驗證分片功能的正確性。
mycat還有其他比較強大的特性還有待進(jìn)一步的研究使用，下一步的工作：
- 完成讀寫分離的配置和測試
- 整合zookeerp實現(xiàn)高可用集群
六、參考資料
- 周繼峰 馮鉆優(yōu) 陳勝尊 左越宗. 分布式數(shù)據(jù)庫架構(gòu)及企業(yè)實踐：基于Mycat中間件[M]. 電子工業(yè)出版社, 2016.
- mycat開源項目組. Mycat權(quán)威指南. http:///document/Mycat_V1.6.0.pdf
|
