v1.3.8 → v1.5.0

目录

1.安装、升级python3 venv 环境

1.1.安装 python36

全新安装需要执行，升级安装可忽略

https://www.cnblogs.com/David-domain/p/11165734.html

1.2.创建Python3 venv 环境（Python>=3.6.5，建议使用虚拟环境 ）

全新安装需要执行，升级安装也建议重新部署一个venv 环境，便于进行切换。

不推荐在原venv环境中直接进行升级。

pip3 install virtualenv

cd /opt/

virtualenv venv4archery150 --python=python36

source /opt/venv4archery150/bin/activate

#确认命令在 $PATH 中，否则安装包时报错

which mysql_config     #确认命令在 $PATH 中，否则安装包时报错

# 下载 archery 源码

yum install -y gcc gcc-c++ epel-release python-pip python-devel unixODBC-devel

cd /opt

wget https://github.com/hhyo/archery/archive/v1.3.8.tar.gz

tar -zxvf v1.5.0.tar.gz

cd archery150

pip3 install -r requirements.txt （-i https://mirrors.ustc.edu.cn/pypi/web/simple/）

1.3.报错处理参考

1.3.1. pyodbc 安装失败

# 安装 unixODBC-devel 包

sudo yum install -y epel-release python-pip gcc-c++ python-devel unixODBC-devel

2.组件安装配置

2.1.MySQL 安装、配置

2.2.数据库账号授权

2.3. Redis 安装——新增

  • 参照wiki 文档安装单实例 redis

2.4.inception 安装、配置、启动

2.5.Percona-toolkit 安装

略

2.6.Nginx 安装、配置——有变更

yum install -y nginx

vim /etc/nginx/conf.d/nginx.conf


server{

        listen 9999; # 监听的端口，未正式使用时，先试用临时端口

        server_name archery150;

        client_max_body_size 20M; #新增

        proxy_read_timeout 600s;     #超时时间与gunicorn超时时间设置一致，主要用于在线查询

        location / {

            proxy_pass http://127.0.0.1:8888/; # 8888 要与 startup.sh 脚本中端口对应

            proxy_set_header Host $host:9999;      #解决重定向404的问题

            proxy_set_header X-Real-IP $remote_addr;

            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

            proxy_set_header X-Forwarded-Proto $scheme;

        }

        location /static {

            alias /opt/archery150/common/static/;        #此处指向settings.py配置项STATIC_ROOT目录的绝对路径，用于nginx收集静态资源

        }

        error_page 404 /404.html;

            location = /40x.html {

        }

        error_page 500 502 503 504 /50x.html;

            location = /50x.html {

        }

    }

2.7.上传 archery 第三方工具

2.8. SQLAdvisor 安装

2.8.1.报错处理

2.9. SchemaSync 安装

3.安装 archery 环境

3.1. 1.2.步已下载源码，略过

略

3.2.修改 archery/settings.py 文件 DATABASES 配置项——有变更

  • 这次升级加入了redis cache，配置略有改动。django_q 使用默认配置即可。
vim /opt/archery/archery150/archery/setting.py


# 12 ~ 13

# SECURITY WARNING: don't run with debug turned on in production!

DEBUG = False #关闭调试模式


# 118 ~ 132

# 该项目本身的mysql数据库地址

DATABASES = {

    'default': {

        'ENGINE': 'django.db.backends.mysql',

        'NAME': 'archery150',

        'USER': 'xxx',

        'PASSWORD': 'xxx',

        'HOST': '10.xx.xx.xx',

        'PORT': '3306',

        'OPTIONS': {

            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",

            'charset': 'utf8mb4'

        }

    }

}


# 145 ~ 158

# Django-Q 默认配置即可，也可参考django-q文档修改

Q_CLUSTER = {

    'name': 'archery',

    'workers': 4,

    'recycle': 500,

    'timeout': 60,

    'compress': True,

    'cpu_affinity': 1,

    'save_limit': 0,

    'queue_limit': 50,

    'label': 'Django Q',

    'django_redis': 'default',

    'sync': False  # 本地调试可以修改为True，使用同步模式

}


# 160 ~ 169

# 缓存配置

CACHES = {

    "default": {

        "BACKEND": "django_redis.cache.RedisCache",

        "LOCATION": "redis://10.33.66.241:50001/0", #单实例

        "OPTIONS": {

            "CLIENT_CLASS": "django_redis.client.DefaultClient",

        }

    }

}


