关闭防火墙和selinux
宿主机安装mysql，创建archery数据库，并给所有权限，允许远程连接到该数据库
grant all privileges on *.* to 'root'@'%' identified by 'jason_zhang' with grant option;
flush privileges;
安装依赖环境
yum install ncurses-libs libncurses5-dev ncurses-devel wget git cmake openssl gcc-c++ zlib zlib-devel openssl-devel -y
注意:centos7 系统自带的bison的版本过高，在后面测试的时候会报错！安装bison-2.5.1
[root@archery tools]# wget http://ftp.gnu.org/gnu/bison/bison-2.5.1.tar.gz [root@archery tools]# tar -zxvf bison-2.5.1.tar.gz [root@archery tools]# cd bison-2.5.1 [root@archery bison-2.5.1]# ./configure &&make &&make install
安装 Python-3.4.1.tgz
[root@archery tools]#wget https://www.python.org/ftp/python/3.4.1/Python-3.4.1.tgz [root@archery tools]# tar zxvf Python-3.4.1.tgz [root@archery tools]# cd Python-3.4.1 [root@archery Python-3.4.1]# ./configure &&make &&make install
安装setuptools
[root@archery tools]# wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-19.6.tar.gz#md5=c607dd118eae682c44ed146367a17e26 [root@archery tools]# tar zxvf setuptools-19.6.tar.gz [root@archery tools]# cd setuptools-19.6 [root@archery setuptools-19.6]# python3 setup.py build [root@archery setuptools-19.6]# python3 setup.py install
安装pip3
[root@archery tools]# wget --no-check-certificate https://pypi.python.org/packages/source/p/pip/pip-8.0.2.tar.gz#md5=3a73c4188f8dbad6a1e6f6d44d117eeb [root@archery tools]tar zxvf pip-8.0.2.tar.gz [root@archery tools]# cd pip-8.0.2 [root@archery pip-8.0.2]# python3 setup.py build [root@archery pip-8.0.2]# python3 setup.py install
安装python3基础虚拟环境
[root@archery tools]# pip3 install virtualenv [root@archery tools]# virtualenv venv4archer --python=python3.4 [root@archery tools]# source venv4archer/bin/activate
下载Release v1.3.7 上传到服务器上、解压
还需要安装一些依赖环境
(venv4archer) [root@archery archery-1.3.7]# yum install python36u-devel mysql-devel -y (venv4archer) [root@archery archery-1.3.7]# yum install python-devel -y (venv4archer) [root@archery archery-1.3.7]# yum install openldap-devel (venv4archer) [root@archery archery-1.3.7]#pip3 install pyldap
(venv4archer) [root@archery archery-1.3.7]# pip3 install -r requirements.txt
安装docker,并启动服务
yum install -y docker yum install -y docker-compose systemctl start docker systemctl enable docker
创建inception和archery配置文件
inception
(venv4archer) [root@archery dockersrc]# mkdir -p /opt/inception
(venv4archer) [root@archery dockersrc]# pwd /opt/inception (venv4archer) [root@archery dockersrc]# vim inc.cnf [inception] general_log=1 general_log_file=inception.log port=6669 socket=/tmp/inc.socket character-set-client-handshake=0 character-set-server=utf8 inception_remote_system_password=jason_zhang inception_remote_system_user=root inception_remote_backup_port=3306 inception_remote_backup_host=192.168.199.177 inception_support_charset=utf8,utf8mb4 inception_osc_on=ON inception_osc_bin_dir=/usr/bin
archery
mkdir -p /opt/archery/downloads/log
cd /opt/archery
(venv4archer) [root@archery archer]# vim settings.py
(venv4archer) [root@archery archer]# cat settings.py |grep -Ev '^$|^#'
# -*- coding: UTF-8 -*-
# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = 'hfusaf2m4ot#7)fkw#di2bu6(cv0@opwmafx5n#6=3d%x^hpl6'
# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True
ALLOWED_HOSTS = ['*']
# 解决nginx部署跳转404
USE_X_FORWARDED_HOST = True
# 请求限制
DATA_UPLOAD_MAX_MEMORY_SIZE = 15728640
# Application definition
INSTALLED_APPS = (
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'django_apscheduler',
'sql',
'themis',
)
MIDDLEWARE = (
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
'django.middleware.security.SecurityMiddleware',
'common.middleware.check_login_middleware.CheckLoginMiddleware',
'common.middleware.exception_logging_middleware.ExceptionLoggingMiddleware',
)
ROOT_URLCONF = 'archery.urls'
TEMPLATES = [
{
'BACKEND': 'django.template.backends.django.DjangoTemplates',
'DIRS': [os.path.join(BASE_DIR, 'common/templates')],
'APP_DIRS': True,
'OPTIONS': {
'context_processors': [
'django.template.context_processors.debug',
'django.template.context_processors.request',
'django.contrib.auth.context_processors.auth',
'django.contrib.messages.context_processors.messages',
'common.utils.global_info.global_info',
],
},
},
]
WSGI_APPLICATION = 'archery.wsgi.application'
# Internationalization
LANGUAGE_CODE = 'zh-hans'
TIME_ZONE = 'Asia/Shanghai'
USE_I18N = True
USE_TZ = False
# 时间格式化
USE_L10N = False
DATETIME_FORMAT = 'Y-m-d H:i:s'
DATE_FORMAT = 'Y-m-d'
# Static files (CSS, JavaScript, Images)
STATIC_URL = '/static/'
STATIC_ROOT = os.path.join(BASE_DIR, 'static')
STATICFILES_DIRS = [os.path.join(BASE_DIR, 'common/static'), ]
# 扩展django admin里users字段用到，指定了sql/models.py里的class users
AUTH_USER_MODEL = "sql.users"
AUTH_PASSWORD_VALIDATORS = [
{
'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
},
{
'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
'OPTIONS': {
'min_length': 9,
}
},
{
'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
},
{
'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
},
]
###############以下部分需要用户根据自己环境自行修改###################
# 该项目本身的mysql数据库地址
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'archery',
'USER': 'root',
'PASSWORD': 'jason_zhang',
'HOST': '192.168.199.177',
'PORT': '3306',
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
'charset': 'utf8mb4'
},
'TEST': {
'NAME': 'test_archery',
'CHARSET': 'utf8',
},
}
}
# themis审核所需mongodb数据库，账号角色必须有"anyAction" to "anyResource"权限
MONGODB_DATABASES = {
"default": {
"NAME": 'themis',
"USER": 'root',
"PASSWORD": 'jason_zhang',
"HOST": 'mongo',
"PORT": 27017,
},
}
# 缓存配置
CACHES = {
'default': {
'BACKEND': 'django.core.cache.backends.filebased.FileBasedCache',
'LOCATION': os.path.join(BASE_DIR, "archery"),
}
}
# LDAP
ENABLE_LDAP = False
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://xxx"
AUTH_LDAP_USER_DN_TEMPLATE = "cn=%(user)s,ou=xxx,dc=xxx,dc=xxx"
AUTH_LDAP_ALWAYS_UPDATE_USER = True # 每次登录从ldap同步用户信息
AUTH_LDAP_USER_ATTR_MAP = { # key为archery.sql_users字段名，value为ldap中字段名，用户同步信息
"username": "cn",
"display": "displayname",
"email": "mail"
}
# LOG配置
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'formatters': {
'verbose': {
'format': '[%(asctime)s][%(threadName)s:%(thread)d][task_id:%(name)s][%(filename)s:%(lineno)d][%(levelname)s]- %(message)s'
},
},
'handlers': {
'default': {
'level': 'DEBUG',
'class': 'logging.handlers.RotatingFileHandler',
'filename': 'downloads/log/archery.log',
'maxBytes': 1024 * 1024 * 100, # 5 MB
'backupCount': 5,
'formatter': 'verbose',
},
'console': {
'level': 'DEBUG',
'class': 'logging.StreamHandler',
'formatter': 'verbose'
}
},
'loggers': {
'default': { # default日志，存放于log中
'handlers': ['default'],
'level': 'DEBUG',
},
'django_auth_ldap': { # django_auth_ldap模块相关日志
'handlers': ['default'],
'level': 'DEBUG',
},
'django_apscheduler': { # django_apscheduler模块相关日志
'handlers': ['default'],
'level': 'DEBUG',
},
# 'django.db': { # 打印SQL语句到console，方便开发
# 'handlers': ['console'],
# 'level': 'DEBUG',
# 'propagate': True,
# },
'django.request': { # 打印请求错误堆栈信息到console，方便开发
'handlers': ['console'],
'level': 'DEBUG',
'propagate': True,
},
}
}
mongodb
mkdir -p /opt/mongo/datadir
新建 docker-compose.yml 引导文件
version: '3'
services:
mongo:
image: mongo:3.6
container_name: mongo
restart: always
volumes:
- "/opt/mongo/datadir:/data/db"
ports:
- 27017:27017
environment:
MONGO_INITDB_ROOT_USERNAME: root
MONGO_INITDB_ROOT_PASSWORD: jason_zhang
inception:
image: registry.cn-hangzhou.aliyuncs.com/lihuanhuan/inception
container_name: inception
restart: always
ports:
- "6669:6669"
volumes:
- "/opt/inception/inc.cnf:/etc/inc.cnf"
archery:
image: registry.cn-hangzhou.aliyuncs.com/lihuanhuan/archery:1.3.7
container_name: archery
restart: always
ports:
- "9123:9123"
volumes:
- "/opt/archery/settings.py:/opt/archery/archery/settings.py"
- "/opt/archery/downloads:/opt/archery/downloads"
command: ["bash","/opt/archery/src/docker/startup.sh"]
environment:
NGINX_PORT: 9123
拉取镜像
(venv4archer) [root@archery opt]# docker-compose -f docker-compose.yml up -d
失败的话可以删除后再次操作
docker-compose -f docker-compose.yml kill
docker-compose -f docker-compose.yml rm
docker-compose -f docker-compose.yml up -d
表结构初始化
docker exec -ti archery /bin/bash ##进入archery容器
cd /opt/archery
source /opt/venv4archery/bin/activate
python3 manage.py makemigrations sql
python3 manage.py migrate
#创建管理用户
python3 manage.py createsuperuser
#日志查看和问题排查
docker logs archery
登录： http://192.168.199.177:9123
初次登录提示密码为空，重启archery容器
Docker
archery镜像：https://dev.aliyun.com/detail.html?spm=5176.1972343.2.2.58c75aaa3iK1Sb&repoId=244140
inception镜像: https://dev.aliyun.com/detail.html?spm=5176.1972343.2.12.7b475aaaLiCfMf&repoId=142093
docker ps -a
docker rm
docker images
docker rmi
要先查看该image有无使用才能删除
进入一个容器
docker exec -ti archery /bin/bash
docker inspect 查看容器IP
测试数据库能否正常连接，在另外一台服务器上连接宿主机的数据库
mysql -h 192.168.199.177 -P 3306 -u root -p
测试inception：1、连接mysql ： mysql -uroot -h192.168.199.224 -P6669 2、运行inception get variables; 3、能查看到信息就成功了
常见问题： 参考：https://github.com/hhyo/archery/wiki/%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E#%E9%98%BF%E9%87%8C%E4%BA%91rds%E7%AE%A1%E7%90%86
https://gitee.com/ilanni/archer?tdsourcetag=s_pcqq_aiomsg
1、“SQL上线” sql检测报错
被检测的数据库密码带 * 号
2、回滚失败
首先检查
1、检查inception配置文件相关用户的权限；
2、检查blog_bin是否开启；log_bin格式，要为ROW ;
连接mysql
show global variables like 'log_bin';
show global variables like '%binlog_format%';
https://blog.csdn.net/king_kgh/article/details/74800513 （mysql5.7开启log_bin）
3、检查要执行DML的表是否存在主键
4、检查语句执行后有无影响数据库的数据
3、慢日志查询
采用percona-toolkit的pt_query_digest收集慢日志，在系统中进行展示，并且支持一键获取优化建议
https://www.cnblogs.com/zishengY/p/6852280.html
安装（archery 容器）
安装percona-toolkit，以centos为例
yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum -y install percona-toolkit.x86_64
使用/opt/archery/src/init_sql/mysql_slow_query_review.sql创建慢archery数据库的日志收集表（下载下来使用第三方客户端导入archery数据库）将/opt/archery/src/script/analysis_slow_query.sh部署到各个mysql实例，注意修改脚本里面的hostname="${mysql_host}:${mysql_port}"与archery实例信息一致，脚本可以放在任意位置
#!/bin/bash DIR="$( cd "$( dirname "$0" )" && pwd )" cd $DIR #配置archery数据库的连接地址 monitor_db_host="192.168.199.177" monitor_db_port=3306 monitor_db_user="root" monitor_db_password="jason_zhang" monitor_db_database="archery" #被监控机慢日志位置 slowquery_file="/home/mysql/log_slow.log" （目录位置存在） pt_query_digest="/usr/bin/pt-query-digest" #被监控机连接信息 hostname="192.168.199.178:3306" # 被监控机连接信息，和archery主库配置内容保持一致，用于archery做筛选 （被收集的mysql服务器IP） #获取上次分析时间，初始化时请删除last_analysis_time_$hostname文件，可分析全部日志数据 if [ -s last_analysis_time_$hostname ]; then last_analysis_time=`cat last_analysis_time_$hostname` else last_analysis_time='1000-01-01 00:00:00' fi #收集日志 #RDS需要增加--no-version-check选项 $pt_query_digest \ --user=$monitor_db_user --password=$monitor_db_password --port=$monitor_db_port \ --review h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review \ --history h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review_history \ --no-report --limit=100% --charset=utf8 \ --since "$last_analysis_time" \ --filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$hostname\" and \$event->{client}=\$event->{ip} " \ $slowquery_file > /tmp/analysis_slow_query.log echo `date +"%Y-%m-%d %H:%M:%S"`>last_analysis_time_$hostname
阿里云RDS
1、确保“系统设置”的阿里云模块开启并正确配置了“系统管理”--“其他项管理”--“全部后台数据”-- “阿里云认证信息” 配置正确
2、阿里云配置
重启一下服务器
数据库审核 （themis）
https://github.com/CreditEaseDBA/Themis
https://tuteng.gitbooks.io/themis/content/
1、上传rule.json文件到mongo容器的data文件夹
将archery-1.3.7.zip上传到宿主机并解压
docker cp /opt/archery-1.3.7 mongo:/data
2、在mongo容器上执行（账户密码查看archery容器的archery配置文件）
mongoimport -h 127.0.0.1 --port 27017 -d themis -c rule -u root -p jason_zhang --upsert /data/archery-1.3.7/src/script/rule.json --authenticationDatabase admin
邮件发送
1、系统设置里面配置一个发送邮件的账户
2、进入archery容器,修改 /opt/archery/sql/notify.py 的异步调用模块
# 异步调用 def send_msg(audit_id, msg_type, **kwargs): logger.debug('异步发送消息通知，消息audit_id={}，msg_type={}'.format(audit_id, msg_type)) #p = Thread(target=_send, args=(audit_id, msg_type), kwargs=kwargs) #p.start() _send(audit_id, msg_type, **kwargs)
删除表，直接删除无法恢复表数据
删除 test表
delete from test where 1=1;
drop table test;
系统升级：https://github.com/jly8866/archer/tree/archer-2.0
https://github.com/hhyo/archery/releases/
系统功能说明： https://github.com/hhyo/archery/wiki/%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E#%E9%98%BF%E9%87%8C%E4%BA%91rds%E7%AE%A1%E7%90%86
参考：
https://www.cnblogs.com/chenjiaxin--007/p/8432795.html
https://blog.csdn.net/xujiamin0022016/article/details/81980393
https://gitee.com/ilanni/archer/tree/archer-2.0/
https://github.com/hhyo/archery
https://github.com/hhyo/archery/wiki/%E9%83%A8%E7%BD%B2
https://gitee.com/ilanni/archer#%E9%98%BF%E9%87%8C%E4%BA%91rds%E7%AE%A1%E7%90%86
https://github.com/hhyo/archery
Archery审核平台集成LDAP认证以及慢日志展示等常见问题解决
https://blog.csdn.net/qq_35209838/article/details/84998460?tdsourcetag=s_pcqq_aiomsg
Archery-1.7.13
参考：https://archerydms.com/installation/docker/
环境：自建的mysql5.7
自建的redis
docker安装archery、inception、goinception
安装mysql
https://www.cnblogs.com/xiaoyou2018/p/9831272.html
redis
https://www.cnblogs.com/xiaoyou2018/p/9597964.html
安装docker-compose
https://www.cnblogs.com/xiaoyou2018/p/12925220.html
下载 Releases文件，解压后进入docker-compose文件夹把里面的文件拷贝至 /opt/archery
cd archery-1.7.13/src/docker-compose/
cp -r * /opt
vim /opt/docker-compose.yml
version: '3' services: # redis: # image: redis:5 # container_name: redis # restart: always # command: redis-server --requirepass 123456 # expose: # - "6379" # mysql: # image: mysql:5.7 # container_name: mysql # restart: always # ports: # - "3306:3306" # volumes: # - "./mysql/my.cnf:/etc/mysql/my.cnf" # - "./mysql/datadir:/var/lib/mysql" # environment: # MYSQL_DATABASE: archery # MYSQL_ROOT_PASSWORD: 123456 inception: image: hhyo/inception container_name: inception restart: always expose: - "6669" volumes: - "/opt/inception/inc.cnf:/etc/inc.cnf" goinception: image: hanchuanchuan/goinception container_name: goinception restart: always expose: - "4000" volumes: - "/opt/inception/config.toml:/etc/config.toml" archery: image: hhyo/archery:1.7.13 container_name: archery restart: always ports: - "9123:9123" volumes: - "/opt/archery/settings.py:/opt/archery/archery/settings.py" - "/opt/archery/soar.yaml:/etc/soar.yaml" - "/opt/archery/docs.md:/opt/archery/docs/docs.md" - "/opt/archery/downloads:/opt/archery/downloads" - "/opt/archery/sql/migrations:/opt/archery/sql/migrations" - "/opt/archery/logs:/opt/archery/logs" entrypoint: "dockerize -wait tcp://192.168.1.179:3306 -wait tcp://192.168.1.179:6379 -timeout 60s /opt/archery/src/docker/startup.sh" environment: NGINX_PORT: 9123
cat /opt/archery/settings.py
# -*- coding: UTF-8 -*- # Build paths inside the project like this: os.path.join(BASE_DIR, ...) import os BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) # SECURITY WARNING: keep the secret key used in production secret! SECRET_KEY = 'hfusaf2m4ot#7)fkw#di2bu6(cv0@opwmafx5n#6=3d%x^hpl6' # SECURITY WARNING: don't run with debug turned on in production! DEBUG = True ALLOWED_HOSTS = ['*'] # 解决nginx部署跳转404 USE_X_FORWARDED_HOST = True # 请求限制 DATA_UPLOAD_MAX_MEMORY_SIZE = 15728640 # Application definition INSTALLED_APPS = ( 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_q', 'sql', 'sql_api', 'common', ) MIDDLEWARE = ( 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', 'django.middleware.security.SecurityMiddleware', 'django.middleware.gzip.GZipMiddleware', 'common.middleware.check_login_middleware.CheckLoginMiddleware', 'common.middleware.exception_logging_middleware.ExceptionLoggingMiddleware', ) ROOT_URLCONF = 'archery.urls' TEMPLATES = [ { 'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [os.path.join(BASE_DIR, 'common/templates')], 'APP_DIRS': True, 'OPTIONS': { 'context_processors': [ 'django.template.context_processors.debug', 'django.template.context_processors.request', 'django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', 'common.utils.global_info.global_info', ], }, }, ] WSGI_APPLICATION = 'archery.wsgi.application' # Internationalization LANGUAGE_CODE = 'zh-hans' TIME_ZONE = 'Asia/Shanghai' USE_I18N = True USE_TZ = False # 时间格式化 USE_L10N = False DATETIME_FORMAT = 'Y-m-d H:i:s' DATE_FORMAT = 'Y-m-d' # Static files (CSS, JavaScript, Images) STATIC_URL = '/static/' STATIC_ROOT = os.path.join(BASE_DIR, 'static') STATICFILES_DIRS = [os.path.join(BASE_DIR, 'common/static'), ] STATICFILES_STORAGE = 'common.storage.ForgivingManifestStaticFilesStorage' # 扩展django admin里users字段用到，指定了sql/models.py里的class users AUTH_USER_MODEL = "sql.Users" # 密码校验 AUTH_PASSWORD_VALIDATORS = [ { 'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator', }, { 'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator', 'OPTIONS': { 'min_length': 9, } }, { 'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator', }, { 'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator', }, ] ############### 以下部分需要用户根据自己环境自行修改 ################### # SESSION 设置 SESSION_COOKIE_AGE = 60 * 300 # 300分钟 SESSION_SAVE_EVERY_REQUEST = True SESSION_EXPIRE_AT_BROWSER_CLOSE = True # 关闭浏览器，则COOKIE失效 # 该项目本身的mysql数据库地址 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'archery', 'USER': 'archery', 'PASSWORD': 'jason_zhang', 'HOST': '192.168.1.179', 'PORT': '3306', 'OPTIONS': { 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", 'charset': 'utf8mb4' }, 'TEST': { 'NAME': 'test_archery', 'CHARSET': 'utf8mb4', }, } } # 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，使用同步模式 } # 缓存配置 CACHES = { "default": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://192.168.1.179:6379/0", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", "PASSWORD": "TTdjy911.500" } }, "dingding": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://192.168.1.179:6379/1", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", "PASSWORD": "TTdjy911.500" } } } # LDAP ENABLE_LDAP = False 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://xxx" AUTH_LDAP_USER_DN_TEMPLATE = "cn=%(user)s,ou=xxx,dc=xxx,dc=xxx" # ldap认证的另一种方式,使用时注释AUTH_LDAP_USER_DN_TEMPLATE """ AUTH_LDAP_BIND_DN = "cn=xxx,ou=xxx,dc=xxx,dc=xxx" AUTH_LDAP_BIND_PASSWORD = "***********" AUTH_LDAP_USER_SEARCH = LDAPSearch('ou=xxx,dc=xxx,dc=xxx',ldap.SCOPE_SUBTREE, '(cn=%(user)s)',) """ AUTH_LDAP_ALWAYS_UPDATE_USER = True # 每次登录从ldap同步用户信息 AUTH_LDAP_USER_ATTR_MAP = { # key为archery.sql_users字段名，value为ldap中字段名，用户同步信息 "username": "cn", "display": "displayname", "email": "mail" } # LOG配置 LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'formatters': { 'verbose': { 'format': '[%(asctime)s][%(threadName)s:%(thread)d][task_id:%(name)s][%(filename)s:%(lineno)d][%(levelname)s]- %(message)s' }, }, 'handlers': { 'default': { 'level': 'DEBUG', 'class': 'logging.handlers.RotatingFileHandler', 'filename': 'logs/archery.log', 'maxBytes': 1024 * 1024 * 100, # 5 MB 'backupCount': 5, 'formatter': 'verbose', }, 'django-q': { 'level': 'DEBUG', 'class': 'logging.handlers.RotatingFileHandler', 'filename': 'logs/qcluster.log', 'maxBytes': 1024 * 1024 * 100, # 5 MB 'backupCount': 5, 'formatter': 'verbose', }, 'console': { 'level': 'DEBUG', 'class': 'logging.StreamHandler', 'formatter': 'verbose' } }, 'loggers': { 'default': { # default日志 'handlers': ['console', 'default'], 'level': 'WARNING' }, 'django-q': { # django_q模块相关日志 'handlers': ['console', 'django-q'], 'level': 'WARNING', 'propagate': False }, 'django_auth_ldap': { # django_auth_ldap模块相关日志 'handlers': ['console', 'default'], 'level': 'WARNING', 'propagate': False }, # 'django.db': { # 打印SQL语句，方便开发 # 'handlers': ['console', 'default'], # 'level': 'DEBUG', # 'propagate': False # }, # 'django.request': { # 打印请求错误堆栈信息，方便开发 # 'handlers': ['console', 'default'], # 'level': 'DEBUG', # 'propagate': False # }, } } [root@jason_test archery]# [root@jason_test archery]# cat settings.py |grep -Ev '^$|^#' import os BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) SECRET_KEY = 'hfusaf2m4ot#7)fkw#di2bu6(cv0@opwmafx5n#6=3d%x^hpl6' DEBUG = True ALLOWED_HOSTS = ['*'] USE_X_FORWARDED_HOST = True DATA_UPLOAD_MAX_MEMORY_SIZE = 15728640 INSTALLED_APPS = ( 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'django_q', 'sql', 'sql_api', 'common', ) MIDDLEWARE = ( 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', 'django.middleware.security.SecurityMiddleware', 'django.middleware.gzip.GZipMiddleware', 'common.middleware.check_login_middleware.CheckLoginMiddleware', 'common.middleware.exception_logging_middleware.ExceptionLoggingMiddleware', ) ROOT_URLCONF = 'archery.urls' TEMPLATES = [ { 'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [os.path.join(BASE_DIR, 'common/templates')], 'APP_DIRS': True, 'OPTIONS': { 'context_processors': [ 'django.template.context_processors.debug', 'django.template.context_processors.request', 'django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', 'common.utils.global_info.global_info', ], }, }, ] WSGI_APPLICATION = 'archery.wsgi.application' LANGUAGE_CODE = 'zh-hans' TIME_ZONE = 'Asia/Shanghai' USE_I18N = True USE_TZ = False USE_L10N = False DATETIME_FORMAT = 'Y-m-d H:i:s' DATE_FORMAT = 'Y-m-d' STATIC_URL = '/static/' STATIC_ROOT = os.path.join(BASE_DIR, 'static') STATICFILES_DIRS = [os.path.join(BASE_DIR, 'common/static'), ] STATICFILES_STORAGE = 'common.storage.ForgivingManifestStaticFilesStorage' AUTH_USER_MODEL = "sql.Users" AUTH_PASSWORD_VALIDATORS = [ { 'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator', }, { 'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator', 'OPTIONS': { 'min_length': 9, } }, { 'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator', }, { 'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator', }, ] SESSION_COOKIE_AGE = 60 * 300 # 300分钟 SESSION_SAVE_EVERY_REQUEST = True SESSION_EXPIRE_AT_BROWSER_CLOSE = True # 关闭浏览器，则COOKIE失效 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'archery', 'USER': 'archery', 'PASSWORD': 'jason_zhang', 'HOST': '192.168.1.179', 'PORT': '3306', 'OPTIONS': { 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", 'charset': 'utf8mb4' }, 'TEST': { 'NAME': 'test_archery', 'CHARSET': 'utf8mb4', }, } } 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，使用同步模式 } CACHES = { "default": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://192.168.1.179:6379/0", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", "PASSWORD": "TTdjy911.500" } }, "dingding": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://192.168.1.179:6379/1", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", "PASSWORD": "TTdjy911.500" } } } ENABLE_LDAP = False 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://xxx" AUTH_LDAP_USER_DN_TEMPLATE = "cn=%(user)s,ou=xxx,dc=xxx,dc=xxx" # ldap认证的另一种方式,使用时注释AUTH_LDAP_USER_DN_TEMPLATE """ AUTH_LDAP_BIND_DN = "cn=xxx,ou=xxx,dc=xxx,dc=xxx" AUTH_LDAP_BIND_PASSWORD = "***********" AUTH_LDAP_USER_SEARCH = LDAPSearch('ou=xxx,dc=xxx,dc=xxx',ldap.SCOPE_SUBTREE, '(cn=%(user)s)',) """ AUTH_LDAP_ALWAYS_UPDATE_USER = True # 每次登录从ldap同步用户信息 AUTH_LDAP_USER_ATTR_MAP = { # key为archery.sql_users字段名，value为ldap中字段名，用户同步信息 "username": "cn", "display": "displayname", "email": "mail" } LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'formatters': { 'verbose': { 'format': '[%(asctime)s][%(threadName)s:%(thread)d][task_id:%(name)s][%(filename)s:%(lineno)d][%(levelname)s]- %(message)s' }, }, 'handlers': { 'default': { 'level': 'DEBUG', 'class': 'logging.handlers.RotatingFileHandler', 'filename': 'logs/archery.log', 'maxBytes': 1024 * 1024 * 100, # 5 MB 'backupCount': 5, 'formatter': 'verbose', }, 'django-q': { 'level': 'DEBUG', 'class': 'logging.handlers.RotatingFileHandler', 'filename': 'logs/qcluster.log', 'maxBytes': 1024 * 1024 * 100, # 5 MB 'backupCount': 5, 'formatter': 'verbose', }, 'console': { 'level': 'DEBUG', 'class': 'logging.StreamHandler', 'formatter': 'verbose' } }, 'loggers': { 'default': { # default日志 'handlers': ['console', 'default'], 'level': 'WARNING' }, 'django-q': { # django_q模块相关日志 'handlers': ['console', 'django-q'], 'level': 'WARNING', 'propagate': False }, 'django_auth_ldap': { # django_auth_ldap模块相关日志 'handlers': ['console', 'default'], 'level': 'WARNING', 'propagate': False }, # 'django.db': { # 打印SQL语句，方便开发 # 'handlers': ['console', 'default'], # 'level': 'DEBUG', # 'propagate': False # }, # 'django.request': { # 打印请求错误堆栈信息，方便开发 # 'handlers': ['console', 'default'], # 'level': 'DEBUG', # 'propagate': False # }, } }
cat /opt/inception/inc.cnf
[inception] general_log=1 general_log_file=inception.log port=6669 socket=/tmp/inc.socket character-set-client-handshake=0 character-set-server=utf8 inception_language_code=zh-CN inception_remote_system_password=jason_zhang inception_remote_system_user=archery inception_remote_backup_port=3306 inception_remote_backup_host=192.168.1.179 inception_support_charset=utf8,utf8mb4 inception_enable_nullable=0 inception_check_primary_key=1 inception_check_column_comment=1 inception_check_table_comment=1 inception_osc_on=OFF inception_osc_bin_dir=/usr/bin inception_osc_min_table_size=10 inception_osc_chunk_time=0.1 inception_enable_blob_type=1 inception_check_column_default_value=1 inception_enable_select_star=ON inception_enable_identifer_keyword=ON inception_enable_autoincrement_unsigned=ON inception_check_identifier=OFF
cat /opt/inception/config.toml
# TiDB Configuration. # TiDB server host. host = "0.0.0.0" # tidb server advertise IP. advertise_address = "" # TiDB server port. port = 4000 # path = "" [inc] check_autoincrement_datatype =true check_autoincrement_init_value =true check_autoincrement_name =true check_column_comment =true check_column_default_value =true check_column_position_change =true check_column_type_change =true check_dml_limit =true check_dml_orderby =true check_dml_where =true check_identifier =true check_index_prefix =true check_insert_field =true check_primary_key =true check_table_comment =true check_timestamp_default =true check_timestamp_count =false enable_autoincrement_unsigned =true enable_blob_type =true enable_column_charset =true enable_drop_database =true enable_drop_table =true enable_enum_set_bit =false enable_fingerprint =true enable_foreign_key =false enable_json_type =true enable_identifer_keyword =false enable_not_innodb =false enable_nullable =false enable_null_index_name =false enable_orderby_rand =true enable_partition_table =true enable_pk_columns_only_int =true enable_select_star =false enable_set_charset =true enable_set_collation =false enable_set_engine =false max_char_length =0 max_insert_rows =0 max_keys =5 max_key_parts =5 max_update_rows =5000 max_primary_key_parts =1 max_allowed_packet =33554432 merge_alter_table =true check_float_double =true support_charset ="utf8,utf8mb4" support_collation ="utf8_general_ci,utf8mb4_general_ci" backup_host = "192.168.1.179" backup_port = 3306 backup_user = "archery" backup_password = "jason_zhang" # 安全更新是否开启. # -1 表示不做操作,基于远端数据库 [默认值] # 0 表示关闭安全更新 # 1 表示开启安全更新 sql_safe_updates = 0 # lang = "en-US" lang = "zh-CN" # 是否记录全量日志 general_log = false # 开启统计功能 enable_sql_statistic = true [inc_level] er_cant_set_engine = 2 er_cant_set_collation = 2 er_table_must_have_comment = 2 er_column_have_no_comment = 2 er_table_must_have_pk = 2 er_index_name_idx_prefix = 1 er_index_name_uniq_prefix = 1 er_autoinc_unsigned = 2 er_alter_table_once = 2 er_pk_too_many_parts = 2 [osc] osc_on = false osc_min_table_size = 16 osc_print_none = false osc_bin_dir = "/usr/local/bin" [ghost] ghost_on = false ghost_allow_on_master = true ghost_assume_rbr = true ghost_chunk_size = 1000 ghost_concurrent_rowcount = true ghost_cut_over = "atomic" ghost_cut_over_lock_timeout_seconds = 3 ghost_default_retries = 60 ghost_heartbeat_interval_millis = 500 ghost_max_lag_millis = 1500 ghost_approve_renamed_columns = true ghost_exponential_backoff_max_interval = 64 ghost_dml_batch_size = 10 [log] # Log level: debug, info, warn, error, fatal. level = "error" # Log format, one of json, text, console. format = "console"
# 启动
docker-compose -f docker-compose.yml up -d
# 表结构初始化
docker exec -ti archery /bin/bash
cd /opt/archery
source /opt/venv4archery/bin/activate
python3 manage.py makemigrations sql
python3 manage.py migrate
# 数据初始化
python3 manage.py dbshell<sql/fixtures/auth_group.sql
python3 manage.py dbshell<src/init_sql/mysql_slow_query_review.sql
# 创建管理用户
python3 manage.py createsuperuser
# 重启
docker restart archery
# 日志查看和问题排查
docker logs archery -f --tail=50
