https://hub.docker.com/r/hhyo/inception
https://hub.docker.com/r/hhyo/archery
https://github.com/hhyo/Archery
https://hub.docker.com/r/hhyo/goinception
https://github.com/hanchuanchuan/goInception
https://inception-document.readthedocs.io/zh_CN/latest/
https://hanchuanchuan.github.io/goInception/
docker run -d --name goinception --restart always -p 4000:4000 --privileged=true --user=root -e TZ='Asia/Shanghai' --ulimit nofile=262144:262144 -v /etc/localtime:/etc/localtime:ro -v /data/site/docker/env/mysql/archery/goinception.toml:/config.toml --link mariadb --link keydb hanchuanchuan/goinception
docker run -d --name archery --restart always -p 9123:9123 --privileged=true --user=root -e TZ='Asia/Shanghai' --ulimit nofile=262144:262144 -v /etc/localtime:/etc/localtime:ro -e AUTH_LDAP_ALWAYS_UPDATE_USER='true' -e DEBUG='false' -e DATABASE_URL="mysql://opt:Wdq2023@mariadb:3306/htmltooarchery" -e CSRF_TRUSTED_ORIGINS='http://127.0.0.1:9123' -e CACHE_URL='redis://keydb:6379/0?PASSWORD=wdqdmm@r' -e AUTH_LDAP_USER_ATTR_MAP='username=cn,display=displayname,email=email' -e ENABLE_LDAP='false' -e Q_CLUISTER_SYNC='false' -e Q_CLUISTER_TIMEOUT='60' -e Q_CLUISTER_WORKERS='4' -e NGINX_PORT='9123' -v /data/file:/data/file -v /data/site/docker/env/mysql/archery/.env:/opt/archery/.env:ro -v /data/site/docker/env/mysql/archery/downloads:/opt/archery/downloads -w /opt/archery --link mariadb --link keydb hhyo/archery:master
cd /data/site/htmltoo.opt
git clone https://github.com/hhyo/Archery.git
cd Archery-1.10.0/src/docker-compose
docker-compose up -d
#表结构初始化
docker exec -ti archery /bin/bash
cd /opt/archery
source /opt/venv4archery/bin/activate
python3 manage.py makemigrations sql
python3 manage.py migrate
pip3 install django
#数据初始化
python3 manage.py dbshell<sql/fixtures/auth_group.sql
python3 manage.py dbshell<src/init_sql/mysql_slow_query_review.sql
#创建管理用户
python3 manage.py createsuperuser
#重启服务
docker restart archery
#日志查看和问题排查
docker logs archery -f --tail=10
logs/archery.log
Archery 定位于 SQL 审核查询平台，旨在提升 DBA 的工作效率，支持主流数据库的 SQL 上线和查询，同时支持丰富的 MySQL 运维功能
inception
hhyo/inception:latest
添加卷:
/etc/localtime:/etc/localtime:ro
/data/file:/data/file
/data/docker/mysql/inception/inception.ini:/etc/inc.cnf:ro
端口:6669-6669
vi /data/docker/mysql/inception/inception.ini
----------------------------------------------------------
[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=wdqdmm@r
inception_remote_system_user=root
inception_remote_backup_port=3306
inception_remote_backup_host=mariadb
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=1
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1
----------------------------------------------------------
===================================
rancher内网base-mariadb上登陆::
mysql -A -uroot -pwdqdmm@r -hfile.htmltoo.com -P6669;
查看参数:
inception get variables;
关于Inception: 一个集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维工具
MySQL语句的审核，在业界都已经基本被认同了，实际上也是对MySQL语句写法的统一化，标准化，而之前的人工审核，针对标准这个问题其实是很吃力的，标准越多，DBA越累，开发也越累。 那么在这个都追求自动化运维的时代，审核也必须要跟上步伐，因此Inception诞生了。而Inception可以做的工作远不止是一个自动化审核工具，同时还具备执行，生成对影响数据的回滚语句（类似闪回的功能），这样一条龙服务的工具，将会给DBA的工作带来翻天覆地的变化，DBA从此就从繁重的审核、登上去执行，出错了很难回滚（如果提前没有备份的话）的被动局面解放了出来，突然发现，做DBA原来可以这么轻松，工作可以不饱和了，那就有更多的自由时间学习、进一步向自动化运维平台的实现等更智能化的方向去发展，是具有里程碑意义的。
Dockerfile:
FROM docker.io/centos #inception RUN yum -y install wget git gcc gcc-c++ make cmake openssl-devel ncurses-devel m4\ && cd /opt \ && git clone https://github.com/hhyo/inception.git \ && rpm -i /opt/inception/dockersrc/bison-2.7-4.el7.x86_64.rpm \ && mv /opt/inception/dockersrc/inc.cnf /etc \ && cd inception \ && ./inception_build.sh debug \ && yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm \ && yum -y install percona-toolkit \ #修改中文支持 && rm -rf /etc/localtime && ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime \ && yum -y install kde-l10n-Chinese && yum -y reinstall glibc-common \ && localedef -c -f UTF-8 -i zh_CN zh_CN.utf8 ENV LC_ALL zh_CN.utf8 #设置环境变量 #port EXPOSE 6669 #start service ENTRYPOINT nohup /opt/inception/debug/mysql/bin/Inception --defaults-file=/etc/inc.cnf && bash
inceptiongo
hanchuanchuan/goinception:latest
添加卷:
/etc/localtime:/etc/localtime:ro
/data/file:/data/file
/data/docker/mysql/archery/goinception.toml:/etc/config.toml:ro
端口:4000-4000
vi /data/docker/mysql/archery/goinception.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 = "mariadb" backup_port = 3306 backup_user = "root" backup_password = "wdqdmm@r" # 安全更新是否开启. # -1 表示不做操作,基于远端数据库 [默认值] # 0 表示关闭安全更新 # 1 表示开启安全更新 sql_safe_updates = 0 # 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"
archery
hhyo/archery:latest
变量:
NGINX_PORT = 9123
添加卷:
/data/file:/data/file
/data/docker/mysql/archery/settings.py:/opt/archery/archery/settings.py:ro
/data/docker/mysql/archery/soar.yaml:/etc/soar.yaml:ro
/data/docker/mysql/archery/docs.md:/opt/archery/docs/docs.md:ro
/data/docker/mysql/archery/downloads:/opt/archery/downloads
端口:19123(http)-9123
进入容器, 初次部署需要初始化数据库和账号信息:
CREATE DATABASE `domsnarchery` /*!40100 COLLATE 'utf8_general_ci' */ # 注意utf8编码
docker exec -ti archery /bin/bash
#表结构初始化 cd /opt/archery source /opt/venv4archery/bin/activate python3 manage.py makemigrations sql python3 manage.py migrate #数据初始化 python3 manage.py loaddata initial_data.json #创建管理用户 python3 manage.py createsuperuser #重启服务 docker restart archery #日志查看和问题排查 docker logs archery -f --tail=10 /downloads/log/archery.log
访问: http://ip:19123/ ihunter wdqdmm@a
vi /data/docker/mysql/archery/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': 'domsnarchery',
'USER': 'root',
'PASSWORD': 'wdqdmm@r',
'HOST': 'mariadb',
'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/11",
"OPTIONS": {
"CLIENT_CLASS": "django_redis.client.DefaultClient",
"PASSWORD": "wdqdmm@r"
}
},
"dingding": {
"BACKEND": "django_redis.cache.RedisCache",
"LOCATION": "redis://redis:6379/12",
"OPTIONS": {
"CLIENT_CLASS": "django_redis.client.DefaultClient",
"PASSWORD": "wdqdmm@r"
}
}
}
# 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': 'downloads/log/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
},
}
}
--------------------------------------------------------------------------
vi /data/docker/mysql/archery/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/downloads/log/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
-----------------------------------------------------------------------------