archer Download - archer Source code download

archer

Website data

自动化数据库SQL操作平台

Download

archer

An automated SQL operation platform based on inception supports work orders, audits, timing tasks, emails, OSC and other functions. It can also configure MySQL query, slow query management, session management, etc.

Main functions

Automatic review initiates SQL launch, work orders are submitted, and the inception is automatically reviewed. After the review is passed, the auditor needs to manually review it.
Manual review
Inception automatically reviewed work orders will be reviewed by other R&D engineers or R&D managers. DBA operations will execute SQL
Why do we need manual review?
This follows the process awareness of online operations in the field of operation and maintenance. If an engineer wants to update the online database SQL, it is best to check it by another engineer. Many times, the DBA does not know the business meaning of SQL, so manual review is best to be reviewed by other R&D engineers or R&D managers. This is the design concept of archer
Rollback statements can be displayed in the rollback data display work order, supporting one-click submission of rollback work orders
Execute SQL regularly
The work orders approved by the DBA can be executed regularly. The execution time can be modified before execution and can be terminated at any time.
pt-osc execution supports pt-osc execution progress display, and you can click to abort the pt-osc process
MySQL query library, tables, and keywords automatically complete query result set restrictions, query result export, table structure display, and multi-result set display
MySQL query permission management is based on inception parsing query statements. Query permissions support restricting the application, review and management of table-level query permissions, supporting audit process configuration, and multi-level auditing.
MySQL query dynamic desensitization is based on inception parsing query statements, and it can achieve dynamic desensitization of sensitive data in combination with desensitization field configuration and desensitization rules (regular expressions).
Slow log management is based on pt_query_digest of percona-toolkit to analyze and store slow logs, and display them on the web side.
Email notifications can be configured with email reminders to notify online applications, permission applications, review results, etc., and notify abnormal login.

Design Specifications

Reasonable database design and specifications are very necessary, especially MySQL databases. The kernel is not as powerful as oracle, db2, SQL Server and other databases. It requires reasonable design to play to your strengths and avoid your weaknesses. The Internet industry has mature MySQL design specifications, and it is written as follows. Please ask readers to train all backend developers with this specification before the company goes online to use the archer system, so that they know the reason and why.
Download link https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md

Main configuration files

archer/archer/settings.py

Adopt docker

docker image, reference wiki
- inception image: https://hub.docker.com/r/hhyo/inception
- archer image: https://hub.docker.com/r/hhyo/archer
Docker image production thanks @小彩票线

Manual installation steps

Environmental preparation

Cloning the code to local or downloading the zip package
git clone https://github.com/jly8866/archer.git
Install inception
Project gallery

Install python3, version number >=3.4 (Since you need to modify the official module, please use virtualenv or venv to isolate the environment separately!)
```
 pip3 install virtualenv
virtualenv venv4archer --python=python3.4
```

Install the required related modules

 source venv4archer/bin/activate
pip3 install -r requirements.txt

pymysql module compatible inception version information uses the file in src/docker/pymysql directory to replace /path/to/python3/lib/python3.4/site-packages/pymysql/ corresponding file

Preparation before starting

Create the database table of the archer itself

Modify all address information of archer/archer/settings.py, including DATABASES and INCEPTION_XXX parts
Create the database table of the archer itself through model. If it is an existing version upgrade, please use the change script in src/init_sql to change the database original v1.1.1 branch. Please use v1.1.1->v2.0.sql to change the original master branch. Please use master->v2.0.sql to change the new installation. Please use the following method to initialize it in the following way
```
 python3 manage.py makemigrations sql  
python3 manage.py migrate 
```

Create an admin system root user (this user can log in to django admin to manage the model)
python3 manage.py createsuperuser

There are two ways to start
(1) Use django built-in runserver to start the service, and it is recommended not to use it in production environment
bash debug.sh
(2) Use gunicorn+nginx to start the service installation module pip3 install gunicorn==19.7.1
nginx configuration example

 server{
        listen 9123; #监听的端口
        server_name archer;
        proxy_read_timeout 600s;  #超时时间与gunicorn超时时间设置一致，主要用于在线查询

        location / {
          proxy_pass http://127.0.0.1:8888;
          proxy_set_header Host $host:9123; #解决重定向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 /archer/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 {
        }
    }

Start bash startup.sh

Official access to log in with the administrator account created above http://XXXX:port/login/

Other features integration

Online query

Change QUERY to True in settings
To the [Background Data Management]-[Slave Library Address Configuration] page, add slave library information
Go to [Background Data Management]-[Workflow Configuration] page configuration review process
Users can conduct online inquiries after applying for permissions and passing the review
If you need to use dynamic desensitization, please change DATA_MASKING_ON_OFF in settings to True, and go to the [Backend Data Management]-[Desensitization Configuration] page to configure desensitization rules and fields

Slow log management

SLOWQUERY is changed to True in settings

Install percona-toolkit (version=3.0.6), taking centos as an example

 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

Create slow log collection tables to archer database using src/script/mysql_slow_query_review.sql
Deploy src/script/analysis_slow_query.sh to each monitoring machine. Note that hostname="${mysql_host}:${mysql_port}" in the script is consistent with the configuration information of the archer main library, otherwise the relevant records will not be filtered.

SQLAdvisor optimization tool

