The classic three-liner for adding a MySQL user, run from a mysql shell connected as root or another account with the CREATE USER privilege. Replace each placeholder with your own value.
1 2 3 | CREATE USER 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' IDENTIFIED BY 'THIS_USER_PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USER_NAME'@'CONNECTING_FROM_WHERE' WITH GRANT OPTION; FLUSH PRIVILEGES; |
The host part — CONNECTING_FROM_WHERE — is the bit most people get wrong on the first try. It tells MySQL which client addresses are allowed to authenticate with this account. Common values:
- ‘%’ — any host (use sparingly)
- ‘localhost’ — connecting via a Unix socket on the same box
- ‘127.0.0.1’ — connecting via TCP loopback (different account from ‘localhost’ — see the gotchas below)
- ‘10.0.%.%’ — a subnet
- ‘app-server.example.com’ — a specific hostname
A few useful additions.
Don’t grant ALL ON *.* with GRANT OPTION to an app user. The grant above gives this user everything on every database, plus the ability to hand those privileges to other users. That’s effectively superuser. For an application account you almost always want a much tighter grant — restrict to the one database the app uses, and drop WITH GRANT OPTION:
1 2 | CREATE USER 'myapp'@'%' IDENTIFIED BY 'a-strong-password'; GRANT SELECT, INSERT, UPDATE, DELETE ON myappdb.* TO 'myapp'@'%'; |
Use GRANT ALL ON myappdb.* if your app needs schema changes (DDL) too. Reserve WITH GRANT OPTION for accounts whose actual job is to manage other users — not your application’s connection pool.
FLUSH PRIVILEGES isn’t needed here. One of the longest-running pieces of MySQL folklore. FLUSH PRIVILEGES is only needed when you modify the underlying mysql.user tables directly with INSERT/UPDATE. CREATE USER and GRANT already update the in-memory privilege tables, so the flush is a no-op. Harmless to leave in, but if you’ve been told it’s mandatory — it isn’t.
MySQL 8: the auth plugin changed. MySQL 8.0 made caching_sha2_password the default authentication plugin (it was mysql_native_password in 5.7). Most modern client libraries handle it fine, but older drivers or short-lived containers connecting to MySQL 8 sometimes throw “Plugin caching_sha2_password could not be loaded”. Two ways out: upgrade the client, or fall back to the old plugin per-user:
1 2 | CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'a-strong-password'; |
localhost and 127.0.0.1 are two different host strings. A user defined as ‘foo’@’localhost’ can only connect over the Unix socket. A user defined as ‘foo’@’127.0.0.1’ can only connect over TCP loopback. They’re separate account rows in mysql.user. If you wonder why your app works in mysql but fails from a script that uses –protocol=tcp, this is usually it. Create both rows, or use ‘%’ if you don’t care about the path.
Companion commands you’ll want next.
1 2 3 4 5 6 7 8 9 10 11 | -- Change a user's password ALTER USER 'myapp'@'%' IDENTIFIED BY 'a-new-password'; -- See what a user is allowed to do SHOW GRANTS FOR 'myapp'@'%'; -- List all users and the host pattern they're tied to SELECT USER, host FROM mysql.user; -- Remove a user (drops every host pattern; for one specific host use 'name'@'host') DROP USER 'myapp'@'%'; |
And of course: if you’re choosing a database tool today and aren’t already on MySQL, take a look at MariaDB (a drop-in fork that follows similar SQL) or PostgreSQL (different syntax, but a noticeably more featureful default). For an existing MySQL deployment though, the recipe above is still exactly the right one. 🐬