MySQL 'root'@'localhost' could not log in
This morning, my colleague said that the MySQL root account could not be logged in. I tried it.
# mysql-u root-p
Prompt "Access denied for user 'root'@'localhost' (using password: YES)"
Because a colleague left after the end of the year, my first reaction was who changed the root password? Reset the password by forgetting the root password:
# / etc/init.d/mysql stop
# mysqld_safe-skip-grant-tables &
# mysql-uroot-p
Mysql > update mysql.user set password=password ('mypassword') where user='root'
Mysql > flush privileges
Mysql > quit
Still unable to log in with the new password, the prompt is the same as above. Log in to a non-root account and check the user table:
Mysql > select user,host from user
+-+-
| | user | host |
+-+-
| | root | 127.0.0.1 | |
| | night |% | |
+-+-
Suspect that the default localhost is not mapped to 127.0.0.1? Try # mysql-u root-p xxxx-h 127.0.0.1, and you can log in.
The student who configured the database before did not authorize 'root'@'localhost'' and 'root'@'ip''.
Grant all privileges on. To 'root'@'localhost' identified by' mypassword' with grant option
Grant all privileges on. To 'root'@'118.192.91.xxx' identified by' mypassword' with grant option
Check the user table again:
Write the picture description here.
Then # mysql-u root-p xxxx, login is successful!
Check the difference between mysql-h localhost and mysql-h 127.0.0.1. You use UNIX socket to connect to mysql through localhost and TCP/IP to connect to mysql through 127.0.0.1. Look at the status:
Mysql-h localhost > status
Connection id: 639
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: "
Using delimiter:
Server version: 5.6.15-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Mysql-h 127.0.0.1 > status
Connection id: 640
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: "
Using delimiter:
Server version: 5.6.15-log Source distribution
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
