> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL Security Hardening > How to secure a MySQL server: running mysql_secure_installation, removing anonymous users, restricting root access, enforcing password policies, auditing privileges, enabling SSL/TLS, and reducing the attack surface. All examples in this guide work on VillageSQL. Install Now → A default MySQL installation prioritizes ease of setup over security. Hardening a production server means removing defaults that made sense on a developer laptop but are inappropriate when the server is reachable by applications or the network. ## Hardening by Deployment Context Not every hardening step carries the same risk/friction tradeoff. Use this table to prioritize: | Step | Internet-facing production | Internal/private network | Dev / staging | | :--------------------------------- | :------------------------- | :-------------------------------- | :----------------------------------- | | Run `mysql_secure_installation` | Required | Required | Recommended | | Remove anonymous users | Required | Required | Recommended | | Restrict root to localhost | Required | Required | Recommended | | Remove test database | Required | Required | Optional | | `validate_password` component | Required | Required | Optional — can block quick iteration | | Password expiration policy | Required | Recommended | Skip | | Least-privilege app accounts | Required | Required | Recommended | | Restrict host in grants (no `'%'`) | Required | Required | Optional | | `REQUIRE SSL` on accounts | Required | Recommended | Skip | | Custom CA-signed TLS certificates | Required | Optional — self-signed acceptable | Skip | | `bind-address` to internal IP | Required | Recommended | Optional | | Disable `local_infile` | Required | Recommended | Optional | | `skip_name_resolve` | Recommended | Recommended | Optional | | Lock or drop unused accounts | Required | Required | Optional | **Internet-facing** means port 3306 is reachable from outside your private network, even if behind a load balancer. Apply every "Required" row without exception. **Internal/private network** covers servers accessible only within a VPC, private subnet, or office network. Some mitigations (CA-signed certs, SSL enforcement on every account) add operational overhead for marginal gain — but strong passwords, least privilege, and bind-address restrictions are still non-negotiable. **Dev/staging** machines are the most common source of credential leaks. Skipping password complexity or leaving `'%'` host grants in place is acceptable only if the instance is unreachable from outside your laptop or CI environment. If staging shares a network with production data, treat it as internal/private at minimum. ## mysql\_secure\_installation Run this immediately after installing MySQL on any server that isn't a local dev machine: ```bash theme={null} mysql_secure_installation ``` It walks through the most critical hardening steps: * Set the root password (if not already set) * Remove anonymous users * Disallow remote root login * Remove the test database * Reload privilege tables These four steps eliminate the most common attack surface on a fresh install. ## Root Account Restrictions Root should only connect from `localhost`. Verify and enforce this: ```sql theme={null} SELECT user, host FROM mysql.user WHERE user = 'root'; -- Should show only 'localhost', '127.0.0.1', '::1' ``` If root has a `'%'` host entry, remove it: ```sql theme={null} DROP USER 'root'@'%'; ``` For remote administration, create a named DBA account with limited host access instead of using root remotely. ## Remove Anonymous Users and Test Database ```sql theme={null} -- Remove any anonymous accounts DELETE FROM mysql.user WHERE user = ''; FLUSH PRIVILEGES; -- Remove the test database (readable by anonymous users by default) DROP DATABASE IF EXISTS test; DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%'; FLUSH PRIVILEGES; ``` ## Password Policy MySQL 8.0+ includes the `validate_password` component, which enforces password complexity rules. It must be installed before use — `mysql_secure_installation` offers to install it automatically. To install manually: ```sql theme={null} INSTALL COMPONENT 'file://component_validate_password'; ``` Verify it's active: ```sql theme={null} SHOW VARIABLES LIKE 'validate_password%'; ``` Configure minimum requirements: ```sql theme={null} SET GLOBAL validate_password.length = 12; SET GLOBAL validate_password.mixed_case_count = 1; SET GLOBAL validate_password.number_count = 1; SET GLOBAL validate_password.special_char_count = 1; SET GLOBAL validate_password.policy = 'MEDIUM'; ``` Policy levels: `LOW` (length only), `MEDIUM` (length + character classes), `STRONG` (+ dictionary check). Set password expiration for accounts that might be forgotten: ```sql theme={null} ALTER USER 'admin'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ``` ## Least-Privilege User Accounts Application accounts should have the minimum privileges needed. See [MySQL User Management](/guides/user-management) for the full GRANT syntax. Common patterns: ```sql theme={null} -- Read-only replica account CREATE USER 'app_read'@'10.0.1.%' IDENTIFIED BY 'strong_password'; GRANT SELECT ON myapp.* TO 'app_read'@'10.0.1.%'; -- Application write account (no schema changes) CREATE USER 'app_write'@'10.0.1.%' IDENTIFIED BY 'strong_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write'@'10.0.1.%'; -- Migration account (schema changes allowed, used only by deploy scripts) CREATE USER 'app_migrate'@'localhost' IDENTIFIED BY 'strong_password'; GRANT ALL PRIVILEGES ON myapp.* TO 'app_migrate'@'localhost'; ``` Restrict hosts as tightly as possible — use subnet notation or specific IPs, not `'%'`. ## Auditing Existing Privileges Review all accounts and their privileges: ```sql theme={null} -- List all user accounts SELECT user, host, account_locked, password_expired FROM mysql.user ORDER BY user, host; -- Check for overly broad grants SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv, Super_priv, Grant_priv FROM mysql.user WHERE host = '%' OR Super_priv = 'Y'; ``` Remove or lock accounts that aren't actively used: ```sql theme={null} ALTER USER 'old_account'@'%' ACCOUNT LOCK; -- Or drop entirely: DROP USER 'old_account'@'%'; ``` ## SSL/TLS Connections MySQL 8.0 enables SSL/TLS by default and auto-generates certificates on startup. Verify it's active: ```sql theme={null} SHOW VARIABLES LIKE '%ssl%'; -- ssl_ca, ssl_cert, ssl_key should show file paths -- have_ssl should be 'YES' ``` Require SSL for specific accounts: ```sql theme={null} ALTER USER 'app_write'@'%' REQUIRE SSL; ``` To verify a client connection is using SSL: ```sql theme={null} SHOW STATUS LIKE 'Ssl_cipher'; -- Non-empty value means the current connection is encrypted ``` For production, replace the auto-generated self-signed certificates with certificates from a trusted CA. ## Network Access Restrict MySQL to listen only on necessary interfaces. In `my.cnf`: ```ini theme={null} [mysqld] bind-address = 127.0.0.1 # local only # or bind-address = 10.0.1.5 # specific internal IP ``` If MySQL should not be reachable from the network at all (application on the same host), bind to `127.0.0.1` and never expose port 3306 externally. Firewall rules are a second layer of defense, not a substitute for restricting the bind address. ## Disabling Risky Features Turn off features your application doesn't use: ```sql theme={null} -- Prevent MySQL from reading/writing files on the server filesystem -- (prevents SQL injection attacks that use LOAD DATA INFILE to read /etc/passwd) SET GLOBAL local_infile = 0; ``` In `my.cnf`: ```ini theme={null} [mysqld] local_infile = 0 skip_name_resolve = 1 # don't do reverse DNS lookups; use IPs in grants ``` ## Frequently Asked Questions #### Is the default MySQL 8.4 install secure? Better than older MySQL defaults, but still not hardened for production. MySQL 8.4 generates a random root password and enables SSL by default. You still need to: verify the root password is strong, remove anonymous users, restrict host access, and configure the `validate_password` component. #### Should I run MySQL as root? Never. MySQL should run as a dedicated low-privilege system user (typically `mysql`). Running the server process as root means a MySQL exploit could lead to full system compromise. ## Troubleshooting | Problem | Solution | | :--------------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `ERROR 1045: Access denied for user 'root'@'%'` | Root remote login is correctly blocked — create a named admin user with specific host access | | Application can't connect after adding `REQUIRE SSL` | Client needs to use SSL flags: `--ssl-mode=REQUIRED`; verify server certificates are valid | | `validate_password` rejecting a password | Check current policy: `SHOW VARIABLES LIKE 'validate_password%'`; ensure the password meets all requirements | | Can't find root password on a new install | MySQL 8.0 logs the temporary root password to the error log on first start — find the log path with `SELECT @@GLOBAL.log_error;`, then `grep 'temporary password' ` | | Host `'%'` grants still visible after DELETE | Run `FLUSH PRIVILEGES` after any direct modifications to `mysql.user` | ## See also * [MySQL User Management](/guides/user-management) — creating users and granting least-privilege access * [Password Hashing in MySQL](/guides/password-hashing) — securing application-level credentials stored in MySQL