2022-01-27 使用liquibase管理mysql执行版本

目录

摘要:

数据库语句自动化执行的工具说明

需求说明:

依赖工具说明:

liquibase工程目录结构和内容示例

管控依赖miniRDS, 使用liquibase对mysql初始化示例:

工作流

新手快速入门模板:

容易发生的问题列表:

liquibase执行时间基准测试:

数据库sql语句编写要点说明:

 数据库开发规范

调研记录


摘要:

产品的管控依赖的miniMysql,  管控在运行前,  mysql中需要执行一些必要的sql语句, 一般为创建表结构和填充规格数据.

此前一直由开发人员将sql语句交给DBA手动执行,  人为执行非常容易引发错误. 是以设计sql的自动化执行的规则. 本文对其使用做说明。

本文包含两部分:

  1.  数据库语句自动化执行的工具说明 
  2.  数据库sql语句的编写要点说明

数据库语句自动化执行的工具说明

需求说明:

  1. 方便快速构建和代码匹配的数据库结构
    1. 增量修改表结构,保持数据
    2. 重建表结构,清理数据
    3. 从0构建对应版本的数据库
  2. 方便多人协作
  3. 方便分支merge
  4. 保证现网发布质量和执行效率

依赖工具说明:

采用liquibase开源工具,使用其中的sql语法描述

官网: 

Liquibase | Open Source Version Control for Your Database

官方文档:

http://www.liquibase.org/documentation/index.html

跨平台镜像:

Docker Hub

工具概述

使用ChangeLog和ChangeSet概念,所有数据库变更放在几个ChangeLog文件中,每个文件包含多个ChangeSet,一个ChangeSet表示一次修改,一个ChangeSet包含一条或者多条sql语句,包含rollback语句

在数据库中使用一张表来记录数据库的状态,包括所有应用过的ChangeSet的名字、md5、时间等

执行更新命令时会对比ChangeLog和数据库状态表,校验每个ChangeSet的md5,如果没有错误则把所有未执行的ChangeSet都执行一遍

使用标准sql,每次变更只需要额外写一行标记changeset的注释,开发人员的学习成本低,使用方便

主要命令

更新到最新版本:update、updateSQL(SQL后缀是指不实际执行命令,而是生成SQL语句)

回退版本:rollbackCount、rollbackCountSQL

清空数据库:dropAll

liquibase工程目录结构和内容示例

脚本放在源码目录下,每个版本一个changelog

目录树:

[root@BJWLC001F05S07 db-migrations]# tree
.
├── build-image-local.sh
├── build-image.sh
├── Dockerfile
├── liquibase
│   ├── db.changelog-master.xml
│   ├── liquibase.properties
│   └── sql
│       └── db.changelog-1.0.sql
└── sh
    └── update.sh

一级目录: db-migrations, 存放liquibase配置相关文件

  • db.changelog-master.xml : liquibase工程主文件
  • iquibase.properties :liquibase环境配置

二级目录: liquibase/sql, 存放sql工程文件

  • db.changelog-1.0.sql

db.changelog-master.xml文件

将sql文件通过include组织起来




    
    



liquibase.properties文件

# liquibase环境配置

# 数据库驱动,目前为mysql
driver: com.mysql.jdbc.Driver

# 日志级别
logLevel: debug

# 日志文件
logFile: lqb.log

# master工程文件
changeLogFile: db.changelog-master.xml

# 数据库地址和库名字,对应的数据库必须已经存在
url: jdbc:mysql://_HOST:_PORT/ndb_manage?useUnicode=true&characterEncoding=utf8&useSSL=false

# mysql登录用户名
username: _USER

# mysql登录密码
password: _PASSWD

db.changelog-1.0.sql文件:

格式说明:

 首行注明所使用的语法: sql

--liquibase formatted sql

changeset注明版本:

所要执行的sql语句必须包含在以下两部分以内: 

  1. --changeset zsl:003-20210715   

    1. 格式为:  作者:版本数字-日期

    2. 该changeset将会被liquibase添加到 DATABASECHANGELOG 表中, 用作执行的版本控制, 详情以下表数据说明
  2. -- rollback  

    1. 如果执行出错, 执行的回滚操作

内容举例:

--liquibase formatted sql

--changeset zsl:001-20210714
CREATE TABLE `t_account` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `account_name` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号名称',
  `host` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '允许登录主机',
  `tablespace` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '绑定的表空间(达梦)',
  `message` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '账号描述',
  `state` int(11) DEFAULT NULL COMMENT '帐号状态 1激活',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库用户表';

CREATE TABLE `t_account_privilege` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `account_name` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号名称',
  `host` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '允许登录主机',
  `database_name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '数据库名称',
  `account_privilege` text COLLATE utf8_bin COMMENT '账号权限',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库用户权限表';

CREATE TABLE `t_backup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `backup_id` varchar(64) COLLATE utf8_bin NOT NULL,
  `tenant_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '集群ID',
  `job_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '任务ID',
  `backup_name` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '备份名',
  `backup_type` int(2) NOT NULL COMMENT '1物理备份/2逻辑备份',
  `backup_mode` int(2) NOT NULL COMMENT '全量|增量',
  `backup_object` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '备份的对象,备份范围为DATABASE时为空,其他不为空',
  `backup_target` int(10) NOT NULL COMMENT '备份范围DATABASE|TABLESPACE|TABLE|SCHEMA|USER',
  `backup_state` int(2) NOT NULL COMMENT '0备份中 1备份完成 2备份错误',
  `base_path` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '本地目录',
  `oss_path` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '远端目录',
  `backup_method` int(2) DEFAULT NULL COMMENT '1手动2自动)',
  `is_tenant` tinyint(1) DEFAULT '0' COMMENT '是否租户操作(1是0否)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `message` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `backup_idx` (`backup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='备份表';

