8.5 Auditing cnDBTier Sites with dbtaudit

This section describes how to use the dbtaudit command-line tool to audit georeplicated cnDBTier sites for schema and row-count consistency. With dbtaudit, you can efficiently compare table structures and row counts across multiple MySQL clusters in distributed (georeplicated) environments and quickly identify inconsistencies.

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.

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

Run the dbtaudit binary and add it to your path: 

chmod +x 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]
         [--compare-schema-only | --compare-rows-only]
         [--always-generate-schema-files]
         [--use-namespace NAMESPACE | --use-kubeconfig KUBECONFIG_FILE | --use-oc]
         [--version] [--debug]

The following are the common options:

Table 8-3 Common Options

Option Description
--connect-timeout SECONDS Set MySQL connection timeout in seconds (default: 5).
--compare-schema-only Only compare schema definitions; skip row counts.
--compare-rows-only Only compare row counts; skip schema definitions.
--always-generate-schema-files Always create per-site schema comparison log files, even if there are no differences.
--use-namespace NAMESPACE Use a specific Kubernetes namespace (via kubectl). Mutually exclusive with the next two.
--use-kubeconfig KUBECONFIG_FILE Use a specific kubeconfig file. Mutually exclusive with the above and below options.
--use-oc Use the OpenShift oc CLI instead of kubectl.
--version Show dbtaudit version and exit.
--debug Enable debug logging for troubleshooting.

Note:

Namespace, kubeconfig, and oc selection options are mutually exclusive. If none are specified, dbtaudit prompts you for a namespace and defaults to using kubectl.

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

Output and Reports

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.
  • 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.

dbtaudit also generates files such as:

  • dbtaudit_schema_<site_id>_<timestamp>.log: Per-site schema comparison logs for easy diffing which contains normalized CREATE TABLE definitions.

Environment and Exit Codes

  • Ensure that you have access to the relevant clusters via kubectl or oc.
  • Make sure MySQL monitor credentials are provisioned in Kubernetes secrets.
Exit Codes:
  • 0: Command completed successfully.
  • 1: Command failed (for example, due to a missing namespace or invalid options).