Chapter 2
Chapter 2 — Initial Security Hardening
A fresh MySQL installation ships in a deliberately permissive state — anonymous users can connect without credentials, a world-accessible test database exists, and root can potentially be reached without a password. None of this is malicious; it's to make the initial experience smooth on a development machine. On a server visible to the internet, these defaults are unacceptable. This chapter hardens them in a single step using MySQL's built-in tool, then verifies the result.
What this chapter covers: What a fresh MySQL install leaves open and why it matters. Running
mysql_secure_installation — a full annotated walkthrough of every prompt and the recommended answer. The Validate Password component and when each policy level is appropriate. The Ubuntu socket-auth decision: whether to keep sudo mysql access or switch to password auth. Manual verification queries to confirm the hardening worked. What this script doesn't cover (a bridge to Chapter 3). Troubleshooting.
What a Fresh Install Leaves Open
Before running mysql_secure_installation, connect as root and look at the user table. What you find explains exactly what the hardening script fixes.
$ sudo mysql
# See every account that can connect to this server
mysql> SELECT user, host, plugin, authentication_string != '' AS has_password
-> FROM mysql.user;
+------------------+-----------+-----------------------+--------------+
| user | host | plugin | has_password |
+------------------+-----------+-----------------------+--------------+
| | localhost | mysql_native_password | 0 |
| | webserver | mysql_native_password | 0 |
| mysql.infoschema | localhost | caching_sha2_password | 1 |
| mysql.session | localhost | caching_sha2_password | 1 |
| mysql.sys | localhost | caching_sha2_password | 1 |
| root | localhost | auth_socket | 0 |
+------------------+-----------+-----------------------+--------------+
6 rows in set (0.00 sec)
# Check if the test database exists
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
Before hardening — security problems
✗ Anonymous users — rows with an empty
✗ test database — world-accessible by anonymous users. Can be used to probe disk space, test injection payloads, or generate load.
✗ Root accessible from multiple hosts — on some installs, a root account exists with
✗ No password policy — nothing stops application users from being created with weak passwords.
user field. Anyone who can reach the MySQL port can connect with no credentials at all.✗ test database — world-accessible by anonymous users. Can be used to probe disk space, test injection payloads, or generate load.
✗ Root accessible from multiple hosts — on some installs, a root account exists with
host='%', meaning remote login as root is possible.✗ No password policy — nothing stops application users from being created with weak passwords.
After hardening — what changes
✓ Anonymous users removed — every connection requires a named account.
✓ test database dropped — no more world-accessible scratch space.
✓ Remote root blocked — root can only connect from
✓ Password validation enabled (optional) — enforces complexity rules on all future
✓ test database dropped — no more world-accessible scratch space.
✓ Remote root blocked — root can only connect from
localhost.✓ Password validation enabled (optional) — enforces complexity rules on all future
CREATE USER and ALTER USER calls.
The Validate Password Component
The first prompt in mysql_secure_installation asks whether to install the VALIDATE PASSWORD component. If enabled, it enforces a complexity policy on any password set or changed from that point forward — including passwords you set for application users in Chapter 3.
LOW (policy 0)
- Minimum 8 characters
- No other requirements
Too permissive — skip
MEDIUM (policy 1)
- Minimum 8 characters
- At least 1 uppercase
- At least 1 lowercase
- At least 1 digit
- At least 1 special character
Recommended
STRONG (policy 2)
- All MEDIUM rules, plus:
- Dictionary file check
- Rejects common words
Can cause friction with scripts
STRONG policy and automated scripts: If you use STRONG, ensure your dictionary file (
/usr/share/mysql/english.txt or similar) exists — if MySQL can't find it, all password attempts fail. MEDIUM is robust without this dependency.
The Ubuntu Decision: Keep Socket Auth or Switch to Password Auth
On Ubuntu, MySQL root uses Unix socket authentication — you connect with sudo mysql and no password is asked. During mysql_secure_installation, you'll be asked whether to change the root password. Your answer determines how you'll administer MySQL going forward.
Keep socket auth (recommended for home servers)
Answer N when asked "Change the password for root?"
How to connect after:
Pros: Simpler, no root password to manage or forget, root is already protected by your Linux user password and SSH keys.
Cons: Some GUI tools (phpMyAdmin, MySQL Workbench) can't authenticate via socket — you'll need a separate admin account for those (Chapter 3 covers this).
How to connect after:
sudo mysql (no password, just sudo access required)Pros: Simpler, no root password to manage or forget, root is already protected by your Linux user password and SSH keys.
Cons: Some GUI tools (phpMyAdmin, MySQL Workbench) can't authenticate via socket — you'll need a separate admin account for those (Chapter 3 covers this).
Switch to password auth (better for GUI tools)
Answer Y and set a strong password.
How to connect after:
Pros: phpMyAdmin and MySQL Workbench can authenticate as root directly.
Cons: A root database password to store and manage. If forgotten, recovery requires stopping MySQL and starting in skip-grant mode.
How to connect after:
mysql -u root -p (you'll be prompted for the password). sudo mysql may no longer work.Pros: phpMyAdmin and MySQL Workbench can authenticate as root directly.
Cons: A root database password to store and manage. If forgotten, recovery requires stopping MySQL and starting in skip-grant mode.
On Debian, you already set a root password during installation.
mysql_secure_installation may offer to change it — decide whether you want a new one. The socket-auth question doesn't apply in the same way.
Running mysql_secure_installation — Full Annotated Walkthrough
$ sudo mysql_secure_installation
Step 1 of 5 — Validate Password Component
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Answer Y to install the component and enter 1 for MEDIUM. This policy (8+ chars, mixed case, digit, special character) covers the vast majority of attack scenarios without causing friction when creating application users.
Step 2 of 5 — Root Password
Estimated strength of the password: 0
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
Ubuntu with socket auth: Answer N to keep
Debian with a password already set: Answer Y only if you want to change the password you set during installation. Otherwise N is fine.
If you answer Y: MySQL switches root from
sudo mysql working. Your root connection is already secured by your Linux sudo password + SSH keys.
Debian with a password already set: Answer Y only if you want to change the password you set during installation. Otherwise N is fine.
If you answer Y: MySQL switches root from
auth_socket to caching_sha2_password. You must use mysql -u root -p from that point forward — sudo mysql will no longer work.
Step 3 of 5 — Anonymous Users
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Always Y. Anonymous users (the accounts with an empty username you saw in the
mysql.user table earlier) can connect to MySQL without any credentials. This is never acceptable on a server. Removing them means every connection requires a named account with a password.
Step 4 of 5 — Remote Root Login
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
Always Y. This removes any root accounts where
host is not localhost. Root should only ever be used locally, from the server itself. Remote root access, even with a strong password, exposes your entire database server to brute-force attempts on the most privileged account. Application databases use non-root accounts (Chapter 3).
Step 5a of 5 — Test Database
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Always Y. The test database is accessible by anonymous users and can be used to probe the server — writing large amounts of data to measure disk speed, running expensive queries to generate load, or simply exploring what functions are available before attempting an injection attack. Remove it entirely.
Step 5b of 5 — Reload Privileges
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Always Y. The changes made to the
mysql system database (removing anonymous users, removing remote root) don't take effect for currently-connected clients until the privilege tables are reloaded. FLUSH PRIVILEGES is what this step runs internally. Without it, a connected anonymous session could theoretically remain active until MySQL restarts.
Verifying the Hardening Worked
Don't take the script's "Success" messages at face value — check the results directly. These three queries confirm everything the script promised to do.
$ sudo mysql
# 1 — Check that anonymous users are gone (should return 0 rows)
mysql> SELECT user, host, plugin FROM mysql.user WHERE user = '';
Empty set (0.00 sec)
# 2 — Check that root only exists for localhost (no remote root accounts)
mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
+------+-----------+-------------+
| user | host | plugin |
+------+-----------+-------------+
| root | localhost | auth_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)
# ✓ Only one row, host is 'localhost', plugin shows socket auth is intact
# 3 — Confirm the test database is gone
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# ✓ test database is absent — back to the 4 system databases only
# 4 — Full user audit: see everything in the user table cleanly
mysql> SELECT user, host, plugin, password_expired, account_locked
-> FROM mysql.user;
+------------------+-----------+-----------------------+------------------+----------------+
| user | host | plugin | password_expired | account_locked |
+------------------+-----------+-----------------------+------------------+----------------+
| mysql.infoschema | localhost | caching_sha2_password | N | Y |
| mysql.session | localhost | caching_sha2_password | N | Y |
| mysql.sys | localhost | caching_sha2_password | N | Y |
| root | localhost | auth_socket | N | N |
+------------------+-----------+-----------------------+------------------+----------------+
4 rows in set (0.00 sec)
# ✓ mysql.* system accounts are locked — they can't be used to log in
# ✓ root is the only active account, localhost only, socket auth
Confirm you can still connect after hardening. Open a new terminal and run
sudo mysql — you should land at the MySQL prompt as before. If you chose to switch to password auth in Step 2, test with mysql -u root -p using your new password. Only once you've confirmed you can still get in should you consider the hardening complete.
Manual Steps — If mysql_secure_installation Didn't Catch Everything
Occasionally, particularly on Debian or if you interrupted the script, some of the steps may not have completed. These SQL commands do exactly what the script does — run them inside MySQL as root if needed.
$ sudo mysql
# Remove any remaining anonymous users manually
mysql> DELETE FROM mysql.user WHERE user = '';
Query OK, 2 rows affected (0.01 sec)
# Block remote root login manually
mysql> DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost';
Query OK, 0 rows affected (0.00 sec)
# Drop the test database manually
mysql> DROP DATABASE IF EXISTS test;
Query OK, 1 row affected (0.01 sec)
# Remove any leftover privileges on the test database
mysql> DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%';
Query OK, 1 row affected (0.00 sec)
# Apply all privilege changes immediately
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Always run
FLUSH PRIVILEGES after any direct edits to mysql.user or mysql.db. MySQL caches grant tables in memory. Changes written directly to these tables don't take effect until you reload the cache. FLUSH PRIVILEGES is the command that does this. If you use CREATE USER, GRANT, or REVOKE statements (covered in Chapter 3), MySQL updates the cache automatically and you don't need to flush.
What This Chapter Doesn't Cover
mysql_secure_installation removes the worst defaults. But there's a broader set of security concerns it deliberately ignores — these are the topics for the rest of the course.
Chapter 3
Application users and privileges
Creating dedicated database accounts per application, granting only the permissions each one needs, revoking them, auditing who has what.
Chapter 4
MySQL configuration
Tuning memory limits, max connections, the slow query log, character sets, and other settings in
mysqld.cnf.Chapter 5
Backup and restore
Regular automated dumps, verifying backups, restoring from
mysqldump output.Chapter 6
Remote access
Controlling which interfaces MySQL listens on, UFW rules for port 3306, and SSL/TLS encrypted connections.
Chapter 9
phpMyAdmin security
Restricting the phpMyAdmin URL, IP allowlisting, and ensuring it doesn't expose root access to the web.
Troubleshooting
mysql_secure_installation: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
On Ubuntu,
mysql_secure_installation must be run with sudo. Without sudo it tries to connect as the current Linux user rather than root and gets rejected. Run: sudo mysql_secure_installation. On Debian, you need the root password: run as root with su - first, then run mysql_secure_installation (without sudo), or use sudo mysql_secure_installation if sudo is configured.
After choosing Y to change root password, sudo mysql no longer works
Choosing Y in Step 2 switches the root plugin from
auth_socket to caching_sha2_password. Socket auth is gone — use mysql -u root -p with the password you set. If you want to go back to socket auth: sudo mysql -u root -p, then: ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;, then FLUSH PRIVILEGES;. After this, sudo mysql will work again and the root password is no longer used.
Your password does not satisfy the current policy requirements
MEDIUM policy requires: 8+ characters, at least one uppercase letter, one lowercase letter, one digit, and one special character (e.g.
!, @, #, $, %). Example compliant password: Secure!Db9. If you're setting a password for an application service account and it's being rejected, also check that you haven't accidentally hit STRONG policy — check with: SHOW VARIABLES LIKE 'validate_password%'; inside MySQL.
mysql_secure_installation exits immediately with no prompts on Debian
This usually means it connected but encountered an error before the first prompt. Check with:
mysql_secure_installation 2>&1 | head -20 to see the error. Common cause on Debian: the root password set at install time has expired (password_expired = Y in mysql.user). Fix by connecting via sudo mysql and running: ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword!'; then retry.
mysql.user query shows root still has a host='%' row after hardening
Some MySQL setups or manual configurations add a wildcard root account that the script may not have caught. Remove it manually:
DELETE FROM mysql.user WHERE user='root' AND host='%'; then FLUSH PRIVILEGES;. Verify: SELECT user, host FROM mysql.user WHERE user='root'; — should show only localhost.
Quick Reference — Chapter 2
| Command / Query | Purpose |
|---|---|
| sudo mysql_secure_installation | Run the MySQL hardening wizard (always with sudo on Ubuntu) |
| SELECT user, host, plugin FROM mysql.user; | Full audit of all MySQL accounts — run before and after hardening |
| SELECT user, host FROM mysql.user WHERE user = ''; | Check for anonymous users (expect empty set after hardening) |
| SELECT user, host FROM mysql.user WHERE user = 'root'; | Confirm root is restricted to localhost only |
| SHOW DATABASES; | Confirm the test database is gone (should show only 4 system databases) |
| DELETE FROM mysql.user WHERE user = ''; | Remove anonymous users manually |
| DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost'; | Block remote root login manually |
| DROP DATABASE IF EXISTS test; | Remove the test database manually |
| FLUSH PRIVILEGES; | Apply changes to mysql.user/mysql.db immediately (always needed after direct table edits) |
| ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket; | Switch root back to socket auth if you accidentally changed it |
| SHOW VARIABLES LIKE 'validate_password%'; | See the current password policy settings |