[原创]archery 平台升级部署实践

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服务，才能生效。



posted @ 2019-07-10 21:31  指弹小能手  阅读(2477)  评论(0)  编辑  收藏  举报

关于作者：专注数据库、Python 开发、ROS、深度学习，请多多赐教！

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出, 原文链接 如有问题， 可邮件（iamkuboy@163.com）咨询.