14 Essential MySQL Security Practices to Protect Your Data

Scenario

Data security is a critical aspect of internet security; breaches can be irreversible and catastrophic.

Solution Overview

Key measures include ensuring application security, controlling system and database user permissions, protecting sensitive data, maintaining data integrity, standardizing operations, and properly segmenting business responsibilities.

Application Security

Delete default MySQL accounts and the test database to eliminate unnecessary attack surface.

mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql> delete from user where not(host='localhost' and user='root');
mysql> flush privileges;

Disable LOCAL INFILE

The LOCAL INFILE command can be abused to read arbitrary files such as /etc/passwd. Disable it by adding the following parameter to the MySQL configuration under [mysqld]: set-variable=local-infile=0 Example of the risky command and a simpler alternative:

mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE table1;
# Simpler method:
mysql> SELECT load_file("/etc/passwd");

Control User Permissions

Manage database users by restricting IP access to trusted networks only, and define roles with specific privileges:

view : read‑only access, no modifications – used for data verification.

update : can modify data but not schema – used by running applications.

operate : can alter schema, add/modify tables, but cannot drop tables or databases – reserved for product releases.

…additional roles as needed.

Encrypt Sensitive Information

Use hashing algorithms such as MD5 or SHA to store passwords and other confidential fields, reducing the impact of data loss.

Ensure Data Integrity

Implement replication (master‑slave or master‑master) to avoid single points of failure.

Regularly perform backup and restore procedures.

Standardize Daily Operations

Adopt the principle of least privilege; use read‑only accounts when possible.

For data or schema changes, require dual‑person approval or automated deployment tools to minimize human error.

Test changes in a staging environment before applying to production.

Business Segmentation

Minimize database usage by leveraging caching and static content.

Apply sharding and separate sensitive from frequently accessed data at the table design level (e.g., separate user profile from payment information).

Continuously optimize SQL queries to prevent resource exhaustion.

Verification Method

Validate permissions by logging in with different accounts and confirming that each can only perform its intended actions.

References

Original article: http://www.yunweipai.com/archives/8351.html

Related resources: 14 Best Practices for Securing MySQL, MySQL Security Configuration, High Performance MySQL.