Auditing cnDBTier Sites with dbtaudit

Prerequisites

Before running dbtaudit, ensure the following requirements are met:

Your operating system is CentOS Linux 7 (Core) or later.
The dbtaudit binary file is available, marked as executable (chmod +x dbtaudit), and placed in your system PATH.
kubectl or OpenShift oc CLI is installed and configured for cluster access.
MySQL monitoring credentials are stored in the appropriate Kubernetes secrets in your environment. dbtaudit retrieves these automatically.
dbtaudit supports MySQL password encryption starting with cnDBTier 26.1.200 and later releases.

Tool Overview

The dbtaudit utility connects to MySQL instances running in Kubernetes clusters managed as part of a cnDBTier deployment. It compares database schemas and row counts, checks for the presence of tables, and summarizes key metrics per site.

Key Features:

Schema Comparison: Compares detailed table schemas (columns, indexes, etc.) between sites, ignoring AUTO_INCREMENT differences. Generates per-site schema files to support in-depth diffing when differences are found or when explicitly requested.
Row Count Comparison: Checks row counts for all tables across sites, highlighting discrepancies.
Existence Checks: Reports databases and tables that are missing on any site. Empty (non-system) MySQL databases are included in the comparison. System databases (mysql, information_schema, performance_schema, sys, ndbinfo) are always excluded.
Kubernetes Native Integration: Works seamlessly with kubectl and OpenShift oc. You can select sites via namespace, a custom kubeconfig, or OpenShift context.
Comprehensive Reporting: Outputs formatted tables summarizing schema, row counts, object presence, and overall metrics.
Secure and Configurable: Credentials are retrieved securely via Kubernetes secrets. Debug logging is available for troubleshooting.

Installation of dbtaudit

Grant run permission to the dbtaudit binary:
```
chmod +x dbtaudit
```
Before running dbtaudit, source the supplied source_me file from the directory that contains it:
```
cd Artifacts/Scripts/tools
source ./source_me
```
Sourcing source_me adds the tools’ bin directory to PATH, prompts for the namespace and sets DBTIER_NAMESPACE, and sets DBTIER_LIB to the directory containing the libraries required by dbtaudit.
Ensure that either kubectl or oc is installed and configured to access clusters where your cnDBTier is running.

Usage and Command Syntax

The general syntax for running dbtaudit is:

dbtaudit [--connect-timeout SECONDS]
         [--mysql-operation-timeout SECONDS]
         [--kubernetes-operation-timeout SECONDS]
         [--compare-schema-only | --compare-rows-only]
         [--always-generate-schema-files]
         [--use-namespace NAMESPACE | --use-kubeconfig KUBECONFIG_FILE | --use-oc]
         [--debug]

dbtaudit [--version]
dbtaudit [-h | --help]

The following are the common options:

Table 8-3 Common Options

Option	Description
`--connect-timeout SECONDS`	Sets the MySQL connection-establishment timeout in seconds. `dbtaudit` passes this option to `mysql` only, not to `mysqldump`. The `--mysql-operation-timeout` option bounds schema extraction, including `mysqldump` and its enclosing `kubectl` or `oc exec` command. Specify a positive integer. Minimum: `1`. Default: `5`.
`--mysql-operation-timeout SECONDS`	Sets the end-to-end deadline in seconds for each MySQL or `mysqldump` operation, including its enclosing `kubectl` or `oc exec` command. Specify a positive integer. Minimum: `1`. Default: `300`.
`--kubernetes-operation-timeout SECONDS`	Sets the end-to-end deadline in seconds for Kubernetes or OpenShift discovery, reads, and secret or configuration retrieval. Specify a positive integer. Minimum: `1`. Default: `60`.
`--compare-schema-only`	Compares schema definitions only and skips row-count comparisons. Do not combine this option with `--compare-rows-only`.
`--compare-rows-only`	Compares row counts only and skips schema-definition comparisons. Do not combine this option with `--compare-schema-only` or `--always-generate-schema-files`.
`--always-generate-schema-files`	Always writes detailed per-site schema comparison log files, regardless of whether the audit finds differences. Do not combine this option with `--compare-rows-only`.
`--use-namespace NAMESPACE`	Uses `kubectl` with the specified namespace. Specify a Kubernetes DNS-1123 label containing 1–63 lowercase letters, digits, or hyphens, beginning and ending with a letter or digit. Do not combine this option with `--use-kubeconfig` or `--use-oc`.
`--use-kubeconfig KUBECONFIG_FILE`	Uses an existing, readable regular kubeconfig file. The tool passes paths containing spaces and punctuation as literal paths. Do not combine this option with `--use-namespace` or `--use-oc`.
`--use-oc`	Uses the OpenShift `oc` CLI instead of `kubectl`. The active `oc` context supplies the namespace. Do not combine this option with `--use-namespace` or `--use-kubeconfig`.
`--version`	Show `dbtaudit` version and exit.
`--debug`	Enable debug logging for troubleshooting.

