Essential MySQL Security Practices: From Storage to Data Encryption

MySQL Server Security Overview

Security is essential for any system; only with security can stability and performance be pursued. Data is a company's wealth, and incidents like data deletions highlight the need for robust MySQL security.

Storage Security

For on‑premises MySQL servers, RAID is used to avoid single‑disk failures. Hardware RAID (recommended) or software RAID via mdadm can create RAID10 (mirrored + striped) for optimal read/write performance. Typical production uses eight 800 GB disks, creating two volume groups: rootvg for the OS and datavg for MySQL data, mounted under /data. This layout ensures data is written to logical volumes that map to mirrored physical disks.

In public‑cloud RDS, high‑availability deployments use a primary‑secondary (master‑slave) architecture, but the slave disks are usually not RAID‑protected.

Network Security

Whitelist : Use hardware firewalls or cloud security groups to allow only trusted application servers to connect to MySQL on port 3306.

External Access : MySQL should be reachable only from the internal network; public IPs must be disabled.

Operating‑System Security

Run MySQL under the mysql user, not root, to prevent file‑system privilege abuse.

Enforce strong, 20‑character passwords for all Linux accounts.

Close unnecessary ports (e.g., SSH, FTP, Telnet) on the database host.

Patch known OS vulnerabilities (e.g., ntp, vsftpd) before installing MySQL.

Disable direct password logins; use a jump‑server (e.g., JumpServer) with individual accounts and read‑only privileges.

Audit all actions performed via the jump‑server and retain command history with an enlarged HISTSIZE.

Configure resource limits in /etc/security/limits.conf (processes, open files, memory, etc.).

Perform regular full‑system and MySQL data backups (Linux directories and MySQL files such as data, binlog, undo log, redo log) and verify backup integrity; for Alibaba Cloud ECS use daily snapshots retained for seven days.

Account Security

Use 20‑character complex passwords for all database accounts.

Separate accounts per service in distributed systems; each service gets its own database and credentials.

Apply the principle of least privilege: grant only SELECT, INSERT, UPDATE, DELETE as needed.

Enable SSL/TLS for client connections to protect SQL text from packet sniffers, accepting the performance overhead of the handshake.

System Privileges

Avoid granting mysql.user modification rights to non‑root accounts.

Change passwords using ALTER USER … IDENTIFIED BY … rather than editing the table directly.

Be aware of authentication plugins: mysql_native_password vs. auth_socket.

Limit the PROCESS privilege; granting it to read‑only accounts can expose sensitive SQL statements.

Restrict the FILE privilege to prevent arbitrary file export/import.

Enable the MySQL Audit Plugin to log all SQL statements for troubleshooting and performance analysis.

Implement regular physical backups (e.g., Percona XtraBackup) for self‑hosted servers and configure automated backups for RDS.

Data Security

Implement disaster‑recovery across regions for critical business systems (e.g., transaction, payment).

Enforce data‑access approval workflows; changes must be submitted through a ticketing system with multi‑level approval and pre‑backup.

Use a database management platform to record all operations and audit logs.

Apply data masking for sensitive columns (name, email, phone, ID) using platforms such as Alibaba Cloud DMS; masked data appears as asterisks unless explicit permission is granted.

Encrypt InnoDB tablespaces (MySQL 5.7+) with ENCRYPTION='Y'; encryption uses AES with a key stored in the tablespace header and a master key on disk.

Keyring‑based encryption stores the master key within the tablespace, enhancing protection against file theft.

Delayed Replication

MySQL’s default master‑slave replication is near‑real‑time. Configuring a delayed slave (e.g., one‑hour lag) allows rapid recovery from accidental deletions by replaying the master’s binlog after the delay.

Conclusion

MySQL security spans server hardening, network controls, OS safeguards, account management, privilege restrictions, auditing, backup, encryption, and disaster‑recovery. Applying these measures helps prevent data breaches and ensures stable, high‑performance database operations.