Log inRequest a demoStart free

Guides

Test data management for MySQL

How to get realistic, masked MySQL and MariaDB test data without dumping production into QA, and what the MySQL connector does and does not do yet.

Guide

MySQL and its compatible forks power a huge number of web applications, e-commerce platforms and SaaS back ends. Test data for these systems is usually produced with mysqldump, sometimes piped straight from production into a developer database. That is convenient and it is exactly how personal data ends up in places nobody audits. This guide covers the problems to solve and what DataNivra offers for MySQL today.

Current status: the MySQL connector is available

DataNivra ships a dedicated MySQL connector in the standard agent image (agent 0.2.0 and later), and it is available. It reads MySQL and, as a separately tested engine, MariaDB, each with its own recorded conformance run. The MySQL integration page and the MariaDB integration page show the live status, the tested server versions and every known limitation, all generated from the product's connector registry and its recorded conformance runs.

What the connector does today: catalog discovery through information_schema, primary and foreign keys, row estimates from table statistics, aggregate column profiles computed in the database, and unbuffered streaming reads into the local engine. Before it reads anything it proves the account is read-only (see below) and puts every session into a read-only transaction.

Known limitations include agent 0.2.0 or later being required (agent 0.1.0 does not include the driver), password authentication only, managed services such as Amazon RDS and Aurora not yet covered by a recorded test run, spatial values read as WKT text, and nested roles on MariaDB refused rather than expanded. MariaDB is never assumed to behave like MySQL just because the same driver connects: it has its own compatibility record.

Common MySQL test-data problems

  • Engines without foreign keys. Older tables on MyISAM, or schemas where the application team simply never declared constraints, mean the database cannot tell you how records relate. Row sampling then produces orders without customers.
  • Zero dates and loose modes. Legacy data may contain 0000-00-00 dates or values that newer strict SQL modes reject. A test copy loaded into a stricter server fails in ways production never does.
  • JSON columns. Personal data hides inside JSON documents: addresses, notes, contact preferences. Column-level masking has to understand which JSON fields are sensitive.
  • Developer-driven refreshes. Because dumps are easy, every team refreshes on its own schedule, with no record of what was copied or who has it.

What a safe extraction looks like

The connector's read-only proof parses SHOW GRANTS FOR CURRENT_USER() and expands any granted roles. Only SELECT, SHOW VIEW and USAGE are accepted; any write, DDL, administrative privilege or grant option refuses the source. A least-privilege account looks like this:

CREATE USER 'tdm_reader'@'10.20.%' IDENTIFIED BY '<from your secret store>' REQUIRE SSL;
GRANT SELECT, SHOW VIEW ON claims.* TO 'tdm_reader'@'10.20.%';

Connect it to a replica where possible, store the password in your secret store, and give the agent a connection document that refers to it:

{
  "kind": "MYSQL",
  "host": "claims-replica.db.example.internal",
  "user": "tdm_reader",
  "password_ref": "vault://tdm/claims-mysql#password",
  "databases": ["claims"],
  "tls": "verify-full",
  "ssl_ca": "/etc/datanivra/ca/claims-mysql.pem"
}

TLS with certificate and host-name verification is the default. Tables without declared foreign keys (MyISAM, or constraints never created) still need their relationships declared for subsetting.

Alternative: export to Parquet or CSV

If you cannot give the agent network access to the database, produce an extract inside your network and read it with a file connector:

  1. Export the tables to Parquet or CSV with existing tooling. The extract remains sensitive until masked, so keep it on protected storage.
  2. Register the directory as a read-only Local files source, or use Amazon S3 or Azure Blob / Data Lake storage you control.
  3. For CSV, declare whether the first line is a header; DataNivra refuses to guess, because a headerless first line is a data record.
  4. Declare relationships in policy, then subset, mask, certify and provision.

A certified dataset can be provisioned to a directory or to a PostgreSQL test database today; loading back into a MySQL test server is a step your pipeline performs from the certified files.

How DataNivra approaches it

Discovery and classification run inside your network and report only table names, column names, types and aggregate profiles. Masking replaces names, emails, phone numbers and addresses with synthetic values and pseudonymises identifiers deterministically under your key, so joins keep working. Certification checks masking completion and referential integrity and blocks any dataset that fails. The CI/CD tutorial shows how certified versions feed automated pipelines.

Next steps

  • Check the integrations page for MySQL and MariaDB status and limitations.
  • Estimate subset size and storage with the free tools.
  • Read the documentation for agent installation and file-set sources.
  • Start free and try the full workflow on synthetic data in the sandbox.