dble集群搭建

dble官方文档

部署环境版本

1. 操作系统：CentOS7
2. jdk：1.8.0_144
3. 数据库：mysql8
4. dble：dble-3.21.06.5

安装

前置环境：安装jdk

# 安装jdk
    上传jdk压缩包 & 解压
# 配置环境变量
vim /etc/profile
export JAVA_HOME=/usr/java/jdk1.8.0_144
export PATH=$PATH:$JAVA_HOME/bin

chmod -R 777 /usr/java/jdk1.8.0_144
source /etc/profile
java -version

1、下载dble安装包

也可以在本地下载好，并上传到服务器

# 下载
wget https://github.com/actiontech/dble/releases/download/3.21.06.5%2Ftag/dble-3.21.06.5-20220824033617-linux.tar.gz
# 解压
tar -xvf dble-3.21.06.5-20220824033617-linux.tar.gz /opt/dble
cd /opt/dble/conf 
# 初始配置文件
cp cluster_template.cnf cluster.cnf
cp bootstrap_template.cnf bootstrap.cnf
cp db_template.xml db.xml
cp user_template.xml user.xml
cp sharding_template.xml sharding.xml

2、配置

|-db.xml				数据库连接配置
|-user.xml				数据库用户配置
|-sharding.xml			数据分片配置
|-cluster.cnf			集群配置
|-bootstrap.cnf			实例配置（instanceId、instanceName）

2.1、dble.xml

<dble:db xmlns:dble="http://dble.cloud/" version="4.0">
    <dbGroup name="dbGroup1" rwSplitMode="0" delayThreshold="100" disableHA="true">
        <heartbeat errorRetryCount="1" timeout="10">show slave status</heartbeat>
        <dbInstance name="instanceM2" url="ip:3306" user="root" password="xxx" maxCon="1000" minCon="10"
          primary="true">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>
    <dbGroup name="dbGroup2" rwSplitMode="2" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="instanceM2" url="ip:3306" user="root" password="xxx" maxCon="1000" minCon="10"
          primary="true" readWeight="1" id="xx1">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>
    <dbGroup name="dbGroup3" rwSplitMode="2" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="instanceM3" url="ip:3306" user="root" password="xxx" maxCon="1000" minCon="10"
          primary="true" readWeight="1" id="xx1">
            <property name="testOnCreate">true</property>
        </dbInstance>
    </dbGroup>
</dble:db>

2.2、user.xml

dble管理端和客户端登陆账号

<dble:user xmlns:dble="http://dble.cloud/" version="4.0">
    <managerUser name="man1" password="xxx" maxCon="100"/>
    <managerUser name="user" password="xxx" usingDecrypt="true" whiteIPs="127.0.0.1,0:0:0:0:0:0:0:1" readOnly="true"/>
    <shardingUser name="root" password="xxx" maxCon="4000" schemas="database1,database2,database3" readOnly="false"/>
    <blacklist name="blacklist1">
        <property name="selelctAllow">true</property>
    </blacklist>
</dble:user>

2.3、sharding.xml

dbGroup：对应一个mysql实例
database：对于一个mysql库

<dble:sharding xmlns:dble="http://dble.cloud/" version="4.0">
    <schema name="dble_database1">
        <shardingTable name="table1" shardingNode="sharding_$1-6" function="func_hashString" shardingColumn="user_id" incrementColumn="id"/>
        <globalTable name="table2,table3,table4" shardingNode="sharding_$1-6"/>
        <singleTable name="table5,table6" shardingNode="single_node"/>
    </schema>
    <shardingNode name="sharding_1" dbGroup="dbGroup1" database="db_01"/>
    <shardingNode name="sharding_2" dbGroup="dbGroup1" database="db_02"/>
    <shardingNode name="sharding_3" dbGroup="dbGroup2" database="db_03"/>
    <shardingNode name="sharding_4" dbGroup="dbGroup2" database="db_04"/>
    <shardingNode name="sharding_5" dbGroup="dbGroup3" database="db_05"/>
    <shardingNode name="sharding_6" dbGroup="dbGroup3" database="db_06"/>
    <shardingNode name="single_node" dbGroup="dbGroup1" database="db_single"/>
    <function name="func_hashString" class="StringHash">
        <property name="partitionCount">1,5</property>
        <property name="partitionLength">1,5</property>
        <property name="hashSlice">0:5</property>
    </function>
</dble:sharding>

2.4、cluster.conf

配置zookeeper地址、集群标识

