archery搭建流程

 

先在本机安装MySQL,Redis。过程不再重复，参考安装标准。

在mysql中创建 root 用户和 repl 用户。

create user 'root'@'%' identified by 'xxxx';

grant all privileges on . to root;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '见密码文件';

 

安装mydumper gh-ost

 

 

1、安装docker

#参考官方文档
https://docs.docker.com/engine/install/ubuntu/
#执行如下命令
apt-get update

apt-get install \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg \
    lsb-release

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

echo \
  "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu \
  $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

apt-get update

apt-get install docker-ce docker-ce-cli containerd.io

apt install docker-compose

 

2、安装Archery 1.8.1

2.1 安装Archery 1.8.1

wget https://github.com/hhyo/Archery/archive/refs/tags/v1.8.1.tar.gz
tar xvf v1.8.1.tar.gz -C /data
cd /data
mv Archery-1.8.1/ Archery

 

2.2 修改docker-compose

(主要去掉了mysql redis goinception部分内容)

注意配置文件中的3306端口地址和6379端口地址，修改为实际mysql redis的地址

vi /data/Archery/src/docker-compose/docker-compose.yml

----------------------------------------------------------
version: '3'

services:
  inception:
    image: hhyo/inception
    container_name: inception
    network_mode: host
    restart: always
    expose:
      - "6669"
    volumes:
      - "./inception/inc.cnf:/etc/inc.cnf"

  archery:
    image: hhyo/archery:1.8.1
    container_name: archery
    network_mode: host
    restart: always
    expose:
      - "9123"
    #ports:
      #- "9123:9123"
    volumes:
      - "./archery/settings.py:/opt/archery/archery/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"
    entrypoint: "dockerize -wait tcp://10.16.4.34:3306 -wait tcp://10.16.4.34:6379 -timeout 60s /opt/archery/src/docker/startup.sh"
    environment:
      NGINX_PORT: 9123
--------------------------------------------------------

 

2.3 安装goinception

参考：https://github.com/hanchuanchuan/goInception

apt install golang-go
cd /data
git clone https://github.com/hanchuanchuan/goInception.git
cd goInception
go build -o goInception tidb-server/main.go

如果报错：【SECURITY ERROR
This download does NOT match an earlier download recorded in go.sum.】

#执行这两个命令
go clean -modcache
rm go.sum
再继续
go build -o goInception tidb-server/main.go

 

再编辑配置文件

注意：修改配置文件中的 backup_host 这些数据库地址连接信息，否则无法备份数据。

vi /data/goInception/config/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          =false
check_column_default_value       =false
check_column_position_change      =false
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         =false
check_timestamp_count          =false
enable_autoincrement_unsigned      =false
enable_blob_type            =true
enable_blob_not_null          =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        =true
enable_not_innodb            =false
enable_nullable             =true
enable_null_index_name         =false
enable_orderby_rand           =true
enable_partition_table         =true
enable_pk_columns_only_int       =false
enable_select_star           =false
enable_set_charset           =true
enable_set_collation          =false
enable_set_engine            =true
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           =false
support_charset             ="utf8,utf8mb4"
support_collation            ="utf8_general_ci,utf8mb4_general_ci"
uniq_index_prefix            ="uni_"


backup_host = "10.16.4.34"
backup_port = 3306
backup_user = "root"
backup_password = "见密码文件"


# 设置执行SQL时，会话变量
# 0 表示不做操作，基于远端数据库【默认值】
# > 0 值表示，会话在执行SQL 时获取锁超时的时间
lock_wait_timeout = 10

# 安全更新是否开启.
# -1 表示不做操作,基于远端数据库 [默认值]
# 0 表示关闭安全更新
# 1 表示开启安全更新
sql_safe_updates = -1

# lang = "en-US"
lang = "zh-CN"

# 是否记录全量日志
general_log = false

# 开启统计功能
enable_sql_statistic = true


[inc_level]
et_engine = 1
er_cant_set_collation = 1
er_table_must_have_comment = 1
er_column_have_no_comment = 1
er_table_must_have_pk = 1
er_index_name_idx_prefix = 1
er_index_name_uniq_prefix = 1
er_autoinc_unsigned = 1
er_alter_table_once = 1
er_pk_too_many_parts = 1

[osc]
osc_on = false
osc_min_table_size = 1024
osc_print_none = false
osc_bin_dir = "/usr/local/bin"
ignore_osc_alter_stmt = "drop index"

