This issue recommends an Archery positioning SQL audit query platform.
Introduction
Archery is a branch project of archer, which is located in the SQL audit and query platform, aiming to improve the work efficiency of DBA, support the SQL online and query of multiple databases, and support rich MySQL operation and maintenance functions, all of which are compatible with mobile operation.
Manual deployment
< Install Python and virtualenv
# Install dependency
yum install wget gcc make zlib-devel openssl openssl-devel
Wget < span class = "HLJS - string" > "https://www.python.org/ftp/python/3.6.5/Python-3.6.5.tar.xz" < / span >
tar -xvJf Python-3.6.5.tar.xz
# compile
cd Python-3.6.5
./configure prefix=/usr/local/python3
make & & make install
ln -fs /usr/local/python3/bin/python3 /usr/bin/python3
ln -fs /usr/local/python3/bin/pip3 /usr/bin/pip3
# virtualenv
pip3 install virtualenv -i https://mirrors.ustc.edu.cn/pypi/web/simple/
ln -fs /usr/local/python3/bin/virtualenv /usr/bin/virtualenv yum Install
# Install EPEL source
yum install epel-release
# Install python36 and pip36
yum install python36 python36-pip
# virtualenv
pip3.6 install virtualenv -i https://mirrors.ustc.edu.cn/pypi/web/simple/ Installing Archery
# Prepare the virtual environment
# Compiling and installing python
virtualenv venv4archery --python=python3
# yum install use
virtualenv venv4archery --python=python3.6
# Switch python runtime environment to virtual environment
source venv4archery/bin/activateModify the configuration
vi archery/settings.pySecurity modification
Be sure to modify the SECRET_KEY information in the configuration file, which is used to encrypt sensitive information
< Basic configuration
# SECRET_KEY
# Turn off debug mode
DEBUG = False
# Set ALLOWED_HOSTS, recommend limiting Intranet access
ALLOWED_HOSTS = [
'.example.com', # Allow domain and subdomains
'.example.com.', # Also allow FQDN and subdomains
]
# Request size limit, which can be modified if the submitted SQL statement is too large
DATA_UPLOAD_MAX_MEMORY_SIZE = 15728640
# Password verification, user registration and add password verification rules
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', # Database name
'USER': 'root', # Database user
'PASSWORD': "" , # Database password
'HOST': '127.0.0.1', # Database HOST, if docker started and associated, can use the container name to connect
'PORT': '3306', # Database port
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", # SQL_MODE，Compatible select * group by, can be adjusted as needed
'charset': 'utf8mb4'
},
'TEST': {
'NAME': 'test_archery',
'CHARSET': 'utf8mb4',
},
}
}Start
runserver startup (as a local test only)
source /opt/venv4archery/bin/activate
# Start Django-Q, keep it running in the background
python3 manage.py qcluster
# Start service
python3 manage.py runserver 0.0.0.0:9123 --insecure Gunicorn+Nginx start
# nginx configuration example
server{
listen 9123; # Listening port
server_name archery;
client_max_body_size 20M; # Handling Request Entity Too Large
proxy_read_timeout 600s; # The timeout time is set the same as the Gunicorn timeout time, mainly for online queries
location / {
proxy_pass http://127.0.0.1:8000;
proxy_set_header Host $host:9123; # Fix redirection 404 issue, keep it consistent with listen port, if docker, keep it consistent with host mapping port
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; # Here points to the absolute path to the settings.py configuration item STATIC_ROOT directory used by nginx to collect static resources
}
error_page 404 /404.html;
location = /40x.html {
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
}
}
# Start
source /opt/venv4archery/bin/activate
bash startup.shUse
Add an instance
-
- < li data – track = “109” > instance type is given priority to library/from library, support for MySQL/MsSQL database type/Redis/PostgreSQL/Oracle/mongo/Phoenix, support detail can view the function list < / li >
- Resource group: instances need to be associated with resource groups so that users of associated resource groups can access
- Instance tag: Control whether the instance is displayed in SQL on-line/query through tags that support on-line and query. To use on-line and query instances, associate tags
are required.
Adding a Resource group
A resource group is a collection of resource objects that are associated with users and used to isolate resource access rights. Generally, it can be divided according to the project team.
Associated user/instance with resource group
Users must be associated with resource groups to access instance resources in resource groups. – Associated object management allows you to associate instances and users in batches. – You can also associate resource groups in batches when adding users and instances.
< Set the approval process of work order on-line and query
The project provides a simple multi-level approval process configuration, which is related to resource groups and approval types. Different resource groups and approval types can configure different approval processes, and the approval process is configured with permission groups, which can avoid the problem of single approver
