kafka监控软件kafka-web-console-2.1.0，连接Mysql数据库（ kafkamonitor ），20180505的建表语句

/*
Navicat MySQL Data Transfer


Source Server         : 206 docker mysql 13306
Source Server Version : 50720
Source Host           : 192.168.7.206:13306
Source Database       : kafkamonitor


Target Server Type    : MYSQL
Target Server Version : 50720
File Encoding         : 65001


Date: 2018-05-05 18:32:21
*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for `groups`
-- ----------------------------
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `id` bigint(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of groups
-- ----------------------------
INSERT INTO `groups` VALUES ('0', 'ALL');
INSERT INTO `groups` VALUES ('1', 'DEVELOPMENT');
INSERT INTO `groups` VALUES ('2', 'PRODUCTION');
INSERT INTO `groups` VALUES ('3', 'STAGING');
INSERT INTO `groups` VALUES ('4', 'TEST');


-- ----------------------------
-- Table structure for `offsetHistory`
-- ----------------------------
DROP TABLE IF EXISTS `offsetHistory`;
CREATE TABLE `offsetHistory` (
  `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,
  `zookeeperId` bigint(16) DEFAULT NULL,
  `topic` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of offsetHistory
-- ----------------------------
INSERT INTO `offsetHistory` VALUES ('1', '1', 'dbsync-topic');
INSERT INTO `offsetHistory` VALUES ('2', '1', 'test');
INSERT INTO `offsetHistory` VALUES ('3', '1', '__consumer_offsets');
INSERT INTO `offsetHistory` VALUES ('4', '3', 'test');
INSERT INTO `offsetHistory` VALUES ('5', '3', 'dbsync-topic');
INSERT INTO `offsetHistory` VALUES ('6', '3', '__consumer_offsets');
INSERT INTO `offsetHistory` VALUES ('7', '4', 'test');
INSERT INTO `offsetHistory` VALUES ('8', '4', 'dbsync-topic');
INSERT INTO `offsetHistory` VALUES ('9', '4', '__consumer_offsets');


-- ----------------------------
-- Table structure for `offsetPoints`
-- ----------------------------
DROP TABLE IF EXISTS `offsetPoints`;
CREATE TABLE `offsetPoints` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `consumerGroup` varchar(255) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `offsetHistoryId` bigint(20) DEFAULT NULL,
  `partition` int(2) DEFAULT NULL,
  `offset` bigint(16) DEFAULT NULL,
  `logSize` bigint(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of offsetPoints
-- ----------------------------


-- ----------------------------
-- Table structure for `play_evolutions`
-- ----------------------------
DROP TABLE IF EXISTS `play_evolutions`;
CREATE TABLE `play_evolutions` (
  `id` int(11) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `applied_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `apply_script` text,
  `revert_script` text,
  `state` varchar(255) DEFAULT NULL,
  `last_problem` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of play_evolutions
-- ----------------------------
INSERT INTO `play_evolutions` VALUES ('1', 'd8bf02bc30462083ba80bfcdefcd4d5819fae616', '2018-05-05 10:27:25', 'CREATE TABLE zookeepers (\nname VARCHAR,\nhost VARCHAR,\nport INT,\nstatusId LONG,\ngroupId LONG,\nPRIMARY KEY (name)\n);\n\nCREATE TABLE groups (\nid LONG,\nname VARCHAR,\nPRIMARY KEY (id)\n);\n\nCREATE TABLE status (\nid LONG,\nname VARCHAR,\nPRIMARY KEY (id)\n);\n\nINSERT INTO groups (id, name) VALUES (0, \'ALL\');\nINSERT INTO groups (id, name) VALUES (1, \'DEVELOPMENT\');\nINSERT INTO groups (id, name) VALUES (2, \'PRODUCTION\');\nINSERT INTO groups (id, name) VALUES (3, \'STAGING\');\nINSERT INTO groups (id, name) VALUES (4, \'TEST\');\n\nINSERT INTO status (id, name) VALUES (0, \'CONNECTING\');\nINSERT INTO status (id, name) VALUES (1, \'CONNECTED\');\nINSERT INTO status (id, name) VALUES (2, \'DISCONNECTED\');\nINSERT INTO status (id, name) VALUES (3, \'DELETED\');', 'DROP TABLE IF EXISTS zookeepers;\nDROP TABLE IF EXISTS groups;\nDROP TABLE IF EXISTS status;', 'applied', 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'\nhost VARCHAR,\nport INT,\nstatusId LONG,\ngroupId LONG,\nPRIMARY KEY (name)\n)\' at line 2 [ERROR:1064, SQLSTATE:42000]');
INSERT INTO `play_evolutions` VALUES ('2', 'f067a8799026a738d280d57a6c64352f157223a0', '2018-05-05 10:27:26', 'ALTER TABLE zookeepers ADD COLUMN chroot VARCHAR;', 'ALTER TABLE zookeepers DROP COLUMN chroot;', 'applied', 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'\' at line 1 [ERROR:1064, SQLSTATE:42000]');
INSERT INTO `play_evolutions` VALUES ('3', '843d0d81b91da54831c4a59368197e64975afcb7', '2018-05-05 10:27:27', 'ALTER TABLE zookeepers DROP PRIMARY KEY;\nALTER TABLE zookeepers ADD COLUMN id LONG NOT NULL AUTO_INCREMENT;\nALTER TABLE zookeepers ADD PRIMARY KEY (id);\nALTER TABLE zookeepers ALTER COLUMN name SET NOT NULL;\nALTER TABLE zookeepers ALTER COLUMN host SET NOT NULL;\nALTER TABLE zookeepers ALTER COLUMN port SET NOT NULL;\nALTER TABLE zookeepers ALTER COLUMN statusId SET NOT NULL;\nALTER TABLE zookeepers ALTER COLUMN groupId SET NOT NULL;\nALTER TABLE zookeepers ADD UNIQUE (name);\n\nCREATE TABLE offsetHistory (\nid LONG AUTO_INCREMENT PRIMARY KEY,\nzookeeperId LONG,\ntopic VARCHAR(255),\nFOREIGN KEY (zookeeperId) REFERENCES zookeepers(id),\nUNIQUE (zookeeperId, topic)\n);\n\nCREATE TABLE offsetPoints (\nid LONG AUTO_INCREMENT PRIMARY KEY,\nconsumerGroup VARCHAR(255),\ntimestamp TIMESTAMP,\noffsetHistoryId LONG,\npartition INT,\noffset LONG,\nlogSize LONG,\nFOREIGN KEY (offsetHistoryId) REFERENCES offsetHistory(id)\n);\n\nCREATE TABLE settings (\nkey_ VARCHAR(255) PRIMARY KEY,\nvalue VARCHAR(255)\n);\n\nINSERT INTO settings (key_, value) VALUES (\'PURGE_SCHEDULE\', \'0 0 0 ? * SUN *\');\nINSERT INTO settings (key_, value) VALUES (\'OFFSET_FETCH_INTERVAL\', \'30\');', 'DROP TABLE IF EXISTS offsetPoints;\nDROP TABLE IF EXISTS offsetHistory;\nDROP TABLE IF EXISTS settings;\n\nALTER TABLE zookeepers DROP PRIMARY KEY;\nALTER TABLE zookeepers DROP COLUMN id;\nALTER TABLE zookeepers ADD PRIMARY KEY (name);\nALTER TABLE zookeepers ALTER COLUMN host SET NULL;\nALTER TABLE zookeepers ALTER COLUMN port SET NULL;\nALTER TABLE zookeepers ALTER COLUMN statusId SET NULL;\nALTER TABLE zookeepers ALTER COLUMN groupId SET NULL;', 'applied', 'Incorrect table definition; there can be only one auto column and it must be defined as a key [ERROR:1075, SQLSTATE:42000]');


-- ----------------------------
-- Table structure for `settings`
-- ----------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `key_` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of settings
-- ----------------------------
INSERT INTO `settings` VALUES ('PURGE_SCHEDULE', '0 0 0 ? * SUN *');
INSERT INTO `settings` VALUES ('OFFSET_FETCH_INTERVAL', '30');


-- ----------------------------
-- Table structure for `status`
-- ----------------------------
DROP TABLE IF EXISTS `status`;
CREATE TABLE `status` (
  `id` bigint(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of status
-- ----------------------------
INSERT INTO `status` VALUES ('0', 'CONNECTING');
INSERT INTO `status` VALUES ('1', 'CONNECTED');
INSERT INTO `status` VALUES ('2', 'DISCONNECTED');
INSERT INTO `status` VALUES ('3', 'DELETED');


-- ----------------------------
-- Table structure for `zookeepers`
-- ----------------------------
DROP TABLE IF EXISTS `zookeepers`;
CREATE TABLE `zookeepers` (
  `name` varchar(64) NOT NULL,
  `host` varchar(64) NOT NULL,
  `port` int(6) NOT NULL,
  `statusId` bigint(32) NOT NULL,
  `groupId` bigint(32) NOT NULL,
  `chroot` varchar(64) DEFAULT NULL,
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of zookeepers
-- ----------------------------
INSERT INTO `zookeepers` VALUES ('zookeeper204', '192.168.7.204', '2181', '1', '0', '', '1');
INSERT INTO `zookeepers` VALUES ('zookeeper203', '192.168.7.203', '2181', '1', '0', '', '3');
INSERT INTO `zookeepers` VALUES ('zookeeper201', '192.168.7.201', '2181', '1', '0', '', '4');