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

Question

The following is the result of the query select host, user from mysql.user from a freshly 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 computer, which is the local host. I can't find any difference after deleting rows with seemingly duplicate host / user pairs from the table.

What is the difference between the two? Can I safely remove the other two?

Answer 1

From the manual :

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

AND:

Some accounts have a root username. These are superuser accounts that have all privileges and can do anything. The original root account has empty passwords, so anyone can connect to the MySQL server as root without a password and get all the privileges.

On Unix, each root account allows you to connect to a local host. Connections can be made by specifying the localhost hostname or the actual hostname or IP address.

However, I cannot find any explanation for this. There is nothing else in the manual, which suggests that this is required for any specific reason, but it seems to cover all the bases for request verification . There may be some cases where one outgoing local connection uses one account, while others require alternatives; I suppose it was decided that providing local root access would always work, no matter what the edge was, it was fine.

Answer 2

In mysql, user1 @ host1 is a different user than user1 @ host2 without a relationship between them. Fortunately, these three root users must have the same password and permissions.

Use caution when deleting these root accounts. In one example, the manual warns that you are using bind-address

.. If you bind the server to :: 1, it only accepts connections at this address. In this case, first make sure that the account 'root' @ ':: 1' is present in the mysql.user table, so you can connect to the server to disable it.

The confusion may be that mysql can resolve the host IP address as some host as the host name and use the appropriate permissions (or if they do not match it, it can match the template host% if present). You may run into problems if you are particularly concerned about the performance or security of dns and use skip_name_resolve . That is, read http://dev.mysql.com/doc/refman/5.5/en/host-cache.html

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