# 171 ~ 191

# LDAP

ENABLE_LDAP = True

if ENABLE_LDAP:

    import ldap

    from django_auth_ldap.config import LDAPSearch

    AUTHENTICATION_BACKENDS = (

        'django_auth_ldap.backend.LDAPBackend',  # 配置为先使用LDAP认证，如通过认证则不再使用后面的认证方式

        'django.contrib.auth.backends.ModelBackend',  # django系统中手动创建的用户也可使用，优先级靠后。注意这2行的顺序

    )

    AUTH_LDAP_SERVER_URI = "ldap://xx.xx.xx.com"

    AUTH_LDAP_BIND_DN = "CN=auth,CN=Users,DC=xxx,DC=xxx,DC=com"

    AUTH_LDAP_BIND_PASSWORD = "xxxx"

    AUTH_LDAP_USER_SEARCH = LDAPSearch("ou=xxx,dc=xxx,dc=xxx,dc=com",ldap.SCOPE_SUBTREE, "(sAMAccountName=%(user)s)")

    AUTH_LDAP_ALWAYS_UPDATE_USER = True  # 每次登录从ldap同步用户信息

    AUTH_LDAP_USER_ATTR_MAP = {  # key为archery.sql_users字段名，value为ldap中字段名，用户同步信息

        "username": "xxxxx",

        "display": "cn",

        "email": "mail"

    }

3.3.数据库初始化

  • 如下命令在测试环境中执行，生产环境直接执行脚本。
# 数据库初始化

python3 manage.py makemigrations sql

python3 manage.py migrate 

# 编译翻译文件

python3 manage.py compilemessages    #新增步骤
  • 官方文档虽然说可以不必初始化，但是由于权限的更新，权限ID 对应关系可能错乱，所以使用1.38数据库升级后，迁移到1.50数据库的方式。
/*

 Navicat Premium Data Transfer

 Source Server         : archery150

 Source Server Type    : MySQL

 Source Server Version : 50722

 Source Host           :

 Source Schema         : archery_new

 Target Server Type    : MySQL

 Target Server Version : 50722

 File Encoding         : 65001

 Date: 17/04/2019 15:13:10

*/

create database archery150 charset utf8;

use archery150;

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for aliyun_access_key

-- ----------------------------

DROP TABLE IF EXISTS `aliyun_access_key`;

CREATE TABLE `aliyun_access_key` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `ak` varchar(50) NOT NULL,

  `secret` varchar(100) NOT NULL,

  `is_enable` tinyint(4) NOT NULL COMMENT '是否启用',

  `remark` varchar(50) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for aliyun_rds_config

-- ----------------------------

DROP TABLE IF EXISTS `aliyun_rds_config`;

CREATE TABLE `aliyun_rds_config` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `instance_id` int(11) NOT NULL,

  `rds_dbinstanceid` varchar(100) NOT NULL,

  `is_enable` tinyint(4) NOT NULL COMMENT '是否启用',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_iid` (`instance_id`),

  CONSTRAINT `fk_instanid__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for auth_group

-- ----------------------------

DROP TABLE IF EXISTS `auth_group`;

CREATE TABLE `auth_group` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `name` varchar(80) NOT NULL COMMENT '组',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限组';

-- ----------------------------

-- Table structure for auth_group_permissions

-- ----------------------------

DROP TABLE IF EXISTS `auth_group_permissions`;

CREATE TABLE `auth_group_permissions` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `permission_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_gid_pid` (`group_id`,`permission_id`),

  KEY `idx_pid` (`permission_id`),

  CONSTRAINT `fk_groupid__auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),

  CONSTRAINT `fk_perid__auth_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for auth_permission

-- ----------------------------

DROP TABLE IF EXISTS `auth_permission`;

CREATE TABLE `auth_permission` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `content_type_id` int(11) NOT NULL,

  `codename` varchar(100) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_ctid` (`content_type_id`,`codename`),

  CONSTRAINT `fk_ctypeid__django_content_type_id` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for data_masking_columns

-- ----------------------------

DROP TABLE IF EXISTS `data_masking_columns`;

CREATE TABLE `data_masking_columns` (

  `column_id` int(11) NOT NULL AUTO_INCREMENT,

  `rule_type` int(11) NOT NULL,

  `active` tinyint(4) NOT NULL COMMENT '激活状态',

  `instance_id` int(11) NOT NULL,

  `table_schema` varchar(64) NOT NULL,

  `table_name` varchar(64) NOT NULL,

  `column_name` varchar(64) NOT NULL,

  `column_comment` varchar(1024) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`column_id`),

  KEY `idx_iid` (`instance_id`),

  CONSTRAINT `fk_instance_id__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for data_masking_rules

