I have similar problem as here. The problem is that solution proposed there does not work for me:
Just for now I have users:
Can't connect to MySQL using 'localhost' but using '127.0.0.1' it's ok? Ask Question 10. If the you specify 127.0.0.1 instead of localhost in the hostname field during installation, it causes installation paths to get messed up giving me other errors, but specifying localhost fails to connect the database! What's caused this situation?
both without passwords (just for now. I know it is bad!).
I'm connecting to mysql using this command:
After I logged in, I get:
How to login as 'root'@127.0.0.1?
2 Answers
Good evening, it's @RolandoMySQLDBA, the author of that post you mentioned.
Look at the last three lines
The last line says 127.0.0.1 via TCP/IP
so you are 100% using the TCP/IP protocol.
Did you run SELECT USER(),CURRENT_USER();
?
If you did, it should say
It would seem weird for the output to be
Please notice the following: Since both root users have identical grants and identical passwords (none in this case), mysqld decided to pick root@localhost
. At this point, you are probably wondering, 'Why would mysqld
make such a choice ?'
In the DBA StackExchange, I have this 2-year-old post (MySQL error: Access denied for user 'a'@'localhost' (using password: YES)). In that past, I describe exactly how mysqld peforms user authentication. Note the paragraphs from pages 486,487 of MySQL 5.0 Certification Study Guide
There are two stages of client access control:
In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the clent connects, the username, and the password.
In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.
The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:
![Localhost and 127.0.0.1 Localhost and 127.0.0.1](https://www.homeandlearn.uk/images/databases/code/con_code3.png)
As I mentioned from the Certification Book, it's possible for more than one record to match
. Therefore, mysqld
made the choice.
If you delete root@localhost
from mysql.user
, that's one way to get SELECT USER(),CURRENT_USER();
to match. Another way is to give [email protected]
a password.
Having 2 root users with identical means of being used for user authentication is the root cause as to why you cannot see [email protected]
. Notwithstanding, you are using TCP/IP
.
Please make sure 127.0.0.1
is defined in the OS. Run this:
If you get 0
, then the OS does not know about it. You would need to add 127.0.0.1
to /etc/hosts
and restart the network and then mysql.
What I am about to say may sound silly, but the Documentation uses TCP instead of tcp
On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:
The --protocol option enables you to establish a particular type of connection even when the other options would normally default to some other protocol.
Try using protocol=TCP like this
to see if it makes a difference.
You need to look over the /etc/hosts
carefully
If you see this in /etc/hosts
then logging in as [email protected]
should work properly
If you see this in /etc/hosts
then [email protected]
stands no chance of working
PHP is still trying to use the default socket location. This problem can appear if you have moved the MariaDB/MySQL folder from /var/lib/mysql to another location. In order to solve the problem you have to define the new socket's location in the /etc/php.ini file.
Watch out, depending on which driver you use, you may have to specify the pdo_mysql.default_socket=!
In order to check your current directory run the following command in mysql:
Not the answer you're looking for? Browse other questions tagged mysqllocalhost or ask your own question.
Any idea what vmware software sets up the vmware-localhost entry? Probably either the thick client or a web client plugin
What functionality that entry provides?
C:>type Windowssystem32driversetchosts
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each # entry should be kept on an individual line. The IP address should # be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one # space.
#
# Additionally, comments (such as these) may be inserted on individual # lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
# 102.54.94.97 rhino.acme.com # source server
# 38.25.63.10 x.acme.com # x client host
# localhost name resolution is handled within DNS itself.
# 127.0.0.1 localhost
# ::1 localhost
- 127.0.0.1 vmware-localhost
::1 vmware-localhost