How to Achieve GDPR Compliance in MySQL

Why does GDPR matter for my MySQL database?

GDPR fines can reach 4% of global revenue. You must safeguard personal data, prove you minimized it, and delete it on request. MySQL stores PII in tables such as Customers and Orders, so compliance is critical.

How do I identify personal data columns quickly?

Query INFORMATION_SCHEMA to scan column names for keywords like “email,” “name,” or “address.” Save results in a review table so you can track remediation status.

SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'shop'
AND column_name REGEXP 'email|name|phone';

How can I minimize stored data?

Keep only what you need. Remove obsolete columns or tables, and archive raw logs outside the production schema. Use ALTER TABLE to drop or rename sensitive columns.

ALTER TABLE Customers
DROP COLUMN phone_number;

How do I pseudonymize or anonymize customer data?

Pseudonymization masks data but allows recovery; anonymization removes any link. Use AES_ENCRYPT for reversible encryption and SHA2 or random tokens for irreversible masking.

-- Reversible encryption
UPDATE Customers
SET email = TO_BASE64(AES_ENCRYPT(email, 'secret_key'))
WHERE id = 42;

-- Irreversible hashing
UPDATE Customers
SET name = SHA2(name, 256)
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 YEAR);

How can I handle a “right to be forgotten” request?

Create a stored procedure that deletes or anonymizes customer data across Customers, Orders, and OrderItems while keeping referential integrity through NULLs or surrogate keys.

CALL forget_customer(42);
-- Example procedure
CREATE PROCEDURE forget_customer(IN cust_id INT)
BEGIN
DELETE FROM Orders WHERE customer_id = cust_id;
DELETE FROM Customers WHERE id = cust_id;
END;

How do I log data access for audits?

Enable general_log only for sessions with elevated privileges, or use binary logs and a dedicated audit table triggered by SELECT, UPDATE, or DELETE events.

CREATE TRIGGER log_customer_access
AFTER SELECT ON Customers
FOR EACH ROW
INSERT INTO audit_log(user, action, accessed_at)
VALUES (CURRENT_USER(), 'SELECT Customers', NOW());

What are best practices for ongoing compliance?

Automate data retention checks with EVENTS, restrict permissions via GRANT, encrypt backups, and document all processes in a GDPR register. Review your schema after every release.