Master MySQL Data Masking: Percona Plugin & Custom Functions Guide

Introduction

Data masking (or data obfuscation) is a technique for protecting sensitive information by replacing it with non‑real values. In modern digital environments, safeguarding personal and confidential data is essential for privacy, regulatory compliance, and security. MySQL offers powerful tools and extensibility that enable effective data masking.

Tools and Methods

Percona data_masking Plugin

The open‑source Percona data_masking plugin provides ready‑made masking for common high‑sensitivity fields such as ID numbers, phone numbers, and bank cards.

Installation

INSTALL PLUGIN data_masking SONAME 'data_masking.so';

After installation, verify the plugin status:

SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'data%';

Successful installation shows an ACTIVE status for data_masking.

Usage Examples

ID Card Masking : Keep the first 6 and last 2 characters, replace the middle with asterisks.

SELECT mask_inner('110101199901011234', 6, 2, '*') AS ID_card;

Result: 110101**********34 Phone Number Masking : Keep the first 3 and last 2 digits.

SELECT mask_inner('13912345678', 3, 2, '*') AS mobile;

Result:

139******78

Custom Masking Functions

For data types not covered by the plugin (e.g., email, Chinese names, amounts, addresses), custom SQL functions can be created. The author provides a set of functions in a GitHub repository.

Installation of Custom Functions

USE yourDB;
SOURCE /tmp/mask_amount.sql;
SOURCE /tmp/mask_email.sql;
SOURCE /tmp/mask_address.sql;
SOURCE /tmp/mask_chinese_name.sql;

These scripts define functions such as mask_email, mask_chinese_name, mask_amount, and mask_address.

Usage Examples

Email Masking SELECT mask_email('[email protected]') AS email; Result: ***@sohu.com Name Masking (Chinese)

SELECT mask_chinese_name('张三') AS name;
SELECT mask_chinese_name('张某三') AS name;

Results: 张* and 张** Amount Masking

SELECT mask_amount('343.34') AS money;
SELECT mask_amount('99999.34') AS money;

Results: **** for any input amount.

Address Masking

SELECT mask_address('北京市朝阳区霄云路8号') AS address;

Result:

北京市朝阳区*****

Conclusion

By combining the Percona data_masking plugin with custom masking functions, MySQL administrators can effectively protect sensitive data while preserving data usability and system performance. These techniques satisfy privacy regulations, boost customer trust, and reduce the risk of data breaches, turning robust data masking into a competitive advantage.