-- ----------------------------

DROP TABLE IF EXISTS `data_masking_rules`;

CREATE TABLE `data_masking_rules` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `rule_type` int(11) NOT NULL,

  `rule_regex` varchar(255) NOT NULL,

  `hide_group` int(11) NOT NULL,

  `rule_desc` varchar(100) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_rule_type` (`rule_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_admin_log

-- ----------------------------

DROP TABLE IF EXISTS `django_admin_log`;

CREATE TABLE `django_admin_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `action_time` datetime(6) NOT NULL,

  `object_id` longtext,

  `object_repr` varchar(200) NOT NULL,

  `action_flag` smallint(5) unsigned NOT NULL,

  `change_message` longtext NOT NULL,

  `content_type_id` int(11) DEFAULT NULL,

  `user_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_ctid` (`content_type_id`),

  KEY `idx_uid` (`user_id`),

  CONSTRAINT `fk_ctid__django_content_type_id` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),

  CONSTRAINT `fk_users_id__sql_users_id` FOREIGN KEY (`user_id`) REFERENCES `sql_users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_content_type

-- ----------------------------

DROP TABLE IF EXISTS `django_content_type`;

CREATE TABLE `django_content_type` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `app_label` varchar(100) NOT NULL,

  `model` varchar(100) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_app_label__model` (`app_label`,`model`)

) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_migrations

-- ----------------------------

DROP TABLE IF EXISTS `django_migrations`;

CREATE TABLE `django_migrations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `app` varchar(255) NOT NULL,

  `name` varchar(255) NOT NULL,

  `applied` datetime(6) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_q_ormq

-- ----------------------------

DROP TABLE IF EXISTS `django_q_ormq`;

CREATE TABLE `django_q_ormq` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `key` varchar(100) NOT NULL,

  `payload` longtext NOT NULL,

  `lock` datetime(6) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_q_schedule

-- ----------------------------

DROP TABLE IF EXISTS `django_q_schedule`;

CREATE TABLE `django_q_schedule` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `func` varchar(256) NOT NULL,

  `hook` varchar(256) DEFAULT NULL,

  `args` longtext,

  `kwargs` longtext,

  `schedule_type` varchar(1) NOT NULL,

  `repeats` int(11) NOT NULL,

  `next_run` datetime(6) DEFAULT NULL,

  `task` varchar(100) DEFAULT NULL,

  `name` varchar(100) DEFAULT NULL,

  `minutes` smallint(5) unsigned DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_q_task

-- ----------------------------

DROP TABLE IF EXISTS `django_q_task`;

