Creating Linux MySQL Admin Users: CREATE USER, GRANTS, FLUSH PRIVILEGES

If you manage Linux hosts that still lean on relational storage, carving out an explicit mysql admin persona is boring work that pays off whenever teammates need DDL rights without borrowing root.

⏱️ Estimated effort: roughly five focused minutes at the mysql prompt once the daemon answers on the box — longer if firewall policy needs co-sign with whoever owns UFW or cloud security groups.

Prerequisites

Bring these to the terminal before firing SQL:

• mysql-server installed and reachable (fresh Ubuntu guidance lives in the MySQL 8 install walkthrough)
• sudo shell access plus enough patience to rerun FLUSH PRIVILEGES after each grant tweak
• A password manager slot for whichever admin username you mint—'app_admin' in the snippets below stands in for yours

Whenever I stage UFW allow rules next to databases, I still anchor on Ubuntu nginx apt plus firewall sequencing so ufw enable tempo matches Redis and PostgreSQL siblings.

Connect with sudo mysql

On stock Ubuntu-derived installs, hopping in as root commonly looks like this:

sudo mysql

That unix-socket-backed path skips shipping a dangling root password on local boxes while you still wield full authority.

SELECT USER(), CURRENT_USER();

Seeing root@localhost in both columns confirms you are operating from the expected bootstrap account before you mutate grant tables.

Create users with CREATE USER

Mint a deliberate admin-style login—replace placeholders with real secrets before pasting somewhere durable:

CREATE USER 'app_admin'@'%' IDENTIFIED BY 'REPLACE_WITH_LONG_RANDOM_SECRET';

What each fragment is doing:

• 'app_admin' — human-readable account name; choose something operators recognize in SHOW PROCESSLIST
• '%' — wildcard host so remote clients can authenticate when networking and grants align; swap to 'app_admin'@'10.0.0.12' when you know the sender
• IDENTIFIED BY — stores the password using mysqld default auth plugin semantics (watch for legacy mysql_native_password versus caching_sha2_password mismatches on older clients)

Grant privileges with GRANT ALL

Hand the new account full instance reach only when you truly want an operator key, not an app service user:

GRANT ALL PRIVILEGES ON *.* TO 'app_admin'@'%' WITH GRANT OPTION;

Decode the sharp edges:

Reload grants with FLUSH PRIVILEGES

After mutating users or grants, force mysql to reread grant tables:

FLUSH PRIVILEGES;

Skipping this step is a classic reason fresh credentials look missing until the daemon restarts—especially painful when you are already tailing error.log for unrelated noise.

Verify the account from the CLI

Drop back to the shell and exercise the account like an operator would:

mysql -u app_admin -p

Enter the password interactively, then run something lightweight:

SHOW GRANTS FOR CURRENT_USER;

You should see the GRANT ALL fan-out you expect. If authentication fails before you reach the prompt, jump to the troubleshooting section instead of hammering the same password.

Security checklist for powerful MySQL admins

• Rotate these passwords on the same schedule as shell sudo credentials
• Prefer least privilege service accounts for applications; keep ALL PRIVILEGES for break-glass boxes
• Layer UFW or cloud allow lists so @'%' never means the entire internet by default—when I need the fast ufw allow / reload / status verbose loop without ad-libbing, the paste-friendly nginx plus UFW snippet is still the muscle-memory deck
• Document who owns each admin login so audits do not turn into archaeology

Troubleshooting authentication and connectivity failures

Authentication failed tends to cluster around four culprits:

• Typoed passwords or secrets stored with trailing whitespace in automation
• user@host tuples that disagree with where the packet originated (localhost vs LAN IP semantics trip people constantly)
• Forgetting FLUSH PRIVILEGES after edits
• Older clients negotiating the wrong authentication plugin—match SHOW CREATE USER 'app_admin'@'%'\G expectations with connector defaults

Connectivity failures after auth works usually mean bind-address, skip-networking, mysqld not listening on 3306, or perimeter rules blocking the SYN. While you reconcile those layers on Ubuntu, the single-page nginx apt and UFW sheet keeps firewall edits parallel to other database exposure guides you might already rely on.

Next steps across this site

Sequence this guide after mysql install on ubuntu and before widening network paths in mysql remote linux. Operators who routinely script grants should tuck paste-ready nginx and UFW lines next to provisioning playbooks—the ordering is tedious to re-derive mid-incident even when nginx itself is unrelated.

Frequently asked questions

When should I run FLUSH PRIVILEGES after CREATE USER or GRANT?

Run FLUSH PRIVILEGES immediately after CREATE USER or GRANT mutations so privilege tables reload and new tuples apply without waiting on a systemctl restart mysql roulette unless your playbook already implicitly reloads grants.

What is the difference between creating a user for localhost and the percent host wildcard?

mysql stores identities as username + host_pattern. localhost only matches sockets or loopback connections from that machine. '%' wildcard-matches whichever host identifier the client presents—which only stays safe when bind-address, security groups, and UFW agree with who may reach TCP 3306.

Why would mysql show authentication failed after I created the user?

Typical causes are wrong passwords, user@host mismatches (app_admin@203.0.113.45 dialing in when you only minted 'app_admin'@'localhost'), skipped FLUSH PRIVILEGES, or client plugins refusing caching_sha2_password.

Is GRANT ALL PRIVILEGES ON *.* safe for production?

Comfortable inside sandboxes—reckless for multitenant workloads. Prefer schema-scoped GRANT ... ON app_db.* for services and reserve *.* for operators who consciously accept blast-radius risk every time DROP DATABASE lurks one typo away.

How does this relate to enabling remote MySQL access?

This article covers identities and GRANT mechanics; widening bind-address, opening TCP 3306, and pairing cloud firewalls sits in the remote MySQL hardening companion plus whatever Ubuntu firewall choreography you already trust beside other infra notes.