README

Artwork Commissioned and Copyright by Ben Fleuter

Cadfael 是一个针对 MySQL 数据库提供批判性分析的静态分析工具。

文档

所有检查及其理由、考虑因素和补救措施都在 wiki 中有详细记录。

安装

根据个人喜好，有多种安装选项。

Phar

您可以从最新发布版获取 phar 文件。这确保了您不会遇到任何依赖冲突。

如果您想将其设置为全局，请将其移动到您的 usr bin 文件夹

sudo mv cadfael.phar /usr/local/bin/cadfael

全局

如果您希望 Cadfael 在系统的任何位置都可用，您可以选择全局安装。

请注意，您可能会遇到与其他全局安装包的依赖冲突。

composer global require --no-dev cadfael/cadfael

请确保您的全局 composer vendor bin 文件夹已设置在您的路径中。您可能需要将其添加到您的 .bashrc 文件。

export PATH=$PATH:~/.config/composer/vendor/bin

本地

如果您想在一个特定项目中使用它，可以使用以下命令安装：

composer require cadfael/cadfael

可执行文件路径将在 ./vendor/bin/。

用法

您可以直接针对您的数据库运行 Cadfael，或者您也可以针对包含您的 MySQL 表和模式创建语句的文件运行它。

为了获得高质量的结果，我们建议您针对数据库运行它，因为这里有大量可供分析的信息（请参阅高级用法部分）。

# Run cadfael against a specific MySQL schema in your database
cadfael run --host 127.0.0.1 --username root --port 3306 [schema_name]

但是，有时您可能无法访问数据库来运行它（可能是由于您的环境或安全考虑）。您可能会发现此选项更适合 CI/CD 管道使用。

# Run cadfael against the creation definitions in this file
cadfael run-statment resources/mysql/sample.sql

请注意，这是一个 实验性功能，因为此方法使用第三方库，存在一些限制，因此目前并非所有分析功能都受支持。

高级用法

如果您正在针对您的数据库运行 Cadfael，您还可以包含 --performance_schema 标志，如果您希望针对 performance_schema 模式运行检查，该模式收集自上次重启以来关于您的服务器的分析数据。如果您想了解数据库的使用情况并检测与查询、表访问以及重或不良优化的查询相关的问题，这特别有用。

为了获得有意义的成果，您应该针对生产环境中使用的数据库运行此操作，否则您将只检查开发环境中收集的指标。 但首先，在针对生产数据库运行来自互联网的随机工具之前，请务必与您的 DBA/安全人员沟通。

环境变量

您可能希望通过环境（尤其是如果您想将其集成到构建管道中或想安全地管理机密）将参数传递给 Cadfael。

以下环境变量可以用作二进制参数的替代

MYSQL_HOST
MYSQL_PORT
MYSQL_DATABASE
MYSQL_USER
MYSQL_PASSWORD

您可以从命令行按如下方式测试此操作

MYSQL_HOST=127.0.0.1 MYSQL_USER=root MYSQL_PORT=3306 MYSQL_DATABASE=[database_to_scan] cadfael run

输出

Cadfael CLI Tool 0.3.3

Host: localhost:3306
User: [username]

What is the database password? 

MySQL Version: 8.0.30-0ubuntu0.22.04.1
Uptime: 3.6 days

Attempting to scan schema test
Tables Found: 6

.w..w.....w.....w.....w.....o............wo.......o........w
o.....wo.....w.o.o...o..

Checks passed: 67/84
(.) Ok: 67, (o) Concern: 8, (w) Warning: 9

Showing: Warning and higher


> Empty table

Description: Empty tables add unnecessary cognitive load similar to dead code.
Reference: https://github.com/xsist10/cadfael/wiki/Empty-Table

+-----------------------------+---------+-----------------------------------------------------------------+
| Entity                      | Status  | Message                                                         |
+-----------------------------+---------+-----------------------------------------------------------------+
| table_empty                 | Warning | Table contains no records.                                      |
| table_with_index_prefix     | Warning | Table contains no records.                                      |
| table_with_large_text_index | Warning | Table contains no records.                                      |
| table_without_index_prefix  | Warning | Table contains no records.                                      |
| user                        | Concern | Table is empty but has allocated free space.                    |
|                             |         | This table is in a shared tablespace so this doesn't mean much. |
+-----------------------------+---------+-----------------------------------------------------------------+

