MySQL Security: Fix the Basics Before You Get Breached

Every MySQL breach I've seen comes down to the same five mistakes. Fix these and you're already ahead of most installations.

SQL Injection: Yeah, It's Still A Thing

Found SQL injection in a production app last week. Brand new React frontend hitting a Node API that just concatenated user input straight into queries.

-- This is what they had:
SELECT * FROM users WHERE email = '${userEmail}';

-- Works fine until someone enters: test@example.com'; DROP TABLE users;--
-- Then you have no users table and a very unhappy Monday morning

The developer said "but we validate the email format on the frontend." Sure, and I can disable JavaScript in about 2 seconds.

Same thing with JSON functions. Just because it says JSON doesn't mean it's magically safe from injection. JSON_EXTRACT() with user input is still user input going into SQL.

Default Passwords Are Everywhere

Check any MySQL server that's been running for more than six months. Guaranteed there's at least one account with a default password. Usually root with no password at all.

-- This works on more servers than it should:
mysql -u root -p
-- Just hit Enter when it asks for password

New MySQL versions use caching_sha2_password which is more secure, but it breaks old PHP applications. So instead of updating their code, developers just switch back to the weak authentication:

-- The "fix" I see everywhere:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Yeah, they literally use "password" as the password. I've seen this in production.

Exposed MySQL Servers

Found a MySQL server bound to 0.0.0.0 last month during a security scan. Default root password, accessible from anywhere on the internet. When I asked about it, they said "oh that was just for testing."

The server had been running like that for over a year.

## What they had in my.cnf:
bind-address = 0.0.0.0
## What it should be:
bind-address = 127.0.0.1

Happens all the time. Developer can't connect from their machine, googles the error, finds a Stack Overflow answer that says to bind to 0.0.0.0, and never changes it back.

Everyone Gets SUPER Privileges

Half the MySQL servers I audit have application accounts with way too many permissions. Usually because someone was fixing a production issue at 2am and just granted everything to make it work.

-- The panic fix:
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
-- "I'll fix this properly later" (they never do)

Your web application doesn't need to create databases or modify user accounts. Give it SELECT, INSERT, UPDATE, DELETE on the specific databases it needs. Nothing more.

The Test Database Nobody Deletes

MySQL creates a test database during installation that any user can access. It's supposed to be deleted after setup, but somehow never is.

-- Clean this up right now:
DROP DATABASE test;
DELETE FROM mysql.db WHERE db='test%';
FLUSH PRIVILEGES;

Found cryptocurrency miners running in test databases before. Also found backup copies of production data sitting there unencrypted because someone thought "it's just the test database."

What Actually Works in MySQL 8.4

Password Management

The dual password feature is actually useful. You can change a password without breaking every application connection:

-- Add new password, keep old one working
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPassword123!' RETAIN CURRENT PASSWORD;
-- Deploy apps with new password, then remove old one
ALTER USER 'app_user'@'%' DISCARD OLD PASSWORD;

This has saved me from weekend maintenance windows.

Encryption Is Slow But Works

TDE (transparent data encryption) encrypts your data with AES-256. Applications don't need to change, but encrypting large tables locks them up while it runs.

Tried encrypting a production table once during business hours. Bad idea. Locked everything for about two hours while it processed.

Fix These Five Things or Get Fucked

MySQL security breaks down to the same five problems every single time:

1. Default passwords - Change root, change everything
2. Exposed to internet - bind-address = 127.0.0.1, not 0.0.0.0
3. Too many privileges - Stop giving everyone SUPER privileges
4. Test database - Delete it and those anonymous users
5. No SSL - Force encrypted connections for production data

Fix these five things and you've prevented 95% of MySQL breaches. Everything else is compliance theater.

If auditors want documentation, MySQL 8.4 has checkboxes for whatever they're asking for. Most compliance requirements aren't technically challenging - they just want proof you're not completely incompetent.

Run mysql_secure_installation after every fresh MySQL install. It's basic but fixes the obvious problems like anonymous users and the test database.

For everything else, the official MySQL security docs are actually decent once you skip the marketing fluff.

Most authentication problems: weak passwords and dead accounts from people who left the company. Fix these two things before you worry about anything else.

Password Policies That Actually Work

Password Expiration Breaks Things
Password expiration sounds good until service accounts start failing because their passwords expired. Got woken up at 3am once because our monitoring system couldn't connect to the database.

-- Service accounts should never expire:
CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'ActuallySecurePassword2025!'
  PASSWORD EXPIRE NEVER;

