环境配置

  • 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

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

访问Archery

浏览器输入域名或者IP:30103,使用初始化时设置的账号密码登录

archery1.png

archery2.png

文章作者: 鲜花的主人
版权声明: 本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 爱吃可爱多
Kubernetes Kubernetes
喜欢就支持一下吧
打赏
微信 微信
支付宝 支付宝