CREATE TABLE `t_backup_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `region_code` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '区域ID',
  `tenant_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '集群ID',
  `instance_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'pod ID ',
  `engine_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库类别(mysql/dm)',
  `engine_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库引擎版本(DMB/DME/DMS)',
  `policy_mode` tinyint(2) NOT NULL COMMENT '备份模式(1数据备份 2日志备份)',
  `backup_type` tinyint(2) NOT NULL COMMENT '备份类型(1物理备份/2逻辑备份)',
  `backup_time_start` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '可备份时间开始(HH:mm)',
  `backup_time_end` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '可备份时间结束(HH:mm)',
  `cron` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'cron表达式',
  `backup_period` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '备份周期 周一/二/三/四/五/六/日 对应取值 1,2,3,4,5,6,7 多个取值用英文逗号,隔开',
  `retention_period` int(10) DEFAULT '0' COMMENT '数据备份保留天数,取值:7~730',
  `enable_backup_log` tinyint(4) DEFAULT '0' COMMENT '1启用 0不启用',
  `log_backup_retention_period` int(10) DEFAULT '0' COMMENT '日志备份保留天数,取值:7~730',
  `local_log_retention_hours` int(10) DEFAULT '0' COMMENT '日志备份本地保留小时数,取值:0~7*24',
  `local_log_retention_space` int(10) DEFAULT '0' COMMENT '本地日志最大循环空间使用率,超出后,则从最早的Binlog开始清理,直到空间使用率低于该比例 取值:0~50',
  `high_space_usage_protection` int(10) DEFAULT '0' COMMENT '实例使用空间大于80%,或者剩余空间小于5GB时,是否无条件清理Binlog 0清理 1不清理',
  `log_backup_frequency` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '日志备份频率 默认与数据备份频率一致',
  `compress_type` tinyint(2) DEFAULT '0' COMMENT '备份压缩方式 1:zlib压缩,格式为tar.gz',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_delete` tinyint(4) DEFAULT '0' COMMENT '1删0正常',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='备份配置表';

CREATE TABLE `t_cluster` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `name` varchar(256) COLLATE utf8_bin DEFAULT '' COMMENT '集群名称',
  `zone_code` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '可用区编码',
  `region_code` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '区域ID',
  `namespace` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT 'namespace',
  `arch_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'CPU架构类型',
  `resource_type` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '资源类型,取值:1:独占0:共享',
  `engine_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库类别(mysql/dm)',
  `engine_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库引擎版本(DMB/DME/DMS)',
  `minor_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '小版本(v8.1.1.1)',
  `vpc_id` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT 'vpcId',
  `subnet` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '子网',
  `ha_state` int(11) DEFAULT NULL COMMENT '高可用状态',
  `cpu` int(11) DEFAULT NULL COMMENT 'CPU核数',
  `memory` bigint(20) DEFAULT NULL COMMENT '内存(MB)',
  `class_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '实例规格ID',
  `disk_space` bigint(20) DEFAULT NULL COMMENT '磁盘空间(GB)',
  `instance_mode` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '实例模式(基本/高可用/readonly)',
  `param_group_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '参数模板ID',
  `cluster_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '集群IP地址',
  `monitor_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'monitor ip地址',
  `connection_url` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '连接字符串',
  `root_user` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'root账户',
  `root_password` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'root账户密码',
  `super_user` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'super账户',
  `super_password` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'super账户密码',
  `state` int(11) DEFAULT NULL COMMENT '状态',
  `param_state` int(11) DEFAULT NULL COMMENT '参数组状态',
  `port` int(11) DEFAULT NULL COMMENT '端口',
  `message` varchar(512) COLLATE utf8_bin DEFAULT '' COMMENT '备注信息',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='集群表';

CREATE TABLE `t_cluster_ca` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `root_ca` text COLLATE utf8_bin COMMENT '自签名证书',
  `server_cert` text COLLATE utf8_bin COMMENT 'Server证书',
  `server_key` text COLLATE utf8_bin COMMENT 'Serverkey',
  `client_cert` text COLLATE utf8_bin,
  `client_key` text COLLATE utf8_bin,
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_cluster_extension` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `time_zone` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '时区',
  `account_name` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建实例时创建账号的名称',
  `account_password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建实例时创建账号的密码',
  `security_group_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '安全组ID',
  `private_ip_address` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '创建时指定的IP地址',
  `enable_ssl` tinyint(1) DEFAULT NULL COMMENT '是否启用ssl',
  `is_mini` tinyint(1) DEFAULT '0' COMMENT '是否mini实例 1是 0否',
  `extension_json` text COLLATE utf8_bin COMMENT '预留扩展字段 json',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='集群扩展属性表';

CREATE TABLE `t_cluster_image` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `engine_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库类别(mysql/dm)',
  `engine_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库引擎版本(DMB/DME/DMS)',
  `major_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库大版本(5.7/8.0)',
  `minor_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '小版本(v8.1.1.1)',
  `image` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '镜像',
  `sidecar_image` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT 'sidecar镜像',
  `is_default` tinyint(3) unsigned DEFAULT '0' COMMENT '是否默认版本',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库版本';

CREATE TABLE `t_cluster_maintain_time` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `tenant_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '租户ID',
  `region_code` varchar(128) COLLATE utf8_bin NOT NULL COMMENT '区域ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '集群ID',
  `maintain_time_start` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '可维护时间开始(HH:mm)',
  `maintain_time_end` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '可维护时间结束(HH:mm)',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='可维护时间段设置表';

CREATE TABLE `t_cluster_param` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '参数组ID',
  `param_name` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '参数名称key',
  `param_value` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '当前值value',
  `param_type` tinyint(2) DEFAULT NULL COMMENT '参数类型(1静态 2动态 3系统)',
  `default_value` varchar(128) COLLATE utf8_bin DEFAULT '' COMMENT '默认值value',
  `value_type` tinyint(2) DEFAULT NULL COMMENT '参数值类型(1字符 2整数 3枚举 4布尔型 5浮点数)',
  `value_range` varchar(512) COLLATE utf8_bin DEFAULT '' COMMENT '参数值范围',
  `remark` varchar(512) COLLATE utf8_bin DEFAULT '' COMMENT '备注信息',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='参数属性表';


CREATE TABLE `t_cluster_param_group` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '参数组ID',
  `group_name` varchar(256) COLLATE utf8_bin DEFAULT '' COMMENT '参数组名称',
  `group_type` tinyint(1) DEFAULT NULL COMMENT '参数组类别(0系统默认 1租户定义)',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID,当类别为租户定义的参数组时有效',
  `engine_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库类别(mysql/dm)',
  `engine_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库引擎版本(DMB/DME/DMS)',
  `major_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '大版本(v8.1)',
  `minor_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '小版本(v8.1.1)',
  `is_default` tinyint(1) DEFAULT '0' COMMENT '是否默认参数组(1是0否)',
  `remark` varchar(512) COLLATE utf8_bin DEFAULT '' COMMENT '备注信息',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='参数组表';

CREATE TABLE `t_cluster_tag` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `region_code` varchar(128) COLLATE utf8_bin NOT NULL,
  `tenant_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '租户ID',
  `engine_type` varchar(20) COLLATE utf8_bin NOT NULL,
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `tag_key` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `tag_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_delete` tinyint(4) DEFAULT '0' COMMENT '1删0正常',
  PRIMARY KEY (`id`),
  KEY `index_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='实例标签表';

