k8s部署ArcherySQL审核平台
环境配置
- Archery版本：1.10.0
- Kubernetes版本：1.20.0
前置准备
创建namespace
[root@k8s01 archery]# kubectl create ns archery
namespace/archery created
创建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"
数据持久化
[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，注意: setting配置文件中：CSRF_TRUSTED_ORIGINS（访问域名，配置不当访问403） mysql redis 根据实际情况修改
[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 goinception-conf --from-file=config.toml
[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@k8s01 archery]# cat settings.py
# -*- coding: UTF-8 -*-
# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os
from typing import List
from datetime import timedelta
import environ
import requests
import logging
logging.basicConfig(
level=logging.INFO, format="%(asctime)s - %(name)s - %(levelname)s - %(message)s"
)
logger = logging.getLogger(__name__)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
environ.Env.read_env(os.path.join(BASE_DIR, ".env"))
env = environ.Env(
DEBUG=(bool, False),
ALLOWED_HOSTS=(list, ["*"]),
SECRET_KEY=(str, "hfusaf2m4ot#7)fkw#di2bu6(cv0@opwmafx5n#6=3d%x^hpl6"),
DATABASE_URL=(str, "mysql://root:@mysql:3306/archery"),
CACHE_URL=(str, "redis://redis:6379/0"),
# 系统外部认证目前支持LDAP、OIDC、DINGDING三种，认证方式只能启用其中一种，如果启用多个，实际生效的只有一个，优先级LDAP > DINGDING > OIDC
ENABLE_LDAP=(bool, False),
ENABLE_OIDC=(bool, False),
ENABLE_DINGDING=(
bool,
False,
), # 钉钉认证方式参考文档：https://open.dingtalk.com/document/orgapp/tutorial-obtaining-user-personal-information
AUTH_LDAP_ALWAYS_UPDATE_USER=(bool, True),
AUTH_LDAP_USER_ATTR_MAP=(
dict,
{"username": "cn", "display": "displayname", "email": "mail"},
),
Q_CLUISTER_SYNC=(bool, False), # qcluster 同步模式, debug 时可以调整为 True
# CSRF_TRUSTED_ORIGINS=subdomain.example.com,subdomain.example2.com subdomain.example.com
CSRF_TRUSTED_ORIGINS=(list, []),
ENABLED_ENGINES=(
list,
[
"mysql",
"clickhouse",
"goinception",
"mssql",
"redis",
"pqsql",
"oracle",
"mongo",
"phoenix",
"odps",
"cassandra",
],
),
)
# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = env("SECRET_KEY")
# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = env("DEBUG")
ALLOWED_HOSTS = env("ALLOWED_HOSTS")
# https://docs.djangoproject.com/en/4.0/ref/settings/#csrf-trusted-origins
#CSRF_TRUSTED_ORIGINS = env("CSRF_TRUSTED_ORIGINS")
CSRF_TRUSTED_ORIGINS=['http://10.48.0.215:30103']
# 解决nginx部署跳转404
USE_X_FORWARDED_HOST = True
# 请求限制
DATA_UPLOAD_MAX_MEMORY_SIZE = 15728640
AVAILABLE_ENGINES = {
"mysql": {"path": "sql.engines.mysql:MysqlEngine"},
"cassandra": {"path": "sql.engines.cassandra:CassandraEngine"},
"clickhouse": {"path": "sql.engines.clickhouse:ClickHouseEngine"},
"goinception": {"path": "sql.engines.goinception:GoInceptionEngine"},
"mssql": {"path": "sql.engines.mssql:MssqlEngine"},
"redis": {"path": "sql.engines.redis:RedisEngine"},
"pqsql": {"path": "sql.engines.pgsql:PgSQLEngine"},
"oracle": {"path": "sql.engines.oracle:OracleEngine"},
"mongo": {"path": "sql.engines.mongo:MongoEngine"},
"phoenix": {"path": "sql.engines.phoenix:PhoenixEngine"},
"odps": {"path": "sql.engines.odps:ODPSEngine"},
}
ENABLED_ENGINES = env("ENABLED_ENGINES")
# 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",
"rest_framework",
"django_filters",
"drf_spectacular",
)
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",
"django.middleware.gzip.GZipMiddleware",
"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": {
**env.db(),
**{
"DEFAULT_CHARSET": "utf8mb4",
"CONN_MAX_AGE": 50,
'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": env("Q_CLUISTER_WORKERS", default=4),
"recycle": 500,
"timeout": env("Q_CLUISTER_TIMEOUT", default=60),
"compress": True,
"cpu_affinity": 1,
"save_limit": 0,
"queue_limit": 50,
"label": "Django Q",
"django_redis": "default",
"sync": env("Q_CLUISTER_SYNC"), # 本地调试可以修改为True，使用同步模式
}
# 缓存配置
CACHES = {
"default": {
**env.cache(),
**{
"BACKEND": "django_redis.cache.RedisCache",
"LOCATION": "redis://redis:6379/0",
"PASSWORD": "super" #如若未设置密码删除本行
"OPTIONS": {
"CLIENT_CLASS": "django_redis.client.DefaultClient",
},
},
}
}
# https://docs.djangoproject.com/en/3.2/ref/settings/#std-setting-DEFAULT_AUTO_FIELD
DEFAULT_AUTO_FIELD = "django.db.models.AutoField"
# API Framework
REST_FRAMEWORK = {
"DEFAULT_SCHEMA_CLASS": "drf_spectacular.openapi.AutoSchema",
"DEFAULT_RENDERER_CLASSES": ("rest_framework.renderers.JSONRenderer",),
# 鉴权
"DEFAULT_AUTHENTICATION_CLASSES": (
"rest_framework_simplejwt.authentication.JWTAuthentication",
"rest_framework.authentication.SessionAuthentication",
),
# 权限
"DEFAULT_PERMISSION_CLASSES": ("sql_api.permissions.IsInUserWhitelist",),
# 限速（anon：未认证用户 user：认证用户）
"DEFAULT_THROTTLE_CLASSES": (
"rest_framework.throttling.AnonRateThrottle",
"rest_framework.throttling.UserRateThrottle",
),
"DEFAULT_THROTTLE_RATES": {"anon": "120/min", "user": "600/min"},
# 过滤
"DEFAULT_FILTER_BACKENDS": ("django_filters.rest_framework.DjangoFilterBackend",),
# 分页
"DEFAULT_PAGINATION_CLASS": "rest_framework.pagination.PageNumberPagination",
"PAGE_SIZE": 5,
}
# Swagger UI
SPECTACULAR_SETTINGS = {
"TITLE": "Archery API",
"DESCRIPTION": "OpenAPI 3.0",
"VERSION": "1.0.0",
}
# API Authentication
SIMPLE_JWT = {
"ACCESS_TOKEN_LIFETIME": timedelta(hours=4),
"REFRESH_TOKEN_LIFETIME": timedelta(days=3),
"ALGORITHM": "HS256",
"SIGNING_KEY": SECRET_KEY,
"AUTH_HEADER_TYPES": ("Bearer",),
}
# OIDC
ENABLE_OIDC = env("ENABLE_OIDC", False)
if ENABLE_OIDC:
INSTALLED_APPS += ("mozilla_django_oidc",)
AUTHENTICATION_BACKENDS = (
"common.authenticate.oidc_auth.OIDCAuthenticationBackend",
"django.contrib.auth.backends.ModelBackend",
)
OIDC_RP_WELLKNOWN_URL = env(
"OIDC_RP_WELLKNOWN_URL"
) # 例如 https://keycloak.example.com/realms/<your realm>/.well-known/openid-configuration
OIDC_RP_CLIENT_ID = env("OIDC_RP_CLIENT_ID")
OIDC_RP_CLIENT_SECRET = env("OIDC_RP_CLIENT_SECRET")
response = requests.get(OIDC_RP_WELLKNOWN_URL)
response.raise_for_status()
config = response.json()
OIDC_OP_AUTHORIZATION_ENDPOINT = config["authorization_endpoint"]
OIDC_OP_TOKEN_ENDPOINT = config["token_endpoint"]
OIDC_OP_USER_ENDPOINT = config["userinfo_endpoint"]
OIDC_OP_JWKS_ENDPOINT = config["jwks_uri"]
OIDC_OP_LOGOUT_ENDPOINT = config["end_session_endpoint"]
OIDC_RP_SCOPES = env("OIDC_RP_SCOPES", default="openid profile email")
OIDC_RP_SIGN_ALGO = env("OIDC_RP_SIGN_ALGO", default="RS256")
LOGIN_REDIRECT_URL = "/"
# Dingding
ENABLE_DINGDING = env("ENABLE_DINGDING", False)
if ENABLE_DINGDING:
INSTALLED_APPS += ("django_auth_dingding",)
AUTHENTICATION_BACKENDS = (
"common.authenticate.dingding_auth.DingdingAuthenticationBackend",
"django.contrib.auth.backends.ModelBackend",
)
AUTH_DINGDING_AUTHENTICATION_CALLBACK_URL = env(
"AUTH_DINGDING_AUTHENTICATION_CALLBACK_URL"
)
AUTH_DINGDING_APP_KEY = env("AUTH_DINGDING_APP_KEY")
AUTH_DINGDING_APP_SECRET = env("AUTH_DINGDING_APP_SECRET")
# LDAP
ENABLE_LDAP = env("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 = env("AUTH_LDAP_SERVER_URI", default="ldap://xxx")
AUTH_LDAP_USER_DN_TEMPLATE = env("AUTH_LDAP_USER_DN_TEMPLATE", default=None)
if not AUTH_LDAP_USER_DN_TEMPLATE:
del AUTH_LDAP_USER_DN_TEMPLATE
AUTH_LDAP_BIND_DN = env(
"AUTH_LDAP_BIND_DN", default="cn=xxx,ou=xxx,dc=xxx,dc=xxx"
)
AUTH_LDAP_BIND_PASSWORD = env("AUTH_LDAP_BIND_PASSWORD", default="***********")
AUTH_LDAP_USER_SEARCH_BASE = env(
"AUTH_LDAP_USER_SEARCH_BASE", default="ou=xxx,dc=xxx,dc=xxx"
)
AUTH_LDAP_USER_SEARCH_FILTER = env(
"AUTH_LDAP_USER_SEARCH_FILTER", default="(cn=%(user)s)"
)
AUTH_LDAP_USER_SEARCH = LDAPSearch(
AUTH_LDAP_USER_SEARCH_BASE, ldap.SCOPE_SUBTREE, AUTH_LDAP_USER_SEARCH_FILTER
)
AUTH_LDAP_ALWAYS_UPDATE_USER = env(
"AUTH_LDAP_ALWAYS_UPDATE_USER", default=True
) # 每次登录从ldap同步用户信息
AUTH_LDAP_USER_ATTR_MAP = env("AUTH_LDAP_USER_ATTR_MAP")
SUPPORTED_AUTHENTICATION = [
("LDAP", ENABLE_LDAP),
("DINGDING", ENABLE_DINGDING),
("OIDC", ENABLE_OIDC),
]
# 计算当前启用的外部认证方式数量
ENABLE_AUTHENTICATION_COUNT = len(
[enabled for (name, enabled) in SUPPORTED_AUTHENTICATION if enabled]
)
if ENABLE_AUTHENTICATION_COUNT > 0:
if ENABLE_AUTHENTICATION_COUNT > 1:
logger.warning(
"系统外部认证目前支持LDAP、DINGDING、OIDC三种，认证方式只能启用其中一种，如果启用多个，实际生效的只有一个，优先级LDAP > DINGDING > OIDC"
)
authentication = "" # 默认为空
for name, enabled in SUPPORTED_AUTHENTICATION:
if enabled:
authentication = name
break
logger.info("当前生效的外部认证方式：" + authentication)
logger.info("认证后端：" + AUTHENTICATION_BACKENDS.__str__())
# 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",
},
"django-q": {
"level": "DEBUG",
"class": "logging.handlers.RotatingFileHandler",
"filename": "logs/qcluster.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": "WARNING",
},
"django-q": { # django_q模块相关日志
"handlers": ["console", "django-q"],
"level": "WARNING",
"propagate": False,
},
"django_auth_ldap": { # django_auth_ldap模块相关日志
"handlers": ["console", "default"],
"level": "WARNING",
"propagate": False,
},
"mozilla_django_oidc": {
"handlers": ["console", "default"],
"level": "WARNING",
"propagate": False,
},
# 'django.db': { # 打印SQL语句，方便开发
# 'handlers': ['console', 'default'],
# 'level': 'DEBUG',
# 'propagate': False
# },
# 'django.request': { # 打印请求错误堆栈信息，方便开发
# 'handlers': ['console', 'default'],
# 'level': 'DEBUG',
# 'propagate': False
# },
},
}
MEDIA_ROOT = os.path.join(BASE_DIR, "media")
if not os.path.exists(MEDIA_ROOT):
os.mkdir(MEDIA_ROOT)
PKEY_ROOT = os.path.join(MEDIA_ROOT, "keys")
if not os.path.exists(PKEY_ROOT):
os.mkdir(PKEY_ROOT)
try:
from local_settings import *
except ImportError:
print("import local settings failed, ignored")
[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
[root@k8s01 archery]# cat 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 =true
check_column_default_value =true
check_column_position_change =true
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 =true
check_timestamp_count =false
enable_autoincrement_unsigned =true
enable_blob_type =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 =false
enable_not_innodb =false
enable_nullable =false
enable_null_index_name =false
enable_orderby_rand =true
enable_partition_table =true
enable_pk_columns_only_int =true
enable_select_star =false
enable_set_charset =true
enable_set_collation =false
enable_set_engine =false
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 =true
support_charset ="utf8,utf8mb4"
support_collation ="utf8_general_ci,utf8mb4_general_ci"
backup_host = "mysql"
backup_port = 3306
backup_user = "root"
backup_password = "123456"
# 设置执行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]
er_cant_set_engine = 2
er_cant_set_collation = 2
er_table_must_have_comment = 2
er_column_have_no_comment = 2
er_table_must_have_pk = 2
er_index_name_idx_prefix = 1
er_index_name_uniq_prefix = 1
er_autoinc_unsigned = 2
er_alter_table_once = 2
er_pk_too_many_parts = 2
[osc]
osc_on = false
osc_min_table_size = 16
osc_print_none = false
osc_bin_dir = "/usr/local/bin"
[ghost]
ghost_on = false
ghost_allow_on_master = true
ghost_assume_rbr = true
ghost_chunk_size = 1000
ghost_concurrent_rowcount = true
ghost_cut_over = "atomic"
ghost_cut_over_lock_timeout_seconds = 3
ghost_default_retries = 60
ghost_heartbeat_interval_millis = 500
ghost_max_lag_millis = 1500
ghost_approve_renamed_columns = true
ghost_exponential_backoff_max_interval = 64
ghost_dml_batch_size = 10
[log]
# Log level: debug, info, warn, error, fatal.
level = "error"
# Log format, one of json, text, console.
format = "console"
部署服务
部署Mysql
[root@k8s01 archery]# vim 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@k8s01 archery]# vim 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@k8s01 archery]# vim 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@k8s01 archery]# vim goinception.yaml
apiVersion: apps/v1
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
volumeMounts:
- mountPath: /etc/config.toml
name: goinception-conf
readOnly: true
subPath: config.toml
volumes:
- configMap:
name: goinception-conf
name: goinception-conf
---
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@k8s01 archery]# vim inception.yaml
apiVersion: apps/v1
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@k8s01 archery]# vim archery.yaml
apiVersion: apps/v1
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:v1.10.0
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
创建账号
登录到archery容器
[root@k8s01 archery]# kubectl exec -it -n archery archery-cb77bcdc7-c868k bash
[root@archery-cb77bcdc7-c868k archery]# cd /opt/archery
[root@archery-cb77bcdc7-c868k archery]# source /opt/venv4archery/bin/activate
# 表结构初始化
(venv4archery) [root@archery-cb77bcdc7-c868k archery]# python3 manage.py makemigrations sql
(venv4archery) [root@archery-cb77bcdc7-c868k archery]# python3 manage.py migrate
# 数据初始化
(venv4archery) [root@archery-cb77bcdc7-c868k archery]# python3 manage.py dbshell < sql/fixtures/auth_group.sql
(venv4archery) [root@archery-cb77bcdc7-c868k archery]# python3 manage.py dbshell < src/init_sql/mysql_slow_query_review.sql
# 创建管理用户
(venv4archery) [root@archery-cb77bcdc7-c868k archery]# python3 manage.py createsuperuser
import local settings failed, ignored
用户名: admin
电子邮件地址: archery@admin.com
Password:
Password (again):
Superuser created successfully.
日志查看和问题排查
[root@k8s01 archery]# kubectl logs -it -n archery archery-cb77bcdc7-c868k -f
配置调整
遇到的问题：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
通过脚本获取慢查日志并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管理
访问Archery
浏览器输入域名或者IP:30103，使用初始化时设置的账号密码登录
版权声明：
本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
爱吃可爱多！
喜欢就支持一下吧
打赏
微信
支付宝
微信
支付宝