Create MySQL application accounts

MySQL accounts include both a name and a connection host. We need two of them for the notes project: one identity for the running application and one for schema migrations. Separate accounts mean separate passwords, separate privileges, and a clear answer to “what can this credential do if it leaks?”

Connect as the administrator and create both local accounts:

CREATE USER 'notes_app'@'localhost'
  IDENTIFIED BY 'replace-with-a-generated-secret';

CREATE USER 'notes_migrator'@'localhost'
  IDENTIFIED BY 'replace-with-another-generated-secret';

Use generated secrets in real deployments. Do not copy the example text into production.

Verify that both accounts exist:

SELECT user, host FROM mysql.user WHERE user LIKE 'notes%';

You should see two rows, both with localhost as the host. Then prove the login works from a new terminal:

mysql -u notes_app -p

The connection succeeds, and that is all it does. Creating an account does not grant access to notes_app or any other database. Run SHOW DATABASES; in that session and you see almost nothing, because the account has no privileges yet. We will add only the privileges each responsibility needs.

When the statement fails

Running CREATE USER twice for the same account produces:

ERROR 1396 (HY000): Operation CREATE USER failed for 'notes_app'@'localhost'

The account already exists. In a script you want to run repeatedly, use CREATE USER IF NOT EXISTS ... so the statement succeeds whether or not the account is there. If you instead need to start over — for example, you lost the generated password — drop the account with DROP USER 'notes_app'@'localhost'; and create it again. There is no way to read an existing password back out of MySQL, and that is by design.