There’s often confusion over why MySQL users are created with a host of localhost in addition to a user created with wildcard, %, for their host.
create user ‘some_user’@’localhost’ identified by ‘some_password_here’;
create user ‘some_user’@’%’ identified by ‘some_password_here’;
The wildcard as host implies that the user can connect from any client host (not a best practice for security!); however, it’s common, if not the norm, for the user to also be created specifying a host of “localhost.” This user must be made separately from the same username as the wildcard host. Why not use wildcard only?
Again, using wildcard is a security risk, and it’s not a best practice to use; though, if it is requested or required for the environment, an additional localhost account is often created. The two users with the same user name may have different privileges granted for the two different accounts, lending more security. Given only those with access to the server can connect via localhost, the [email protected] account may be granted higher privileges than the one connecting remotely.
Another reason for specifying localhost is because a localhost connection uses a Unix domain socket by default, whereas other connections use TCP/IP. Unix domain socket connections can be faster than TCP/IP connections, demonstrating lower latency and higher throughput in benchmark tests.
Note that there are situations where users connecting through an account with % as host are denied access due to an anonymous user (security risk!) with a host of localhost. MySQL will authenticate the [email protected]% as the anonymous user due to MySQL’s authentication method. MySQL will authenticate via the most-specific host values first, meaning a host set as localhost with no user is more specific than a defined user with wildcard as host. Users can be denied access due to being authenticated as an anonymous user and having the incorrect password for that user. While this is uncommon, it’s another reason why the localhost account may be created in addition to the wildcard account.
One last thing to note is that it’s possible to explicitly specify and establish a specific transport protocol for a connection, such as TCP/IP, Unix socket, shared memory, or pipe, by adding the option –protocol=”option_here” with login credentials. However, specifying socket, pipe, or memory protocol transport is only supported for connections to local MySQL servers.