Note:

The namespace, kubeconfig and OpenShift selection options are mutually exclusive. If you omit all site-selection options, dbtaudit uses DBTIER_NAMESPACE when you have set it; otherwise, it prompts for a namespace.

Command-Line Validation

dbtaudit validates command-line values before prompting for a namespace, accessing clusters, retrieving credentials, generating reports or working with schema files.

All timeout values must be positive integers. Invalid, missing or non-positive timeout values cause a command-line error. If an operation exceeds its operation deadline, dbtaudit reports a runtime failure.

Namespace values from --use-namespace, DBTIER_NAMESPACE and the interactive prompt must follow the Kubernetes DNS-1123 label rule. The tool validates user-provided values and passes them to helper programs as literal arguments rather than shell-interpreted text.

dbtaudit rejects unknown arguments, invalid types or ranges, missing option values, invalid namespaces, unreadable kubeconfig files, mutually exclusive selectors or comparison modes, and combinations of rows-only mode with schema-file generation. It reports one concise diagnostic through standard error and returns exit status 2. Rejected arguments cause no operational side effects, and the tool does not print a traceback by default.

Exit Status

Exit Status	Description
`0`	The audit completes successfully and displays the green `dbtaudit completed successfully` footer. Schema or row-count differences alone do not cause failure. The `--help` and `--version` commands also return `0`, but do not display a diagnostic footer.
`1`	The tool encounters an application or runtime failure after command-line parsing. If the audit has started, it displays the red `dbtaudit failed` footer after the timing lines.
`2`	The tool detects a command-line or early validation error. It prints one concise standard-error diagnostic, without a traceback by default, before operational side effects, timing information or a status footer.
`130`	The user interrupts the tool with Ctrl+C. If the audit has started, it displays the red `dbtaudit failed` footer after the timing lines.

Here are some example commands:

Run this command to compare schemas only across all sites:
```
dbtaudit --compare-schema-only
```
Run this command to compare both schemas and row counts (default):
```
dbtaudit
```
Run this command to generate detailed schema log files for all sites:
```
dbtaudit --always-generate-schema-files
```
Run the command to specify a namespace and enable debug logging:
```
dbtaudit --use-namespace my-namespace --debug
```
Run this command to use OpenShift oc client instead of kubectl:
```
dbtaudit --use-oc
```

OpenShift Namespace Handling

When you run dbtaudit with --use-oc, the tool uses the OpenShift oc CLI and reads the namespace from the active oc context. It does not prompt for a namespace or add -n <namespace> to oc commands in this mode.

Before running

dbtaudit
        --use-oc

, verify that the active oc context specifies the cnDBTier namespace:

oc config view --minify --output 'jsonpath={..namespace}'

Verify that oc can list the expected cnDBTier pods:

oc get pods

If the first command returns no namespace or the second command does not show the expected cnDBTier pods, set the active context to the cnDBTier namespace:

oc project <namespace>

Then run:

dbtaudit --use-oc

Monitor Access and Identity

dbtaudit maintains one monitor context per audit and Kubernetes command context. It first uses the cached db-monitor-svc target and metadata without a readiness precheck. During initial discovery, it selects Running, Ready, non-terminating pods whose main container is running and ready.