[ghost]
ghost_on = true
ghost_allow_on_master = true
ghost_assume_rbr = true
ghost_chunk_size = 50000
ghost_concurrent_rowcount = true
ghost_cut_over = "atomic"
ghost_cut_over_lock_timeout_seconds = 9
ghost_default_retries = 600
ghost_heartbeat_interval_millis = 1500
ghost_max_lag_millis = 5000
ghost_approve_renamed_columns = true
ghost_exponential_backoff_max_interval = 64
ghost_dml_batch_size = 10
ghost_aliyun_rds = true
ghost_allow_master_master = true
ghost_ok_to_drop_table = true
ghost_initially_drop_ghost_table = true
ghost_initially_drop_old_table = true
ghost_skip_foreign_key_checks = false
#osc_critical_thread_connected = 800
#osc_critical_thread_running = 80
#osc_max_thread_connected = 500
#osc_max_thread_running = 50
osc_min_table_size = 0
osc_print_none = false

[log]
# Log level: debug, info, warn, error, fatal.
level = "error"
# Log format, one of json, text, console.
format = "console"

 

启动goInception

#后台启动
nohup ./goInception -config=config/config.toml &  

 

2.4修改 settings.py 文件

 

vi /data/Archery/src/docker-compose/archery/settings.py

注意DATABASES、 CACHES 部分内容，修改数据库连接地址。

LDAP 修改为实际的地址

# -*- 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 = False

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',
    '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': 'root',
        'PASSWORD': '见密码文件',
        'HOST': '10.16.11.61',
        '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://10.16.11.61:6379/0",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": "见密码文件"
        }
    },
    "dingding": {
        "BACKEND": "django_redis.cache.RedisCache",
        "LOCATION": "redis://10.16.11.61:6379/1",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": "见密码文件"
        }
    }
}

# LDAP
ENABLE_LDAP = True
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_SERVER_URI = "ldap://172.30.1.10:389"
    AUTH_LDAP_BIND_DN = "uid=anonymous,dc=baoban"
    AUTH_LDAP_BIND_PASSWORD = "anonymous"
    AUTH_LDAP_USER_SEARCH = LDAPSearch('ou=people,dc=baoban',ldap.SCOPE_SUBTREE, '(cn=%(user)s)',)
    #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': 'logs/archery.log',
            'maxBytes': 1024 * 1024 * 100,  # 5 MB
            'backupCount': 5,
            'formatter': 'verbose',
        },
        'ldap_logs': {
            'level': 'DEBUG',
            'class': 'logging.handlers.RotatingFileHandler',
            'filename': 'logs/ldap.log',
            'maxBytes': 1024 * 1024 * 100,  # 5 MB
            'backupCount': 5,
            'formatter': 'verbose',
        },
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'verbose'
        }
    },
    'loggers': {
        'default': {  # default日志
            'handlers': ['console', 'ldap_logs'],
            'level': 'DEBUG'
        },
        'django-q': {  # django_q模块相关日志
            'handlers': ['console', 'default'],
            'level': 'WARNING',
            'propagate': False
        },
        'django_auth_ldap': {  # django_auth_ldap模块相关日志
            'handlers': ['console', 'ldap_logs'],
            'level': 'DEBUG',
            'formatter': 'verbose'
        },
        # 'django.db': {  # 打印SQL语句，方便开发
        #     'handlers': ['console', 'default'],
        #     'level': 'DEBUG',
        #     'propagate': False
        # },
        # 'django.request': {  # 打印请求错误堆栈信息，方便开发
        #     'handlers': ['console', 'default'],
        #     'level': 'DEBUG',
        #     'propagate': False
        # },
    }
}

 

2.5 初始化arhcyer

登录本地数据库，创建一个库 create database archery;

要不然进入docker后执行命令会报错

cd /data/Archery/src/docker-compose
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

# 创建管理用户，这里会提示输入用户名和密码，此用户名和密码为archery系统的管理员用户
python3 manage.py createsuperuser


# 重启
退重docker返回操作系统，
docker restart archery

 

2.6 配置archery 界面

http://10.16.11.61:9123/sqlworkflow/ 
进入archery系统，配置goinception
#我的goinception装在本机了，所以指向127.0.0.1
GO_INCEPTION_HOST=127.0.0.1
GO_INCEPTION_PORT=4000
BACKUP_HOST=127.0.0.1
BACKUP_PORT=3306
BACKUP_USER=root
BACKUP_PASSWORD=mima

MAX_EXECUTION_TIME：10
ADMIN_QUERY_LIMIT：10000000


SQLADVISOR_PATH：/opt/archery/src/plugins/sqladvisor
SOAR_PATH：/opt/archery/src/plugins/soar
SOAR_TEST_DSN：archery:xxxxxx@10.16.4.34:3306/archery


#通知时候提示的链接
ARCHERY_BASE_URL:https://archery.advai.net/


企业微信群机器人:ON
飞书Webhook:ON

BINLOG2SQL:/opt/archery/src/plugins/binlog2sql/binlog2sql.py
DEFAULT_AUTH_GROUP：Default