How to secure MySQL or MariaDB installation with mysql_secure_installation

Hardening a fresh MySQL or MariaDB instance removes default access paths that attackers routinely probe, such as anonymous accounts, test databases, and overly-permissive root logins.

The mysql_secure_installation utility is an interactive hardening script shipped with the server packages. It connects to the running database as an administrative user and applies safer defaults by modifying user rows and privileges in the system tables (for example, removing anonymous users, restricting root hosts, dropping the test database, and flushing privileges).

Prompt wording and available options vary across distributions and versions, especially around password policy (the validate_password plugin/component) and local-only admin authentication (unix_socket or auth_socket). Running the script from a local console session reduces the chance of locking out remote administration, since disabling remote root login or changing the authentication plugin can immediately change how administrative logins work.

1. Run mysql_secure_installation from a local console session with sudo.

   $ sudo mysql_secure_installation
   ##### snipped #####
   Enter current password for root (enter for none):
   ##### snipped #####
   Remove anonymous users? [Y/n] Y
   Disallow root login remotely? [Y/n] Y
   Remove test database and access to it? [Y/n] Y
   Reload privilege tables now? [Y/n] Y
   All done!

   Changing root authentication or disabling remote root access can break existing admin workflows immediately, so keep console or out-of-band access available until verification is complete.

2. Use secure defaults when prompted for common hardening decisions.

   Typical safe answers are Y for removing anonymous users, disallowing remote root login, removing the test database, and reloading privilege tables; if a unix_socket or auth_socket option is offered, enabling it keeps root local-only via the OS root account.

3. Open a local root SQL session to confirm administrative access still works.

   $ sudo mysql
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 47
   Server version: 10.11.x-MariaDB
   
   MariaDB [(none)]> SELECT VERSION();
   +----------------+
   | VERSION()      |
   +----------------+
   | 10.11.x-MariaDB |
   +----------------+
   1 row in set (0.00 sec)

   If root was switched to password authentication, use mysql -u root -p instead of sudo mysql.

4. Verify no anonymous accounts remain.

   MariaDB [(none)]> SELECT user, host FROM mysql.user WHERE user = '';
   Empty set (0.00 sec)

5. Verify root is not allowed from wildcard hosts.

   MariaDB [(none)]> SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
   +------+-----------+------------+
   | user | host      | plugin     |
   +------+-----------+------------+
   | root | localhost | unix_socket |
   +------+-----------+------------+
   1 row in set (0.00 sec)

   A root row with host '%' indicates remote root logins are still permitted.

6. Verify the test database is removed.

   MariaDB [(none)]> SHOW DATABASES LIKE 'test';
   Empty set (0.00 sec)

7. Exit the SQL session after verification completes.

   MariaDB [(none)]> exit
   Bye