Archery Kubernetes 部署

Archery是一个开源的SQL审核和查询平台，旨在提升数据库管理的效率和安全性。它支持多种数据库（如MySQL、Oracle、PostgreSQL等），提供SQL审核、查询管理、权限控制、工单系统、性能监控以及报表统计等功能。通过自动化审核和规范化的操作流程，Archery帮助团队减少人为错误，优化数据库性能，同时确保数据操作的可追溯性和安全性，是数据库管理员和开发人员的得力工具。

功能清单

数据库 查询 审核 执行 备份 数据字典 慢日志 会话管理 账号管理 参数管理 数据归档
MySQL √ √ √ √ √ √ √ √ √ √
MsSQL √ × √ × √ × × × × ×
Redis √ × √ × × × × × × ×
PgSQL √ × √ × × × × × × ×
Oracle √ √ √ √ √ × √ × × ×
MongoDB √ √ √ × × × √ √ × ×
Phoenix √ × √ × × × × × × ×
ODPS √ × × × × × × × × ×
ClickHouse √ √ √ × × × × × × ×
Cassandra √ × √ × × × × × × ×
Doris √ × √ × × × × × × ×

K8S部署

使用helm部署

1
helm upgrade --install archery douban/archery -f values.yaml --namespace archery

values.yaml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
# values.yaml values for archery.
# This is a YAML-formatted file.
# Declare variables to be passed into your templates.

replicaCount: 1

image:
  repository: hhyo/archery
  tag: v1.11.3
  pullPolicy: IfNotPresent

nameOverride: ""
fullnameOverride: ""

service:
  type: LoadBalancer
  port: 80
  targetPort: 9123

deployment:
  labels: {}
  annotations: {}

ingress:
  enabled: false
  className: "nginx"
  annotations: {}
    # kubernetes.io/ingress.class: nginx
    # kubernetes.io/tls-acme: "true"
  paths:
    - /
  servicePort: 9123

  hosts:
    - dba.archery.local
  tls: []
  #  - secretName: chart-example-tls
  #    hosts:
  #      - chart-example.local


# subcharts redis
redis:
  embedded: false
  url: "rediss://*******:*******@*******:6379"
  # urlSecret: ""
  # urlSecretKey: "CACHE_URL"
  # dingdingUrl: "redis://<authpwd>@<server_addr>:6379/1"
  # dingdingUrlSecret: ""
  # dingdingUrlSecretKey: "DINGDING_CACHE_URL"
  # embedded 为 false, 会使用外部的 redis, 下方的 redis 就不会生效
  # architecture: standalone
  # auth:
  #   password: "REDIS_PASSWORD"
  # master:
  #   persistence:
  #     enabled: false
  #     size: 8Gi
  #     storageClass: ""

# subcharts mysql
mysql:
  embedded: false
  url: "mysql://*******:*******@*******:3306/archery"
  # urlSecret: ""
  # urlSecretKey: "DATABASE_URL"
  # embedded 为 false时, 会使用外部的 mysql, 下方的 mysql 配置不会生效
  # architecture: standalone
  # auth:
  #   database: archery
  #   rootPassword: "MYSQL_ROOT_PASSWORD"
  primary:
    configuration: |-
      [mysqld]
      default_authentication_plugin=mysql_native_password
      skip-name-resolve
      explicit_defaults_for_timestamp
      basedir=/opt/bitnami/mysql
      plugin_dir=/opt/bitnami/mysql/lib/plugin
      socket=/opt/bitnami/mysql/tmp/mysql.sock
      datadir=/bitnami/mysql/data
      tmpdir=/opt/bitnami/mysql/tmp
      bind-address=0.0.0.0
      pid-file=/opt/bitnami/mysql/tmp/mysqld.pid
      log-error=/opt/bitnami/mysql/logs/mysqld.log
      port            = 3306
      skip-external-locking
      lower_case_table_names=1
      default-time_zone = '+8:00'
      innodb_buffer_pool_size = 512M
      server-id              = 100
      log_bin                        = mysql-bin.log
      expire_logs_days        = 1
      max_binlog_size         = 500M
      character-set-server = utf8mb4
      collation-server = utf8mb4_general_ci
      slow_query_log_file = mysql-slow.log
      slow_query_log      = 1
      long_query_time = 1
      [client]
      default-character-set=utf8mb4
      socket=/opt/bitnami/mysql/tmp/mysql.sock
      plugin_dir=/opt/bitnami/mysql/lib/plugin
      [mysqldump]
      quick
      quote-names
      max_allowed_packet      = 1024M
      [manager]
      port=3306
      socket=/opt/bitnami/mysql/tmp/mysql.sock
      pid-file=/opt/bitnami/mysql/tmp/mysqld.pid

    persistence:
      enabled: true
      accessModes:
        - ReadWriteOnce
      size: 200Gi
      ## Persistent Volume Storage Class
      ## If defined, storageClassName: <storageClass>
      ## If set to "-", storageClassName: "", which disables dynamic provisioning
      ## If undefined (the default) or set to null, no storageClassName spec is
      ##   set, choosing the default provisioner.  (gp2 on AWS, standard on
      ##   GKE, AWS & OpenStack)
      ##
      # storageClass: "-"

      # storageClass: ""

