A.7 Database Data Sync Script for SEPP

Overview:

The dbtdatasync is a powerful command-line utility designed to streamline the process of detecting, comparing, and synchronizing MySQL table data across geo-replicated Kubernetes sites. It is designed for DB Tier–approved operational workflows, this tool allows for table-level dumps, schema comparison, data diff generation, and optional corrective synchronization from primary sites to their remote replicas.

The tool is optimized for MySQL and NDB clusters running within Kubernetes environment.

Purpose

In geo-replicated environments, data drift can occur due to factors like partial replication failures, network partitions, operational incidents, or manual hotfixes on specific sites.

dbtdatasync enables operators to detect inconsistencies early, Identify precise differences at the row level, apply corrections safely with full visibility and user confirmation. This script focuses exclusively on site-specific SEPP databases (Example: seppdb, seppbackupdb).

Key Features:

  • Schema Mismatch Detection: Easily identify mismatches in database schemas between primary and remote sites.
  • Row-Level Data Inconsistencies: Detects inconsistencies in table data.
  • Compressed Table Dumps: Automatically generates compact table dumps for both source and destination sites to simplify data transfer and comparison.
  • SQL Diff Generation: Create explicit SQL diff files outlining the changes required for corrective actions.
  • Interactive Confirmation: Ensure safe synchronization with interactive prompts before any data modifications are made.
  • Schema-Only Comparison Mode: Run a comparison focused solely on schema discrepancies, with a non-zero exit code triggered for mismatches.
  • Comprehensive Output Bundle: The tool produces a single, self-contained .tar.gz bundle that includes:
    • Table dumps
    • SQL diffs
    • Logs for full traceability
    • Summery
  • Kubernetes Integration: Compatible with:
    • kubectl (default)
    • kubectl --kubeconfig
High-Level Workflow of dbtdatasync

The dbtdatasync utility follows a streamlined workflow to ensure efficient and safe synchronization of MySQL table data across geo-replicated Kubernetes sites. Here’s an overview of the steps:

  1. Site Identification: Determines the current (primary) site and its databases from Kubernetes metadata.
  2. Remote Site Discovery: Retrieves replication details to identify remote sites.
  3. Table Selection: Loads the explicit list of tables to compare from tbl_info.txt.
  4. Data Extraction: Dumps specified tables from the primary (source) and remote (destination) sites.
  5. Schema Validation: Compares schemas; exits if mismatches are found.
  6. Data Diffing: Generates SQL diffs for any row-level discrepancies.
  7. User Confirmation: Prompts for approval before applying corrections.
  8. Synchronization (Optional): Applies SQL diffs to remote sites if approved.
  9. Artifact Bundling: Packages all outputs into a timestamped archive and cleans up temporaries.

Prerequisites

Requirements for dbtdatasync

To ensure smooth operation and optimal performance of dbtdatasync, the following runtime environment, tools, and Kubernetes permissions are required:

Runtime Requirements
  • Oracle Linux 9 or later: The utility is compatible with Oracle Linux 9 or any later versions.
  • Network Access: Ensure the system running dbtdatasync has network access to the Kubernetes cluster where the databases reside.
Required Tools
  • kubectl : You will need kubectl (for Kubernetes) to interact with the cluster and manage resources.
  • MySQL Client: A MySQL client must be available inside the database pod to enable seamless MySQL interactions (e.g., performing queries, comparing data).
Kubernetes Permissions

The executing user must have the following permissions within the Kubernetes environment:

  • Execute kubectl exec into Database Pods. (Example: ndbappmysqld-*)
  • Read Kubernetes Secrets (Examples: for DB credentials)
  • List Pods in Target Namespace (Example: sepp1)

Using the dbtdatasync Package

For SEPP deployments, the dbtdatasync package is distributed as a ZIP folder(for example, cnc_dbtdatasync_<version>.zip) and is available in the Scripts directory.

Run the following command to run the datasync script:
./dbtdatasync

Flag Options

Following are the available flag options:

-h, --help: Display all available dbsync command-line options and exit.

--version: Display the script version and exit.

--use-namespace <NAMESPACE>: Specify the target SEPP namespace (for example, sepp1).

--compare-schema: Perform a schema-only comparison and exit with a non-zero status if a mismatch is detected.

--use-kubeconfig <PATH>: Use a custom kubeconfig file (for example, ~/.kube/config-sepp1).

--debug: Enable verbose logging for troubleshooting.

--secret-name <NAME> : Specify a custom secret name. The default is occne-secret-db-monitor-secret.

--secret-namespace <NAMESPACE>: Specify the namespace where the secret is located.

--username-key <KEY>: Specify a custom key for retrieving the database username from the secret.

--password-key <KEY>: Specify a custom key for retrieving the database password from the secret.

Example:
./dbtdatasync \
  --use-namespace sepp1 \
  --secret-name sepp-db-sync-secret \
  --secret-namespace sepp1 \
  --username-key mysql_username_for_metrics \ 
  --password-key mysql_password_for_metrics \ 
  --debug

