After installing MySQL or MariaDB, the usual next step is mysql_secure_installation. It’s a short interactive script that removes insecure defaults: anonymous users, the test database, remote root login. It takes a minute, but it’s worth understanding what each prompt does, and where the script stops.

Run it

sudo mysql_secure_installation
# MariaDB: sudo mariadb-secure-installation  (the old name still works too)

The prompts, explained

1. VALIDATE PASSWORD component. Enables a plugin that rejects weak passwords for new accounts. Levels: LOW (length ≥ 8), MEDIUM (plus digits, mixed case, special characters), STRONG (plus a dictionary check). It’s useful on shared systems, but can break applications that create users with simple generated passwords. Choose MEDIUM if unsure, or skip it and enforce strong passwords yourself.

2. Root password. On Ubuntu and Debian, MySQL’s root account uses the auth_socket plugin by default: you log in as root with sudo mysql, no password needed, and remote password guessing against root is impossible. That’s a secure setup, and you can leave it as is.

3. Remove anonymous users? Yes. Anonymous accounts let anyone connect without a username.

4. Disallow root login remotely? Yes. Root should only connect from localhost. Create separate users for applications and remote administration.

5. Remove the test database? Yes. The test database is accessible to all users by default.

6. Reload privilege tables? Yes, to apply the changes immediately.

The root password loop on Ubuntu

On some versions, choosing to set a root password on Ubuntu fails with:

... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server.

and the script keeps asking again. Root uses auth_socket, so there is no password to set. Options:

  • Press Ctrl+C, then rerun the script and skip the password step (answer “n” to changing the root password). This is the recommended choice.
  • Or switch root to password authentication first, if you really need it:
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'StrongRootPassword!';
FLUSH PRIVILEGES;

After that, sudo mysql no longer works without a password. Use mysql -u root -p. See MySQL error 1045 access denied and resetting the MySQL root password.

MariaDB differences

MariaDB’s script asks about unix_socket authentication for root (“Switch to unix_socket authentication?”). Saying yes gives the same sudo mariadb login as MySQL’s auth_socket. The other prompts (anonymous users, remote root, test database) are the same. See MariaDB vs MySQL.

Non-interactive equivalent

For automation (Ansible, cloud-init), run the same steps as SQL:

DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Beyond the script: real hardening

mysql_secure_installation covers the basics. A production server also needs:

Bind to localhost unless remote access is required (Ubuntu’s default is bind-address = 127.0.0.1):

[mysqld]
bind-address = 127.0.0.1

If remote access is needed, allow only specific IPs in the firewall, and require TLS. See MySQL remote access and UFW firewall.

One user per application, with privileges only on its own database:

CREATE USER 'shop'@'localhost' IDENTIFIED BY 'LongRandomPassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop'@'localhost';

See creating MySQL users and granting privileges.

Other measures:

  • Never expose port 3306 to the whole internet. Use an SSH tunnel for admin tools. See SSH tunnels and port forwarding.
  • Keep MySQL updated with security releases.
  • Disable LOCAL INFILE if you don’t need it (local_infile = 0).
  • Back up regularly, and keep backups off the server. See database backup automation.
  • Review accounts periodically: SELECT user, host, plugin FROM mysql.user;.

Quick reference

Prompt Recommended answer
Validate password component Optional (MEDIUM)
Change root password (Ubuntu) No: keep auth_socket
Remove anonymous users Yes
Disallow remote root Yes
Remove test database Yes
Reload privileges Yes

Related: installing MySQL on Ubuntu and database security.

  • MySQL
  • Security
  • Database