k8s部署ArcherySQL审核平台

k8s部署ArcherySQL审核平台

Scroll Down

部署版本:Archery1.7.12
附:官方使用说明

创建namespace

[root@k8s01 archery]# kubectl create ns archery
namespace/archery created

创建mongo和mysql的secret

[root@master archery]# vim secrets.yaml
apiVersion: v1
kind: Secret
metadata:
  namespace: archery
  name: mysql-secret
data:
  mongodb-pass: "MTIzNDU2"
  mongodb-user: "cm9vdA=="  
  mysql-db: "YXJjaGVyeQ=="
  mysql-root-pass: "MTIzNDU2"

mongo, mysql和archery的数据持久化

[root@master archery]# vim pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: archery-pv-claim
  namespace: archery
spec:
  storageClassName: managed-nfs-storage
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 2Gi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mongodb-pv-claim
  namespace: archery
spec:
  storageClassName: managed-nfs-storage
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 5Gi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
  namespace: archery
spec:
  storageClassName: managed-nfs-storage
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 10Gi

创建建所需configmap

以文件创建configMap

[root@master archery]# kubectl create cm -n archery setting --from-file=settings.py
[root@master archery]# kubectl create cm -n archery inception-conf --from-file=inc.cnf
[root@master archery]# kubectl create cm -n archery soar --from-file=soar.yaml
[root@master archery]# kubectl create cm -n archery mysqld-config --from-file=my.cnf
[root@master archery]# cat inc.cnf
[inception]
general_log=1
general_log_file=inception.log
port=6669
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_language_code=zh-CN
inception_remote_system_password=123456
inception_remote_system_user=root
inception_remote_backup_port=3306
inception_remote_backup_host=mysql
inception_support_charset=utf8,utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_on=OFF
inception_osc_bin_dir=/usr/bin
inception_osc_min_table_size=10
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1

inception_enable_select_star=ON
inception_enable_identifer_keyword=ON
inception_enable_autoincrement_unsigned=ON
inception_check_identifier=OFF
[root@master archery]# cat my.cnf
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
lower_case_table_names=1
default-time_zone = '+8:00'

innodb_buffer_pool_size = 512M

server-id              = 100
log_bin                        = /var/log/mysql/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

[mysqldump]
quick
quote-names
max_allowed_packet      = 1024M


!includedir /etc/mysql/conf.d/
[root@master archery]# cat settings.py
# -*- 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': '123456',
        'HOST': 'mysql',
        '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://redis:6379/0",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": "123456"
        }
    },
    "dingding": {
        "BACKEND": "django_redis.cache.RedisCache",
        "LOCATION": "redis://redis:6379/1",
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
            "PASSWORD": "123456"
        }
    }
}

# LDAP
ENABLE_LDAP = False
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_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',
        },
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
            'formatter': 'verbose'
        }
    },
    'loggers': {
        'default': {  # default日志
            'handlers': ['console', 'default'],
            'level': 'DEBUG'
        },
        'django-q': {  # django_q模块相关日志
            'handlers': ['console', 'default'],
            'level': 'DEBUG',
            'propagate': False
        },
        'django_auth_ldap': {  # django_auth_ldap模块相关日志
            'handlers': ['console', 'default'],
            'level': 'DEBUG',
            'propagate': False
        },
        # 'django.db': {  # 打印SQL语句,方便开发
        #     'handlers': ['console', 'default'],
        #     'level': 'DEBUG',
        #     'propagate': False
        # },
        'django.request': {  # 打印请求错误堆栈信息,方便开发
            'handlers': ['console', 'default'],
            'level': 'DEBUG',
            'propagate': False
        },
    }
}
[root@master archery]# cat 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

部署Mysql

[root@master archery]# vim deploy-mysql.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: archery
    tier: db
  name: mysql
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: archery
      tier: db
  template:
    metadata:
      labels:
        app: archery
        name: mysql
        tier: db
    spec:
      containers:
      - env:
        - name: MYSQL_DATABASE
          valueFrom:
            secretKeyRef:
              key: mysql-db
              name: mysql-secret
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mysql-root-pass
              name: mysql-secret
        image: mysql:5.7
        name: mysql
        ports:
        - containerPort: 3306
          name: mysql
          protocol: TCP
        volumeMounts:
        - mountPath: /var/lib/mysql
          name: mysqld-persistent-storage
        - mountPath: /etc/mysql/my.cnf
          name: mysqld-config
          readOnly: true
          subPath: my.cnf
      volumes:
      - name: mysqld-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pv-claim
      - name: mysqld-config
        configMap:
          name: mysqld-config
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: archery
    tier: db
  name: mysql
  namespace: archery
spec:
  ports:
  - name: mysql
    port: 3306
    nodePort: 30306
    protocol: TCP
    targetPort: 3306
  selector:
    name: mysql
  type: NodePort

部署Mongo

[root@master archery]# vim deploy-mongo.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: archery
    tier: db
  name: mongodb-server
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: archery
      tier: db
  template:
    metadata:
      labels:
        app: archery
        name: mongodb-server
        tier: db
    spec:
      containers:
      - env:
        - name: MONGO_INITDB_ROOT_USERNAME
          valueFrom:
            secretKeyRef:
              key: mongodb-user
              name: mysql-secret
        - name: MONGO_INITDB_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: mongodb-pass
              name: mysql-secret
        image: mongo:3.6
        name: mongodb
        ports:
        - containerPort: 27017
          name: mongodb
          protocol: TCP
        volumeMounts:
        - mountPath: /data/db
          name: mongodb-persistent-storage
      volumes:
      - name: mongodb-persistent-storage
        persistentVolumeClaim:
          claimName: mongodb-pv-claim

