[原创]archery平台升级部署实践v1.3.8 → v1.5.0
⽬录
1.安装、升级python3 venv 环境
1.1.安装 python36
全新安装需要执⾏，升级安装可忽略
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 github/hhyo/archery/archive/v1.3.
tar -zxvf v1.5.
cd archery150
pip3 install - （-i mirrors.ustc.edu/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.f
server{
listen 9999; # 监听的端⼝，未正式使⽤时，先试⽤临时端⼝
server_name archery150;
client_max_body_size 20M; #新增
proxy_read_timeout 600s;    #超时时间与gunicorn超时时间设置⼀致，主要⽤于在线查询
location / {
proxy_pass 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.sql',
'NAME': 'archery150',
'USER': 'xxx',
'PASSWORD': 'xxx',
'HOST': '',
'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_fig import LDAPSearch
AUTHENTICATION_BACKENDS = (
'django_auth_ldap.backend.LDAPBackend',  # 配置为先使⽤LDAP认证，如通过认证则不再使⽤后⾯的认证⽅式
'ib.auth.backends.ModelBackend',  # django系统中⼿动创建的⽤户也可使⽤，优先级靠后。注意这2⾏的顺序
)
AUTH_LDAP_SERVER_URI = "ldap://"
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 user-- ----------------------------
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`) REFERENC
ES `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,

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。