CREATE TABLE `django_q_task` (

  `id` varchar(32) NOT NULL,

  `name` varchar(100) NOT NULL,

  `func` varchar(256) NOT NULL,

  `hook` varchar(256) DEFAULT NULL,

  `args` longtext,

  `kwargs` longtext,

  `result` longtext,

  `started` datetime(6) NOT NULL,

  `stopped` datetime(6) NOT NULL,

  `success` tinyint(4) NOT NULL,

  `group` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for django_session

-- ----------------------------

DROP TABLE IF EXISTS `django_session`;

CREATE TABLE `django_session` (

  `session_key` varchar(40) NOT NULL,

  `session_data` longtext NOT NULL,

  `expire_date` datetime(6) NOT NULL,

  PRIMARY KEY (`session_key`),

  KEY `idx_expire_date` (`expire_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for param_history

-- ----------------------------

DROP TABLE IF EXISTS `param_history`;

CREATE TABLE `param_history` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `instance_id` int(11) NOT NULL COMMENT '实例ID',

  `variable_name` varchar(64) NOT NULL COMMENT '参数名',

  `old_var` varchar(1024) NOT NULL COMMENT '修改前参数值',

  `new_var` varchar(1024) NOT NULL COMMENT '修改后参数值',

  `set_sql` varchar(1024) NOT NULL COMMENT '在线变更配置执行的SQL语句',

  `user_name` varchar(30) NOT NULL COMMENT '修改人',

  `user_display` varchar(50) NOT NULL COMMENT '修改人中文名',

  `update_time` datetime(6) NOT NULL COMMENT '修改时间',

  PRIMARY KEY (`id`),

  KEY `idx_iid` (`instance_id`),

  CONSTRAINT `fk_instanceid__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for param_template

-- ----------------------------

DROP TABLE IF EXISTS `param_template`;

CREATE TABLE `param_template` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `db_type` varchar(10) NOT NULL COMMENT '数据库类型，mysql、mssql、redis、pgsql',

  `variable_name` varchar(64) NOT NULL COMMENT '参数名',

  `default_value` varchar(1024) NOT NULL COMMENT '默认参数值',

  `editable` tinyint(4) NOT NULL COMMENT '是否支持修改',

  `valid_values` varchar(1024) NOT NULL COMMENT '有效参数值',

  `description` varchar(1024) NOT NULL COMMENT '参数描述',

  `create_time` datetime(6) NOT NULL COMMENT '创建时间',

  `sys_time` datetime(6) NOT NULL COMMENT '创建时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_db_type__variable_name` (`db_type`,`variable_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for query_log

-- ----------------------------

DROP TABLE IF EXISTS `query_log`;

CREATE TABLE `query_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `instance_name` varchar(50) NOT NULL,

  `db_name` varchar(64) NOT NULL,

  `sqllog` longtext NOT NULL,

  `effect_row` bigint(20) NOT NULL,

  `cost_time` varchar(10) NOT NULL,

  `username` varchar(30) NOT NULL,

  `user_display` varchar(50) NOT NULL,

  `priv_check` tinyint(4) NOT NULL COMMENT '查询权限是否正常校验',

  `hit_rule` tinyint(4) NOT NULL COMMENT '查询是否命中脱敏规则',

  `masking` tinyint(4) NOT NULL COMMENT '查询结果是否正常脱敏',

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for query_privileges

-- ----------------------------

DROP TABLE IF EXISTS `query_privileges`;

CREATE TABLE `query_privileges` (

  `privilege_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限id',

  `user_name` varchar(30) NOT NULL COMMENT '用户',

  `user_display` varchar(50) NOT NULL COMMENT '下拉菜单筛选名',

  `instance_id` int(11) NOT NULL,

  `table_name` varchar(64) NOT NULL COMMENT '表',

  `db_name` varchar(64) NOT NULL COMMENT '数据库',

  `valid_date` date NOT NULL COMMENT '有效时间',

  `limit_num` int(11) NOT NULL COMMENT '结果集',

  `priv_type` tinyint(4) NOT NULL COMMENT '权限级别',

  `is_deleted` tinyint(4) NOT NULL COMMENT '删除标记',

  `create_time` datetime(6) NOT NULL COMMENT '申请时间',

  `sys_time` datetime(6) NOT NULL COMMENT '系统时间',

  PRIMARY KEY (`privilege_id`),

  KEY `idx_uname__iid__db_name__vdate` (`user_name`,`instance_id`,`db_name`,`valid_date`),

  KEY `idx_iid` (`instance_id`),

  CONSTRAINT `fk_instid__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for query_privileges_apply

-- ----------------------------

DROP TABLE IF EXISTS `query_privileges_apply`;

CREATE TABLE `query_privileges_apply` (

  `apply_id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `group_name` varchar(100) NOT NULL,

  `title` varchar(50) NOT NULL,

  `user_name` varchar(30) NOT NULL,

  `user_display` varchar(50) NOT NULL,

  `instance_id` int(11) NOT NULL,

  `db_list` longtext NOT NULL,

  `table_list` longtext NOT NULL,

  `valid_date` date NOT NULL,

  `limit_num` int(11) NOT NULL,

  `priv_type` tinyint(4) NOT NULL,

  `status` int(11) NOT NULL,

  `audit_auth_groups` varchar(255) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`apply_id`),

  KEY `idx_iid` (`instance_id`),

  CONSTRAINT `fk_insid__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for resource_group

-- ----------------------------

DROP TABLE IF EXISTS `resource_group`;

CREATE TABLE `resource_group` (

  `group_id` int(11) NOT NULL AUTO_INCREMENT,

  `group_name` varchar(100) NOT NULL,

  `group_parent_id` bigint(20) NOT NULL,

  `group_sort` int(11) NOT NULL,

  `group_level` int(11) NOT NULL,

  `ding_webhook` varchar(255) NOT NULL,

  `is_deleted` tinyint(4) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`group_id`),

  UNIQUE KEY `idx_uni_group_name` (`group_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for resource_group_relations

-- ----------------------------

DROP TABLE IF EXISTS `resource_group_relations`;

CREATE TABLE `resource_group_relations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `object_type` tinyint(4) NOT NULL,

  `object_id` int(11) NOT NULL,

  `object_name` varchar(100) NOT NULL,

  `group_id` int(11) NOT NULL,

  `group_name` varchar(100) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_oid__gid__object_type` (`object_id`,`group_id`,`object_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_config

-- ----------------------------

DROP TABLE IF EXISTS `sql_config`;

CREATE TABLE `sql_config` (

  `item` varchar(50) NOT NULL,

  `value` varchar(200) NOT NULL,

  `description` varchar(200) NOT NULL,

  PRIMARY KEY (`item`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_instance

-- ----------------------------

DROP TABLE IF EXISTS `sql_instance`;

CREATE TABLE `sql_instance` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `instance_name` varchar(50) NOT NULL,

  `type` varchar(6) NOT NULL,

  `db_type` varchar(10) NOT NULL,

  `host` varchar(200) NOT NULL,

  `port` int(11) NOT NULL,

  `user` varchar(100) NOT NULL,

  `password` varchar(300) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `update_time` datetime(6) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_instance_name` (`instance_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_permission

-- ----------------------------

DROP TABLE IF EXISTS `sql_permission`;

CREATE TABLE `sql_permission` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_users

-- ----------------------------

DROP TABLE IF EXISTS `sql_users`;

CREATE TABLE `sql_users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `PASSWORD` varchar(128) NOT NULL COMMENT '密码',

  `last_login` datetime(6) DEFAULT NULL COMMENT '上次登录',

  `is_superuser` tinyint(4) NOT NULL COMMENT '超级用户状态:1是,0否',

  `username` varchar(150) NOT NULL COMMENT '用户名',

  `first_name` varchar(30) NOT NULL COMMENT '名,无值',

  `last_name` varchar(150) NOT NULL COMMENT '姓,无值',

  `email` varchar(254) NOT NULL COMMENT '电子邮箱地址',

  `is_staff` tinyint(4) NOT NULL COMMENT '职员状态(是否能管理django后台):1是,0否',

  `is_active` tinyint(4) NOT NULL COMMENT '有效(禁用用户标签):1是,0否',

  `date_joined` datetime(6) NOT NULL COMMENT '加入日期(第一次登录时间)',

  `display` varchar(50) NOT NULL COMMENT '显示的中文名',

  `failed_login_count` int(11) NOT NULL COMMENT '登陆失败次数',

  `last_login_failed_at` datetime DEFAULT NULL COMMENT '上次失败登录时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_username` (`username`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_users_groups

-- ----------------------------

DROP TABLE IF EXISTS `sql_users_groups`;

CREATE TABLE `sql_users_groups` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `users_id` int(11) NOT NULL,

  `group_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_uid__gid` (`users_id`,`group_id`),

  KEY `idx_gid` (`group_id`),

  CONSTRAINT `fk_gid__auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),

  CONSTRAINT `fk_usersid__sql_users_id` FOREIGN KEY (`users_id`) REFERENCES `sql_users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_users_user_permissions

-- ----------------------------

DROP TABLE IF EXISTS `sql_users_user_permissions`;

CREATE TABLE `sql_users_user_permissions` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `users_id` int(11) NOT NULL,

  `permission_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_uid__pid` (`users_id`,`permission_id`),

  KEY `idx_pid` (`permission_id`),

  CONSTRAINT `fk_pid__auth_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),

  CONSTRAINT `fk_uid__sql_users_id` FOREIGN KEY (`users_id`) REFERENCES `sql_users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_workflow

-- ----------------------------

DROP TABLE IF EXISTS `sql_workflow`;

CREATE TABLE `sql_workflow` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `workflow_name` varchar(50) NOT NULL,

  `group_id` int(11) NOT NULL,

  `group_name` varchar(100) NOT NULL,

  `instance_id` int(11) NOT NULL,

  `db_name` varchar(64) NOT NULL COMMENT '数据库',

  `engineer` varchar(30) NOT NULL,

  `engineer_display` varchar(50) NOT NULL,

  `audit_auth_groups` varchar(255) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `finish_time` datetime(6) DEFAULT NULL,

  `STATUS` varchar(50) NOT NULL,

  `is_backup` tinyint(4) NOT NULL COMMENT '是否备份',

  `is_manual` tinyint(4) NOT NULL,

  `syntax_type` tinyint(4) NOT NULL COMMENT '工单类型 1、DDL，2、DML',

  PRIMARY KEY (`id`),

  KEY `idx_iid` (`instance_id`),

  CONSTRAINT `fk_iid__sql_instance_id` FOREIGN KEY (`instance_id`) REFERENCES `sql_instance` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sql_workflow_content

-- ----------------------------

DROP TABLE IF EXISTS `sql_workflow_content`;

CREATE TABLE `sql_workflow_content` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `workflow_id` int(11) NOT NULL COMMENT 'SQL工单ID',

  `sql_content` longtext NOT NULL COMMENT '提交的SQL文本',

  `review_content` longtext NOT NULL COMMENT '自动审核内容的JSON格式',

  `execute_result` longtext NOT NULL COMMENT '执行结果的JSON格式',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_uni_workflow_id` (`workflow_id`),

  CONSTRAINT `fk_wfid__sql_workflow_id` FOREIGN KEY (`workflow_id`) REFERENCES `sql_workflow` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for workflow_audit

-- ----------------------------

DROP TABLE IF EXISTS `workflow_audit`;

CREATE TABLE `workflow_audit` (

  `audit_id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `group_name` varchar(100) NOT NULL,

  `workflow_id` bigint(20) NOT NULL,

  `workflow_type` tinyint(4) NOT NULL,

  `workflow_title` varchar(50) NOT NULL,

  `workflow_remark` varchar(140) NOT NULL,

  `audit_auth_groups` varchar(255) NOT NULL,

  `current_audit` varchar(20) NOT NULL,

  `next_audit` varchar(20) NOT NULL,

  `current_status` tinyint(4) NOT NULL,

  `create_user` varchar(30) NOT NULL,

  `create_user_display` varchar(50) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`audit_id`),

  UNIQUE KEY `idx_uni_wfid__workflow_type` (`workflow_id`,`workflow_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for workflow_audit_detail

-- ----------------------------

DROP TABLE IF EXISTS `workflow_audit_detail`;

CREATE TABLE `workflow_audit_detail` (

  `audit_detail_id` int(11) NOT NULL AUTO_INCREMENT,

  `audit_id` int(11) NOT NULL,

  `audit_user` varchar(30) NOT NULL,

  `audit_time` datetime(6) NOT NULL,

  `audit_status` tinyint(4) NOT NULL,

  `remark` varchar(140) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`audit_detail_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for workflow_audit_setting

-- ----------------------------

DROP TABLE IF EXISTS `workflow_audit_setting`;

CREATE TABLE `workflow_audit_setting` (

  `audit_setting_id` int(11) NOT NULL AUTO_INCREMENT,

  `group_id` int(11) NOT NULL,

  `group_name` varchar(100) NOT NULL,

  `workflow_type` tinyint(4) NOT NULL,

  `audit_auth_groups` varchar(255) NOT NULL,

  `create_time` datetime(6) NOT NULL,

  `sys_time` datetime(6) NOT NULL,

  PRIMARY KEY (`audit_setting_id`),

  UNIQUE KEY `idx_uni_gid__workflow_type` (`group_id`,`workflow_type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for workflow_log

-- ----------------------------

DROP TABLE IF EXISTS `workflow_log`;

CREATE TABLE `workflow_log` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `audit_id` bigint(20) NOT NULL COMMENT '工单审批id',

  `operation_type` tinyint(4) NOT NULL COMMENT '操作类型，0提交/待审核、1审核通过、2审核不通过、3审核取消/取消执行、4定时执行、5执行工单、6执行结束',

  `operation_type_desc` char(10) NOT NULL COMMENT '操作类型描述',

  `operation_info` varchar(200) NOT NULL COMMENT '操作信息',

  `operator` varchar(30) NOT NULL COMMENT '操作人',

  `operator_display` varchar(50) NOT NULL COMMENT '操作人中文名',

  `operation_time` datetime(6) NOT NULL COMMENT '操作时间',

  PRIMARY KEY (`id`),

  KEY `idx_aid` (`audit_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

3.4.创建管理用户

cd /opt/archery150/

python3 manage.py createsuperuser

Username: archery_admin    # 手动输入

Email address: xx@xx.com

Password: xxxx       #输入密码

Password (again): xxxxx      #确认密码

Superuser created successfully.

4.数据库升级

4.1.导出线上库

# 10.xx.xx.xx

sudo su -

mysqldump -S xx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF archery > archery138to150.sql

4.2.在目标实例上恢复数据

# 10.xx.xx.xx

sudo su -

mysql -S xx.sock

create database archery138to150 charset utf8mb4;

\q

mysql -S xx/mysql.sock archery138to150 < archery138to150.sql

4.3.中间库执行数据库升级脚本

# 10.xx.xx.xx

sudo su -

cd /opt/archery150/src/init_sql

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery138to150 < v1.3.8_v1.4.0.sql

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery138to150 < v1.4.2_v1.4.3.sql

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery138to150 < v1.4.3_v1.4.5.sql

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery138to150 < v1.4.5_v1.5.0.sql

4.4.数据库标准化更新——新增——新版本数据库执行

4.4.1.添加慢日志收集表、添加阿里云参数

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery150 < mysql_slow_query_review.sql

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery150 < rds_param_template.sql

4.4.2.标准化SQL脚本，调整索引名称、外键名称、字段类型等

4.4.3.字段添加注释SQL脚本，已PR到1.5.3版本代码中。

mysql -h 10.xx.xx.xx -P3306 -uxx -p archery138to150 < 1.5.3_comment.sql

4.4.4.导出恢复库升级数据，并导入 archery 数据库。

SET FOREIGN_KEY_CHECKS = 0;

insert into archery150.aliyun_rds_config select * from archery138to150.aliyun_rds_config;

insert into archery150.auth_group select * from archery138to150.auth_group;

insert into archery150.data_masking_columns select * from archery138to150.data_masking_columns;

insert into archery150.data_masking_rules select * from archery138to150.data_masking_rules;

insert into archery150.django_admin_log select * from archery138to150.django_admin_log;

insert into archery150.django_session select * from archery138to150.django_session;

insert into archery150.mysql_slow_query_review select * from archery138to150.mysql_slow_query_review;

insert into archery150.mysql_slow_query_review_history select * from archery138to150.mysql_slow_query_review_history;

insert into archery150.query_log select * from archery138to150.query_log;

insert into archery150.query_privileges select * from archery138to150.query_privileges;

insert into archery150.query_privileges_apply select * from archery138to150.query_privileges_apply;

insert into archery150.resource_group select * from archery138to150.resource_group;

insert into archery150.resource_group_relations select * from archery138to150.resource_group_relations;

insert into archery150.sql_instance select * from archery138to150.sql_instance;

truncate table archery150.sql_users;

insert into archery150.sql_users select * from archery138to150.sql_users;

insert into archery150.sql_users_groups select * from archery138to150.sql_users_groups;

insert into archery150.sql_workflow select * from archery138to150.sql_workflow;

insert into archery150.sql_workflow_content select * from archery138to150.sql_workflow_content;

insert into archery150.workflow_audit select * from archery138to150.workflow_audit;

insert into archery150.workflow_audit_detail select * from archery138to150.workflow_audit_detail;

insert into archery150.workflow_audit_setting select * from archery138to150.workflow_audit_setting;

insert into archery150.workflow_log select * from  archery138to150.workflow_log;

5.启动

5.1. 方式 1（推荐）：gunicorn+nginx启动

server{

        listen 9999; # 监听的端口

        server_name archery150;

        client_max_body_size 20M; # 处理Request Entity Too Large

        proxy_read_timeout 600s;  # 超时时间与Gunicorn超时时间设置一致，主要用于在线查询

        location / {

            proxy_pass http://127.0.0.1:8888;

            proxy_set_header Host $host:9123; # 解决重定向404的问题，和listen端口保持一致，如果是docker则和宿主机映射端口保持一致

            proxy_set_header X-Real-IP $remote_addr;

            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

            proxy_set_header X-Forwarded-Proto $scheme;

        }

        location /static {

            alias /opt/archery/static; # 此处指向settings.py配置项STATIC_ROOT目录的绝对路径，用于nginx收集静态资源

        }

        error_page 404 /404.html;

            location = /40x.html {

        }

        error_page 500 502 503 504 /50x.html;

            location = /50x.html {

        }


cd /opt/archery/archery-1.5.0/

source /opt/venv4archery150/bin/activate

sed -i 's#8000#8888#g' startup.sh

bash startup.sh

5.1.1.报错

  • 启动不正常先检查nginx 是否安装、是否正常启动
  • 再检查nginx 配置是否正确
  • 检查 qcluster 启动是否正常，是否可以看到进程
  • 查看 startup.sh 的 nohup 日志信息

5.2.方式2（不推荐）——runserver 启动:

# 杀掉已有进程

ps aux | grep python3 | grep -v grep | awk '{print $2}' | xargs kill -9

cd /opt/archery/archery150/

source /opt/venv4archery/bin/activate

python3 manage.py qcluster &

source /opt/venv4archery/bin/activate

python3 manage.py runserver 0.0.0.0:9999  --insecure &

6.配置archery

6.1.Inception配置

访问 http://10.xx.xx.xx:9999

INCEPTION_HOST 10.33.66.241

INCEPTION_PORT 6669

REMOTE_BACKUP_HOST 10.33.66.231

REMOTE_BACKUP_PORT 3306

REMOTE_BACKUP_USER incep

REMOTE_BACKUP_PASSWORD xxxx

6.2.功能模块配置

6.2.1.SQL上线

CRITICAL_DDL_REGEX  ^drop|^truncate|^rename

AUTO_REVIEW_WRONG  2

AUTO_REVIEW OFF

6.2.2.SQL查询

QUERY_CHECK  ON

DISABLE_STAR

DATA_MASKING  ON

MAX_EXECUTION_TIME

ADMIN_QUERY_LIMIT

6.2.3.SQL优化

SQLADVISOR_PATH  /opt/archery/sqladvisor

SOAR_PATH  /opt/archery/soar

SOAR_TEST_DSN  xxx:xxxx@10.xx.xx.xxx:3307/archery150

6.3.通知配置

ARCHERY_BASE_URL  https://xx.xx.xx.com

MAIL  ON

MAIL_SSL  ON

MAIL_SMTP_SERVER  xx.xx.com

MAIL_SMTP_PORT  xx

MAIL_SMTP_USER  xx@xx.com

MAIL_SMTP_PASSWORD

DDL_NOTIFY_AUTH_GROUP  dinglu@ybm100.com

DING  ON

6.4.其他配置

SCHEMASYNC  /opt/archery/schemasync

BINLOG2SQL  /opt/archery/binlog2sql.py

DEFAULT_AUTH_GROUP  RD

DEFAULT_RESOURCE_GROUP  ALL

LOCK_TIME_THRESHOLD

LOCK_CNT_THRESHOLD

SIGN_UP_ENABLED  OFF

7.修改

修改后需要重启 qcluster 或者重启python3服务，才能生效。





最新文章

  1. NSArray遍历和修改崩溃
  2. asp.net发布到IIS中出现错误：处理程序“PageHandlerFactory-Integrated”在其模块列表中有一个错误模块“ManagedPipelineHandler”
  3. error LNK1281: 无法生成 SAFESEH 映像 LNK2026&#160;模块对于 SAFESEH 映像是不安全的 VS2015 /win10
  4. C#集合实现接口一览表
  5. Java基础知识强化之IO流笔记74：NIO之 Buffer
  6. Android从零单排之免费短信验证
  7. “互联网+”引发IT人才招工荒-新华网安徽频道
  8. Kerberos认证流程详解
  9. [Ruby] LEVEL 2 Methods and Classes
  10. 设置iOS项目BuildVersion自动增加－备用
  11. windows phone (27) 基础Button
  12. html小知识
  13. Fail-Fast机制详解
  14. Vulkan Tutorial 02 编写Vulkan应用程序框架原型
  15. (办公)工作中的编码不良习惯Java(不定时更新)
  16. 根据考试成绩输出对应的礼物，90分以上爸爸给买电脑，80分以上爸爸给买手机， 60分以上爸爸请吃一顿大餐，60分以下爸爸给买学习资料。 要求：该题使用多重if完成
  17. 计算机信息系统安全保护等级划分准则(GB 17859-1999)
  18. Django中的缓存（内存，文件，redis）
  19. Using Spring.net in console application
  20. 浅析C# Dictionary实现原理

热门文章

  1. 各种Message中文解释（一部分）
  2. 使用path制作各类型动画路径
  3. Qt中事件处理的顺序
  4. C# 屏蔽Ctrl Alt Del 快捷键方法+屏蔽所有输入
  5. xe5 firemonkey关闭应用程序
  6. inno setup 配合istools
  7. 如何在 cmd 命令行中查看、修改、删除与添加环境变量
  8. SYN6107型 GPS北斗双模子钟
  9. hadoop之hive集合数据类型
  10. 30441数据定义语言DDL