K3S搭建基于MySQL的archery开源工具自动化工单系统

【环境介绍】

系统环境：CentOS Linux release 7.3 + Docker version 20.10.2 + k3s version v1.20.4+k3s1 + archery v1.7.13

【背景】

需要自动化管理MySQL数据库及其他数据库，如果只是MySQL数据库，则使用yearning，这里涉及到Oracle跟PG等数据库，则使用archery工具

https://github.com/cookieY/Yearning

https://github.com/hhyo/Archery

这里使用K3S安装archery，docker安装比较简单，直接看文档项目文档即可，建议先了解archery功能及前提条件后再安装

【安装操作】

前提：创建好PV,命名空间

创建MySQL

配置文件目录

mkdir -p /mnt/v1/archery/mysql

数据文件目录

mkdir -p /mnt/v1/archery/mysql-data

cd /mnt/v1/archery/mysql

创建yml文件

cat >mysql.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: archery-mysql
namespace: archery
labels:
app: archery-mysql
spec:
selector:
matchLabels:
app: archery-mysql
template:
metadata:
labels:
app: archery-mysql
spec:
containers:
- name: archery-mysql
image: mysql:5.7
imagePullPolicy: IfNotPresent
env:
- name: MYSQL_DATABASE
value: archery
- name: MYSQL_ROOT_PASSWORD
value: "123456"
volumeMounts:
- mountPath: /var/lib/mysql
name: data
- mountPath: /etc/mysql/my.cnf
name: config
#subPath: my.cnf
resources:
requests:
memory: "512Mi"
cpu: "200m"
limits:
memory: "2048Mi"
cpu: "1000m"
volumes:
- name: data
hostPath:
path: /mnt/v1/archery/mysql-data
type: DirectoryOrCreate
- name: config
#configMap:
# name: archery-mysql
hostPath:
path: /mnt/v1/archery/mysql/my.cnf

---

apiVersion: v1
kind: Service
metadata:
name: archery-mysql
namespace: archery
labels:
app: archery-mysql
spec:
type: NodePort
ports:
- port: 3306
nodePort: 32312
selector:
app: archery-mysql

创建MySQL配置文件

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/

直接运行创建：kubectl apply -f mysql.yml 没有报错即可

创建redis

创建yml文件

cat >redis.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: archery-redis
namespace: archery
labels:
app: archery-redis
spec:
selector:
matchLabels:
app: archery-redis
template:
metadata:
labels:
app: archery-redis
spec:
containers:
- name: archery-redis
image: redis:5
imagePullPolicy: IfNotPresent
command:
- sh
- -c
- redis-server --requirepass 123456
resources:
requests:
memory: "128Mi"
cpu: "50m"
limits:
memory: "2048Mi"
cpu: "1000m"

---

apiVersion: v1
kind: Service
metadata:
name: archery-redis
namespace: archery
labels:
app: archery-redis
spec:
selector:
app: archery-redis
ports:
- port: 6379

直接运行创建：kubectl apply -f redis.yml 没有报错即可

创建inception

创建配置文件目录 mkdir -p /mnt/v1/archery/inception

cd /mnt/v1/archery/inception

添加配置文件

cat /mnt/v1/archery/inception/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=archery-mysql.archery
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

创建yml文件

cat >inception.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: archery-inception
namespace: archery
labels:
app: archery-inception
spec:
selector:
matchLabels:
app: archery-inception
template:
metadata:
labels:
app: archery-inception
spec:
containers:
- name: archery-inception
image: hhyo/inception
imagePullPolicy: IfNotPresent
volumeMounts:
- mountPath: /etc/inc.cnf
name: config
#subPath: inc.cnf
resources:
requests:
memory: "128Mi"
cpu: "10m"
limits:
memory: "2048Mi"
cpu: "1000m"
volumes:
- name: config
#configMap:
# name: archery-inception
hostPath:
path: /mnt/v1/archery/inception/inc.cnf

---

apiVersion: v1
kind: Service
metadata:
name: archery-inception
namespace: archery
labels:
app: archery-inception
spec:
ports:
- port: 6669
selector:
app: archery-inception

直接运行创建：kubectl apply -f inception.yml没有报错即可

创建goinception

创建配置文件

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 = "archery-mysql.archery"
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 = true
osc_min_table_size = 16
osc_print_none = false
osc_bin_dir = "/usr/local/bin"
osc_max_thread_connected = 3000
osc_critical_thread_connected = 2500
osc_check_replication_filters = false
osc_alter_foreign_keys_method = "auto"