部署Redis

[root@master archery]# vim deploy-redis.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: redis
  name: redis
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: redis
  template:
    metadata:
      labels:
        app: redis
    spec:
      containers:
      - image: redis:5.0
        name: redis
        ports:
        - containerPort: 6379
---
apiVersion: v1
kind: Service
metadata:
  name: redis
  namespace: archery
  labels:
    app: redis
spec:
  ports:
    - port: 6379
  selector:
    app: redis
  type: ClusterIP

部署goinception

[root@master archery]# vim deploy-goinception.yaml
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    app: goinception
  name: goinception
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: goinception
  template:
    metadata:
      labels:
        app: goinception
    spec:
      containers:
      - image: hanchuanchuan/goinception
        imagePullPolicy: Always
        name: goinception
        ports:
        - containerPort: 4000
          name: http
          protocol: TCP
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: goinception
  name: goinception
  namespace: archery
spec:
  ports:
  - port: 4000
    protocol: TCP
    targetPort: 4000
  selector:
    app: goinception
  sessionAffinity: None
  type: ClusterIP

部署inception

[root@master archery]# vim deploy-inception.yaml
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    app: inception
  name: inception
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: inception
  template:
    metadata:
      labels:
        app: inception
    spec:
      containers:
      - image: hhyo/inception
        imagePullPolicy: Always
        name: inception
        ports:
        - containerPort: 6669
          name: http
          protocol: TCP
        volumeMounts:
        - mountPath: /etc/inc.cnf
          name: inception-conf
          readOnly: true
          subPath: inc.cnf
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      volumes:
      - configMap:
          name: inception-conf
        name: inception-conf
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: inception
  name: inception
  namespace: archery
spec:
  clusterIP: None
  ports:
  - port: 6669
    protocol: TCP
    targetPort: 6669
  selector:
    app: inception
  sessionAffinity: None
  type: ClusterIP     

部署archery

[root@master archery]# vim deploy-archery.yaml
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    app: archery
  name: archery
  namespace: archery
spec:
  replicas: 1
  selector:
    matchLabels:
      app: archery
  template:
    metadata:
      labels:
        app: archery
        name: archery
    spec:
      containers:
      - args:
        - -c
        - /opt/archery/src/docker/startup.sh
        - --log-level=debug
        command:
        - bash
        env:
        - name: NGINX_PORT
          value: "9123"
        image: hhyo/archery:1.7.12
        imagePullPolicy: IfNotPresent
        name: archery
        ports:
        - containerPort: 9123
          name: http
          protocol: TCP
        volumeMounts:
        - mountPath: /opt/archery/archery/settings.py
          name: setting
          subPath: settings.py
        - mountPath: /opt/archery/downloads
          name: archery-downloads
        - mountPath: /etc/soar.yaml
          name: soar
          readOnly: true
          subPath: soar.yaml
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      volumes:
      - configMap:
          defaultMode: 420
          name: setting
        name: setting
      - name: archery-downloads
        persistentVolumeClaim:
          claimName: archery-pv-claim
      - configMap:
          defaultMode: 420
          name: soar
        name: soar
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: archery
  name: archery
  namespace: archery
spec:
  ports:
  - name: web-http
    nodePort: 30103
    port: 9123
    protocol: TCP
    targetPort: 9123
  selector:
    name: archery
  sessionAffinity: None
  type: NodePort

创建账号

登录到archery容器

kubectl exec -it -n archery archery-58f6558fd9-9s49q bash
cd /opt/archery
source /opt/venv4archery/bin/activate
python3 manage.py makemigrations sql 
python3 manage.py migrate
#创建管理用户
python3 manage.py createsuperuser
# 日志查看和问题排查
kubectl logs -it -n archery archery-58f6558fd9-9s49q -f

配置调整

遇到的问题1:archery容器启动不起来,查看日志没有/log/archery.log文件或目录
在pvc:archer-pv-claim下面需要手动创建log/archery.log

本地库慢查日志

表初始化:/home/archery/Archery-1.6.0/src/init_sql/mysql_slow_query_review.sql
本地test和release环境的慢日志展示需要借助percona

yum install percona-toolkit-3.0.12-1.el6.x86_64.rpm

数据库打开慢查日志,设定慢查阈值1s
archery1.png
通过脚本获取慢查日志并push到archery展示

vim analysis_slow_query.sh
#!/bin/bash
DIR="$( cd "$( dirname "$0"  )" && pwd  )"
cd $DIR
 
#配置archery数据库的连接地址
monitor_db_host="192.168.200.203"
monitor_db_port=30306
monitor_db_user="root"
monitor_db_password="123456"
monitor_db_database="archery"
 
#实例慢日志位置
slowquery_file="/var/lib/mysql/db01-slow.log"
pt_query_digest="/usr/bin/pt-query-digest"
 
#实例连接信息
hostname="192.168.200.111:3306" # 和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

#crontab -e
0 11,23 * * * /home/analysis_slow_query.sh

阿里云数据库接入

阿里云的三个生产库创建账号(forarchery)并赋予所有权限
RDS设置白名单为本地IP,由于服务器出口目前是pppoe, IP不固定,暂时谢了当前IP段,后面考虑通过策略路由使192.168.200.x出口为专线,这样白名单里面的IP就可以写成专线IP
功能说明:调用阿里云SDK对RDS进行管理,支持管理慢日志、进程、表空间,其中进程和表空间需要管理权限的key
相关配置:
在其他配置管理-全部后台数据中,添加阿里云账号的accesskey信息、实例对应关系,即可使用rds管理
archery2.png