DB审核查询平台Archery

  • Z时代
  • 2024-01-10
  • 分类：综合

database

Archery介绍

Archery是archer的分支项目，定位于SQL审核查询平台，旨在提升DBA的工作效率，支持多数据库的SQL上线和查询，同时支持丰富的MySQL运维功能，所有功能都兼容手机端操作。

功能清单

数据库类型查询审核执行备份数据字典慢日志会话管理账号管理参数管理 数据归档

MySQL

√

√

√

√

√

√

√

√

√

√

MsSQL

√

×

√

×

×

×

×

×

×

×

Redis

√

×

√

×

×

×

×

×

×

×

PgSQL

√

×

√

×

×

×

×

×

×

×

Oracle

√

×

√

√

×

×

×

×

×

×

MongoDB

√

×

√

×

×

×

×

×

×

×

因基础环境不同，在安装部署中可能遇到的异常，如下：

1.libmysqlclient.so.20 异常

Watching for file changes with StatReloader
Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/__init__.py", line 18, in <module>
from . import _mysql
ImportError: libmysqlclient.so.20: cannot open shared object file: No such file or directory
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/runserver.py", line 110, in inner_run
    autoreload.raise_last_exception()
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 76, in raise_last_exception
raise _exception[1]
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 357, in execute
    autoreload.check_errors(django.setup)()
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/usr/local/lib/python3.6/site-packages/django/apps/registry.py", line 114, in populate
    app_config.import_models()
  File "/usr/local/lib/python3.6/site-packages/django/apps/config.py", line 211, in import_models
    self.models_module = import_module(models_module_name)
  File "/usr/lib64/python3.6/importlib/__init__.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 994, in _gcd_import
  File "<frozen importlib._bootstrap>", line 971, in _find_and_load
  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/usr/local/lib/python3.6/site-packages/django/contrib/auth/models.py", line 2, in <module>
from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
  File "/usr/local/lib/python3.6/site-packages/django/contrib/auth/base_user.py", line 48, in <module>
class AbstractBaseUser(models.Model):
  File "/usr/local/lib/python3.6/site-packages/django/db/models/base.py", line 122, in__new__
    new_class.add_to_class("_meta", Options(meta, app_label))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/base.py", line 326, in add_to_class
    value.contribute_to_class(cls, name)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/options.py", line 206, in contribute_to_class
    self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
  File "/usr/local/lib/python3.6/site-packages/django/db/__init__.py", line 28, in__getattr__
return getattr(connections[DEFAULT_DB_ALIAS], item)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 214, in__getitem__
    backend = load_backend(db["ENGINE"])
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 111, in load_backend
return import_module("%s.base" % backend_name)
  File "/usr/lib64/python3.6/importlib/__init__.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 15, in <module>
import MySQLdb as Database
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/__init__.py", line 24, in <module>
    version_info, _mysql.version_info, _mysql.__file__
NameError: name "_mysql"isnot defined

解决方案：

因为我们本机安装了mysql，本地由libmysqlclient.so.20文件包，所以简单做个软链接即可。

ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20

2.安装安装pyodbc报错

Using legacy "setup.py install"for pyodbc, since package "wheel"isnot installed.
Installing collected packages: pyodbc
    Running setup.py install for pyodbc ... error
    ERROR: Command errored out with exit status 1:
     command: /usr/bin/python3 -u -c "import io, os, sys, setuptools, tokenize; sys.argv[0] = """""/tmp/pip-install-jys5r5zz/pyodbc_bXXXXXd/setup.py"""""; __file__="""""/tmp/pip-install-jys5r5zz/pyodbc_b2bXXXX4d/setup.py""""";f = getattr(tokenize, """""open""""", open)(__file__) if os.path.exists(__file__) else io.StringIO("""""from setuptools import setup; setup()""""");code = f.read().replace("""""
""""", """""
""""");f.close();exec(compile(code, __file__, """""exec"""""))" install --record /tmp/pip-record-4o7hogt9/install-record.txt --single-version-externally-managed --compile --install-headers /usr/local/include/python3.6m/pyodbc
         cwd: /tmp/pip-install-jys5r5zz/pyodbc_b2bc9990d79a4871bf031ae2d0c51d4d/
    Complete output (18 lines):
/usr/local/lib/python3.6/site-packages/setuptools/dist.py:726: UserWarning: Usage of dash-separated "library-dirs" will not be supported in future versions. Please use the underscore name "library_dirs" instead
% (opt, underscore_opt)
    running install