[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"

创建yml文件

cat >goinception.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: archery-goinception
namespace: archery
labels:
app: archery-goinception
spec:
selector:
matchLabels:
app: archery-goinception
template:
metadata:
labels:
app: archery-goinception
spec:
containers:
- name: archery-goinception
image: hanchuanchuan/goinception
#image: ccr.ccs.tencentyun.com/shc-infrastructure/archery-goinception:v1.2.4-37
imagePullPolicy: IfNotPresent
volumeMounts:
- mountPath: /etc/config.toml
name: config
#subPath: config.toml
resources:
requests:
memory: "128Mi"
cpu: "10m"
limits:
memory: "2048Mi"
cpu: "1000m"
volumes:
- name: config
#configMap:
# name: archery-goinception
hostPath:
path: /mnt/v1/archery/inception/config.toml

---

apiVersion: v1
kind: Service
metadata:
name: archery-goinception
namespace: archery
labels:
app: archery-goinception
spec:
ports:
- port: 4000
selector:
app: archery-goinception

直接运行创建：kubectl apply -f goinception.yml没有报错即可

创建archery

创建配置目录mkdir -p /mnt/v1/archery/archery

创建日志目录mkdir -p /mnt/v1/archery/archery-logs

在archery目录创建三个文件

[root@k8snode1 archery]# ls -trl
总用量 16
-rw-r--r-- 1 root root 1127 3月 22 18:14 soar.yaml
-rw-r--r-- 1 root root 7229 3月 22 18:14 settings.py
-rw-r--r-- 1 root root 42 3月 22 18:14 docs.md
[root@k8snode1 archery]# pwd
/mnt/v1/archery/archery
[root@k8snode1 archery]# cat
^C
[root@k8snode1 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@k8snode1 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': 'archery-mysql.archery',
'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://archery-redis.archery:6379/0",
"OPTIONS": {
"CLIENT_CLASS": "django_redis.client.DefaultClient",
"PASSWORD": "123456"
}
},
"dingding": {
"BACKEND": "django_redis.cache.RedisCache",
"LOCATION": "redis://archery-redis.archery: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': 'WARNING'
},
'django-q': { # django_q模块相关日志
'handlers': ['console', 'default'],
'level': 'WARNING',
'propagate': False
},
'django_auth_ldap': { # django_auth_ldap模块相关日志
'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
# },
}
}
[root@k8snode1 archery]# cat docs.md
# 请替换docs目录下的Markdown文件

创建yml配置文件

cat >archery.yml
apiVersion: apps/v1
kind: Deployment
metadata:
name: archery
namespace: archery
labels:
app: archery
spec:
selector:
matchLabels:
app: archery
template:
metadata:
labels:
app: archery
spec:
imagePullSecrets:
- name: tencentcloudsecret
containers:
- name: archery
image: hhyo/archery:1.7.13
# imagePullPolicy: IfNotPresent
# image: ccr.ccs.tencentyun.com/shc-infrastructure/archery:1.7.13-custom
imagePullPolicy: Always
command:
- sh
- -c
- dockerize -wait tcp://archery-mysql.archery:3306 -wait tcp://archery-redis.archery:6379 -timeout 60s /opt/archery/src/docker/startup.sh
env:
- name: NGINX_PORT
value: "9123"
#- name: ACTIVE_PROFILE
# value: {{ACTIVE_PROFILE}}
volumeMounts:
- mountPath: /opt/archery/archery/settings.py
name: settings
#subPath: settings.py
- mountPath: /etc/soar.yaml
name: soar
#subPath: soar.yaml
- mountPath: /opt/archery/docs/docs.md
name: docs
#subPath: docs.md
- mountPath: /opt/archery/downloads
name: downloads
- mountPath: /opt/archery/sql/migrations
name: sql-migrations
- mountPath: /opt/archery/logs
name: logs
resources:
requests:
memory: "512Mi"
cpu: "10m"
limits:
memory: "2048Mi"
cpu: "1000m"
volumes:
- name: settings
#configMap:
# name: archery
hostPath:
path: /mnt/v1/archery/archery/settings.py
- name: soar
#configMap:
# name: archery
hostPath:
path: /mnt/v1/archery/archery/soar.yaml
- name: docs
#configMap:
# name: archery
hostPath:
path: /mnt/v1/archery/archery/docs.md
- name: downloads
hostPath:
path: /mnt/v1/archery/archery-downloads
type: DirectoryOrCreate
- name: sql-migrations
hostPath:
path: /mnt/v1/archery/archery-sql-migrations
type: DirectoryOrCreate
- name: logs
hostPath:
path: /mnt/v1/archery/archery-logs
type: DirectoryOrCreate

---

apiVersion: v1
kind: Service
metadata:
name: archery
namespace: archery
labels:
app: archery
spec:
type: NodePort
ports:
- port: 9123
selector:
app: archery

直接运行创建：kubectl apply -f goinception.yml没有报错即可

【效果】

rancher查看

k3s查看

访问archery查看

测试功能操作：

数据库创建读写用户：

create user 'readwrite'@'%' identified by 'Mysql_1234';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON *.* TO 'readwrite'@'%';

archery创建资源组

archery添加实例

创建个审核流程

测试提工单操作：

修改archery启动OSC功能参数，修改本地的/mnt/v1/archery/inception/config.toml参数

然后重启goinception容器

然后查看容器上面的配置文件是否生效

kubectl exec -it -n archery archery-goinception-7b6c56575b-jslkr sh

cat /etc/config.toml

MySQL库造一些数据

需要修改参数：set global log_bin_trust_function_creators=on; 否则调用OSC会报错：

archery提单测试操作：

审核执行操作

查看进度

这里有个BUG，已经issue，当前已经反馈修复，需要合并操作才能看到百分比进度

https://github.com/hhyo/Archery/issues/1033

重新build本地镜像

将文件下载到定义的目录后，然后解压文件，删除tar包：

创建dockerfile

[root@k8snode1 go]# cat goInception-Dockerfile
FROM hanchuanchuan/goinception:v1.2.4
COPY goInception /goInception
RUN chmod 755 /goInception
[root@k8snode1 go]#

然后build本地镜像文件

docker build -t hanchuanchuan/goinception:v1.2.4-37 -f goInception-Dockerfile .