今天早上同事说 MySQL root 账号登录不上了. 我试了一下
#mysql -u root -p
提示 "Access denied for user'root'@'localhost'(using password: YES)"
因为年后有同事离职, 我第一反应是谁修改了 root 密码? 按照忘记 root 密码来重置一下密码:
- #/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
用新密码还是无法登录, 提示跟上面一样. 换一个非 root 账号登录, 查看一下 user 表:
- mysql> select user,host from user;
- +----+---+
- | user | host |
- +----+---+
- | root | 127.0.0.1 |
- | night | % |
- +----+---+
怀疑默认的 localhost 没有映射到 127.0.0.1? 试试 #mysql -u root -p xxxx -h 127.0.0.1, 果然可以登录.
之前配置数据库的同学没有给'root'@'localhost'和'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;
再查询一下用户表:
然后 #mysql -u root -p xxxx, 登录成功!
查了一下 mysql -h localhost 和 mysql -h 127.0.0.1 的区别, 通过 localhost 连接到 mysql 是使用 UNIX socket, 而通过 127.0.0.1 连接到 mysql 是使用 TCP/IP. 看看状态:
- 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
来源: http://www.linuxidc.com/Linux/2018-05/152244.htm