问题描述
MySQL 只能远程登录,不能本地登录。(这个问题最直观的表现就是这样,还有下面的表现)
① 本地登不上去
[root@master ~]# mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
② MySQL 没有权限
mysql> use mysql
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
③ mysqld:Shutdown complete
[root@master ~]# mysqld
220811 9:21:48 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
220811 9:21:48 [ERROR] Aborting
220811 9:21:48 [Note] mysqld: Shutdown complete
④ mysqld 陷入无限循环
[root@node1 sbin]# mysqld --user=root restart
220811 8:46:42 [Note] Plugin 'FEDERATED' is disabled.
220811 8:46:42 InnoDB: Initializing buffer pool, size = 8.0M
220811 8:46:42 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
220811 8:46:42 InnoDB: Retrying to lock the first data file
解决方法
几个小时后突然想起来什么,就用本地的 SQLyog 连了一下 CentOS 的 MySQL,发现可以进入。
查询 mysql 库中的 user 表,发现 localhost 的 User
与 Password
为空。
为其重新分配 User
与 Password
mysql> grant all privileges on *.* to root@'localhost' identified by '123456';
mysql> flush privileges;
可以看到已经分配 User
与 Password
本地登录成功!
结束。