MySQL security configurations include a range of topics, along with their possible effects on MySQL servers and corresponding applications. In this article, I look at MySQL security encryption services, account-associated authorization systems, and other required security precautions to ensure protection against misuse and attacks. This security guide will help you protect sensitive data, even if the MySQL service is compromised at some point.

Most of the advanced MySQL security configurations require changes to the server's main configuration file my.cnf. This file is generally located inside the /etc/mysql directory or in the /opt/lampp/etc/ folder for LAMPP installations. However, you can locate the file with the find or locate command in Linux.

Encryption at Rest

By default, MySQL stores plaintext data inside InnoDB tables. Any non-admin user with access to these files can read tables and pose a security threat. Data at rest or in transit both require protection to avoid potential risk. MySQL transparent data encryption (TDE) enables encryption at rest to prevent information threats and privacy breaches, even if your system is compromised at some level.

MySQL provides TDE by enabling encryption at rest for physical files in the database. It encrypts data automatically on the go before writing to the storage and decrypts before reading. In this section, I will show you how to configure encryption at rest to ensure protection from physical data theft.

Getting Started

MySQL InnoDB tablespaces are stored in .ibd file format and are generally located in the /var/lib/mysql directory. You can also find them with the locate command:

$ locate ibd | less
/var/lib/mysql/testDB/testTB.ibd
/var/lib/mysql/sys/sys_config.ibd
...

Once located, you can retrieve sensitive plaintext data with the cat or strings command:

$ cat /var/lib/mysql/testDB/testTB.ibd | head -n 20

The command output will generate plaintext information containing raw data stored in the corresponding database table.

Key Management

The centralized key management solution offers electronic code book (ECB) and cipher block chaining (CBC) block encryption for tablespace keys and data encryption in the MySQL server. Encryption at rest for the InnoDB search engine and tablespaces involve a two-tier key architecture that implements easy key management and master key rotation:

• Tablespace key: An encrypted key stored in the tablespace header.
• Master key: A key that decrypts the tablespace key.

MySQL implements InnoDB tablespace encryption with the use of tablespace keys. After tablespace encryption, the master key encrypts the tablespace key to place it inside the tablespace header.

When an authenticated user accesses the encrypted table, InnoDB uses the master key to decrypt the tablespace key. The decrypted tablespace key allows you to perform read/write operations on data.

Master Encryption Key Rotation

A decrypted tablespace key never changes; instead, you can only change the master key by key rotation, which is an instance-level operation that re-encrypts all the tablespace keys and saves them back to the tablespace header:

mysql> alter instance rotate InnoDB master key;

However, the process does not re-encrypt or decrypt the tablespace data.

MySQL Keyring Plugin

Data-at-rest encryption in MySQL supports keyring plugins that enable internal server components to retrieve sensitive content. Here, I use a keyring_file plugin to store the keyring data inside local files in the server host. Configuration requires system variables in the mysql.cnf file, which is located inside the /etc/mysql/conf.d directory:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring-data/keyring

The system variable keyring_file_data defines the keyring_file data location for data storage.