Kafka-web-console Connecting tables and data information to MySQL database

/*
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 forgroups

DROP TABLE IF EXISTS groups ;
CREATE TABLE groups (
idbigint (+) not NULL,
namevarchar (+) 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 foroffsetHistory

DROP TABLE IF EXISTS offsetHistory ;
CREATE TABLE offsetHistory (
idbigint (+) unsigned not NULL auto_increment,
zookeeperIdbigint (+) DEFAULT NULL,
topicvarchar (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 foroffsetPoints

DROP TABLE IF EXISTS offsetPoints ;
CREATE TABLE offsetPoints (
idbigint (+) not NULL auto_increment,
consumerGroupvarchar (255) DEFAULT NULL,
timestampTimestamp null DEFAULT null on UPDATE current_timestamp,
offsetHistoryIdbigint () DEFAULT NULL,
partitionInt (2) DEFAULT NULL,
offsetbigint (+) DEFAULT NULL,
logSizebigint (+) DEFAULT NULL,
PRIMARY KEY ( id )
) Engine=innodb DEFAULT Charset=utf8;

--Records of Offsetpoints

--Table structure forplay_evolutions

DROP TABLE IF EXISTS play_evolutions ;
CREATE TABLE play_evolutions (
idInt (one) is not NULL,
hashvarchar (255) Not NULL,
applied_atTimestamp not NULL for DEFAULT current_timestamp on UPDATE current_timestamp,
apply_scriptText
revert_scriptText
statevarchar (255) DEFAULT NULL,
last_problemText
PRIMARY KEY ( id )
) Engine=innodb DEFAULT Charset=utf8;

--Records of Play_evolutions

INSERT intoplay_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 in To groups (ID, name) values (2, \ ' production\ '): \ninsert into groups (ID, name) VALUES (3, \ ' staging\ '); \ninsert into Grou PS (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, NAM e) VALUES (3, \ ' deleted\ '); ', ' DROP table if EXISTS zookeepers;\ndrop table if EXISTS groups;\ndrop table if EXISTS status ; ', ' Applied ', ' you had an error in yourR SQL Syntax; Check the manual, corresponds to your MySQL server version for the right syntax to use near \ ' \nhost Varchar,\nport in T,\nstatusid long,\ngroupid long,\nprimary KEY (name) \ n) \ ' at line 2 [error:1064, sqlstate:42000] ');
INSERT intoplay_evolutionsVALUES (' 2 ', ' f067a8799026a738d280d57a6c64352f157223a0 ', ' 2018-05-05 10:27:26 ', ' ALTER TABLE ' zookeepers ADD COLUMN Chroot VARCHAR; ', ' ALTER TABLE zookeepers DROP COLUMN chroot; ', ' Applied ', ' You have a ' error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-to-use near \ ' @ Line 1 [error:1064 , sqlstate:42000] ');
INSERT intoplay_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 zoo Keepers alter column port SET not null;\nalter table zookeepers ALTER column Statusid SET not null;\nalter TABLE zookeeper s ALTER COLUMN groupId SET not null;\nalter table zookeepers ADD UNIQUE (name) \n\ncreate table offsethistory (\nid LONG A Uto_increment PRIMARY key,\nzookeeperid long,\ntopic VARCHAR (255), \nforeign KEY (Zookeeperid) REFERENCES zookeepers (ID ), \nunique (Zookeeperid, topic) \ n) \n\ncreate TABLE offsetpoints (\nid LONG AUTOINCREMENT 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 (\nkeyvarchar (255) PRIMARY Key,\nvalue VARCHAR (255) \ n); \n\ninsert into settings (Key_, value) VALUES (\ ' PURGEschedule\ ', \ ' 0 0 0? SUN \ '); \ninsert into settings (key, value) VALUES (\ ' offset_fetch_interval\ ', \ ' 30\ '); ', ' DROP table if EXISTS offsetpoints;\ndrop table if EXISTS offsethis Tory;\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 zookeeper S 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 is only one auto column and it must is defined as a key [error:1075, sqlstate:42000] ');

--Table structure forsettings

DROP TABLE IF EXISTS settings ;
CREATE TABLE settings (
key_varchar (255) DEFAULT NULL,
valuevarchar (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 forstatus

DROP TABLE IF EXISTS status ;
CREATE TABLE status (
idbigint (+) not NULL,
namevarchar (+) 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 forzookeepers

DROP TABLE IF EXISTS zookeepers ;
CREATE TABLE zookeepers (
namevarchar (+) is not NULL,
hostvarchar (+) is not NULL,
portInt (6) is not NULL,
statusIdbigint (+) not NULL,
groupIdbigint (+) not NULL,
chrootvarchar (+) DEFAULT NULL,
idbigint (+) 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 ');

Kafka-web-console Connecting tables and data information to MySQL database