#
# Copyright (C) 2016-2020 ActionTech.
# License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher.
# 是否开启集群
clusterEnable=true
# cluster ucore/zk
clusterMode=zk
# zk地址
# zk:  clusterIP=10.186.19.aa:2281,10.186.60.bb:2281
clusterIP=ip1:2181,ip2:2181,ip3:2181
# zk not need cluster.port
# clusterPort=5700
rootPath=/dble
#cluster namespace, please use the same one in one cluster
# 集群标识
clusterId=cluster-1
# if HA need sync by cluster, only useful when useOuterHa=true
# needSyncHa=false
# unit is millisecond
# showBinlogStatusTimeout=60000
sequenceHandlerType=2
# valid for sequenceHandlerType=2 or 3
#sequenceStartTime=2010-11-04 09:42:54
# valid for sequenceHandlerType=3 and clusterMode is zk, default true
#sequenceInstanceByZk=true

2.5、bootstrap.cnf

每个dble节点的配置，DinstanceName、DinstanceId、DserverId

#encoding=UTF-8
-agentlib:jdwp=transport=dt_socket,server=y,address=8088,suspend=n
-server
-XX:+AggressiveOpts
-Dfile.encoding=UTF-8
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=1984
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.host=127.0.0.1
-Xmx4G
-Xms1G
-Xss256k
-XX:MaxDirectMemorySize=2G
-XX:MetaspaceSize=100M
-XX:-OmitStackTraceInFastThrow
# GC Log
-XX:+PrintHeapAtGC
-XX:+PrintGCDateStamps
-Xloggc:./logs/gc_%WRAPPER_TIME_YYYYMMDDHHIISS%_%p.log
-XX:+PrintGCTimeStamps
-XX:+PrintGCDetails
-XX:+PrintTenuringDistribution
# CMS
-XX:+UseConcMarkSweepGC
-XX:+UseParNewGC
-XX:+CMSParallelRemarkEnabled
-XX:+UseCMSCompactAtFullCollection
-XX:CMSFullGCsBeforeCompaction=0
-XX:+CMSClassUnloadingEnabled
-XX:LargePageSizeInBytes=128M
-XX:+UseFastAccessorMethods
-XX:+UseCMSInitiatingOccupancyOnly
-XX:CMSInitiatingOccupancyFraction=70
#  base config
-DhomePath=.

# dble实例标识
-DinstanceName=dble-node1
-DinstanceId=1
-DserverId=1
#-DbindIp=0.0.0.0
#-DserverPort=8066
#-DmanagerPort=9066
#-DmaxCon=1024
#-Dprocessors=4
#-DbackendProcessors=12
#-DprocessorExecutor=4
#-DbackendProcessorExecutor=12
#-DcomplexExecutor=8
#-DwriteToBackendExecutor=4


-DfakeMySQLVersion=5.7.11

# serverBacklog size,default 2048
-DserverBacklog=2048

#-DusePerformanceMode=0
# if need out HA
-DuseOuterHa=true

# connection
#-Dcharset=utf8mb4
-DmaxPacketSize=167772160
-DtxIsolation=2
#-Dautocommit=1
#-DidleTimeout=60000

# option
#-DuseCompression=1
#-DcapClientFoundRows=false
-DusingAIO=0

-DuseThreadUsageStat=1
#  query time cost statistics
#-DuseCostTimeStat=0
#-DmaxCostStatSize=100
#-DcostSamplePercent=1


# consistency
#  check the consistency of table structure between nodes,default not
-DcheckTableConsistency=0
#  check period, he default period is 60000 milliseconds
-DcheckTableConsistencyPeriod=60000

#  processor check conn
-DprocessorCheckPeriod=1000
-DsqlExecuteTimeout=3000


#-DbackSocket unit:bytes
#-DbackSocketSoRcvbuf=4194304
#-DbackSocketSoSndbuf=1048576
#-DbackSocketNoDelay=1

#  frontSocket
#-DfrontSocketSoRcvbuf=1048576
#-DfrontSocketSoSndbuf=4194304
#-DfrontSocketNoDelay=1


#  query memory used for per session,unit is M
-DotherMemSize=4
-DorderMemSize=4
-DjoinMemSize=4


#  off Heap unit:bytes
-DbufferPoolChunkSize=32767
#-DbufferPoolPageNumber=256
-DbufferPoolPageSize=2097152
#-DmappedFileSize=2097152

# Whether the server-side cursor  is enable or not.
#-DenableCursor=false
# used for temp table persistence of cursor , Temp table which size larger than that will save to disk.
#-DmaxHeapTableSize=32768
# used for temp table persistence of cursor, setting for read-buffer size.
#-DheapTableBufferChunkSize=32768

#  sql statistics
#  1 means use SQL statistics, 0 means not
-DuseSqlStat=1
#-DbufferUsagePercent=80
-DclearBigSQLResultSetMapMs=600000
#-DsqlRecordCount=10
#-DmaxResultSet=524288


