Why MySQL Login Fails: Host/User Sorting Rules and a Real‑World Debugging Case

MySQL authenticates a client by first loading the user table into memory, sorting the rows, and then scanning the sorted list to find the first entry that matches the client’s host and username.

Login verification steps

MySQL server reads the user table into memory and sorts it according to a specificity rule.

The client attempts to connect; the server scans the sorted in‑memory rows.

The first row whose host and user match the client is selected, and the password is verified.

Sorting principle

The most‑specific entries appear first.

In the host column, literal hostnames and IP addresses are the most specific. A subnet mask (e.g., 192.168.1.0/255.255.255.0) is as specific as a single IP. The wildcard % is the least specific; an empty string '' (any host) is slightly more specific than %.

If the host values are equal, the user column is compared similarly: a concrete username is more specific, while an empty user (any user) is the least specific.

Summary of the rules

The host and user columns are loaded into memory sorted by specificity; the most specific rows are examined first.

If a host matches, MySQL then matches the user by specificity. If no exact user is found, it falls back to less specific entries, eventually failing if no match is found.

Case study: replication user login problem

In a test environment with three machines (master gp‑s2 and slaves gp‑s1, gp‑s3) all in the same subnet, the master’s user table contained the following rows (excerpt):

+-------------------------+----------+-------------------------------------------+
| host                    | user     | password                                  |
+-------------------------+----------+-------------------------------------------+
| %                       | root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %                       | test3    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.%               | rep1     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.%               | test     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.%               | test2    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.0/255.255.255.0| wang     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.18             | root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 10.9.15.19             | rep1     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| gp‑s1                   |          |                                           | <-- problematic row
| gp‑s1                   | root     |                                           |
| gp‑s1                   | test     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| gp‑s3                   | test     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------+----------+-------------------------------------------+

Attempting to connect as rep1 from gp‑s1 failed:

mysql -urep1 -p123456 -h gp-s2
ERROR 1045 (28000): Access denied for user 'rep1'@'gp-s1' (using password: YES)

From gp‑s3 the same user succeeded:

mysql -urep1 -p123456 -h gp-s2
Welcome to the MySQL monitor...

After granting replication rights to a more specific host:

grant replication slave on *.* to 'rep1'@'10.9.15.19' identified by "123456";

Login from gp‑s1 then succeeded, but the matched entry was 10.9.15.19 rather than the wildcard 10.9.15.%. Querying the current user showed:

select user(), current_user();
+------------+-----------------+
| user()     | current_user() |
+------------+-----------------+
| rep1@gp-s1 | [email protected] |
+------------+-----------------+

From gp‑s3 the match was the expected wildcard entry:

select user(), current_user();
+------------+-----------------+
| user()     | current_user() |
+------------+-----------------+
| [email protected] | [email protected].% |
+------------+-----------------+

The root cause was the row | gp‑s1 | | | (host gp‑s1 with an empty user). When MySQL matched the host gp‑s1, it then looked for a matching user; finding none, it fell back to the empty user, which represents “any user”. Because the password field was also empty, remote login was denied.

Removing that empty‑user row resolved the issue.

Special notes

If the username is empty but the password is set, any remote user can log in as long as the password matches.

Empty‑user entries should be deleted promptly to avoid unexpected authentication failures.