--- title: "How to Secure MySQL and MariaDB on Ubuntu 24.04" description: "Harden MySQL 8.0 or MariaDB on Ubuntu 24.04: run the security script, create least-privilege users, restrict network access, require TLS and lock brute force." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/mysql-mariadb-security-configuration updated: 2026-09-25 --- # How to Secure MySQL and MariaDB on Ubuntu 24.04 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.0 | MariaDB 10.11 | |---|---|---| | Server config directory | `/etc/mysql/mysql.conf.d/` | `/etc/mysql/mariadb.conf.d/` | | Service name | `mysql` | `mariadb` | | Client command | `mysql` | `mariadb` (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: ```bash 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. > Note: On MariaDB the script is also available as `mariadb-secure-installation`. When asked to switch to `unix_socket` authentication, answer `n` if it is already in use (the default on Ubuntu), and do not set a root password unless you need one. ## Step 2 - Auditing existing accounts Open a root session: ```bash sudo mysql ``` List every account, the host it may connect from and its authentication plugin: ```sql SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user; ``` ```output +------------------+-----------+-----------------------+----------------+ | 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: ```sql 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: ```sql 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: ```sql SHOW GRANTS FOR 'appuser'@'localhost'; ``` ```output +-----------------------------------------------------------------------------+ | 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: ```sql 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: ```ini 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: ```bash sudo nano /etc/mysql/mysql.conf.d/99-hardening.cnf ``` For MariaDB, use `/etc/mysql/mariadb.conf.d/99-hardening.cnf` instead. Add: ```ini [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. > Note: Ubuntu's MySQL package already sets `secure_file_priv` to `/var/lib/mysql-files`. On MariaDB, create the directory first with `sudo install -d -o mysql -g mysql -m 750 /var/lib/mysql-files`. Restart the server (`mariadb` instead of `mysql` on MariaDB): ```bash sudo systemctl restart mysql ``` Verify the listening address and the options: ```bash sudo ss -tlnp | grep mysqld sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('bind_address','local_infile','skip_name_resolve','secure_file_priv');" ``` ```output 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: ```bash 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`: ```ini 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: ```bash sudo systemctl restart mysql sudo mysql -e "SHOW VARIABLES LIKE 'require_secure_transport';" ``` ```output +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | ON | +--------------------------+-------+ ``` From the application server, connect with the remote account and check the connection status: ```bash mysql -h your_server_private_ip -u appuser -p -e '\s' | grep -i ssl ``` ```output 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). > Note: MariaDB 10.11 on Ubuntu does not create certificates automatically. Generate a CA and server certificate, set `ssl_ca`, `ssl_cert` and `ssl_key` in the `[mysqld]` section, restart, and then set `require_secure_transport = ON`. Check with `SHOW VARIABLES LIKE 'have_ssl';`, which must return `YES`. ## 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: ```bash sudo ls -ld /var/lib/mysql ``` ```output 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: ```bash 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`: ```ini log_error_verbosity = 3 ``` After a restart, failed logins appear in `/var/log/mysql/error.log`: ```bash sudo grep 'Access denied' /var/log/mysql/error.log | tail -n 5 ``` ```output 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.