The tool uses cached peer targets only when it positively identifies a missing pod or container during Kubernetes exec, or a transport failure. If no cached peer succeeds, it performs one targeted pod refresh. It retains a successful replacement for subsequent monitor reads.

Authentication errors, HTTP or application errors, invalid JSON, invalid identity information and unknown errors stop monitor-access recovery immediately.

Each logical monitor read shares one --kubernetes-operation-timeout deadline across initial setup, attempts and refresh. The tool validates the runtime site ID and site name together and reuses that identity for the remainder of the audit, even if the monitor pod later disappears.

If the required SQL pod or container is missing, the tool fails before making IP-family or SQL calls. Discovery failures return exit status 1 and one failure footer. The tool does not repeat SQL operations, procedure creation or the audit.

On Linux, the packaged binary restores the original library search path for external child programs. Its bundled libraries remain private to the dbtaudit process. This behavior does not change monitor retry classification or deadlines.

Output and Reports

If replication_info.ndb_count_all_tables does not exist on an audited endpoint, dbtaudit creates this helper stored procedure before auditing. Therefore, an audit is not necessarily read-only. Invalid-argument rejection causes no operational side effects.

dbtaudit prints a series of reports in table format:

Schema Comparison Report: Identifies tables whose schema definitions differ across sites. When a difference is found (or if --always-generate-schema-files is set), dbtaudit writes per-site schema logs for use with diff tools.
Row Count Comparison Report: Highlights tables where the number of rows does not match across sites.

Note:
A row-count mismatch for replication_info.DBTIER_INITIAL_BINLOG_POSTION does not indicate a cnDBTier product issue or replication failure. If this table alone accounts for the total row-count difference, treat the mismatch as informational.
Table/Database Presence Report: Lists tables and databases and indicates where they exist or are missing on each site.
Summary Report: Summarizes key site metrics, such as the count of user databases and tables, and total row count. Empty, non-system databases are included in these counts.
Note:
The tool may display a “Database count mismatch detected across sites” message when it finds differences, including differences involving empty databases.
For an audit that starts, the final informational lines show the end time in UTC, the overall duration and exactly one final status line. A successful audit ends with the green
```
dbtaudit completed
        successfully
```
message. An operational failure ends with the red dbtaudit failed message. This footer appears last and supplements the detailed diagnostics and reports.

dbtaudit also generates files such as:

dbtaudit_schema_<site_id>_<timestamp>.log: When schema differences exist, or when you specify --always-generate-schema-files, dbtaudit writes per-site schema logs in CSV-like lines containing the database name, table name and CREATE TABLE statement without AUTO_INCREMENT. Use diff tools to inspect the differences.

Expected Inconsistencies Identified by dbtaudit for Site-Specific cnDBTier Tables:

dbtaudit can report row-count or data mismatches for the following cnDBTier metadata tables. These differences do not necessarily indicate a replication or cnDBTier issue.

backup_info:
- DBTIER_BACKUP_INFO
- DBTIER_BACKUP_TRANSFER_INFO
- DBTIER_AGGREGATE_BACKUP_TRANSFER_INFO
- DBTIER_BACKUP_PARTS_TRANSFER_INFO
replication_info:
- DBTIER_INITIAL_BINLOG_POSTION
- DBTIER_REPL_EVENT_INFO
- DBTIER_CLUSTER_EVENT_INFO
- DBTIER_SITE_CONFIG
cluster_info:
- DBTIER_CLUSTER_STATUS
- DBTIER_CONFIG
- DBTIER_COMMAND_QUEUE
grr_info:
- DBTIER_SITE_INFO
- DBTIER_REPL_SITE_INFO
- DBTIER_NDB_RESTORE_STATUS
- DBTIER_GRR_BACKUP_CHECKSUM_STATUS

In particular, DBTIER_CLUSTER_EVENT_INFO and DBTIER_REPL_EVENT_INFO can differ between georeplication sites because events are generated locally and older non-START events can be purged after the configured event-count threshold is reached.

8.5 Auditing cnDBTier Sites with dbtaudit