Secure MySQL: users, TLS, roles and least privilege

A MySQL with default settings after mysql_secure_installation is better than at first startup, but still not enough for production: MySQL listens on every interface, the root account is an all-powerful superuser, no TLS encryption is enforced, roles are not separated, and connections are not audited. Each of these is an exploitable attack vector.

This guide works layer by layer: first understand authentication (caching_sha2_password), then create restricted users (host + privileges), then encrypt the transport (TLS), and finally restrict the network and trace actions. The examples come from a MySQL 8.4 LTS lab: the note below gives the version and the system they were captured on.

Version used

This guide relies on MySQL 8.4 LTS on Debian 12. The version installable from the Oracle repository on 2026-10-20 is 8.4.11, and that repository also serves Debian 13 (trixie): the examples in this guide were captured on Debian 12 (bookworm), while the associated lab runs on Debian 13. MySQL 8.4 disabled mysql_native_password by default (deprecated since 8.0.34, removed in 9.0) and carried on splitting administrative privileges into finer ones (FLUSH_PRIVILEGES, OPTIMIZE_LOCAL_TABLE, ROLE_ADMIN…).

What you will learn

• Understand caching_sha2_password authentication and the end of mysql_native_password
• Create users restricted by host, with strong passwords
• Separate roles with the admin / application / monitoring / backup pattern
• Enable TLS encryption and force encrypted connections
• Restrict network exposure (bind_address, skip_name_resolve)
• Configure connection auditing, the slow query log and connection_control

In what context?

You need to secure MySQL in these situations:

• You are moving from a development lab (localhost, root without network password) to production
• Your team shares the root account for every operation, and you want to separate responsibilities
• You must restrict the network listening of MySQL, which listens on all interfaces by default, before opening it to a remote application with encrypted connections
• An old client (PHP 5.x, Python connector mysqlclient < 2.0) refuses to connect after the migration to MySQL 8.4 (authentication problem)
• You are preparing a security audit and must prove the access policy, the encryption and the traceability

What this guide does not cover

MySQL security touches many neighbouring subjects covered elsewhere on this site. This page focuses on access control, transport encryption and network restriction; engine tuning, backup strategy and performance observation have their own guides: Server configuration for the engine, Backup and restore, Monitoring and maintenance for performance, and Replication.

Prerequisites

• MySQL installed and the service running (see the Installation guide)
• Access to the root account through mysql -u root -p
• Know the basics of the mysql client (see the Getting started with the mysql client guide)
• Root or sudo access on the server (to edit mysqld.cnf and manage certificates)

MySQL authentication

In MySQL, the authentication method is not a global rule: it is attached to each account as a plugin. Two accounts on the same server can therefore use different mechanisms, which explains why a version migration can break some clients and not others. Understanding which plugin does what is the prerequisite for any other hardening measure.

caching_sha2_password: the default since MySQL 8.0

MySQL uses a system of authentication plugins: each user account is associated with a plugin that handles password verification. Since MySQL 8.0, the default plugin is caching_sha2_password.

default_authentication_plugin removed in 8.4

The default_authentication_plugin variable no longer exists in MySQL 8.4. The default plugin is now controlled by authentication_policy (detailed below in its own section).

To check which plugin each account actually uses:

SELECT user, host, plugin
FROM mysql.user
WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')
ORDER BY user, host;

+-------------+-----------+-----------------------+
| user        | host      | plugin                |
+-------------+-----------+-----------------------+
| admin_app   | localhost | caching_sha2_password |
| app_writer  | 10.0.1.%  | caching_sha2_password |
| root        | localhost | caching_sha2_password |
+-------------+-----------+-----------------------+

How it works: the password is hashed with SHA-256 and then cached on the server side. A user's first authentication requires a secure exchange (a TLS connection, or an RSA key exchange). Later authentications use the cache, hence the name "caching".

Plugin	Algorithm	Security	Default since
caching_sha2_password	SHA-256 + cache	Strong	MySQL 8.0
mysql_native_password	Double SHA-1	Weak (vulnerable to replay attacks)	MySQL 4.1 to 5.7
sha256_password	SHA-256 without cache	Correct but slow	-
auth_socket	Unix identity (like peer in PostgreSQL)	Strong	-

Swipe to see more

Difference with PostgreSQL

PostgreSQL uses scram-sha-256 in pg_hba.conf, a standard (RFC 7677) that resists replay attacks. MySQL chose its own protocol, caching_sha2_password, which offers a similar level of security but is not SCRAM-compatible. The approach differs: in PostgreSQL, the method is defined per line in pg_hba.conf; in MySQL, the plugin is attached to the user account.

