What's the difference between 127.0.0.1, localhost, [hostname] in mysql.user table

Below is the result of the query select host, user from mysql.user from a fresh installed MySQL 5.

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| 127.0.0.1 | root             |
| localhost | debian-sys-maint |
| localhost | root             |
| ubuntu    | root             |
+-----------+------------------+

127.0.0.1, localhost, 开发者_如何转开发and ubuntu all point to the same machine, which is the local host. I can't find any difference after removing the rows with the seemingly duplicate host/user pairs from the table.

What's the difference between these? Can I safely remove the other two?

From the manual:

When you install MySQL, the grant tables are populated with an initial set of accounts. The names and access privileges for these accounts are described in Section 2.10.3, “Securing the Initial MySQL Accounts”, which also discusses how to assign passwords to them.

And:

Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root without a password and be granted all privileges.

On Unix, each root account permits connections from the local host. Connections can be made by specifying a host name of localhost or the actual host name or IP address.

However, I can't find any rationale for this. There's nothing elsewhere in the manual that suggests that this is required for any particular reason, but presumably it's to cover all the bases for request verification. There may be some instances where one originating local connection uses one account whilst others require use alternatives; I guess it was decided that ensuring local root access will always work, no matter what the edge case, was a good thing.

In mysql, user1@host1 is a different user than user1@host2 with no relationship between them. Fortunately those three root users should just happen to have the same password and permissions.

Be careful deleting these root accounts. One example the manual warns you of is using bind-address

..if you bind the server to ::1, it accepts connections only on that address. In that case, first make sure that the 'root'@'::1' account is present in the mysql.user table so you can still connect to the server to shut it down.

The confusion can be that mysql can resolve an IP address host as being some host as a hostname and use the corresponding permissions (or failing those, it can match the wildcard host % if present). You could run into problems if you are particularly concerned about dns performance or security and use skip_name_resolve. I.e. read http://dev.mysql.com/doc/refman/5.5/en/host-cache.html

For example I found jdbc connecting to localhost would use 127.0.0.1 and so I would have to define my users accordingly, not as user@localhost.