在Ubuntu安装完mysql 发现只有使用root权限才能登录,使用普通账号的话会出现如下错误:
~$ mysql -u root -p
Enter password:
ERROR 1698(2000): Access denied for user 'root'@'localhost'
参照了网络很多方法并没有解决
后来不断搜索和尝试终于找到了解决办法
首先使用sudo 进入mysql
~$ sudo mysql -u root
然后查询mysql.user表
mysql> select user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
| root | localhost | auth_socket |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
在此处你将 auth_socket 更新成 mysql_native_password其实并不能解决问题。
这个时候我们需要将root用户删除并重新建立个root 用户并授权,具体操作步骤如下:
mysql> DROP USER 'root'@'localhost';
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'passwd';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
然后再刷新权限
mysql> FLUSH PRIVILEGES;
最后
mysql> exit
然后就可以用普通账户登录了。
重新进入后查询user表,具体情况如下:
mysql> select user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
| root | % | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
网上查了很久的解决办法,希望对大家有帮助