A default MySQL or MariaDB installation is usable, but several settings should be tightened before the server holds real data. In this tutorial you will harden MySQL 8.0 on Ubuntu 24.04 step by step: run the security script, audit accounts, create least-privilege application users, restrict network exposure, enforce encrypted connections and lock accounts after repeated failed logins. Where MariaDB behaves differently, the difference is noted.

Prerequisites

To follow this guide you need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS.
  • MySQL 8.0 (sudo apt install mysql-server) or MariaDB 10.11 (sudo apt install mariadb-server) installed from the Ubuntu repositories.
  • A non-root user with sudo privileges.
  • UFW enabled with SSH allowed.

The configuration file paths differ between the two servers:

MySQL 8.0MariaDB 10.11
Server config directory/etc/mysql/mysql.conf.d//etc/mysql/mariadb.conf.d/
Service namemysqlmariadb
Client commandmysqlmariadb (or mysql)

In this guide you will put all server hardening options in one drop-in file, 99-hardening.cnf, inside the config directory for your server. Files are read in alphabetical order, so it overrides the packaged defaults.

Step 1 - Running the security script

Both servers include a script that removes insecure defaults: anonymous users, remote root login and the test database. Run it:

sudo mysql_secure_installation

On MySQL, answer the prompts as follows:

  • VALIDATE PASSWORD component: Y, then choose 2 (STRONG) or 1 (MEDIUM). This rejects weak passwords for new accounts.
  • Remove anonymous users: Y.
  • Disallow root login remotely: Y.
  • Remove test database: Y.
  • Reload privilege tables: Y.

On Ubuntu, MySQL's root account uses the auth_socket plugin: it can only log in from the local system user root (sudo mysql), without a password. The script detects this and skips setting a root password. Keep it that way; socket authentication cannot be brute-forced over the network.

Step 2 - Auditing existing accounts

Open a root session:

sudo mysql

List every account, the host it may connect from and its authentication plugin:

SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user;

+------------------+-----------+-----------------------+----------------+
| user             | host      | plugin                | account_locked |
+------------------+-----------+-----------------------+----------------+
| debian-sys-maint | localhost | caching_sha2_password | N              |
| mysql.infoschema | localhost | caching_sha2_password | Y              |
| mysql.session    | localhost | caching_sha2_password | Y              |
| mysql.sys        | localhost | caching_sha2_password | Y              |
| root             | localhost | auth_socket           | N              |
+------------------+-----------+-----------------------+----------------+

Look for these problems and fix them:

  • Accounts with host %, which may connect from anywhere. Replace them with accounts tied to specific IP addresses.
  • Accounts with an empty user (anonymous).
  • Application accounts with global privileges. Check any account with SHOW GRANTS FOR 'name'@'host';.

The mysql.* accounts are locked system accounts and debian-sys-maint is used by Ubuntu's maintenance scripts; leave them alone.

On MariaDB, run SELECT user, host, plugin FROM mysql.user; instead. The system accounts listed are different, but the checks are the same.

Step 3 - Creating least-privilege application users

Each application should have its own account, limited to its own database, to the host it connects from and to the statements it needs. Still in the root session, create a database and a user that may connect only from the local machine:

CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';

If the application runs migrations with the same account, add CREATE, ALTER, INDEX, DROP, REFERENCES on appdb.*. Never grant ALL PRIVILEGES ON *.*, FILE, SUPER or GRANT OPTION to an application account.

For an application server on your private network, create a separate account bound to that IP address:

CREATE USER 'appuser'@'your_app_server_ip' IDENTIFIED BY 'your_strong_password' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'your_app_server_ip';

REQUIRE SSL refuses unencrypted logins for that account. Check the result:

SHOW GRANTS FOR 'appuser'@'localhost';

+-----------------------------------------------------------------------------+
| Grants for appuser@localhost                                                |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `appuser`@`localhost`  |
+-----------------------------------------------------------------------------+

GRANT USAGE means "no global privileges", which is what you want.

Step 4 - Locking accounts after failed logins

MySQL 8.0 can temporarily lock an account after a number of consecutive failed logins, which slows down password guessing. Apply it to the application accounts:

ALTER USER 'appuser'@'localhost' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
ALTER USER 'appuser'@'your_app_server_ip' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

After 5 failed attempts the account is locked for 1 day. An administrator can unlock it earlier with ALTER USER 'appuser'@'localhost' ACCOUNT UNLOCK;.

On MariaDB, the equivalent is the global max_password_errors variable, which blocks further logins until an administrator runs FLUSH PRIVILEGES. Add it to the hardening file in the next step:

max_password_errors = 5

Type exit to leave the MySQL session.

Step 5 - Restricting network access and risky features

Create the hardening file. For MySQL:

sudo nano /etc/mysql/mysql.conf.d/99-hardening.cnf

For MariaDB, use /etc/mysql/mariadb.conf.d/99-hardening.cnf instead. Add:

[mysqld]
# Listen only on loopback (add a private IP if remote apps connect)
bind-address = 127.0.0.1