goinception:
  embedded: true
  image:
    repository: hanchuanchuan/goinception
    tag: v1.3.0
    pullPolicy: IfNotPresent

  nameOverride: ""
  fullnameOverride: ""

  service:
    type: ClusterIP
    port: 4000
    targetPort: 4000

  initEnv:
    - name: BACKUP_HOST
      value: "*******"
    - name: BACKUP_PORT
      value: "3306"
      # mysql superuser
    - name: BACKUP_USER
      value: "admin"
    - name: BACKUP_PASSWORD
      value: "*******"
  # you can override more options , please see goinception chart readme and values
  # https://github.com/douban/charts/blob/master/charts/goinception/values.yaml

resources: {}
  # We usually recommend not to specify default resources and to leave this as a conscious
  # choice for the user. This also increases chances charts run on environments with little
  # resources, such as Minikube. If you do want to specify resources, uncomment the following
  # lines, adjust them as necessary, and remove the curly braces after 'resources:'.
  # limits:
  #  cpu: 100m
  #  memory: 128Mi
  # requests:
  #  cpu: 100m
  #  memory: 128Mi
envs: {}
volumeMounts:
  # 请慎重更改以下值, 如需更改建议复制粘贴然后按需求增加键值对
  # 更改后可能会造成部分其他配置失效
  - name: archery-settings
    subPath: local_settings.py
    mountPath: /opt/archery/local_settings.py
  - name: archery-settings
    subPath: soar.yaml
    mountPath: /etc/soar.yaml
  - name: archery-settings
    subPath: analysis_slow_query.sh
    mountPath: /opt/archery/src/script/analysis_slow_query.sh
  - name: archery-settings
    subPath: startup.sh
    mountPath: /opt/archery/src/docker/startup.sh
  - name: archery-settings
    subPath: init-archery.sh
    mountPath: /opt/archery/src/docker/init-archery.sh
  - name: archery-settings
    subPath: createsuperuser.py
    mountPath: /opt/archery/src/docker/createsuperuser.py
nodeSelector: {}

tolerations: []

affinity: {}

volumes:
  # 请慎重更改以下值, 如需更改建议复制粘贴然后按需求增加键值对
  # 更改后可能会造成部分其他配置失效
  - name: archery-settings
    configMap:
      name: archery-config
      defaultMode: 420
  - name: archery-download
    hostPath:
      path: /data/archery/downloads
  - name: archery-script
    hostPath:
      path: /data/archery/scripts

configMap:
  enabled: true
  # admin password
  superuser:
    username: admin
    password: password  # 请尽快修改
    email: "archery@example.com"
  data:
    local_settings.py: |-
        # -*- coding: UTF-8 -*-
        # override your configs here
        DEBUG = True
        # django跨域配置，登录账号报错Forbidden时要配置这个
        CSRF_TRUSTED_ORIGINS = ["https://*.xxx.xx:443"]
        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://xxxx:389'
            AUTH_LDAP_BIND_DN = 'cn=xxx,cn=Users,dc=xxx,dc=com,dc=cn'
            AUTH_LDAP_BIND_PASSWORD = 'xxx'
            AUTH_LDAP_USER_SEARCH = LDAPSearch('ou=xxx,dc=xxxx,dc=com,dc=cn',ldap.SCOPE_SUBTREE, '(cn=%(user)s)')
            AUTH_LDAP_ALWAYS_UPDATE_USER = True
            AUTH_LDAP_USER_ATTR_MAP = {
                'username': 'cn',
                'display': 'sn',
                'email': 'mail',
            }
    soar.yaml: |-
        # 是否允许测试环境与线上环境配置相同
        allow-online-as-test: false
        # 是否清理测试时产生的临时文件
        drop-test-temporary: true
        # 语法检查小工具
        only-syntax-check: false
        sampling-data-factor: 100
        sampling: false
        sampling-statistic-target: 100
        profiling: false
        trace: false
        # 日志级别，[0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
        log-level: 3
        log-output: /opt/archery/logs/soar.log
        # 优化建议输出格式
        report-type: markdown
        ignore-rules:
        - ""
        # 启发式算法相关配置
        max-join-table-count: 5
        max-group-by-cols-count: 5
        max-distinct-count: 5
        max-index-cols-count: 5
        max-total-rows: 9999999
        spaghetti-query-length: 2048
        allow-drop-index: false
        # EXPLAIN相关配置
        explain-sql-report-type: pretty
        explain-type: extended
        explain-format: traditional
        explain-warn-select-type:
        - ""
        explain-warn-access-type:
        - ALL
        explain-max-keys: 3
        explain-min-keys: 0
        explain-max-rows: 10000
        explain-warn-extra:
        - ""
        explain-max-filtered: 100
        explain-warn-scalability:
        - O(n)
        query: ""
        list-heuristic-rules: false
        list-test-sqls: false
        verbose: true
    analysis_slow_query.sh: |-
        #!/bin/bash
        DIR="$( cd "$( dirname "$0"  )" && pwd  )"
        cd $DIR
        #配置archery数据库的连接地址
        monitor_db_host="archery-mysql"
        monitor_db_port=3306
        monitor_db_user="root"
        monitor_db_password="MYSQL_ROOT_PASSWORD"
        monitor_db_database="archery"
        #实例慢日志位置
        slowquery_file="/home/mysql/log_slow.log"
        pt_query_digest="/usr/bin/pt-query-digest"
        #实例连接信息
        hostname="mysql_host:mysql_port" # 和archery实例配置内容保持一致，用于archery做筛选
        #获取上次分析时间，初始化时请删除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