MySQL Encryption at Rest and in Transit: InnoDB, TLS, and Binary Log Encryption

-- Enable keyring plugin (MySQL 8.0+)
-- Add to my.cnf:
-- early-plugin-load=keyring_file.so
-- keyring_file_data=/var/lib/mysql-keyring/keyring

-- Encrypt a table
CREATE TABLE sensitive_data (
  id INT PRIMARY KEY,
  payload TEXT
) ENCRYPTION='Y';

-- Encrypt existing table (online DDL)
ALTER TABLE existing_table ENCRYPTION='Y';

-- Check encryption status
SELECT name, encryption
FROM information_schema.innodb_tablespaces
WHERE encryption = 'Y';

[mysqld]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
default_table_encryption = ON

# Generate CA and server certs
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

# This creates: ca.pem, server-cert.pem, server-key.pem, client-cert.pem, client-key.pem

[mysqld]
ssl_ca   = /var/lib/mysql/ca.pem
ssl_cert = /var/lib/mysql/server-cert.pem
ssl_key  = /var/lib/mysql/server-key.pem
require_secure_transport = ON

-- Require TLS per user
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- Or require specific cipher
ALTER USER 'app_user'@'%' REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Verify TLS is active on a connection
SHOW STATUS LIKE 'Ssl_cipher';

[mysqld]
binlog_encryption = ON
-- Requires keyring plugin to be loaded

-- Application-level encryption for specific columns
SET @key = SHA2('my-secret-key', 256);

INSERT INTO users (email, ssn_encrypted)
VALUES ('user@example.com', AES_ENCRYPT('123-45-6789', @key));

SELECT AES_DECRYPT(ssn_encrypted, @key) AS ssn
FROM users WHERE email = 'user@example.com';