-- Human accounts can expire:
ALTER USER 'admin'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Test password expiration in a staging environment that actually looks like production. Most "staging" environments are just developer laptops.

The Dual Password Thing Is Useful
This lets you change passwords without breaking everything:

-- Add new password, keep old one working
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass!' RETAIN CURRENT PASSWORD;
-- Update half your apps, test, update the rest
-- Then remove old password
ALTER USER 'app'@'%' DISCARD OLD PASSWORD;

Actually saved me from weekend maintenance windows.

User Account Cleanup

Check for old accounts regularly. Found accounts for contractors who left years ago still active with full database access.

-- Find accounts that haven't logged in recently
SELECT user, host,
       IFNULL(last_login, 'Never') as last_login
FROM performance_schema.accounts
WHERE user NOT IN ('mysql.sys', 'mysql.session');

Delete accounts you don't recognize. Better to break something and fix it than leave backdoors open.

Roles Make Life Easier

MySQL roles let you group permissions instead of managing them per user. Useful when you have more than a few people.

-- Create roles for different job functions
CREATE ROLE 'read_only_user';
CREATE ROLE 'app_user';
CREATE ROLE 'admin_user';

-- Grant permissions to roles
GRANT SELECT ON production_db.* TO 'read_only_user';
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'app_user';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user';

-- Assign roles to users
CREATE USER 'john'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT 'read_only_user' TO 'john'@'%';
SET DEFAULT ROLE 'read_only_user' TO 'john'@'%';

Network Restrictions Work

Restrict which IPs can connect to specific accounts:

-- Admin access only from office network
CREATE USER 'admin'@'10.0.1.%' IDENTIFIED BY 'AdminPass123!';

-- App servers from specific subnet
CREATE USER 'app_user'@'192.168.10.%' IDENTIFIED BY 'AppPass456!';

-- Don't do this (allows connections from anywhere):
-- CREATE USER 'user'@'%'

That's it. Basic user management and maybe some roles if you have more than a handful of people. Don't overcomplicate this shit unless compliance specifically requires it.

Most people encrypt MySQL because compliance demands it, not because they give a shit about security. But hey, when your server gets compromised, encrypted data is useless to attackers.

How MySQL Encryption Works

MySQL uses transparent data encryption (TDE) which encrypts your tables automatically. Applications don't need to change, which is nice.

There are two layers - master keys and tablespace keys. Master key encrypts the tablespace keys, tablespace keys encrypt your actual data. Sounds complicated but it means you can rotate the master key without re-encrypting huge tables.

-- Create encrypted table
CREATE TABLE sensitive_data (
  id INT PRIMARY KEY,
  ssn VARCHAR(11),
  email VARCHAR(255)
) ENCRYPTION='Y';

-- Or encrypt existing table (this locks it while running)
ALTER TABLE user_data ENCRYPTION='Y';

Warning: encrypting large existing tables locks them and uses extra disk space. Test on copies first.

Setting Up Encryption Keys

MySQL stores encryption keys in a keyring. Simplest setup is file-based:

## Add to my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

Create the keyring directory and make sure MySQL can read it:

mkdir /var/lib/mysql-keyring
chown mysql:mysql /var/lib/mysql-keyring
chmod 750 /var/lib/mysql-keyring

Get the permissions wrong and MySQL won't start. Ask me how I know.

Don't Forget Binary Logs

Binary logs contain every data change. Encrypt them too:

-- Enable binary log encryption (requires restart)
SET PERSIST binlog_encryption = ON;
-- Restart MySQL
-- Force rotation to encrypt existing logs
FLUSH BINARY LOGS;

Backup Encryption

Encrypt your backups or encrypted databases are pointless:

## mysqldump with encryption
mysqldump database_name | \\
  openssl enc -aes-256-cbc -salt -k \"BackupPassword123!\" > backup.sql.enc

## Restore
openssl enc -aes-256-cbc -d -salt -k \"BackupPassword123!\" \\
  -in backup.sql.enc | mysql database_name

Test restoring encrypted backups before you need them. Found out the hard way that I'd been using the wrong password for six months of backups.

SSL Connections

Force SSL connections for sensitive data:

-- Require SSL for specific users
CREATE USER 'sensitive_app'@'%'
  IDENTIFIED BY 'SecurePass123!'
  REQUIRE SSL;

-- Or force SSL for all connections
SET PERSIST require_secure_transport=ON;

MySQL generates SSL certificates automatically, which usually work fine unless you need specific certificate attributes.

That's encryption. Enable TDE for sensitive tables, encrypt your backups, force SSL. Done. Don't overthink this unless compliance specifically demands some exotic feature.