mysql_native_password: disabled by default in 8.4

MySQL 8.4 disabled mysql_native_password by default. The plugin is no longer loaded at startup:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME = 'mysql_native_password';

+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| mysql_native_password | DISABLED      |
+-----------------------+---------------+

Impact: old clients or connectors that do not support caching_sha2_password can no longer connect. This is the case for:

• PHP with mysqli + an old libmysqlclient (PHP < 7.4 without mysqlnd)
• Python with mysqlclient < 2.0
• Some legacy tools (old MySQL 5.x clients)

If you must temporarily re-enable mysql_native_password for a migration:

[mysqld]
mysql_native_password = ON

Only re-enable mysql_native_password temporarily

mysql_native_password uses a double SHA-1 hash, vulnerable to replay attacks and rainbow tables. Deprecated since MySQL 8.0.34, disabled by default in 8.4, and removed in MySQL 9.0. Re-enabling it must be temporary: update your clients and migrate the accounts to caching_sha2_password.

authentication_policy: multi-level control

MySQL 8.4 supports multifactor authentication (MFA). The authentication_policy parameter defines the requirements for each factor:

SHOW VARIABLES LIKE 'authentication_policy';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| authentication_policy | *,,   |
+-----------------------+-------+

The value *,, means: the first factor is mandatory (any plugin), the second and third are optional. To force caching_sha2_password as the first factor:

SET PERSIST authentication_policy = 'caching_sha2_password,,';

User management

A MySQL account is not identified by its name alone: it is the user + host pair that counts. This changes how you reason, since network restriction and password control are declared in the same place. The commands below cover creation, modification, locking and the complexity policy.

CREATE USER with host restriction

Unlike PostgreSQL, which controls access by IP in pg_hba.conf, MySQL builds the network restriction directly into the user account through the @host part:

-- Local connection only
CREATE USER 'admin_app'@'localhost'
  IDENTIFIED BY 'AdminApp2026!Secure';

-- Connection from an application network
CREATE USER 'app_writer'@'10.0.1.%'
  IDENTIFIED BY 'AppWriter2026!Prod';

-- Connection from one specific IP
CREATE USER 'monitoring'@'10.0.1.50'
  IDENTIFIED BY 'Monitor2026!Read';

@host pattern	Meaning	Use
'localhost'	Unix socket; a TCP connection to 127.0.0.1 does not always match it (see below)	Admin, local CLI
'10.0.1.%'	The whole 10.0.1.x subnet	Application network
'10.0.1.50'	A single IP	Monitoring, a specific service
'%'	Any IP	Dangerous, to be avoided

Swipe to see more

MySQL evaluates user@host as a unique pair: 'alice'@'localhost' and 'alice'@'10.0.1.%' are two distinct accounts with potentially different passwords and privileges.

MySQL matches the host as the connection presents it. Measured on MySQL 8.4: an @'localhost' account refuses a TCP connection to 127.0.0.1, with an Access denied that names 'account'@'127.0.0.1', a host nobody declared. An account that must connect over the network, even from the local machine, is therefore declared with its address, not with localhost.

@'%': the most dangerous rule

Creating a user with @'%' lets it connect from any IP. This is the equivalent of host all all 0.0.0.0/0 in pg_hba.conf. Never use @'%' for an account with write or administration privileges.

ALTER USER: change the password and the plugin

ALTER USER covers both needs: changing the secret, or switching an account to another authentication plugin. The IDENTIFIED WITH clause requires a new password, because the old hash cannot be converted from one algorithm to another.

-- Change the password
ALTER USER 'app_writer'@'10.0.1.%'
  IDENTIFIED BY 'NewPassword2026!Strong';

-- Migrate an account from mysql_native_password to caching_sha2_password
ALTER USER 'legacy_account'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'NewPassword2026!';

To list the accounts and their authentication plugin:

SELECT user, host, plugin, password_expired, account_locked
FROM mysql.user
WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')
ORDER BY user, host;

+-------------+-----------+-----------------------+------------------+----------------+
| user        | host      | plugin                | password_expired | account_locked |
+-------------+-----------+-----------------------+------------------+----------------+
| admin_app   | localhost | caching_sha2_password | N                | N              |
| app_writer  | 10.0.1.%  | caching_sha2_password | N                | N              |
| labadmin    | localhost | caching_sha2_password | N                | N              |
| monitoring  | 10.0.1.50 | caching_sha2_password | N                | N              |
| root        | localhost | caching_sha2_password | N                | N              |
+-------------+-----------+-----------------------+------------------+----------------+

Account locking and password expiration