#  transaction log
#  1 enable record the transaction log, 0 disable ,the unit of transactionRotateSize is M
-DrecordTxn=0
#-DtransactionLogBaseDir=/txlogs
#-DtransactionLogBaseName=server-tx
#-DtransactionRotateSize=16
#  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
-DxaSessionCheckPeriod=1000
#  use XA transaction ,the finished XA log will removed. the default period is 1000 milliseconds
-DxaLogCleanPeriod=1000
#  XA Recovery Log path
# -DxaRecoveryLogBaseDir=/xalogs/
#  XA Recovery Log name
#-DxaRecoveryLogBaseName=xalog
#  XA Retry count, retry times in backend, 0 means always retry until success
#-DxaRetryCount=0

#-DviewPersistenceConfBaseDir=/viewPath
#-DviewPersistenceConfBaseName=viewJson

#  for join tmp results
#-DmergeQueueSize=1024
#-DorderByQueueSize=1024
#-DjoinQueueSize=1024


#  true is use JoinStrategy, default false
#-DuseJoinStrategy=true
-DnestLoopConnSize=4
-DnestLoopRowsSize=2000


#  if enable the slow query log
-DenableSlowLog=1
#  the slow query log location
#-DslowLogBaseDir=./slowlogs
#-DslowLogBaseName=slow-query
#  the max period for flushing the slow query log from memory to disk  after last time , unit is second
-DflushSlowLogPeriod=1
#  the max records for flushing the slow query log from memory to disk after last time
-DflushSlowLogSize=1000
#  the threshold for judging if the query is slow , unit is millisecond
-DsqlSlowTime=100

#  used for load data,maxCharsPerColumn means max chars length for per column when load data
#-DmaxCharsPerColumn=65535
#  used for load data, because dble need save to disk if loading file contains large size
#-DmaxRowSizeToFile=100000
#  if enable the batch load data
#-DenableBatchLoadData=1

#-DenableFlowControl=false
#-DflowControlStartThreshold=4096
#-DflowControlStopThreshold=256

#  if enable the general log
#-DenableGeneralLog=1
#  general log file path
#-DgeneralLogFile=general/general.log
#  maximum value of file, unit is mb
#-DgeneralLogFileSize=16
#  the queue size must not be less than 1 and must be a power of 2
#-DgeneralLogQueueSize=4096

#  if enable statistic sql
#-DenableStatistic=1
#-DassociateTablesByEntryByUserTableSize=1024
#-DfrontendByBackendByEntryByUserTableSize=1024
#-DtableByUserByEntryTableSize=1024
#  processing queue size must not be less than 1 and must be a power of 2
#-DstatisticQueueSize=4096
#-DinSubQueryTransformToJoin=false
#-DgroupConcatMaxLen = 1024

3、启动

cd /opt/dble/bin
# 启动 ｜ 重启
sh dble start | restart
# 查看启动日志
tail -f /opt/dble/logs/wrapper.log
tail -f /opt/dble/logs/dble.log

4、zookeeper验证

# 查看zk安装地址
which zookeeper

# 进入zookeeper目录
cd /$zkPath/bin

# 进入zk客户端
sh zkCli.sh

# 查看节点数量
ls /dble/cluster-1/online

# 查看指定节点
get /dble/cluster-1/online/1

初始化

1、初始化分片节点

使用mysql命令或者远程连接工具，连接dble管理端（9066），执行初始化命令

• 使用navicat等工具连接，mysql驱动版本需要选择5.7

# 与sharding.xml中shardingNode配置保持一致
create database @@shardingnode='sharding_$1-6';
create database @@shardingnode='single_node';

2、初始化数据库表结构

任意一个dble节点ip，连接dble客户端（8066）。执行建表sql

验证

1、验证分库是否创建

• 任意一个dble节点ip，连接dble客户端（8066），验证dble_database1是否已创建
• 连接dbGroup1，验证db_01、db_02、db_single库是否已创建
• 连接dbGroup2，验证db_03、db_04库是否已创建
• 连接dbGroup3，验证db_05、db_06库是否已创建

2、验证新增数据分片结果

• 新增N条分片表table1数据，观察是否分配到db_[01-06]库中
• 新增一条全局表table2数据，观察是否分配到db_[01-06]库中，且每个库都是全量数据
• 新增一条单节点表table5数据，观察是否只存在db_single库中

其他

1、修改配置文件user.xml，db.xml，sharding.xml

如果中dble_node1节点修改，需要使用dble_node1节点ip登陆dble管理端。
执行以下命令，可以将配置文件同步到其他节点。

# 重载节点配置文件
reload @@config_all;

2、mysql修改全局配置后，需要逐个重启dble实例才可以生效

3、dble节点不支持动态扩容。增加或者减少节点，需要重新执行初始化分片，并从dble客户端导入旧数据