> Index Prefix

Description: High cardinality indexes with text columns should consider using prefixes.
Reference: https://github.com/xsist10/cadfael/wiki/Index-Prefix

+-------------+---------+-----------------------------------------------------------------------------------------------------+
| Entity      | Status  | Message                                                                                             |
+-------------+---------+-----------------------------------------------------------------------------------------------------+
| users.email | Concern | Column `email` (length 255) has no index prefix and a cardinality ratio of 1.                       |
|             |         | Since the column has high cardinality, it's recommended that you limit the index by using a prefix. |
|             |         | This will reduce disk space usage and insert/update performance on this table.                      |
+-------------+---------+-----------------------------------------------------------------------------------------------------+

> Require Primary Key Configuration

Description: Ensure MySQL is configured to block the creation of tables without PRIMARY KEYs.
Reference: https://github.com/xsist10/cadfael/wiki/Force-Primary-Key-Requirement

+----------------+---------+--------------------------------------------------------------------------------------------------------+
| Entity         | Status  | Message                                                                                                |
+----------------+---------+--------------------------------------------------------------------------------------------------------+
| localhost:3306 | Warning | You are running MySQL 8.0.13+ (MySQL 8.0.27-0ubuntu0.21.10.1) without sql_require_primary_key enabled. |
|                |         | Every table should have a primary key, so it's better to enforce it via configuration.                 |
+----------------+---------+--------------------------------------------------------------------------------------------------------+

> Reserved Keywords

Description: Identifies all columns whose names match reserved keywords.
Reference: https://dev.mysqlserver.cn/doc/refman/8.0/en/keywords.html

+----------------------------------+---------+------------------------------------------------+
| Entity                           | Status  | Message                                        |
+----------------------------------+---------+------------------------------------------------+
| table_with_index_prefix.name     | Concern | `name` is a reserved keyword in MySQL 8.0.     |
| table_with_large_text_index.name | Concern | `name` is a reserved keyword in MySQL 8.0.     |
| table_without_index_prefix.name  | Concern | `name` is a reserved keyword in MySQL 8.0.     |
| user.name                        | Concern | `name` is a reserved keyword in MySQL 8.0.     |
| users.name                       | Concern | `name` is a reserved keyword in MySQL 8.0.     |
| users.password                   | Concern | `password` is a reserved keyword in MySQL 8.0. |
+----------------------------------+---------+------------------------------------------------+

> Sane AUTO_INCREMENT definition

Description: AUTO_INCREMENT definitions should follow some basic guidelines.
Reference: https://github.com/xsist10/cadfael/wiki/Sane-Auto-Increment

+-------------------------------+---------+------------------------------------------------+
| Entity                        | Status  | Message                                        |
+-------------------------------+---------+------------------------------------------------+
| table_with_index_prefix.id    | Warning | This field should be an unsigned integer type. |
| table_without_index_prefix.id | Warning | This field should be an unsigned integer type. |
| user.id                       | Warning | This field should be an unsigned integer type. |
| users.id                      | Warning | This field should be an unsigned integer type. |
+-------------------------------+---------+------------------------------------------------+

试试看

您可以使用 resources/sample.sql 创建一个测试数据库，查看 Cadfael 检查的一些示例。

mysql -h <host> -u <user> -p <database> < resources/sample.sql

或者，您也可以尝试在几个公开数据源上测试 Cadfael。

警告：这些数据源并不总是在线且可用。

+-----------------------------+------+-----------+----------+--------------------------+
| Host                        | Port | Username  | Password | Schema                   |
+-----------------------------+------+-----------+----------+--------------------------+
| ensembldb.ensembl.org       | 5306 | anonymous |          | homo_sapiens_core_103_38 |
| mysql-rfam-public.ebi.ac.uk | 4497 | rfamro    |          | Rfam                     |
| mysql-db.1000genomes.org    | 4272 | anonymous |          | homo_sapiens_core_73_37  |
+-----------------------------+------+-----------+----------+--------------------------+

贡献

本项目采用贡献者行为准则。

如果您发现任何问题或有任何建议或请求，请随时提出。

cadfael / cadfael

维护者

详细信息

README

文档

安装

Phar

全局

本地

用法

高级用法

环境变量

输出

试试看

贡献