-zxvf actiontech-dble-2.19.11.0
.tar.gz
mv dble /opt/
2
.mysql操作
搭建主库环境
省略......
角色 主机IP server_id
Master 192.168.119.130:3306 62
Slave 192.168.119.130:3307 63
3
.修改配置文件
[root@testdb1 conf]# cat schema.xml
<?xml version=
"1.0"?>
<!--
~ Copyright (C) 2016-2020
ActionTech.
~ License: http://www.gnu.org/licenses/gpl.html GPL version 2
or higher.
-->
<!DOCTYPE dble:schema SYSTEM
"schema.dtd">
<dble:schema xmlns:dble=
"http://dble.cloud/" version=
"2.0">
<schema name=
"scott" dataNode=
"dnscott"></schema>
<schema name=
"testdb" dataNode=
"dntestdb"></schema>
<dataNode name=
"dnscott" dataHost=
"testdb1" database=
"scott"/>
<dataNode name=
"dntestdb" dataHost=
"testdb1" database=
"testdb"/>
<dataHost name=
"testdb1" maxCon=
"1000" minCon=
"10" balance=
"2" switchType=
"-1" slaveThreshold=
"100">
<heartbeat>show slave status</heartbeat>
<!-- can have multi write hosts -->
<writeHost host=
"testdb3306" url=
"192.168.119.130:3306" user=
"root" password=
"chengce243">
<!-- can have multi read hosts -->
<readHost host=
"testdb3307" url=
"192.168.119.130:3307" user=
"root" password=
"chengce243"/>
</writeHost>
</dataHost>
</dble:schema>
[root@testdb1 conf]# cat server.xml
<?xml version=
"1.0" encoding=
"UTF-8"?>
<!--
~ Copyright (C) 2016-2020
ActionTech.
~ License: http://www.gnu.org/licenses/gpl.html GPL version 2
or higher.
-->
<!-- - - Licensed under the Apache License, Version 2.0 (the
"License");
- you may
not use this file
except in compliance with the License. -
You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law
or agreed to
in writing, software -
distributed under the License is distributed on an
"AS IS" BASIS, -
WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -
See the
License for the specific language governing permissions
and -
limitations
under the License. -->
<!DOCTYPE dble:server SYSTEM
"server.dtd">
<dble:server xmlns:dble=
"http://dble.cloud/" version=
"2.0">
<system>
<property name=
"sequnceHandlerType">2</property>
<!-- serverBacklog size,default 2048-->
<property name=
"serverBacklog">2048</property>
<property name=
"checkTableConsistency">0</property>
<!-- check periodt, he default period
is 60000 milliseconds -->
<property name=
"checkTableConsistencyPeriod">60000</property>
<property name=
"dataNodeIdleCheckPeriod">300000</property>
<property name=
"dataNodeHeartbeatPeriod">10000</property>
<!-- processor check conn-->
<property name=
"processorCheckPeriod">1000</property><!-- unit millisecond -->
<property name=
"sqlExecuteTimeout">300</property><!-- unit second -->
<property name=
"idleTimeout">1800000</property><!-- unit millisecond -->
<property name=
"recordTxn">0</property>
<!-- XA transaction -->
<!-- use XA transaction ,
if the mysql service crash,the unfinished XA commit/rollback will retry
for several times
it is the check period
for ,default
is 1000 milliseconds-->
<property name=
"xaSessionCheckPeriod">1000</property>
<!-- use XA transaction ,the finished XA log will removed. the default period
is 1000 milliseconds-->
<property name=
"xaLogCleanPeriod">1000</property>
<!-- true
is use JoinStrategy, default false-->
<property name=
"useJoinStrategy">true</property>
<property name=
"nestLoopConnSize">4</property>
<property name=
"nestLoopRowsSize">2000</property>
<!-- query memory used
for per session,unit
is M-->
<property name=
"otherMemSize">4</property>
<property name=
"orderMemSize">4</property>
<property name=
"joinMemSize">4</property>
<property name=
"bufferPoolChunkSize">4096</property>
<property name=
"bufferPoolPageNumber">256</property>
<property name=
"bufferPoolPageSize">2097152</property>
<property name=
"useSqlStat">0</property>
<property name=
"enableSlowLog">0</property>
<property name=
"flushSlowLogPeriod">1</property>
<property name=
"flushSlowLogSize">1000</property>
<property name=
"sqlSlowTime">100</property>
</system>
<user name=
"man1">
<property name=
"password">654321</property>
<property name=
"manager">true</property>
<!-- manager user can
‘t set schema-->
</user>
<user name=
"root">
<property name=
"password">chengce243</property>
<property name=
"schemas">scott,testdb</property>
</user>
<user name=
"user">
<property name=
"password">user</property>
<property name=
"usingDecrypt">0</property>
<property name=
"schemas">scott,testdb</property>
<property name=
"readOnly">true</property>
<property name=
"maxCon">100</property>
</user>
</dble:server>
[root@testdb1 conf]# cat rule.xml
<?xml version=
"1.0" encoding=
"UTF-8"?>
<!--
~ Copyright (C) 2016-2020
ActionTech.
~ License: http://www.gnu.org/licenses/gpl.html GPL version 2
or higher.
-->
<!-- - - Licensed under the Apache License, Version 2.0 (the
"License");
- you may
not use this file
except in compliance with the License. -
You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law
or agreed to
in writing, software -
distributed under the License is distributed on an
"AS IS" BASIS, -
WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -
See the
License for the specific language governing permissions
and -
limitations
under the License. -->
<!DOCTYPE dble:rule SYSTEM
"rule.dtd">
<dble:rule xmlns:dble=
"http://dble.cloud/" version=
"2.0">
<tableRule name=
"rule_enum">
<rule>
<columns>code</columns>
<algorithm>func_enum</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_range">
<rule>
<columns>id</columns>
<algorithm>func_range</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_common_hash">
<rule>
<columns>id</columns>
<algorithm>func_common_hash</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_common_hash2">
<rule>
<columns>id2</columns>
<algorithm>func_common_hash</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_uneven_hash">
<rule>
<columns>id</columns>
<algorithm>func_uneven_hash</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_mod">
<rule>
<columns>id</columns>
<algorithm>func_mod</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_jumpHash">
<rule>
<columns>code</columns>
<algorithm>func_jumpHash</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_hashString">
<rule>
<columns>code</columns>
<algorithm>func_hashString</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_date">
<rule>
<columns>create_date</columns>
<algorithm>func_date</algorithm>
</rule>
</tableRule>
<tableRule name=
"rule_pattern">
<rule>
<columns>id</columns>
<algorithm>func_pattern</algorithm>
</rule>
</tableRule>
<!-- enum partition -->
<function name=
"func_enum" class=
"Enum">
<property name=
"mapFile">partition-enum.txt</property>
<property name=
"defaultNode">0</property><!--the default
is -1,means unexpected value will report error-->
<property name=
"type">0</property><!--0 means key
is a number, 1 means key
is a string-->
</function>
<!-- number range partition -->
<function name=
"func_range" class=
"NumberRange">
<property name=
"mapFile">partition-number-range.txt</property>
<property name=
"defaultNode">0</property><!--he default
is -1,means unexpected value will report error-->
</function>
<!-- Hash partition,when partitionLength=1, it
is a mod partition-->
<!--MAX(sum(count*length[i]) must
not more then 2880-->
<function name=
"func_common_hash" class=
"Hash">
<property name=
"partitionCount">2</property>
<property name=
"partitionLength">512</property>
</function>
<!-- Hash partition,when partitionLength=1, it
is a mod partition-->
<!--MAX(sum(count*length[i]) must
not more then 2880-->
<function name=
"func_uneven_hash" class=
"Hash">
<property name=
"partitionCount">2,1</property>
<property name=
"partitionLength">256,512</property>
</function>
<!-- eg: mod 4 -->
<function name=
"func_mod" class=
"Hash">
<property name=
"partitionCount">4</property>
<property name=
"partitionLength">1</property>
</function>
<!-- jumpStringHash partition
for string-->
<function name=
"func_jumpHash" class=
"jumpStringHash">
<property name=
"partitionCount">2</property>
<property name=
"hashSlice">0:2</property>
</function>
<!-- Hash partition
for string-->
<function name=
"func_hashString" class=
"StringHash">
<property name=
"partitionCount">4</property>
<property name=
"partitionLength">256</property>
<property name=
"hashSlice">0:2</property>
<!--<property name=
"hashSlice">-4:0</property> -->
</function>
<!-- date partition -->
<!-- 4
case:
1.set sEndDate
and defaultNode: input <sBeginDate ,router to defaultNode; input>
sEndDate ,mod the period
2.set sEndDate, but no defaultNode:input <sBeginDate report error; input>
sEndDate ,mod the period
3.set defaultNode without sEndDate: input <sBeginDate router to defaultNode;input>sBeginDate + (node size)*sPartionDay-1 will report error(expected
is defaultNode,but can
‘t control now)
4.sEndDate
and defaultNode are all
not set: input <sBeginDate report error;input>sBeginDate + (node size)*sPartionDay-1
will report error
-->
<function name=
"func_date" class=
"Date">
<property name=
"dateFormat">yyyy-MM-dd</property>
<property name=
"sBeginDate">2015-01-01</property>
<property name=
"sEndDate">2015-01-31 </property> <!--
if not set sEndDate,then
in fact ,the sEndDate = sBeginDate+ (node size)*sPartionDay-1 -->
<property name=
"sPartionDay">10</property>
<property name=
"defaultNode">0</property><!--the default
is -1-->
</function>
<!-- pattern partition -->
<!--mapFile must contains all value of 0~patternValue-1,key
and value must be Continuous increase-->
<function name=
"func_pattern" class=
"PatternRange">
<property name=
"mapFile">partition-pattern.txt</property>
<property name=
"patternValue">1024</property>
<property name=
"defaultNode">0</property><!--contains string which
is not number,router to default node-->
</function>
</dble:rule>
4
.启动 dble
cd /opt/dble/
bin
./dble start &
查看dble 状态：
./
dble status
dble-server
is running (1656
).
如果是running则是正常，若不是runing状态，则需要看log日志排查原因，一般都是配置文件配置错误导致。
5
.查询
[root@testdb1 ~]
# mysql -uuser -puser -h192.168.119.130 -P8066 -A
分别测试下它们是否能读、写数据
[root@testdb1 ~]
# mysql -uuser -puser -h192.168.119.130 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 63 |
+-------------+
读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。
[root@testdb1 ~]
# mysql -uuser -puser -h192.168.119.130 -P8066 -e ‘\
start transaction;select @@server_id;commit;select @@server_id;‘
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 62 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 63 |
+-------------+
dble主从读写分离范例
标签：time sof ice pattern scott day imp port size
