The exposure of sensitive data in test environments presents a dilemma for most organizations: the need to use realistic information to validate systems versus the imperative to protect privacy. In databases like MySQL, this challenge demands a precise technical solution to mitigate risk.



Data masking is the technique that solves this paradox. It is a specific practice within data anonymization that allows you to work with functionally valid information without compromising security. Implementing this technique is, therefore, a central pillar for effective Test Data Management.



This article explores how to apply data masking directly on MySQL, ensuring security and compliance without slowing down your work cycles.




1. Schema Scanning and Sensitive Data Detection



Before applying masking rules, it is necessary to:


  • Identify primary keys, foreign keys, constraints, and relationships.

  • Classify columns with Personally Identifiable Information (PII), financial, health, or confidential data.

  • Extract the DDL using SHOW CREATE TABLE or mysqldump --no-data, or analyze metadata using information_schema views to identify primary keys, foreign keys, and referential integrity constraints.


This step allows you to define specific rules by data type and apply transformations without compromising referential integrity.




2. Applying Masking Techniques



a. Substitution with Deterministic Hashing


This ensures consistency between environments and internal relationships:


UPDATE users
SET email = CONCAT(first_name, '@example.com');

For sensitive fields such as email addresses, it is recommended to generate synthetic values with a valid format. If persistence and uniqueness are required, you can use deterministic hashing on a unique identifier concatenated with a fictitious domain.



b. Controlled Random Value Generation


This is ideal for numeric or specific format fields:


UPDATE customers
SET phone = CONCAT('+34 ', FLOOR(600000000 + RAND() * 99999999));


c. Structured Replacement for Sensitive Data


This substitutes real identifiers with valid synthetic values:


UPDATE credit_cards
SET number = LPAD(FLOOR(RAND() * 10000000000000000), 16, '4');


d. Preserving Relationships Between Tables


This requires prior mapping and cascading execution to maintain referential integrity:


-- Auxiliary mapping
CREATE TEMPORARY TABLE id_map AS
SELECT original_id, UUID() as fake_id FROM clients;

-- Cross-relational update
UPDATE orders o
JOIN id_map m ON o.client_id = m.original_id
SET o.client_id = m.fake_id;


Ensure that the masking preserves the formats and validations required in the synthetic data. For related data, generate a deterministic mapping between the original and masked values, guaranteeing referential integrity across all dependent tables.




3. Workflow Automation



In CI/CD environments, masking must be replicable and traceable. Some recommendations:


  • Integrate scripts with GitLab CI, Jenkins, or GitHub Actions pipelines.

  • Use tools like Gigantics to record schema versions and applied transformations.

  • Define external configurations to adapt rules by environment.



4. Audit and Regulatory Compliance



A solid masking strategy must record:


  • Which fields have been modified.

  • Which rules and algorithms have been applied.

  • When and on which schema version the execution was performed.


This allows compliance with regulatory frameworks such as GDPR and NIS2, which demand traceability in non-production environments.




Common Errors When Masking Data in MySQL



Masking data in MySQL can seem like a simple task if it is reduced to replacing sensitive values with fictitious information. However, without a structured and automated approach, this process introduces technical and operational risks that directly affect testing quality, environment integrity, and regulatory compliance.



Here are some of the most frequent errors:


  1. Breaking Referential Integrity: Applying masking without respecting relationships between tables (e.g., foreign keys) generates inconsistent data that breaks functional and automated tests. This translates into false negatives, failures in CI/CD pipelines, and a loss of trust in the environments.
  2. Not Preserving Business Logic: Substituting sensitive data without validating formats, business rules, or system constraints (regex, lengths, encodings) can cause tests to fail not due to actual errors, but due to poorly structured data.
  3. Masking Without Traceability or Audit: When it is not recorded which fields have been transformed, with what technique, and at what time, traceability is lost—increasing the risk of non-compliance in audits, especially under frameworks like NIS2 or GDPR.
  4. Using Manual Scripts or Non-Specialized Tools: Many teams still resort to scripts in SQL, Python, or Excel to mask data. These solutions, in addition to scaling poorly, generate inconsistent results between executions, without version control or structural validation. It is worth mentioning that there are specialized Python frameworks, so the problem is not the language, but the absence of systematic control.
  5. Lack of Prior Data Classification: Masking without a prior phase of PII identification and classification leads to inadvertent exposures: columns that contain sensitive data and have not been processed due to lack of knowledge or because they rely on non-standard or non-intuitive naming conventions.


How Gigantics Solves This



At Gigantics, we work directly on your MySQL databases to:


  • Scan the complete schema and detect sensitive fields.

  • Classify them with configurable labels (PII, confidential, etc.).

  • Apply masking rules without breaking relationships or structures.

  • Generate automatic audits for traceability and compliance with GDPR, NIS2, and other regulations.