Install SQLAdvisor, project address
Modify the configuration file SQLADVISOR to the program path, the path needs to be complete, such as '/opt/SQLAdvisor/sqladvisor/sqladvisor'

Alibaba Cloud rds management

Modify the configuration file ALIYUN_RDS_MANAGE=True

Install the module

 pip3 install aliyun-python-sdk-core==2.3.5
pip3 install aliyun-python-sdk-core-v3==2.5.3
pip3 install aliyun-python-sdk-rds==2.1.1

On the [Backend Data Management]-[Ali Cloud Certification Information] page, add the accesskey information of the Alibaba Cloud account and restart the service
On the [Background Data Management]-[Ali Cloud rds Configuration] page, add instance information to realize process management and slow log management of Alibaba Cloud rds

Admin backend reinforcement, brute-proof cracking

In the patch directory, the name is django_1.8.17_admin_secure_archer.patch

Use commands

 patch  python/site-packages/django/contrib/auth/views.py django_1.8.17_admin_secure_archer.patch

Integrated ldap

Modify the configuration file ENABLE_LDAP=True, install the relevant module, you can enable the ldap account login, take centos as an example
```
 yum install openldap-devel
pip install django-auth-ldap==1.3.0
```
If ldaps is used and it is a self-signed certificate, you need to open the comment of AUTH_LDAP_GLOBAL_OPTIONS in settings
Configurations starting with AUTH_LDAP in settings need to be modified according to their own ldap corresponding

System experience

Click to experience

Role	account	password
administrator	archer	archer
engineer	engineer	archer
Reviewer	auditor	archer
DBA	dba	archer

Some problems solved

View error log

/tmp/default.log & /tmp/archer.err

Page style display exception

runserver/debug.sh starts
Debug is closed in settings, that is, DEBUG = False, and it needs to add --insecure after the startup command, and become

nginx+gunicorn/startup.sh start
The static resource configuration of nginx is incorrect

 location /static {
              alias /archer/static; #此处指向settings.py配置项STATIC_ROOT目录的绝对路径，用于nginx收集静态资源，一般默认为archer按照目录下的static目录
            }

User Management

Sometimes the user error is reported by nginx+gunicorn/startup.sh. Multi-worker deployment may occur. The problem has not been solved at present.
Unable to log in (confirm that the username and password are correct)
Check whether the user is_active field is 1

SQL is online

The cluster does not display database
archer will filter some system databases by default, and the filter list is 'information_schema', 'performance_schema', 'mysql', 'test', 'sys'
The reviewer does not show valid users who are not a reviewer/DBA role
There is no execution button after the review is passed
archer's SQL launch process is: the engineer submits SQL->Auditor review->DBA execution. The auditor can only review the data that belongs to his own review, and the DBA executes all data.

Detect SQL error

invalid literal for int() with base 10:'Inception2'
Adjust pymysql to make it compatible with Inception version information.
Use the files in the src/docker/pymysql directory to replace the files in the /path/to/python3/lib/python3.4/site-packages/pymysql/ directory
invalid source information
The password cannot include *
Must start as begin statement
python3's pymysql module will send SHOW WARNINGS statement to inception, causing the inception to return a "Must start as begin statement" error. The archer will report that the file in the /path/to/python3/lib/python3.4/site-packages/pymysql/ directory is used in the log to replace the file in the /path/to/python3/lib/python3.4/site-packages/pymysql/ directory.
Incorrect database name ''
Inception check does not support subquery
Invalid remote backup information
Inception cannot connect to backup library

Cannot generate a rollback statement

Check the configuration related to inception in the configuration file

Check the permissions of inception auditing users and backup users, permission reference

 — inception备份用户
GRANT SELECT, INSERT, CREATE ON *.* TO 'inception_bak'
— inception审核用户（主库配置用户，如果要使用会话管理需要赋予SUPER权限，如果需要使用OSC，请额外配置权限）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'inception'
— archer在线查询用户（从库配置用户）
GRANT SELECT ON *.* TO 'archer_read'

Check the binlog format, it needs to be ROW, binlog_row_image is FULL
Check whether the DML table has primary keys
Check whether the statement affects the data
Check whether the backup library is enabled for autocommit
Check whether it is a continuous table update statement
Check whether the execution instance is mysql

Desensitization rules are not in effect

Check if the desensitization field hits (whether it is case sensitive)
Check whether the regular expression of the desensitization rule can match the data. If it cannot match, the original result will be returned.
Check whether the CHECK_QUERY_ON_OFF parameter is turned off, causing the inception to fail to resolve statements that are not desensitized directly return the result desensitization rules configuration reference

Rule type	Regular expressions used for regular desensitization must be grouped, and hidden groups will be replaced by ****.	Groups that need to be hidden	Rule description
Phone number	(.{3})(.*)(.{4})	2	Keep the first three and the last four
ID number	(.*)(.{4})$	2	Hidden the last four
bank card	(.*)(.{4})$	2	Hidden the last four
Mail	(.)@(.)	2	Remove suffix

The reviewer cannot see the query permission to apply for the pending work order

The query permission application to-do list is hidden in the message icon in the upper right corner. The icon will be displayed when there is information to be reviewed. You can enter to view the to-do data.

Slow logs do not display

Check the configuration in the script, and keep the configuration content of the hostname and archer main library consistent, and are used for archer to filter.
Check whether the log information collected by the mysql_slow_query_review_history table is consistent with the hostname