Exit Codes

Table -2 Exit Codes

Code Status Description
0 Success schemas match, or sync completed.
1 General Error Example: missing files/permissions.
2 Script Fail Schema mismatch detected.

Configuration via Environment Variables

dbtdatasync can be configured using environment variables to control various aspects of its operation. Below are the key environment variables and their corresponding values:

Table -3 Configuration via Environment Variables

Variable Description Default Value 
TABLE_INFO_FILE
 File containing tables to sync 
tbl_info.txt
 
DB_INFO_FILE
 Site → database mapping file 
db_info.txt
 
MYSQL_CHARSET
 MySQL character set used for dumps 
utf8mb4
DBQ_POD_SUBSTR Substring to match DB pods (e.g., ndbappmysqld) 
ndbappmysqld
DBQ_CONTAINER MySQL container name in DB pod 
mysqlndbcluster

Required Input Files

Two essential input files are required for dbtdatasync to function effectively: tbl_info.txt and db_info.txt.

tbl_info.txt

This file defines which tables will be compared and synchronized.

Rules:

  • Order matters: Ensure that the list of tables follows the foreign-key and insertion dependency order to avoid issues.
  • Empty lines: These are ignored and can be used for separation.
  • Comments: You can add comments using the # symbol, which will be ignored during processing.

Example of tbl_info.txt:

# List of tables to synchronize
users
orders
products

db_info.txt

This file maps site names to their associated databases. The format is as follows:

<site_name>:<comma-separated-database-list>

Example:

<site-name>:<comma-separated site specific DB names list>
Example:
delhi: seppdb_sepp_delhi,seppbackupdb_sepp_delhi

Each line represents a site and the databases associated with it. This allows dbtdatasync to know which databases to compare for each site.

Database Credential Handling

dbtdatasync securely handles MySQL credentials by reading them from a Kubernetes Secret.

Default Secret Configuration

  • Secret Name: occne-secret-db-monitor-secret
  • Secret Namespace: The same as the value set in --use-namespace
  • Username Key: mysql_username_for_metrics
  • Password Key: mysql_password_for_metrics

How Credentials Are Handled:

  • Read Securely
  • Base64-decoded automatically
  • Masked in Logs

This method ensures that sensitive information like MySQL credentials is handled securely and is not exposed in logs or other outputs.

Using a Custom Database User (Recommended)

While dbtdatasync can work with the default DB monitor user, it's recommended to create a dedicated sync user for better control and security. Here’s how to configure and use a custom database user for synchronization tasks.

Step 1: Create MySQL User

  1. Login to MySQL Cluster: Connect to your MySQL instance or cluster.
  2. Create the User: Run the following SQL commands to create a new user with appropriate privileges: 
CREATE USER '<DB_USERNAME>'@'%' IDENTIFIED BY '<DB_PASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<DB_USERNAME>'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Privileges Required:

  • Read schemas: The user must be able to read database schemas.
  • Dump tables: The user should have permission to create table dumps.
  • Apply corrective SQL: If syncing is enabled, the user must be able to execute SQL changes on the remote database.

Note:

You may scope privileges more tightly based on your security needs, but the user must have the permissions listed above.

Step 2: Create Kubernetes Secret

Once the MySQL user is created, store the credentials in a Kubernetes Secret to securely manage the database credentials.

  1. Run the following command to create the secret: 
kubectl create secret generic <SECRET_NAME> \ 
--from-literal=<USERNAME_KEY>=<DB_USERNAME> \ 
--from-literal=<PASSWORD_KEY>=<DB_PASSWORD> \ 
-n <SECRET_NAMESPACE>
Example:
kubectl create secret generic sepp-db-sync-secret \
  --from-literal=mysql_username_for_metrics=sepp_sync_user \
  --from-literal=mysql_password_for_metrics=secure_password \
  -n sepp1
  • Replace <SECRET_NAME> with the desired name for your secret.
  • Replace <USERNAME_KEY> and <PASSWORD_KEY> with the appropriate keys you intend to use for the username and password.
  • <SECRET_NAMESPACE> should be the Kubernetes namespace where your secret will reside.

Output and Artifacts

Upon completion, dbtdatasync generates a single compressed bundle with the following contents:

  • db_sync_bundle_YYYYMMDDHHMMSS.tar.gz
    • Source and destination dump archives (.tar.gz files).
    • Per-site output directories, named using the format sepp1_to_sepp2_YYYYMMDDHHMMSS.
    • SQL difference files (for example, sync_sql_YYYYMMDDHHMMSS.sql) when schema or data discrepancies are detected.
    • Log file: dbtdatasync.log, which contains execution summaries, status information, and error details.
    • Cleanup:Temporary files created during execution are automatically removed upon completion.
    If no differences are found, the log indicates that the databases are already in sync.