/usr/local/lib/python3.6/site-packages/setuptools/command/install.py:37: SetuptoolsDeprecationWarning: setup.py install is deprecated. Use build and pip and other standards-based tools.
      setuptools.SetuptoolsDeprecationWarning,
    running build
    running build_ext
    building "pyodbc" extension
    creating build
    creating build/temp.linux-x86_64-3.6
    creating build/temp.linux-x86_64-3.6/src
    gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-span --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DPYODBC_VERSION=4.0.30 -I/usr/include/python3.6m -c src/buffer.cpp -o build/temp.linux-x86_64-3.6/src/buffer.o -Wno-write-strings
    In file included from src/buffer.cpp:12:0:
    src/pyodbc.h:56:17: fatal error: sql.h: No such file or directory
#include <sql.h>
                     ^
    compilation terminated.
    error: command "gcc" failed with exit status 1
    ----------------------------------------
ERROR: Command errored out with exit status 1: /usr/bin/python3 -u -c "import io, os, sys, setuptools, tokenize; sys.argv[0] = """""/tmp/pip-install-jys5r5zz/pyodbc_XXXXX/setup.py"""""; __file__="""""/tmp/pip-install-jys5r5zz/pyodbc_XXXXXXX/setup.py""""";f = getattr(tokenize, """""open""""", open)(__file__) if os.path.exists(__file__) else io.StringIO("""""from setuptools import setup; setup()""""");code = f.read().replace("""""
""""", """""
""""");f.close();exec(compile(code, __file__, """""exec"""""))" install --record /tmp/pip-record-4o7hogt9/install-record.txt --single-version-externally-managed --compile --install-headers /usr/local/include/python3.6m/pyodbc Check the logs for full command output.

解决方案：

先安装unixODBC-devel，执行命令如下：

 yum install unixODBC-devel

在此安装就好好了。

3.安装django-auth-ldap报错

In file included from Modules/LDAPObject.c:3:0:
  Modules/common.h:9:20: fatal error: Python.h: No such file or directory
#include "Python.h"
                      ^
  compilation terminated.
  error: command "gcc" failed with exit status 1
  ----------------------------------------
  ERROR: Failed building wheel for python-ldap
Failed to build python-ldap
ERROR: Could not build wheels for python-ldap, which is required to install pyproject.toml-based projects

解决方案： 

需要先安装pip3 install python-ldap，而安装之前需要安装yum install openldap-devel。

安装上面两个插件之后，再此安装就报错了。

参考：https://www.cnblogs.com/wangjian941118/p/13518515.html

4.项目启动报错 

python manage.py runserver

 File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 185, in__init__
    super().__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1049, "Unknown database "archery"")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run
    self.check_migrations()
--------------------------------
File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 185, in__init__
    super().__init__(*args, **kwargs2)
django.db.utils.OperationalError: (1049, "Unknown database "archery"")

解决方案

系统默认的数据库是archery。

我们可以先手动创建创建这个数据库。

5.未初始化导致报错

You have 31 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): admin, auth, contenttypes, django_q, sessions.
Run "python manage.py migrate" to apply them.

解决方案：

执行初始化命令

python manage.py makemigrations
python manage.py migrate

6.执行初始化时报错，不能创建外键

File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 245, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 229, in apply_migration
    migration_recorded = True
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 115, in__exit__
    self.execute(sql)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 90, in__exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.IntegrityError: (1215, "Cannot add foreign key constraint")

解决方案：

set global FOREIGN_KEY_CHECKS=0

参考：

https://stackoverflow.com/questions/12966626/mysql-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails

再次初始化，不再报错。

7.启动还是报错，此次错误说缺少表

[ERROR]- 获取系统配置信息失败:(1146, "Table "archery.sql_config" doesn"t exist")Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1146, "Table "archery.sql_config" doesn"t exist")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/data/scripts/Archery-master/common/config.py", line 25, in get_all_config
for items in all_config:
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 287, in__iter__
    self._fetch_all()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 111, in__iter__
for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 90, in__exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table "archery.sql_config" doesn"t exist")

解决方案：

step 1.将之前存在的库删除掉：

drop database archery

step 2.先初始化sql应用的脚本

python manage.py makemigrations sql
python manage.py migrate sql

step 3 再初始化整个项目的脚本

python manage.py makemigrations
python manage.py migrate

注意上面的顺序。

到此，此项目再正是部署完成，才算基本调通。

8.服务启动，网址可访问

login会显示输入账号，此时需要进行数据初始化。

# 数据初始化
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

 

参考：

1.手动部署--Archery

https://archerydms.com/installation/manual/

2.开源SQL审核平台——Archery 安装、部署心得 

https://www.cnblogs.com/David-domain/archive/2019/07/10/11165734.html

3.Archery SQL审核查询平台

https://archerydms.com/

以上是 DB审核查询平台Archery 的全部内容， 来源链接： utcz.com/z/536326.html

回到顶部