A MySQL account is a user and a host together. 'app'@'localhost' and 'app'@'%' are two different accounts that can have different passwords and different privileges, and this is the source of a great deal of confusion: an account created for localhost cannot connect over the network, and the error says access denied, which sounds like a password problem.
The host part can be a literal name, an IP address, a pattern with %, or % alone for anywhere. Use the narrowest that works. An application on a known server should be pinned to that address rather than allowed from everywhere.
MySQL 8 changed the default authentication plugin to caching_sha2_password, which is better and which older client libraries cannot speak - the symptom is The server requested authentication method unknown to the client. The right fix is to update the driver; mysql_native_password exists as a fallback and should be a temporary one.
Three accounts worth having: one per application, with only the privileges that application needs; one per person who needs direct access, so that SHOW PROCESSLIST and the audit log name a human; and root, which should not be used by anything routine and should not be reachable remotely at all.
-- user AND host together make the account. These are two accounts.
CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-random-password';
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'a-different-one';
-- An account created for localhost CANNOT connect over the network, and
-- the error is "access denied", which reads like a wrong password.
-- MySQL 8's default. Older drivers answer "authentication method unknown
-- to the client" - update the driver rather than weakening the server.
CREATE USER 'reports'@'%'
IDENTIFIED WITH caching_sha2_password BY 'another-long-one';
-- A temporary fallback for a legacy client, not a destination.
-- ALTER USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY '...';
-- Expiry and reuse policy, per account.
ALTER USER 'reports'@'%'
PASSWORD EXPIRE INTERVAL 90 DAY
PASSWORD HISTORY 5
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
-- Who exists, and how each one authenticates.
SELECT user, host, plugin, account_locked, password_expired FROM mysql.user;
-- A person, not an application, so the processlist names a human.
CREATE USER 'ada'@'%' IDENTIFIED BY '...' PASSWORD EXPIRE;
DROP USER 'old_app'@'%';