# Authenticate by IP only; avoids slow and spoofable reverse DNS lookups
skip-name-resolve = ON

# Block LOAD DATA LOCAL INFILE, which lets a client read server-side files
local_infile = OFF

# Restrict LOAD DATA and SELECT ... INTO OUTFILE to one directory
secure_file_priv = /var/lib/mysql-files

If remote application servers must connect, set bind-address to 127.0.0.1,your_server_private_ip (MySQL 8.0.13 and later accept a comma-separated list; on MariaDB 10.11 use 0.0.0.0 together with the firewall rules below). With skip-name-resolve, accounts must be defined by IP address, not by hostname.

Restart the server (mariadb instead of mysql on MariaDB):

sudo systemctl restart mysql

Verify the listening address and the options:

sudo ss -tlnp | grep mysqld
sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('bind_address','local_infile','skip_name_resolve','secure_file_priv');"

LISTEN 0  151  127.0.0.1:3306  0.0.0.0:*  users:(("mysqld",pid=6021,fd=23))
+-------------------+-----------------------+
| Variable_name     | Value                 |
+-------------------+-----------------------+
| bind_address      | 127.0.0.1             |
| local_infile      | OFF                   |
| secure_file_priv  | /var/lib/mysql-files/ |
| skip_name_resolve | ON                    |
+-------------------+-----------------------+

The mysqlx port 33060 is also bound to loopback by default on Ubuntu (mysqlx-bind-address = 127.0.0.1); leave it that way unless you use the X Protocol.

Firewall rules

If you opened MySQL to a private IP, allow port 3306 only from the application servers:

sudo ufw allow from your_app_server_ip to any port 3306 proto tcp
sudo ufw status

Do not add a general ufw allow 3306 rule.

Step 6 - Requiring encrypted connections

MySQL 8.0 generates a self-signed CA and server certificate in /var/lib/mysql/ on first start and enables TLS automatically. You can force every TCP connection to use it by adding this line to the [mysqld] section of 99-hardening.cnf:

require_secure_transport = ON

Local connections over the Unix socket are still allowed, because the socket never leaves the machine. Restart MySQL and confirm the setting:

sudo systemctl restart mysql
sudo mysql -e "SHOW VARIABLES LIKE 'require_secure_transport';"

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | ON    |
+--------------------------+-------+

From the application server, connect with the remote account and check the connection status:

mysql -h your_server_private_ip -u appuser -p -e '\s' | grep -i ssl

SSL:			Cipher in use is TLS_AES_256_GCM_SHA384

A client that tries to connect without TLS now gets Connections using insecure transport are prohibited while --require_secure_transport=ON.

To stop clients from being fooled by a different server, copy /var/lib/mysql/ca.pem to the application server and connect with --ssl-mode=VERIFY_CA (or the equivalent option in your driver).

Step 7 - Protecting files and logging access denials

The Ubuntu packages already set safe ownership on the data directory (/var/lib/mysql, owned by mysql with mode 700). Confirm it has not been changed:

sudo ls -ld /var/lib/mysql

drwx------ 8 mysql mysql 4096 Sep 25 10:20 /var/lib/mysql

Client option files that contain passwords, such as a ~/.my.cnf used by backup scripts, must be readable only by their owner:

chmod 600 ~/.my.cnf

MySQL writes failed login attempts to the error log only at the most detailed verbosity. To record them, add this to 99-hardening.cnf:

log_error_verbosity = 3

After a restart, failed logins appear in /var/log/mysql/error.log:

sudo grep 'Access denied' /var/log/mysql/error.log | tail -n 5

2026-09-25T10:41:07.512331Z 12 [Note] [MY-010926] [Server] Access denied for user 'appuser'@'localhost' (using password: YES)

You can feed these lines to Fail2ban if the server must stay reachable from outside your private network. MariaDB logs failed logins as warnings with its default log_warnings = 2.

Troubleshooting

The server does not start after adding 99-hardening.cnf: an unknown option stops the server. Check sudo journalctl -u mysql -n 30 (or -u mariadb) and /var/log/mysql/error.log, fix the line and restart. On MySQL you can validate the configuration first with sudo mysqld --validate-config.

Host 'x.x.x.x' is not allowed to connect to this MySQL server: no account matches that IP. With skip-name-resolve on, create the account with the IP address, not a hostname.

Access denied for user 'root'@'localhost' when running mysql -u root -p: root uses socket authentication on Ubuntu. Run sudo mysql instead.

Your password does not satisfy the current policy requirements: the validate password component is active. Use a longer password with mixed case, digits and symbols, or check the policy with SHOW VARIABLES LIKE 'validate_password%';.

Conclusion

Your MySQL or MariaDB server now has no anonymous or remote root accounts, uses least-privilege users bound to specific hosts, listens only where it needs to, requires TLS for network connections and locks accounts that fail to log in repeatedly. As next steps, schedule regular encrypted backups with mysqldump or Percona XtraBackup, keep the server updated with unattended-upgrades, and review accounts and grants whenever an application is retired.