MySQL offers additional controls on accounts:

-- Lock an account (the connection is refused)
ALTER USER 'alice'@'localhost' ACCOUNT LOCK;

-- Unlock
ALTER USER 'alice'@'localhost' ACCOUNT UNLOCK;

-- Force a password change at the next connection
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE;

-- Automatic expiration every 90 days
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Password history (prevents reuse)
ALTER USER 'alice'@'localhost' PASSWORD HISTORY 5;

-- Maximum number of failed attempts before locking
CREATE USER 'bob'@'localhost'
  IDENTIFIED BY 'Bob2026!Secure'
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1;  -- automatic unlock after 1 day

validate_password: enforce complexity

The validate_password component is usually installed by mysql_secure_installation. Check:

SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

Policy	Requirements
LOW	Minimum length only
MEDIUM	Length + uppercase + lowercase + digit + special character
STRONG	MEDIUM + not in a dictionary of common passwords

Swipe to see more

To switch to STRONG:

SET PERSIST validate_password.policy = STRONG;
SET PERSIST validate_password.length = 12;

The role system

Granting privileges account by account becomes unmanageable as soon as a team grows: every new table forces you to go over all the users again. Roles, available since MySQL 8.0, group a set of privileges under a single name that you then assign to accounts. One trap comes with them: a granted role is not necessarily active at login.

CREATE ROLE and GRANT role TO user

A role is a named set of privileges that you assign to several users. The commands below create three roles, grant them rights on the lab database, then assign them to three accounts:

-- Create the roles (no LOGIN: they are privilege groups)
CREATE ROLE 'role_reader', 'role_writer', 'role_admin';

-- Grant privileges to the roles
GRANT SELECT ON lab_mysql.* TO 'role_reader';
GRANT SELECT, INSERT, UPDATE, DELETE ON lab_mysql.* TO 'role_writer';
GRANT ALL PRIVILEGES ON lab_mysql.* TO 'role_admin';

-- Assign the roles to the users
GRANT 'role_reader' TO 'monitoring'@'10.0.1.50';
GRANT 'role_writer' TO 'app_writer'@'10.0.1.%';
GRANT 'role_admin' TO 'admin_app'@'localhost';

A role has no separate table: it is a row of mysql.user, like an account. What sets them apart, by default, is the account_locked column, which CREATE ROLE sets to Y (the role is locked, it cannot log in) and which CREATE USER leaves at N; an ALTER USER can reverse this lock. A "role" created by CREATE USER would therefore be an account that can log in, which is what you want to avoid.

SET DEFAULT ROLE and role activation

By default, roles are not activated automatically at login. The user must either activate them manually (SET ROLE) or you must configure a default role:

-- Activate all roles by default for a user
SET DEFAULT ROLE ALL TO 'app_writer'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'monitoring'@'10.0.1.50';
SET DEFAULT ROLE ALL TO 'admin_app'@'localhost';

Or enable automatic activation globally for all users:

SET PERSIST activate_all_roles_on_login = ON;

Classic trap: the role is granted but not activated

If, after a GRANT 'role_reader' TO 'monitoring'@'10.0.1.50', the monitoring user still cannot read the tables, the role is probably not activated. Check the session with SELECT CURRENT_ROLE();: if the result is NONE, activate the default roles with SET DEFAULT ROLE ALL TO user@host;.

Fine-grained privileges in MySQL 8.4

MySQL 8.4 broke the old SUPER privilege down into finer privileges:

Fine-grained privilege	Replaces (old SUPER)	Use
FLUSH_PRIVILEGES	FLUSH PRIVILEGES	Reload the privilege tables
FLUSH_TABLES	FLUSH TABLES	Flush the table caches
OPTIMIZE_LOCAL_TABLE	OPTIMIZE TABLE	Optimize tables
SYSTEM_VARIABLES_ADMIN	SET GLOBAL/PERSIST	Runtime configuration
CONNECTION_ADMIN	Manage other users' connections	DBA only
ROLE_ADMIN	GRANT role TO user	Role management

Swipe to see more

SET_ANY_DEFINER: a special case

SET_ANY_DEFINER is not a direct replacement for SUPER in the way the privileges above are. It allows you to create views, triggers or routines with a DEFINER different from the current user. Grant it only to accounts that manage schema migrations.

Rule: never grant SUPER; use the fine-grained privileges that match the exact need.

Least privilege: the recommended architecture

Least privilege means granting each account only the rights strictly necessary for its function, and only from the machines it works from. The benefit shows on the day of a leak: a stolen application credential must not allow reading the mysql schema, dropping a table, or connecting from the Internet.