CREATE TABLE `t_database` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `mode_type` tinyint(4) DEFAULT NULL COMMENT '库类别(1 database 2table space 3 schema)',
  `database_name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '数据库名称',
  `character_set_name` varchar(32) COLLATE utf8_bin DEFAULT '' COMMENT '数据库字符集',
  `ddl_info` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '创建DDL语句',
  `db_description` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库描述',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库表';

CREATE TABLE `t_database_account` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `account_name` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号名称',
  `host` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '允许登录主机',
  `tablespace` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '绑定的表空间(达梦)',
  `message` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '账号描述',
  `state` int(11) DEFAULT NULL COMMENT '帐号状态 1激活',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库用户表';

CREATE TABLE `t_database_account_privilege` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `account_name` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号名称',
  `host` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '允许登录主机',
  `database_name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '数据库名称',
  `account_privilege` text COLLATE utf8_bin COMMENT '账号权限',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据库用户权限表';

CREATE TABLE `t_instance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `instance_id` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '实例ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `role` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '角色(master)',
  `pod_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'pod IP地址',
  `node_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '节点IP',
  `agent_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'pod的agent IP地址',
  `state` int(11) DEFAULT NULL COMMENT '状态',
  `message` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '描述信息',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1155 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='实例表';

CREATE TABLE `t_instance_class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `engine_type` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库类别(mysql/dm)',
  `engine_version` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库版本(DMB/DME/DMS)',
  `class_name` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '规格名称',
  `class_code` varchar(256) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '规格编码',
  `arch_type` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT 'CPU架构',
  `resource_type` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '资源类型,取值:1:独占0:共享',
  `instance_mode` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '实例模式(基本/高可用/readonly)',
  `cpu` int(11) DEFAULT '0' COMMENT 'CPU核数',
  `memory` int(11) DEFAULT NULL COMMENT '内存大小(MB)',
  `max_iops` int(11) DEFAULT NULL COMMENT '最大IOPS',
  `max_connection` int(11) DEFAULT NULL COMMENT '最大连接数',
  `series` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '系列',
  `cluster` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '规格族',
  `ip_num` int(11) DEFAULT '0' COMMENT '规格需要的IP数',
  `replica_num` int(11) DEFAULT '0' COMMENT '规格副本数',
  `message` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '规格描述',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `cluster_num` int(11) DEFAULT '0' COMMENT '分片数é‡',
  `proxy_num` int(11) DEFAULT '0' COMMENT '代ç†æ•°é‡',
  `cluster_replica_num` int(11) DEFAULT '0' COMMENT '分片数',
  `minislb_num` int(11) DEFAULT '0' COMMENT 'minislb数',
  `new_connect_per_second_limit` int(11) DEFAULT '0' COMMENT '每秒新建连接数限制',
  `max_connection_by_direct` int(11) DEFAULT '0' COMMENT '直连模式下最大连接数',
  `max_connection_by_proxy` int(11) DEFAULT '0' COMMENT '代理模式下最大连接数',
  `max_band_width` int(11) DEFAULT '0' COMMENT '带宽',
  `qps_refer` int(11) DEFAULT '0' COMMENT 'qps参考值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='实例规格表';

CREATE TABLE `t_op_request_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `source` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '请求源(OP/RDS)',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `req_url` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '请求url',
  `req_method` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '请求方法',
  `req_body` text COLLATE utf8_bin COMMENT '请求body',
  `resp_code` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '响应状态码',
  `resp_body` text COLLATE utf8_bin COMMENT '响应body',
  `source_ip` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '源IP',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=167757 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='OP请求日志表';

CREATE TABLE `t_param_modify_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `tenant_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '集群ID',
  `param_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '参数key',
  `param_old_value` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '参数旧值',
  `param_new_value` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '参数新值',
  `is_effective` tinyint(1) DEFAULT '0' COMMENT '是否生效(1生效0未生效)',
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '逻辑删除标志(1删除0正常)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='参数修改历史表';

CREATE TABLE `t_restore_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `restore_id` varchar(64) COLLATE utf8_bin NOT NULL,
  `backup_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '恢复使用的备份集',
  `tenant_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '租户ID',
  `cluster_id` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '集群ID',
  `job_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '任务ID',
  `restore_type` int(2) NOT NULL COMMENT '1物理恢复/2逻辑恢复 与备份对应',
  `restore_object` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '恢复的对象,恢复范围为DATABASE时为空,其他不为空',
  `restore_target` int(10) NOT NULL COMMENT '恢复范围DATABASE|TABLESPACE|TABLE|SCHEMA|USER  与备份对应',
  `restore_state` int(2) NOT NULL COMMENT '0恢复中 1恢复完成 2恢复错误',
  `is_tenant` tinyint(1) DEFAULT '0' COMMENT '是否租户操作(1是0否)',
  `create_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `message` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `restore_idx` (`restore_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据恢复记录表';

CREATE TABLE `t_tag` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tenant_id` varchar(255) COLLATE utf8_bin NOT NULL,
  `tag_key` varchar(255) COLLATE utf8_bin NOT NULL,
  `tag_value` varchar(255) COLLATE utf8_bin NOT NULL,
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '0正常 1删除',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


--rollback              


--changeset zsl:002-20210714

INSERT INTO `t_instance_class` VALUES (1,'MySQL','MySQL','1C1G','rds.mysql.t1.small1',NULL,'0','Basic',1,1024,600,300,'标准版(单机实例)','共享型(通用型)',2,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-24 09:06:56',0,0,0,0,0,0,0,0,0),(2,'MySQL','MySQL','1C1G','rds.mysql.t1.small',NULL,'0','HA',1,1024,600,300,'标准版(单机实例)','共享型(通用型)',7,2,NULL,0,'systerm','2020-12-09 10:39:50',NULL,'2021-05-25 06:20:00',0,0,0,0,0,0,0,0,0),(3,'DM','DMB','2C4G','rdb.dm.m1.medium',NULL,'0','Basic',2,4096,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(4,'DM','DMB','4C8G','rdb.dm.s1.large',NULL,'0','Basic',4,8192,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(5,'DM','DMB','8C16G','rdb.dm.l1.xlarge',NULL,'0','Basic',8,16384,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(6,'DM','DMB','2C8G','rdb.dm.m2.medium',NULL,'0','Basic',2,8192,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(7,'DM','DMB','4C16G','rdb.dm.s2.large',NULL,'0','Basic',4,16384,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(8,'DM','DMB','8C32G','rdb.dm.l2.xlarge',NULL,'0','Basic',8,32768,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(9,'DM','DMB','12C24G','rdb.dm.c1.xlarge',NULL,'0','Basic',12,24576,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(10,'DM','DMB','16C32G','rdb.dm.c2.xlarge',NULL,'0','Basic',16,32768,NULL,NULL,NULL,NULL,1,1,NULL,1,'systerm','2020-12-09 10:39:50',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(11,'DM','DMB','1C4G','rdb.dmb.small',NULL,'0','Basic',1,4096,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(12,'DM','DMB','2C4G','rdb.dmb.large',NULL,'0','Basic',2,4096,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(13,'DM','DMB','2C8G','rdb.dmb.large2',NULL,'0','Basic',2,8192,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(14,'DM','DMB','4C8G','rdb.dmb.xlarge',NULL,'0','Basic',4,8192,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(15,'DM','DMB','4C16G','rdb.dmb.xlarge2',NULL,'0','Basic',4,16384,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(16,'DM','DMB','8C16G','rdb.dmb.2xlarge',NULL,'0','Basic',8,16384,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(17,'DM','DMB','8C32G','rdb.dmb.2xlarge2',NULL,'0','Basic',8,32768,600,300,'标准版(单机实例)','共享型(通用型)',1,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:47',0,0,0,0,0,0,0,0,0),(18,'DM','DME','2C4G','rdb.dme.large',NULL,'0','HA',2,4096,2000,1200,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(19,'DM','DME','2C8G','rdb.dme.large2',NULL,'0','HA',2,8192,4000,2000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(20,'DM','DME','4C8G','rdb.dme.xlarge',NULL,'0','HA',4,8192,5000,2000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(21,'DM','DME','4C16G','rdb.dme.xlarge2',NULL,'0','HA',4,16384,7000,4000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(22,'DM','DME','8C16G','rdb.dme.2xlarge',NULL,'0','HA',8,16384,8000,4000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(23,'DM','DME','8C32G','rdb.dme.2xlarge2',NULL,'0','HA',8,32768,12000,8000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(24,'DM','DMS','2C4G','rdb.dms.large',NULL,'0','HA',2,4096,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(25,'DM','DMS','2C8G','rdb.dms.large2',NULL,'0','HA',2,8192,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(26,'DM','DMS','4C8G','rdb.dms.xlarge',NULL,'0','HA',4,8192,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(27,'DM','DMS','4C16G','rdb.dms.xlarge2',NULL,'0','HA',4,16384,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(28,'DM','DMS','8C16G','rdb.dms.2xlarge',NULL,'0','HA',8,16384,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(29,'DM','DMS','8C32G','rdb.dms.2xlarge2',NULL,'0','HA',8,32768,NULL,NULL,NULL,NULL,NULL,1,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(30,'MySQL','MySQL','2C4G','rdb.mysql.large',NULL,'0','Basic',2,4096,600,300,'标准版(单机实例)\r\n','共享型(通用型)',1,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-24 03:41:11',0,0,0,0,0,0,0,0,0),(31,'MySQL','MySQL','2C8G','rdb.mysql.large2',NULL,'0','Basic',2,8192,600,300,'标准版(单机实例)\r\n','共享型(通用型)',2,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(32,'MySQL','MySQL','4C8G','rdb.mysql.xlarge',NULL,'0','Basic',4,8192,600,300,'标准版(单机实例)\r\n','共享型(通用型)',2,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(33,'MySQL','MySQL','4C16G','rdb.mysql.xlarge2',NULL,'0','Basic',4,16384,600,300,'标准版(单机实例)\r\n','共享型(通用型)',2,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(34,'MySQL','MySQL','8C16G','rdb.mysql.2xlarge',NULL,'0','Basic',8,16384,600,300,'标准版(单机实例)\r\n','共享型(通用型)',2,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(35,'MySQL','MySQL','8C32G','rdb.mysql.2xlarge2',NULL,'0','Basic',8,32768,600,300,'标准版(单机实例)\r\n','共享型(通用型)',2,1,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(36,'Redis','Redis','1C2G','ndb.redis.large',NULL,'0','HA',1,2048,600,300,'标准版','共享型',NULL,0,NULL,0,'system',NULL,NULL,'2021-07-13 11:28:44',0,2,2,2,0,0,0,0,0),(37,'MySQL','MINI','2C4G','rdb.mini.large',NULL,'0','HA',2,4096,600,300,'mini-rds','共享型(通用型)',1,2,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:41:35',0,0,0,0,0,0,0,0,0),(38,'MySQL','MINI','4C8G','rdb.mini.xlarge',NULL,'0','HA',4,8192,600,300,'mini-rds','共享型(通用型)',1,2,NULL,0,'systerm','2021-03-01 02:04:31',NULL,'2021-05-19 02:41:35',0,0,0,0,0,0,0,0,0),(39,'OB','OB-Oracle','8C16G','rdb.ob.xlarge2',NULL,NULL,'HA',10,65536,600,300,'金融版(三节点实例)','独享型(独占型)',4,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(40,'OB','OB-Oracle','4C8G','rdb.ob.xlarge',NULL,NULL,'HA',4,8192,600,300,'金融版(三节点实例)','独享型(独占型)',4,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(41,'OB','OB-Oracle','32C128G','rdb.ob.8xlarge2',NULL,NULL,'HA',10,65536,600,300,'金融版(三节点实例)','独享型(独占型)',4,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(43,'DM','DME','16C32G','rdb.dme.4xlarge',NULL,'0','HA',16,32768,12000,8000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(44,'DM','DME','16C64G','rdb.dme.4xlarge2',NULL,'0','HA',16,65536,14000,16000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(45,'DM','DME','32C64G','rdb.dme.8xlarge',NULL,'0','HA',32,65536,14000,16000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(46,'DM','DME','32C128G','rdb.dme.8xlarge2',NULL,'0','HA',32,131072,16000,32000,'高可用版(主备实例)','共享型(通用型)',2,2,NULL,0,'systerm','2021-01-06 09:53:53',NULL,'2021-05-19 02:37:48',0,0,0,0,0,0,0,0,0),(47,'ETCD','MINI','4C4G','minietcd.common.large',NULL,'0','HA',4,4096,0,0,NULL,NULL,3,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:41:35',0,0,0,0,0,0,0,0,0),(48,'ETCD','MINI','2C2G','minietcd.common.middle',NULL,'0','HA',2,2048,0,0,NULL,NULL,3,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:41:35',0,0,0,0,0,0,0,0,0),(49,'ETCD','MINI','1C500M','minietcd.common.small',NULL,'0','HA',1,512,0,0,NULL,NULL,3,3,NULL,0,NULL,NULL,NULL,'2021-05-19 02:41:35',0,0,0,0,0,0,0,0,0),(50,'PG','PG','4C4G','postgresql.common.small',NULL,'0','HA',4,4096,NULL,NULL,'高可用版','共享型(通用型)',4,3,NULL,0,NULL,NULL,NULL,'2021-05-19 08:26:42',0,0,0,0,0,0,0,0,0),(51,'PG','PG','8C32G','postgresql.common.middle',NULL,'0','HA',8,32768,NULL,NULL,'高可用版','共享型(通用型)',4,0,NULL,0,NULL,NULL,NULL,'2021-05-19 08:27:03',0,0,0,0,0,0,0,0,0),(52,'PG','PG','16C64G','postgresql.common.large',NULL,'0','HA',16,65536,NULL,NULL,'高可用版','共享型(通用型)',4,0,NULL,0,NULL,NULL,NULL,'2021-05-19 08:29:37',0,0,0,0,0,0,0,0,0),(53,'Redis','Redis','8C4G','redis.ha.logic.sharding.1g.4db.0rodb.4proxy.default',NULL,'0','HA',8,4096,NULL,NULL,NULL,NULL,13,8,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',4,2,2,2,40000,0,480000,0,400000),(54,'Redis','Redis','8C8G','redis.ha.logic.sharding.2g.4db.0rodb.4proxy.default',NULL,'0','HA',8,8192,NULL,NULL,NULL,NULL,13,8,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',4,2,2,2,40000,0,480000,0,400000),(55,'Redis','Redis','8C16G','redis.ha.logic.sharding.4g.4db.0rodb.4proxy.default',NULL,'0','HA',8,16384,NULL,NULL,NULL,NULL,13,8,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',4,2,2,2,40000,0,480000,0,400000),(56,'Redis','Redis','8C32G','redis.ha.logic.sharding.8g.4db.0rodb.4proxy.default',NULL,'0','HA',8,32768,NULL,NULL,NULL,NULL,13,8,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',4,2,2,2,40000,0,480000,0,400000),(57,'Redis','Redis','32C16G','redis.ha.logic.sharding.1g.16db.0rodb.16proxy.default',NULL,'0','HA',32,16384,NULL,NULL,NULL,NULL,37,32,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',16,2,2,2,40000,0,480000,0,1600000),(58,'Redis','Redis','32C32G','redis.ha.logic.sharding.2g.16db.0rodb.16proxy.default',NULL,'0','HA',32,32768,NULL,NULL,NULL,NULL,37,32,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',16,2,2,2,40000,0,480000,0,1600000),(59,'Redis','Redis','32C64G','redis.ha.logic.sharding.4g.16db.0rodb.16proxy.default',NULL,'0','HA',32,65536,NULL,NULL,NULL,NULL,37,32,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:37',16,2,2,2,40000,0,480000,0,1600000),(60,'Redis','Redis','32C128G','redis.ha.logic.sharding.8g.16db.0rodb.16proxy.default',NULL,'0','HA',32,131072,NULL,NULL,NULL,NULL,37,32,NULL,0,NULL,NULL,NULL,'2021-07-14 08:05:38',16,2,2,2,40000,0,480000,0,1600000);

--rollback 


--changeset zsl:003-20210715

INSERT INTO `t_cluster_image` VALUES (null, 'Redis', 'Redis', '5.0', '5.0.12', 'redis:5.0.12-buster', null, 1, 0, null, null, null, null);

--rollback 

管控依赖miniRDS, 使用liquibase对mysql初始化示例:

liquibase代码与管控代码关系说明:

liquibae工程目录:  db-migrations

直接作为管控代码的一部分, 一同参数管控代码的版本控制:

redis管控代码仓库:

https://code.cestc.cn/paas/ndb-redis-manage/-/tree/release-v1.0.2

liquibase工程代码仓库:

https://code.cestc.cn/paas/ndb-redis-manage/-/tree/release-v1.0.2/db-migrations

liquibase的容器使用说明:

将liquibase作为initContainers, 在管控的所有pod启动前, 执行liquibase的镜像

基线代码中使用liquibase示例: 

 https://code.cestc.cn/product-baseline/product-sys-redis/-/blob/master/manage/sys-redis-manage/templates/deployment.yaml

initContainers:

initContainers:
        - name: {{ .Values.initImage.name }}
          image: "{{ .Values.initImage.repository }}/{{ .Values.initImage.imageName }}:{{ .Values.initImage.imageTag }}"
          imagePullPolicy: {{ .Values.initImage.pullPolicy }}
          command:
          - /bin/sh
          - -ec
          - |
            bash /usr/bin/update.sh {{ .Values.requireResource.minirds_myMiniRDS01_endpoint }} {{ .Values.requireResource.minirds_myMiniRDS01_port }} {{ .Values.requireResource.minirds_myMiniRDS01_user }} {{ .Values.requireResource.minirds_myMiniRDS01_password }}

update.sh脚本说明:

  1. update.sh脚本接受mysql的链接方式作为参数, 替换掉 liquibase.properties 中的数据库链接方式
  2. 随后执行liquibase update
[root@BJWLC001F05S07 sh]# cat update.sh 
#!/bin/bash

DIR=/app
CONF=$DIR/liquibase.properties

HOST=$1
PORT=$2
USER=$3
PASSWD=$4

echo HOST=$HOST
echo PORT=$PORT
echo USER=$USER
echo PASSWD=$PASSWD

echo sed -i "s/_HOST/$HOST/g" $CONF
sed -i "s/_HOST/$HOST/g" $CONF

echo sed -i "s/_PORT/$PORT/g" $CONF
sed -i "s/_PORT/$PORT/g" $CONF

echo sed -i "s/_USER/$USER/g" $CONF
sed -i "s/_USER/$USER/g" $CONF

echo sed -i "s/_PASSWD/$PASSWD/g" $CONF
sed -i "s/_PASSWD/$PASSWD/g" $CONF

cat $CONF

cd $DIR

echo liquibase clearCheckSums
liquibase clearCheckSums

echo liquibase update
liquibase update

liquibase启动后mysql的中的结果:

表结构

[root@BJWLC001F05S04 manage]# kubectl -n product-redis exec minirds-2af55cb54eb74fa7-1 -- mysql -P 3331 -umyMiniRDS01_ndb_manage -p218d01e37c -D ndb_manage  -e "show tables;"
Defaulting container name to mysql.
Use 'kubectl describe pod/minirds-2af55cb54eb74fa7-1 -n product-redis' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
Tables_in_ndb_manage
DATABASECHANGELOG
DATABASECHANGELOGLOCK
t_account
t_account_privilege
t_backup
t_backup_config
t_cluster
t_cluster_ca
t_cluster_extension
t_cluster_image
t_cluster_maintain_time
t_cluster_param
t_cluster_param_group
t_cluster_tag
t_database
t_database_account
t_database_account_privilege
t_instance
t_instance_class
t_op_request_log
t_param_modify_log
t_restore_log
t_tag

表中插入的数据

[root@BJWLC001F05S04 manage]# kubectl -n product-redis exec minirds-2af55cb54eb74fa7-1 -- mysql -P 3331 -umyMiniRDS01_ndb_manage -p218d01e37c -D ndb_manage  -e "select * from t_instance_class where engine_type='Redis';"
Defaulting container name to mysql.
Use 'kubectl describe pod/minirds-2af55cb54eb74fa7-1 -n product-redis' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
id	engine_type	engine_version	class_name	class_code	arch_type	resource_type	instance_mode	cpu	memory	max_iops	max_connection	series	cluster	ip_num	replica_num	message	is_delete	create_by	create_time	update_by	update_time	cluster_num	proxy_num	cluster_replica_num	minislb_num	new_connect_per_second_limit	max_connection_by_direct	max_connection_by_proxy	max_band_width	qps_refer
36	Redis	Redis	1C2G	ndb.redis.large	NULL	0	HA	1	2048	600	300	???	???	NULL	0	NULL	0	system	NULL	NULL	2021-07-13 11:28:44	0	2	2	2	0	0	0	0	0
53	Redis	Redis	8C4G	redis.ha.logic.sharding.1g.4db.0rodb.4proxy.default	NULL	0	HA	8	4096	NULL	NULL	NULL	NULL	13	8	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	4	2	2	2 40000	0	480000	0	400000
54	Redis	Redis	8C8G	redis.ha.logic.sharding.2g.4db.0rodb.4proxy.default	NULL	0	HA	8	8192	NULL	NULL	NULL	NULL	13	8	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	4	2	2	2 40000	0	480000	0	400000
55	Redis	Redis	8C16G	redis.ha.logic.sharding.4g.4db.0rodb.4proxy.default	NULL	0	HA	8	16384	NULL	NULL	NULL	NULL	13	8	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	4	2	2	2 40000	0	480000	0	400000
56	Redis	Redis	8C32G	redis.ha.logic.sharding.8g.4db.0rodb.4proxy.default	NULL	0	HA	8	32768	NULL	NULL	NULL	NULL	13	8	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	4	2	2	2 40000	0	480000	0	400000
57	Redis	Redis	32C16G	redis.ha.logic.sharding.1g.16db.0rodb.16proxy.default	NULL	0	HA	32	16384	NULL	NULL	NULL	NULL	37	32	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	16	2	2	2 40000	0	480000	0	1600000
58	Redis	Redis	32C32G	redis.ha.logic.sharding.2g.16db.0rodb.16proxy.default	NULL	0	HA	32	32768	NULL	NULL	NULL	NULL	37	32	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	16	2	2	2 40000	0	480000	0	1600000
59	Redis	Redis	32C64G	redis.ha.logic.sharding.4g.16db.0rodb.16proxy.default	NULL	0	HA	32	65536	NULL	NULL	NULL	NULL	37	32	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:37	16	2	2	2 40000	0	480000	0	1600000
60	Redis	Redis	32C128G	redis.ha.logic.sharding.8g.16db.0rodb.16proxy.default	NULL	0	HA	32	131072	NULL	NULL	NULL	NULL	37	32	NULL	0	NULL	NULL	NULL	2021-07-14 08:05:38	16	2	2	2 40000	0	480000	0	1600000

[root@BJWLC001F05S04 manage]# kubectl -n product-redis exec minirds-2af55cb54eb74fa7-1 -- mysql -P 3331 -umyMiniRDS01_ndb_manage -p218d01e37c -D ndb_manage  -e "select * from t_cluster_image\G;"
Defaulting container name to mysql.
Use 'kubectl describe pod/minirds-2af55cb54eb74fa7-1 -n product-redis' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            id: 12
   engine_type: Redis
engine_version: Redis
 major_version: 5.0
 minor_version: 5.0.12
         image: redis:5.0.12-buster
 sidecar_image: NULL
    is_default: 1
     is_delete: 0
     create_by: NULL
   create_time: NULL
     update_by: NULL
   update_time: NULL

liquibase自己的表DATABASECHANGELOG中的记录:

可以看出, 每一个changset, 是一条执行记录. 

liquibase每次执行, 都会根据DATABASECHANGELOG表中的记录, 自动执行下一条记录.

[root@BJWLC001F05S04 manage]# kubectl -n product-redis exec minirds-2af55cb54eb74fa7-1 -- mysql -P 3331 -umyMiniRDS01_ndb_manage -p218d01e37c -D ndb_manage  -e "select * from DATABASECHANGELOG;"
Defaulting container name to mysql.
Use 'kubectl describe pod/minirds-2af55cb54eb74fa7-1 -n product-redis' to see all of the containers in this pod.
mysql: [Warning] Using a password on the command line interface can be insecure.
ID	AUTHOR	FILENAME	DATEEXECUTED	ORDEREXECUTED	EXECTYPE	MD5SUM	DESCRIPTION	COMMENTS	TAG	LIQUIBASE	CONTEXTS	LABELS	DEPLOYMENT_ID
001-20210714	zsl	sql/db.changelog-1.0.sql	2021-07-15 13:33:35	1	EXECUTED	8:f850de01fccaf45c54a1a3620d627c16	sql		NULL	4.2.2	NULL	NULL	6327215613
002-20210714	zsl	sql/db.changelog-1.0.sql	2021-07-15 13:33:35	2	EXECUTED	8:5dedb624b9a879da871fc3bead0aa609	sql		NULL	4.2.2	NULL	NULL	6327215613
003-20210715	zsl	sql/db.changelog-1.0.sql	2021-07-15 14:29:13	3	EXECUTED	8:4f941c08c71e085ad1b8bacf30d429c4	sql		NULL	4.2.2	NULL	NULL	6330553129

工作流

  1. 每个开发人员私服使用自己的数据库,方便本地测试,通过自动化工具可以快速的构建出和代码匹配的数据库
  2. 开发人员在上传ChangeSet之前要更新代码,检查在自己工作期间是否有新的数据库修改,如果有,需要修改自己的ChangeSet编号并测试数据库变更
  3. 测试成功需要将代码和数据库修改一起上传

新手快速入门模板:

为了便于新手快速使用liquibase将sql的执行由手动替换为自动化, 可参考以下步骤:

一. 代码部分:

db-migrations模板:

https://code.cestc.cn/zhangshilong/db-migrations-liquibase-model

修改流程:

  1. 直接将db-migrations文件夹放入自己的代码项目中
  2. 目录下有  build-image.sh , 可直接编译成镜像
  3. 根据自己的业务, 修改 liquibase/sql/db.changelog-1.0.sql中的sql语句
    1. 如果是第一次部署, 可直接向其中的changset内填写自己业务的sql语句
    2. 如果已经上线, 可让DBA将线上表结构和数据导出, 将导出的sql语句, 写在changeset内

二. 基线部分

在使用mysql的基线中, 添加initContainers, 在其中执行liquibase的镜像

基线示例:

https://code.cestc.cn/product-baseline/product-sys-redis/-/blob/release-v1.0.2/manage/sys-redis-manage/templates/deployment.yaml

initContainers:
        - name: {{ .Values.initImage.name }}
          image: "{{ .Values.image.repository }}/{{ .Values.initImage.imageName }}:{{ .Values.initImage.imageTag }}"
          imagePullPolicy: {{ .Values.image.pullPolicy }}
          command:
          - /bin/sh
          - -ec
          - |
            bash /usr/bin/update.sh {{ .Values.requireResource.minirds_myMiniRDS01_endpoint }} {{ .Values.requireResource.minirds_myMiniRDS01_port }} {{ .Values.requireResource.minirds_myMiniRDS01_user }} {{ .Values.requireResource.minirds_myMiniRDS01_password }}

容易发生的问题列表:

一. 执行liquibase命令时, 数据库未创建

说明:

liquibase的配置文件中配置了数据库名, 作为liquibase连接的数据库, 此数据库必须提前创建.

产品中使用的miniRDS可以执行初始的数据库自动创建.

手动搭建的 mysql, 则必须提前创建该库.

二. rollback的回滚操作包含高风险的sql语句

例如:

  • 未备份数据情况下直接删除数据库
  • 未备份数据情况下直接删除表
  • 未备份数据情况下直接清空表数据

三. 写sql未考虑执行出错的情况, 没有编写对应的rolllback回滚操作

例如:

  • 插入数据, 插入一半执行失败,没有回滚则导致数据库中遗留错误数据

四. 拆分过多的changeset, 没有和代码版本保持统一, 导致一旦出现问题回滚代码版本时, 回滚数据库版本困难

五. sql编写未考虑与线上数据兼容, 导致对线上业务造成影响

例如:

  • 在未备份与转换数据的情况下, 直接删除列, 导致数据丢失
  • 直接修改列的数据类型, 但是没有考虑精度损失

五. 其他的数据库SQL编写问题, 请查看以下的数据库SQL语句编写要点

liquibase执行时间基准测试:

测试注意事项:

  1.  sql语句填充为空, 仅仅测试liquibase镜像从拉取到执行完成所耗费时间
  2.  因所消耗时间受 镜像大小,拉取镜像耗时,启动镜像耗时,执行liquibase update所耗费时间, 为保证测试的精确, 仅测试liquibase update耗时

测试环境:

主机: 10.255.229.101, cecstack环境

cpu: FT2000plus 64cores

测试结果: 

[root@BJ-DEV-CECStack-OC101 liquibase]# time  liquibase  update
Starting Liquibase at Tue, 24 Aug 2021 18:01:56 CST (version 3.6.2 built at 2018-07-03 11:28:09)
Liquibase: Update has been successful.

real	0m2.892s
user	0m7.571s
sys	0m0.434s

数据库sql语句编写要点说明:

 数据库开发规范

一、命名规范

1、库名、表名、字段名、索引名必须使用小写字母,并采用下划线分割。

      Mysql参数lower_case_table_names是会忽略库名、表名的大小写。表现在底层就是,忽略大小写后创建的物理数据库文件全小写,不忽略则会按照大小写命名文件。迁移的时候会留下坑。

2、库名、表名、字段名支持最多64个字符但不要超过32个字符。

    表名 字段名 库名没必要太长。

3、库名、表名、字段名须见名知意。命名与业务、产品线相关联。

    略

4、库名、表名、字段名禁止使用MySQL保留字(如:add/analyze/check/char/to/and/as/before/between/column...)(MySQL :: MySQL 5.7 Reference Manual :: 9.3 Keywords and Reserved Words)。

   使用保留字是可以创建出来,后续的迁移以及某些模块调用会埋下坑。

5、数据库初始化会删除test库 ,库名、表名、字段名禁止使用te4st开头。

   Test表在一些常见的数据库运维工具中是会被过滤掉。在某些场合下,主从也会过滤掉。

6、创建一archive库,存放本地归档,确认无使用计划后,定期转移到归档备份服务器中。

   大表的数据用来归档可以有效减缓主表的备份慢、慢SQL,DDL慢等问题。本地归档也可以方便数据热度的进一步分级。

7、临时库、表名必须以tmp为前缀,并以日期为后缀。例如 tmp_table_20140401。

   略

8、不建议使用存储过程、触发器、视图、UDF、EVENT等。

   触发器:高并发的情况下,风险较高。在表下线、迁移时候也会有坑。

   UDF、存储过程、EVENT、视图:在迁移、标准化的过程中,业务迭代过程中会埋坑。

9、账号命名规则,本业务访问账号:业务名_rw/业务名_ro;跨业务访问账号:访问组_要访问业务名_w/访问组_要访问业务名_r

   通过指定账号名,可以通过账号来进行区分库的会话以及SQL等,在数据库出现问题的时候快速定位,两头处理。

二、库表规范

1、默认使用InnoDB存储引擎。

   Innodb引擎具有行锁、高性能、支持事务、易运维等特性。同时也是mysql官方主打的产品,目前业界主流方向,同时可以兼顾myisam的大部分功能。

2、库、表、字段、client字符集默认使用UTF8,如遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。

   统一字符集可以在日常的迁移、DTS、日常使用中提供方便、降低风险。

3、单库不要超过500个表、单表字段数不要超过50个

    尽量不要产生长宽表。

4、表、字段添加comment(除主键)

    略

5、禁止在数据库中存储图片、文件等大数据。

   影响整体的sql性能,而且会导致表大小不可控。

6、字符类型居多的表建议控制在3000W以内,整型居多的表建议控制在5000W以内。

   影响整体的sql性能,而且会导致表大小不可控。

7、不建议或禁止在线上做数据库压力测试。

   压测一般是压到性能上限,会对线上的语句造成影响。

8、禁止测试环境直连生产环境调试,如有数据测试需求,请走特别申请流程。线上数据经过审批及脱敏处理后可以在测试环境使用。

   保证线上数据的安全性。

9、依据数据冷热,分级存储,历史归档

   控制单表数据量大小。

10、单表不要超过10个G。

    控制单表数据量大小。

三、库表设计

表设计

1、将关键字段、使用率频繁的字段拆分到单独表中,将大字段、访问频率低的字段拆分到单独表中存储,冷热分离。

2、推荐使用HASH、RANGE进行散表,表名后缀使用数字,数字必须从0开始。

3、归档表名需符合YYYY[MM][DD][HH]格式,例如table_201401、table_20140422

    略

   

字段设计

表字段少而精,尽量选择最小数据类型,表结构设计时适当冗余,不在数据库上做大量的cpu运算

1、建表默认3字段,主键、创建时间、修改时间。id、create_time、update_time

2、建议使用UNSIGNED存储非负数值。

3、建议使用INT UNSIGNED存储IPV4。

4、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数,例如支付相关数据。

5、建议整型字段直接使用INT,主键直接使用BIGINT,INT类型固定占4字节存储, 默认是11,最大有效显示宽度是255,例如INT(4)仅代表字符宽度为4位,不代表存储长度。

6、区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型和取值范围(TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL---存储空间逐渐变大,而性能却逐渐变小)。

7、ENUM类型不允许修改默认值,只允许顺序添加。

8、禁止使用TEXT、BLOB类型,如必须使用,建议和主键一起进行拆表处理,可考虑VARCHAR替换。

9、使用VARBINARY类型默认区分大小写,没有字符集概念,可存储大小写敏感的变长字符串或二进制内容。

10、使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N。

11、区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。

12、所有字段均定义为NOT NULL。尽量避免使用NULL,要是必须用NULL,那也可考虑使用0、特殊值或空串来进行代替。

13、字段需要有默认值,时间类型字段,默认值不能为“0000 00-00-00”。

四、索引规范

1、单张表中索引数量不超过5个,单个索引中的字段数不超过5个。

2、非唯一索引按照“idx_字段名称[_字段名称]”进行命名。

3、唯一索引按照“uniq_字段名称[_字段名称]”进行命名。

4、联合索引建议包含所有字段名,过长的字段名可以缩写形式。

5、表必须有主键,推荐使用UNSIGNED自增列作为主键并且该主键为非业务字段。

6、唯一键由3个以下字段组成,并且字段都是整形时,可使用唯一键作为主键。其他情况下,建议使用自增列或全局生成器作主键。

7、禁止冗余、重复索引。

8、禁止索引null列。

9、禁止使用外键,避免产生死锁,通过程序来保证约束。

10、避免数据类型转换,联表查询时,JOIN列的数据类型必须相同,并且要建立索引。

11、选择区分度大的列建立索引。字段的顺序对组合索引有至关重要的作用,组合索引中,区分度大的字段要放在最前面。

12、不在区分度低的上建立索引,例如“性别”。

13、对较长的字符串字段、blob、text使用前缀索引,前缀索引长度不超过8个字符。(使用前缀索引,不仅节省存储空间,提高索引缓存命中率,还可减少disk的IO操作。)

14、不对过长的VARCHAR字段建立索引。建议优先考虑添加CRC32或MD5伪列,并对伪列建立索引,减少索引长度,提高效率。

15、合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

16、合理使用覆盖索引减少IO,避免排序。

17、添加优化的索引之后,再删除多余的索引。

18、单个索引尽可能覆盖更多的sql,更新频率比较高的表要控制索引的数量。

19、大批量变更数据,索引维护成本很高,因此大批量数据更新要拆分出小粒度。

五、SQL设计

1、使用prepared statement,可以提升性能并避免SQL注入。

2、用IN代替OR。SQL语句中IN包含的值不应过多,应少于500个。

3、禁止隐式类型转换。数值类型禁止加引号;字符串类型必须加引号。

4、避免使用JOIN和子查询,禁止使用3表以上的JOIN,必要时推荐用JOIN代替子查询。

5、避免在MySQL中索引列进行数学运算和函数运算。

6、减少与数据库交互次数,尽量采用批量提交SQL语句(INSERT INTO table (column1、column2、column3) VALUES(),(),()),不宜过多。

7、拆分复杂SQL为多个小SQL,避免大事务。

8、获取大量数据时,建议分批次获取数据,每次获取数据少于500条,结果集应小于1M。

9、用UNION ALL替换UNION。

10、禁止在前端业务中使用count(*),可放在memcache、redis、某一从库或增加统计表来维护。

11、多表关联时禁止使用select *语句,只取需要列。

12、SQL中避免出现rand()、sysdate()、current_user()等不确定结果的函数。

13、INSERT INTO语句必须显示指明字段名称。

14、禁止使用 INSERT INTO B SELECT *  FROM  A。

15、禁止单条SQL语句同时更新多个表。

16、建议使用合理的分页方式以提高分页效率。

17、禁止在生产环境从库进行查询分析统计操作,必要时请申请专用统计服务器,前后台分离。

18、程序应有捕获SQL异常的处理机制,必要时通过rollback显式回滚。

19、程序不要修改数据库中事务隔离机制,控制锁的行为。

19、避免全表扫描,重要SQL必须被索引:update、delete的where条件列、order by、group by、distinct字段、多表join字段。

20、如果只是分组,用group by a order by null替换group by a去除排序。

21、禁止使用%前导查询,例如:like “%abc”,无法利用到索引。

22、禁止使用负向查询,例如 not in、!=、not like、<>。

23、禁止在 where 子句中对字段进行 null 值判断,字段通过增加默认值处理。

24、使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort、Using Temporary。

25、禁止DML语句中出现@num变量替代字段名称。

26、UPDATE或DELETE语句加上WHERE条件索引列更新(如果使用非索引列更新会导致锁全表,造成Lock wait timeout exceeded; try restarting transaction),禁用LIMIT语句。

27、拼接sql语句时,注意where条件,防止sql注入时表记录被清空(如:delete from where a/a=a/1=1/a=0 or 1=1/exist返回布尔值为恒真)。

28、DML:sql语句中不允许有drop、truncate、delelet all data等语句。

29、DDL:添加字段语句禁止使用after。例如:alter table t add column a after b;

六、模板:

新建表参考:

create table  tb_cart_goods_special (

  id int unsigned not null  auto_increment,

  area varchar(10)  not null default 'cn' comment '区域简称, e.g. cn,hk,tw,sg',

调研记录

flyway

  • GitHub - flyway/flyway: Flyway by Redgate • Database Migrations Made Easy.
  • java
  • 每个修改一个sql文件,使用V{版本号}作为文件前缀,用作排序
  • 开源版本不支持rollback

liquibase

  • GitHub - liquibase/liquibase: Main Liquibase Source
  • java
  • 多个修改放在一个sql文件中
  • 使用changeset作为版本号
  • 支持xml、json、yaml、sql多种方式来描述变更

node-db-migrate

  • GitHub - db-migrate/node-db-migrate: Database migration framework for node
  • node.js
  • 每个修改对应两个文件,升级文件up和降级文件down
  • 使用时间戳作为文件前缀,用作排序
  • 更新时使用数据库最新版本号进行比较,merge时可能出现部分变更不会执行(小于数据库中记录的时间戳)

goose

  • GitHub - pressly/goose: A database migration tool. Supports SQL migrations and Go functions.
  • go
  • 每个修改对应一个sql文件,使用注释标记up和down部分
  • 推荐开发时使用时间戳作为文件前缀,发布时改为序列号
  • 更新时使用数据库最新版本号进行比较,merge时可能出现部分变更不会执行(小于数据库中记录的时间戳)

你可能感兴趣的