SQL審核平臺Archery1.10部署
原創作者：吳守陽
Archery是用于輔助MySQL數據庫管理和開發的Web工具，適合用于需要管理和開發MySQL數據庫的場景，使數據庫工作更加高效和便捷。
簡介
Archery是用于輔助MySQL數據庫管理和開發的Web工具，適合用于需要管理和開發MySQL數據庫的場景，使數據庫工作更加高效和便捷。
特點：SQL審核、SQL上線、SQL分析、SQL查詢、SQL優化、數據歸檔、實例管理、工具插件、權限管理。
功能清單
安裝前準備
安裝python3.9.10：
yum install libffi-devel wget gcc make zlib-devel openssl openssl-devel ncurses-devel openldap-devel gettext bzip2-devel xz-devel
wget "https://www.python.org/ftp/python/3.9.10/Python-3.9.10.tar.xz"
tar -xvJf Python-3.9.10.tar.xz
# 編譯
cd Python-3.9.10
./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
##安裝一些依賴庫
pip3 install Django
pip3 install django-environ
pip3 install requests升級openssl1.1.1：
wget --no-check-certificate https://www.openssl.org/source/openssl-1.1.1t.tar.gz
tar -zxvf openssl-1.1.1t.tar.gz
cd openssl-1.1.1t/
./config --prefix=/usr/local/my_openssl
make
make install
mv /usr/bin/openssl /usr/bin/oldopenssl
ln -s /usr/local/my_openssl/bin/openssl /usr/bin/openssl
ln -s /usr/local/my_openssl/lib/libssl.so.1.1 /usr/lib64/
ln -s /usr/local/my_openssl/lib/libcrypto.so.1.1 /usr/lib64/下載docker-compose：
curl -L "https://github.com/docker/compose/releases/download/1.24.1/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose下載安裝包
下載Archery相關文件：
wget https://github.com/hhyo/Archery/archive/refs/tags/v1.10.0.zip
unzip Archery-1.10.0.zip
mv /opt/Archery-1.10.0 /opt/archery修改如下配置文件
[root@test19-server08 docker-compose]# cd /opt/Archery-1.10.0/src/docker-compose
[root@test19-server08 docker-compose]# vim docker-compose.yml 修改了密碼和archery的版本
version: '3'
services:
redis:
image: redis:5
container_name: redis
restart: always
command: redis-server --requirepass abcabc
ports:
- "6379: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: abcabc
goinception:
image: hanchuanchuan/goinception
container_name: goinception
restart: always
ports:
- "4000:4000"
volumes:
- "./inception/config.toml:/etc/config.toml"
archery:
image: hhyo/archery:v1.10.0
container_name: archery
restart: always
ports:
- "9123:9123"
volumes:
- "./archery/settings.py:/opt/archery/local_settings.py"
- "./archery/soar.yaml:/etc/soar.yaml"
- "./archery/docs.md:/opt/archery/docs/docs.md"
- "./archery/downloads:/opt/archery/downloads"
- "./archery/sql/migrations:/opt/archery/sql/migrations"
- "./archery/logs:/opt/archery/logs"
- "./archery/keys:/opt/archery/keys"
entrypoint: "dockerize -wait tcp://mysql:3306 -wait tcp://redis:6379 -timeout 60s /opt/archery/src/docker/startup.sh"
env_file:
- .env[root@localhost archery]# cp /opt/archery/.env.list .env
[root@test19-server08 docker-compose]# vim .env
NGINX_PORT=9123
# https://django-environ.readthedocs.io/en/latest/quickstart.html#usage
# https://docs.djangoproject.com/zh-hans/4.1/ref/settings/
DEBUG=false
DATABASE_URL=mysql://root:abcabc@mysql:3306/archery
CACHE_URL=redis://redis:6379/0?PASSWORD=abcabc
# https://docs.djangoproject.com/en/4.0/ref/settings/#csrf-trusted-origins
CSRF_TRUSTED_ORIGINS=http://127.0.0.1:9123
# https://django-auth-ldap.readthedocs.io/en/latest/
ENABLE_LDAP=false
AUTH_LDAP_ALWAYS_UPDATE_USER=true
AUTH_LDAP_USER_ATTR_MAP=username=cn,display=displayname,email=email
# https://django-q.readthedocs.io/en/latest/configure.html#
Q_CLUISTER_WORKERS=4
Q_CLUISTER_TIMEOUT=60
Q_CLUISTER_SYNC=false
修改好的文件拷貝到另一個目錄
[root@localhost archery]# cp /opt/archery/.env /opt/archery/src/docker-compose/[root@test19-server08 inception]# vim config.toml
backup_host = "127.0.0.1"
backup_port = 3306
backup_user = "archery_bak"
backup_password = "abcabc"
[osc]
osc_print_none = false
osc_print_sql = false
osc_on = true
osc_min_table_size = 300
osc_alter_foreign_keys_method = "none"
osc_recursion_method = "processlist"
osc_max_lag = 600
osc_max_flow_ctl = -1
osc_sleep = 0.1
osc_lock_wait_timeout = 60
osc_check_alter = true
osc_check_replication_filters = true
osc_check_unique_key_change = true
osc_drop_old_table = true
osc_drop_new_table = true
osc_max_thread_running = 80
osc_max_thread_connected = 20000
osc_critical_thread_running = 80
osc_critical_thread_connected = 20000
osc_chunk_time = 1.0
osc_chunk_size_limit = 4
osc_chunk_size = 1000
osc_check_interval = 5
osc_bin_dir = "/usr/local/bin"[root@localhost ~]# vim /opt/archery/src/docker-compose/mysql/my.cnf
[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
default-time_zone = '+8:00'
log_bin = /var/lib/mysql/mysql-bin
server_id = 2
binlog_format = row
symbolic-links=0
innodb_buffer_pool_size = 512M
max_connections = 2000
innodb_file_per_table = 1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
default-character-set=utf8mb4
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/初始化配置
[root@centos7Demo docker-compose]# cd /opt/archery/src/docker-compose
#啟動
docker-compose -f docker-compose.yml up -d
#表結構初始化
[root@centos7Demo docker-compose]#docker exec -ti archery /bin/bash
[root@19d718745ad9 archery]#cd /opt/archery
[root@19d718745ad9 archery]#source /opt/venv4archery/bin/activate
[root@19d718745ad9 archery]#python3 manage.py makemigrations sql
[root@19d718745ad9 archery]#python3 manage.py migrate
#數據初始化
python3 manage.py dbshell<sql fixtures="" auth_group.sql="" python3="" manage.py="" dbshellinception show variables;[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
13a38e2c45fa redis:5 "docker-entrypoint.s…" 4 hours ago Up 4 hours 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp redis
409d193e601d hhyo/archery:v1.10.0 "dockerize -wait tcp…" 4 hours ago Up 3 hours 0.0.0.0:9123->9123/tcp, :::9123->9123/tcp archery
105ff841b097 mysql:5.7 "docker-entrypoint.s…" 4 hours ago Up 4 hours 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql
fddcd111f063 hanchuanchuan/goinception "/usr/local/bin/dumb…" 4 hours ago Up 4 hours 0.0.0.0:4000->4000/tcp, :::4000->4000/tcpArchery訪問
此文章主要是針對新版本部署，具體軟件使用后續會發布新的文章。
注意事項
Python需要Python 3.8以上。
OPenssl需要Openssl 1.1.1以上。
需要使用pip3安裝相關依賴庫Django、django-environ、requests。
作者介紹
吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。
責任編輯：姜華
來源：
DBA實戰
