Zebra管理平台库表结构及配置说明

Zebra管理平台库表结构及配置说明

数据库

建库

create database zebra;

建表

读写分离信息表

CREATE TABLE `Jdbcref` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Jdbcref` varchar(128) NOT NULL DEFAULT '',
  `Status` int(11) DEFAULT NULL,
  `Env` varchar(64) DEFAULT NULL,
  `Owner` varchar(30) DEFAULT NULL,
  `Description` text DEFAULT NULL,
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_JDBCREF_ENV` (`Jdbcref`,`Env`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

分库分表信息管理表

CREATE TABLE `Shard` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `RuleName` varchar(64) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `Env` varchar(64) DEFAULT NULL,
  `Owner` varchar(30) DEFAULT NULL,
  `Description` text COLLATE utf8mb4_unicode_ci,
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_RULENAME` (`RuleName`,`Env`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

环境及Zookeeper管理表

CREATE TABLE `ZookeeperConfig` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Host` varchar(64) DEFAULT NULL,
  `Description` text,
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Name` varchar(64) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UNIQUE_NAME` (`Name`),
  UNIQUE KEY `UNIQUE_HOST` (`Host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

配置中心配置格式说明

读写分离配置格式
Key:

zebra.group.${jdbcref}

Value:

<groupConfig>
    <SingleConfig>
        <name>${jdbcref}-n1</name>
        <writeWeight>1</writeWeight>
        <readWeight>1</readWeight>
    </SingleConfig>
    <SingleConfig>
        <name>${jdbcref}-n2</name>
        <writeWeight>-1</writeWeight>
        <readWeight>1</readWeight>
    </SingleConfig>
    <SingleConfig>
        <name>${jdbcref}-n3</name>
        <writeWeight>-1</writeWeight>
        <readWeight>1</readWeight>
    </SingleConfig>
</groupConfig>

单数据源配置格式
Key:

zebra.ds.${dsName}

Value:

 <dsConfig> 
    	<url>jdbc:mysql://127.0.0.1:3306/...</url>
    	<username>root</username>
    	<password>123456</password>
    	<driverClass>com.mysql.jdbc.Driver</driverClass>
    	<properties>idleConnectionTestPeriod=80&..</properties>
    	<active>true</active>
 </dsConfig>

分库分表配置格式
Key:

zebra.shard.${ruleName}

Value:

<tableShardConfigs>
    <tableShardConfig>
        <tableName>tbName</tableName>
        <dimensionConfigs>
            <dimensionConfig>
                <dbRule>#Id#.intdiv(8)%2</dbRule>
                <dbIndexes>jdbcref[0-1]</dbIndexes>
                <tbRule>#Id#%8</tbRule>
                <tbSuffix>alldb:[0,15]</tbSuffix>
                <tbSuffixZeroPadding>false</tbSuffixZeroPadding>
                <isMaster>true</isMaster>
            </dimensionConfig>
        </dimensionConfigs>
    </tableShardConfig>
</tableShardConfigs>