github.com/1aal/kubeblocks@v0.0.0-20231107070852-e1c03e598921/deploy/polardbx/scripts/gms-init.sql (about)

     1  CREATE DATABASE IF NOT EXISTS polardbx_meta_db;
     2  USE polardbx_meta_db;
     3  
     4  CREATE TABLE IF NOT EXISTS server_info (
     5    id BIGINT(11) NOT NULL auto_increment,
     6    gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     7    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
     8    inst_id VARCHAR(128) NOT NULL,
     9    inst_type INT(11) NOT NULL,
    10    ip VARCHAR(128) NOT NULL,
    11    port INT(11) NOT NULL,
    12    htap_port INT(11) NOT NULL,
    13    mgr_port INT(11) NOT NULL,
    14    mpp_port INT(11) NOT NULL,
    15    status INT(11) NOT NULL,
    16    region_id VARCHAR(128) DEFAULT NULL,
    17    azone_id VARCHAR(128) DEFAULT NULL,
    18    idc_id VARCHAR(128) DEFAULT NULL,
    19    cpu_core INT(11) DEFAULT NULL,
    20    mem_size INT(11) DEFAULT NULL,
    21    extras text DEFAULT NULL,
    22    PRIMARY KEY (id),
    23    UNIQUE KEY uk_inst_id_addr (inst_id, ip, port),
    24    INDEX idx_inst_id_status (inst_id, status)
    25  ) engine = innodb DEFAULT charset = utf8;
    26  
    27  CREATE TABLE IF NOT EXISTS storage_info (
    28    id BIGINT(11) NOT NULL auto_increment,
    29    gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    30    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
    31    inst_id VARCHAR(128) NOT NULL,
    32    storage_inst_id VARCHAR(128) NOT NULL,
    33    storage_master_inst_id VARCHAR(128) NOT NULL,
    34    ip VARCHAR(128) NOT NULL,
    35    port INT(11) NOT NULL comment 'port for mysql',
    36    xport INT(11) DEFAULT NULL comment 'port for x-protocol',
    37    user VARCHAR(128) NOT NULL,
    38    passwd_enc text NOT NULL,
    39    storage_type INT(11) NOT NULL comment '0:x-cluster, 1:mysql, 2:polardb',
    40    inst_kind INT(11) NOT NULL comment '0:master, 1:slave, 2:metadb',
    41    status INT(11) NOT NULL comment '0:storage ready, 1:storage not_ready',
    42    region_id VARCHAR(128) DEFAULT NULL,
    43    azone_id VARCHAR(128) DEFAULT NULL,
    44    idc_id VARCHAR(128) DEFAULT NULL,
    45    max_conn INT(11) NOT NULL,
    46    cpu_core INT(11) DEFAULT NULL,
    47    mem_size INT(11) DEFAULT NULL comment 'mem unit: MB',
    48    is_vip INT(11) DEFAULT NULL COMMENT '0:ip is NOT vip, 1:ip is vip',
    49    extras text DEFAULT NULL COMMENT 'reserve for extra info',
    50    PRIMARY KEY (id),
    51    INDEX idx_inst_id_status (inst_id, status),
    52    UNIQUE KEY uk_inst_id_addr (storage_inst_id, ip, port, inst_kind)
    53  ) engine = innodb DEFAULT charset = utf8;
    54  
    55  CREATE TABLE if not exists user_priv (
    56    id bigint(11) NOT NULL AUTO_INCREMENT,
    57    gmt_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    58    gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    59    user_name char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    60    host char(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    61    password char(100) COLLATE utf8_unicode_ci NOT NULL,
    62    select_priv tinyint(1) NOT NULL DEFAULT '0',
    63    insert_priv tinyint(1) NOT NULL DEFAULT '0',
    64    update_priv tinyint(1) NOT NULL DEFAULT '0',
    65    delete_priv tinyint(1) NOT NULL DEFAULT '0',
    66    create_priv tinyint(1) NOT NULL DEFAULT '0',
    67    drop_priv tinyint(1) NOT NULL DEFAULT '0',
    68    grant_priv tinyint(1) NOT NULL DEFAULT '0',
    69    index_priv tinyint(1) NOT NULL DEFAULT '0',
    70    alter_priv tinyint(1) NOT NULL DEFAULT '0',
    71    show_view_priv int(11) NOT NULL DEFAULT '0',
    72    create_view_priv int(11) NOT NULL DEFAULT '0',
    73    create_user_priv int(11) NOT NULL DEFAULT '0',
    74    meta_db_priv int(11) NOT NULL DEFAULT '0',
    75    PRIMARY KEY (id),
    76    UNIQUE KEY uk (user_name, host)
    77  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci COMMENT = 'Users and global privileges';
    78  
    79  CREATE TABLE IF NOT EXISTS quarantine_config (
    80    id BIGINT(11) NOT NULL auto_increment,
    81    gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    82    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP,
    83    inst_id VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    84    group_name VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    85    net_work_type VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    86    security_ip_type VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    87    security_ips text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    88    PRIMARY KEY (id),
    89    UNIQUE KEY uk (inst_id, group_name)
    90  ) engine = innodb DEFAULT charset = utf8 comment = 'Quarantine config';
    91  
    92  
    93  CREATE TABLE IF NOT EXISTS config_listener (
    94    id bigint(11) NOT NULL AUTO_INCREMENT,
    95    gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    96    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    97    data_id varchar(200) NOT NULL,
    98    status int NOT NULL COMMENT '0:normal, 1:removed',
    99    op_version bigint NOT NULL,
   100    extras varchar(1024) DEFAULT NULL,
   101    PRIMARY KEY (id),
   102    INDEX idx_modify_ts (gmt_modified),
   103    INDEX idx_status (status),
   104    UNIQUE KEY uk_data_id (data_id)
   105  ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
   106  
   107  create table if not exists inst_config (
   108    id bigint(11) NOT NULL AUTO_INCREMENT,
   109    gmt_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   110    gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   111    inst_id varchar(128) NOT NULL,
   112    param_key varchar(128) NOT NULL,
   113    param_val varchar(1024) NOT NULL,
   114    PRIMARY KEY (id),
   115    UNIQUE KEY uk_inst_id_key (inst_id, param_key)
   116  ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
   117  
   118  CREATE TABLE IF NOT EXISTS polardbx_extra (
   119    id BIGINT(11) NOT NULL auto_increment,
   120    inst_id VARCHAR(128) NOT NULL,
   121    name VARCHAR(128) NOT NULL,
   122    type VARCHAR(10) NOT NULL,
   123    comment VARCHAR(256) NOT NULL,
   124    status INT(4) NOT NULL,
   125    gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   126    gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on
   127               UPDATE CURRENT_TIMESTAMP,
   128    PRIMARY KEY (id),
   129    UNIQUE uk_inst_id_name_type (inst_id, name, type)
   130  ) engine = innodb DEFAULT charset = utf8 COLLATE = utf8_unicode_ci comment = 'extra table for polardbx manager';
   131  
   132  CREATE TABLE IF NOT EXISTS schema_change (
   133      id           BIGINT(11)      NOT NULL AUTO_INCREMENT,
   134      table_name   varchar(64)     NOT NULL,
   135      version      int unsigned    NOT NULL,
   136      gmt_created  timestamp       NOT NULL DEFAULT CURRENT_TIMESTAMP,
   137      gmt_modified timestamp       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   138      PRIMARY KEY (id),
   139      UNIQUE KEY table_name (table_name)
   140      )   ENGINE = innodb DEFAULT CHARSET=utf8;
   141  
   142  CREATE TABLE IF NOT EXISTS k8s_topology (
   143      id BIGINT(11) NOT NULL AUTO_INCREMENT,
   144      uid VARCHAR(128) NOT NULL,
   145      name VARCHAR(128) NOT NULL,
   146      type VARCHAR(10) NOT NULL,
   147      gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   148      gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   149      PRIMARY KEY (id),
   150      UNIQUE KEY(uid),
   151      UNIQUE KEY(name, type)
   152  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci COMMENT = 'PolarDBX K8s Topology';
   153