Separate the accounts

Five profiles are enough to cover most installations. Note that the administration and backup accounts stay confined to the local socket: they have no reason to be reachable from the network.

Role	Privileges	Typical account	Host
DBA	ALL PRIVILEGES + administration	admin_app@localhost	Local socket only
Application (write)	SELECT, INSERT, UPDATE, DELETE on the schema	app_writer@10.0.1.%	Application network
Application (read)	SELECT on the schema	app_reader@10.0.1.%	Application network
Monitoring	PROCESS, REPLICATION CLIENT, SELECT on performance_schema	monitoring@10.0.1.50	IP of the monitoring server
Backup	SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER	backup@localhost	Local only

Swipe to see more

1. Create the monitoring account (performance reading + processes):

   CREATE USER 'monitoring'@'10.0.1.50'
     IDENTIFIED BY 'Monitor2026!Readonly';
   GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitoring'@'10.0.1.50';
   GRANT SELECT ON performance_schema.* TO 'monitoring'@'10.0.1.50';
   GRANT SELECT ON sys.* TO 'monitoring'@'10.0.1.50';

2. Create the backup account (mysqldump, xtrabackup):

   CREATE USER 'backup'@'localhost'
     IDENTIFIED BY 'Backup2026!Secure';
   GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
     ON *.* TO 'backup'@'localhost';
   GRANT BACKUP_ADMIN ON *.* TO 'backup'@'localhost';

3. Check the privileges:

   SHOW GRANTS FOR 'monitoring'@'10.0.1.50';

   +-------------------------------------------------------------------+
   | Grants for monitoring@10.0.1.50                                   |
   +-------------------------------------------------------------------+
   | GRANT PROCESS, REPLICATION CLIENT ON *.* TO `monitoring`@`10.0.1.50` |
   | GRANT SELECT ON `performance_schema`.* TO `monitoring`@`10.0.1.50`   |
   | GRANT SELECT ON `sys`.* TO `monitoring`@`10.0.1.50`                  |
   +-------------------------------------------------------------------+

Partial revokes: revoke on a specific schema

MySQL 8.0.16+ supports partial revokes, the ability to revoke a global privilege for a specific schema. First enable the feature:

SET PERSIST partial_revokes = ON;

Example: give a global SELECT except on the mysql schema (which holds the passwords):

GRANT SELECT ON *.* TO 'app_reader'@'10.0.1.%';
REVOKE SELECT ON mysql.* FROM 'app_reader'@'10.0.1.%';

Side effect of partial_revokes

When partial_revokes = ON, unescaped % and _ characters in schema names are interpreted as literal characters in partial privilege assignments, not as wildcards. For example, GRANT SELECT ON 'test_%'.* ... only targets the literal schema test_%, not every schema starting with test_.

Avoid GRANT ALL and SUPER

These three shortcuts turn up in almost every inherited database, often set "just to get the application working" and never removed.

Avoid	Why	Alternative
GRANT ALL PRIVILEGES ON *.*	Gives all rights on all databases	Grant per schema + exact privilege
GRANT SUPER	Monolithic privilege on its way to deprecation	Use the fine-grained 8.4 privileges
GRANT ALL ON db.*	Includes DROP, ALTER, GRANT	List SELECT, INSERT, UPDATE, DELETE

Swipe to see more

TLS encryption

Without TLS, the MySQL protocol travels in cleartext: queries, results and, depending on the authentication plugin, part of the password negotiation. Any machine on the network path can read them. MySQL is able to encrypt from installation thanks to auto-generated certificates, but nothing forces clients to use them until you require it explicitly.

Check the current TLS state

These four variables tell you whether the server can encrypt; they say nothing about what clients actually do.

SHOW VARIABLES WHERE Variable_name IN ('ssl_ca','ssl_cert','ssl_key','tls_version');

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| ssl_ca        | ca.pem          |
| ssl_cert      | server-cert.pem |
| ssl_key       | server-key.pem  |
| tls_version   | TLSv1.2,TLSv1.3|
+---------------+-----------------+

have_ssl and have_openssl removed in 8.4

The have_ssl and have_openssl variables no longer exist in MySQL 8.4. To check that TLS is active, inspect the ssl_* variables above and the session status Ssl_cipher.

Check whether the current connection is encrypted:

SHOW SESSION STATUS LIKE 'Ssl_cipher';

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

If Ssl_cipher is empty, the connection is not encrypted.

Certificates auto-generated by MySQL

During the first initialization (mysqld --initialize), MySQL automatically generates self-signed certificates in the datadir:

ls -la /var/lib/mysql/*.pem

-rw------- 1 mysql mysql 1704 Apr 13 08:30 /var/lib/mysql/ca-key.pem
-rw-r--r-- 1 mysql mysql 1131 Apr 13 08:30 /var/lib/mysql/ca.pem
-rw-r--r-- 1 mysql mysql 1131 Apr 13 08:30 /var/lib/mysql/server-cert.pem
-rw------- 1 mysql mysql 1704 Apr 13 08:30 /var/lib/mysql/server-key.pem
-rw-r--r-- 1 mysql mysql 1131 Apr 13 08:30 /var/lib/mysql/client-cert.pem
-rw------- 1 mysql mysql 1704 Apr 13 08:30 /var/lib/mysql/client-key.pem

These self-signed certificates are enough for transport encryption but do not let the client verify the identity of the server. In production, use certificates signed by an internal PKI, that is, your organization's certification infrastructure, whose authority signs your servers' certificates.

Configure TLS with custom certificates

The three paths point to the certificate authority, the server certificate and its private key. That key must belong to the mysql user and be readable only by it, otherwise the server refuses to start.

[mysqld]
ssl_ca   = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key  = /etc/mysql/ssl/server-key.pem
tls_version = TLSv1.2,TLSv1.3

# Permissions on the private key
chown mysql:mysql /etc/mysql/ssl/server-key.pem
chmod 600 /etc/mysql/ssl/server-key.pem

sudo systemctl restart mysql

Enforce TLS in CREATE USER (REQUIRE SSL / X509)

MySQL lets you force TLS at the user account level, unlike PostgreSQL, which controls it in pg_hba.conf:

-- Force TLS (encryption only)
CREATE USER 'app_secure'@'10.0.1.%'
  IDENTIFIED BY 'Secure2026!TLS'
  REQUIRE SSL;

-- Force TLS with a client certificate (mutual authentication)
CREATE USER 'app_mutual'@'10.0.1.%'
  IDENTIFIED BY 'Mutual2026!Cert'
  REQUIRE X509;

-- On an existing account
ALTER USER 'app_writer'@'10.0.1.%' REQUIRE SSL;

REQUIRE	Encryption	Verifies client certificate	Use
NONE	Not required	No	Default, dangerous in production
SSL	Yes	No	Recommended minimum
X509	Yes	Yes	Automation, critical services
ISSUER '...' SUBJECT '...'	Yes	Yes + checks the issuer/subject	Very restrictive

Swipe to see more

REQUIRE targets one account. To impose encryption on the whole instance, the require_secure_transport = ON variable in the [mysqld] section refuses every unencrypted TCP connection, all accounts included; the local Unix socket remains allowed. This is a different action, to take when no account should ever connect in cleartext over the network.

Test of a connection with forced TLS:

# Encrypted connection: works
mysql -u app_secure -h 10.0.1.5 -p --ssl-mode=REQUIRED

# Connection without TLS: fails
mysql -u app_secure -h 10.0.1.5 -p --ssl-mode=DISABLED
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Why a cleartext connection is refused: two errors, two origins

The app_secure account uses the default plugin of MySQL 8.4 (the authentication plugin, here caching_sha2_password), and it is this plugin that refuses here: it does not want to send the password over a cleartext channel, and it says so. Measured on MySQL 8.4, an account with REQUIRE SSL reached without TLS returns two different errors depending on its plugin:

Account plugin	Error returned	Who refuses
caching_sha2_password (default since 8.0)	ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.	The plugin, before REQUIRE SSL is even consulted
mysql_native_password	ERROR 1045 (28000): Access denied for user '…'@'…' (using password: YES)	REQUIRE SSL itself

Swipe to see more

Both errors are correct, each in its own case. The 2061 is the error of a full authentication, the first one for an account as long as its hash is not cached (see "How it works" above); the case where the hash is already cached was not measured here. To get the 1045, you need a native account, so you must re-enable the plugin, which is only acceptable temporarily (see the section on mysql_native_password). Many documents quote the 1045 because it was the expected error when the native plugin was the default.

The point to remember: the refusal does not come from the same place, and only the 2061 says why. An "Access denied" sends you looking for a password, while the password is right and the channel is the problem. Faced with an Access denied on a REQUIRE SSL account, first reconnect with --ssl-mode=REQUIRED before touching the password.

Encryption vs identity verification (client side)

REQUIRE SSL on the account forces encryption of the transport, but does not check that the client is connecting to the right server. On the client side, the --ssl-mode parameter controls the level of verification:

--ssl-mode	Encryption	Verifies server certificate	Verifies hostname	Use
DISABLED	No	No	No	Forbidden in production
PREFERRED	If possible	No	No	Default, encrypts if the server supports it
REQUIRED	Yes	No	No	Forces encryption, but not verification
VERIFY_CA	Yes	Yes	No	Checks that the certificate is signed by a trusted CA
VERIFY_IDENTITY	Yes	Yes	Yes	The safest, also checks that the hostname matches the certificate's CN/SAN

Swipe to see more

VERIFY_IDENTITY and self-signed certificates

The certificates auto-generated by MySQL during initialization do not contain a Subject Alternative Name (SAN) matching the server's hostname. --ssl-mode=VERIFY_IDENTITY will fail with these certificates. In production, use certificates signed by an internal PKI with the server's hostname in the SAN.

Recommendation: in production, configure REQUIRE SSL on the server side and --ssl-mode=VERIFY_CA (or VERIFY_IDENTITY) on the client side to protect against man-in-the-middle attacks.

Reload certificates without a restart

When you renew the certificates, reload them without restarting MySQL:

ALTER INSTANCE RELOAD TLS;

Then check:

SHOW STATUS LIKE 'Ssl_server_not_after';

Restrict network access

Application access control does not replace network restriction: a service that does not listen on an interface cannot be attacked through it. Three independent levers add up here, from the closest to the server to the widest: listening (bind_address), name resolution (skip_name_resolve) and the host firewall.

bind_address: which interfaces does MySQL listen on?

The MySQL 8.4 default for bind_address is *: the server listens on all interfaces, IPv4 and IPv6 (MySQL 8.4 documentation, "Server System Variables" page). Oracle packages do not change it: the .deb ships a mysqld.cnf that only contains pid-file, socket, datadir and log-error, and the EL9 .rpm ships an /etc/my.cnf equally free of any bind-address line. A fresh installation is therefore open to the network, and the restriction is a setting you must apply yourself. With a package from another source, read the actual value rather than assuming it:

SELECT @@bind_address, @@mysqlx_bind_address;

+----------------+-----------------------+
| @@bind_address | @@mysqlx_bind_address |
+----------------+-----------------------+
| *              | *                     |
+----------------+-----------------------+

To restrict listening, set both variables in the [mysqld] section of /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu) or /etc/my.cnf (RHEL/Rocky), then restart the service. bind_address accepts several addresses separated by commas:

[mysqld]
bind_address        = 127.0.0.1,10.0.1.5
mysqlx_bind_address = 127.0.0.1

Trap: bind_address does not restrict port 33060 (the X protocol), which has its own variable, mysqlx_bind_address. Measured on MySQL 8.4: setting bind_address = 127.0.0.1 alone leaves 33060 open on all interfaces, and you wrongly believe you have closed the server. The "How do you restrict MySQL's network listening?" section of the Installation guide details the check with ss.

The table ranks the bind_address values from the most closed to the most open:

Configuration	Exposure	Use
127.0.0.1	Local only	Dev, lab, application on the same machine
127.0.0.1,10.0.1.5	Local + one network IP	Production, dedicated application network
0.0.0.0	All IPv4 interfaces	Not recommended unless behind a firewall/proxy
* (default)	All interfaces, IPv4 and IPv6	Not recommended unless behind a firewall/proxy

Swipe to see more

skip_name_resolve: disable reverse DNS

By default, MySQL performs a reverse DNS lookup for every connection. This adds delay and creates a dependency on DNS. Disable it:

[mysqld]
skip_name_resolve = ON

Once it is enabled, use only IP addresses in the @host part of CREATE USER accounts.

System firewall

Add a system firewall on top of bind_address and mysqlx_bind_address. The examples only close port 3306: if you keep the X protocol, apply the same rule to port 33060, which otherwise stays open as long as mysqlx_bind_address is not restricted:

nftables (Debian 12+)

nft add rule inet filter input tcp dport 3306 ip saddr 10.0.1.0/24 accept
nft add rule inet filter input tcp dport 3306 drop

ufw (Ubuntu)

sudo ufw allow from 10.0.1.0/24 to any port 3306
sudo ufw deny 3306

firewalld (RHEL/Rocky)

sudo firewall-cmd --new-zone=mysql --permanent
sudo firewall-cmd --zone=mysql --add-source=10.0.1.0/24 --permanent
sudo firewall-cmd --zone=mysql --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

Audit connections and queries

Knowing who connected, from where and to run what is the last layer of hardening: it blocks nothing, but it lets you detect an intrusion and measure its extent. MySQL Community does not provide a complete audit plugin; you have to assemble several existing logs to get usable traceability.

connection_control: limit connection attempts

The connection_control plugin slows down connections after a certain number of failures, a protection against brute force:

-- Install the plugin
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
  SONAME 'connection_control.so';

-- Configure
SET PERSIST connection_control_failed_connections_threshold = 3;
SET PERSIST connection_control_min_connection_delay = 1000;      -- 1 second
SET PERSIST connection_control_max_connection_delay = 86400000;   -- 24 hours max

After 3 authentication failures, MySQL imposes an increasing delay before each new attempt.

Check the failed attempts:

SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

Slow query log for query auditing

The slow query log, configured in the Configuration guide, is also an audit tool: it records the queries that exceed a time threshold and, if log_queries_not_using_indexes is enabled, those that use no index.

Audit plugin (MySQL Enterprise) vs open source alternatives

Structured auditing, which records every connection and every statement in a format a SIEM can use (a tool that centralizes security logs), does not exist in MySQL Community. The table compares the four available options and their license, a decisive criterion if you have to meet a compliance requirement.

Solution	License	Features
MySQL Enterprise Audit	Commercial	Full audit (connections, queries, DDL), XML/JSON format, filtering by user/event
Percona Audit Log	Open source (Percona Server)	Compatible with Enterprise Audit, XML/JSON format
MariaDB Audit Plugin	Open source	Compatible with MySQL Community (with version limitations)
McAfee MySQL Audit Plugin	Open source (archived)	Basic audit, no longer maintained

Swipe to see more

For MySQL Community, the combination of slow_query_log + general_log (temporary) + connection_control + log_error covers basic audit needs. For a complete audit, consider Percona Server or MySQL Enterprise.

Hardening checklist

These twelve checks take a few minutes on an existing instance and give a report of the configuration state, to attach to an audit. Run each command as the administrator account and compare the output with the "Expected" column: any gap points directly to the section of the guide to go back to. These commands read settings, not behaviours: the section after the table says where each one stops.

Check	Command	Expected
No account without a password	SELECT user, host FROM mysql.user WHERE authentication_string = '';	No rows
No @'%' for the admin	SELECT user, host FROM mysql.user WHERE host = '%' AND Super_priv = 'Y';	No rows
TLS configured	SHOW VARIABLES LIKE 'ssl_cert';	Path to the certificate (not empty)
Current connection encrypted	SHOW SESSION STATUS LIKE 'Ssl_cipher';	Not empty
validate_password active	SHOW VARIABLES LIKE 'validate_password.policy';	MEDIUM or STRONG
mysql_native_password disabled	SELECT PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME='mysql_native_password';	DISABLED
bind_address restricted	SHOW VARIABLES LIKE 'bind_address';	Not 0.0.0.0 nor *
mysqlx_bind_address restricted	SHOW VARIABLES LIKE 'mysqlx_bind_address';	Not 0.0.0.0 nor *
skip_name_resolve enabled	SHOW VARIABLES LIKE 'skip_name_resolve';	ON
Separate roles	SELECT user, host FROM mysql.user;	Distinct admin, writer, reader, monitoring, backup
connection_control installed	SHOW PLUGINS LIKE 'CONNECTION_CONTROL%';	ACTIVE
Slow query log enabled	SHOW VARIABLES LIKE 'slow_query_log';	ON

Swipe to see more

What these checks do not prove

Reading a variable proves that a setting is in place, not that the expected behaviour follows from it. Four checks in the table stop there, and an attentive auditor will ask the question:

• Separate roles: the list of accounts shows that they exist, not what they can do. The proof is SHOW GRANTS FOR on each one.
• TLS configured: ssl_cert shows that the server knows how to encrypt, not that a client is forced to. The proof is the refusal of a cleartext connection on a REQUIRE SSL account, obtained in the "Enforce TLS in CREATE USER" section.
• Current connection encrypted: Ssl_cipher only holds for the session asking the question. Run the check from a client machine, over the network (-h), and not from the server's local socket.
• Restricted listening: bind_address and mysqlx_bind_address give the setting, not the ports actually open. The check with ss, described in the Installation guide, remains the proof.

Put it into practice

The lab asks you to properly redo a setup left open: two roles that carry the privileges, an application account that receives only one of them, and TLS required. The validation reads no GRANT in the catalog: it connects with the account and looks at what it can do, because that is the only way to tell a granted right from an effective one.

Three facts already met in this guide explain this method. A granted role is not active: the GRANT appears in the catalog while CURRENT_ROLE() answers NONE and the slightest SELECT fails. An @localhost account refuses a TCP connection to 127.0.0.1, and the error names a host nobody declared. Finally, the error code of a cleartext refusal depends on the account's plugin: ERROR 2061 with caching_sha2_password, ERROR 1045 with mysql_native_password. The lab therefore requires no code: it requires that cleartext be refused and that the encrypted connection succeed.

Guided lab Practice

Learn how to do it

Grant through roles, activate them, and refuse an unencrypted login

$ dsoxlab run mysql-04-securiser

Environment: VM·Duration: ~40 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-04-securiser checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-04-securiser
dsoxlab check mysql-04-securiser

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Troubleshooting

Hardening almost always breaks something on the first try, and the two dominant causes are authentication (a client too old for caching_sha2_password) and inactive roles (privileges granted but not loaded at login). The table matches the exact error message with its cause, to avoid looking in the wrong place.

Symptom	Likely cause	Fix
Authentication plugin 'caching_sha2_password' cannot be loaded	Old MySQL client (5.x) without SHA2 support	Update the client or temporarily re-enable mysql_native_password
Access denied after migrating to 8.4	mysql_native_password disabled	ALTER USER ... IDENTIFIED WITH caching_sha2_password BY '...'
ERROR 2061 ... Authentication requires secure connection	caching_sha2_password account reached without TLS: the plugin refuses, before REQUIRE SSL	Connect with --ssl-mode=REQUIRED, or configure the connector for TLS
Access denied ... (using password: YES) although the password is right	mysql_native_password account with REQUIRE SSL reached without TLS: REQUIRE SSL refuses, without saying why	Reconnect with --ssl-mode=REQUIRED before resetting the password
ERROR 2026: SSL connection error	Expired or invalid server certificate	Check Ssl_server_not_after, renew and ALTER INSTANCE RELOAD TLS
TLS connection impossible from the application	The application does not pass --ssl-mode=REQUIRED	Configure the connector with mandatory SSL
SELECT CURRENT_ROLE() returns NONE	Roles are not activated by default	SET DEFAULT ROLE ALL TO user@host; or SET PERSIST activate_all_roles_on_login = ON;
Role granted but privileges missing	activate_all_roles_on_login = OFF	SET PERSIST activate_all_roles_on_login = ON;
Port 33060 stays reachable although bind_address = 127.0.0.1	mysqlx_bind_address is not restricted (value *)	Set mysqlx_bind_address = 127.0.0.1, restart, then check with sudo ss -tlnp | grep mysqld
Forgotten root password	-	Restart with --skip-grant-tables, reset, then restart normally (see the Installation guide)
validate_password blocks creation	Password too simple	Follow the policy (length, uppercase, digit, special) or temporarily lower the policy

Swipe to see more

Key takeaways

• caching_sha2_password is the default plugin (controlled by authentication_policy, no longer by default_authentication_plugin, which does not exist in 8.4). mysql_native_password is deprecated since 8.0.34, disabled by default in 8.4, and removed in 9.0.
• Network restriction is done at the account level (@host), unlike PostgreSQL, which uses pg_hba.conf. An @'%' account with elevated privileges is a major risk.
• REQUIRE SSL in CREATE USER forces encryption; add --ssl-mode=VERIFY_CA or VERIFY_IDENTITY on the client side to verify the server's identity. Without TLS, the refusal does not come from the same place depending on the plugin: ERROR 2061 for caching_sha2_password (the plugin refuses and says why), ERROR 1045 for mysql_native_password (REQUIRE SSL refuses without saying). To impose encryption on the whole instance and not on one account, use require_secure_transport = ON.
• Roles are not activated automatically at login: think of SET DEFAULT ROLE ALL or activate_all_roles_on_login = ON. A role is a row of mysql.user that CREATE ROLE locks by default (account_locked = Y): that is what sets it apart from an account.
• Never grant GRANT ALL ON *.* or SUPER; use the fine-grained privileges of MySQL 8.4 (FLUSH_PRIVILEGES, SYSTEM_VARIABLES_ADMIN, etc.).
• partial_revokes lets you revoke a global privilege on a specific schema, essential to protect the mysql schema.
• Install connection_control to slow down brute force after 3 failed attempts.
• bind_address is * by default (all interfaces) and Oracle packages do not change that: restrict it together with mysqlx_bind_address (port 33060), then add skip_name_resolve = ON and a system firewall, three layers of network restriction.
• The slow query log (long_query_time = 1) also serves as an audit tool: enable it from day one.

Knowledge check

Eight questions on the role that is granted but grants nothing, what sets a role apart from an account in mysql.user, the two error codes of a cleartext connection refusal depending on the plugin, and the global privilege that opens mysql.user.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →