mysql 1045 using password_解决mysql登录报错ERROR 1045 (28000): Access denied for user 'root'@'localhost' (...

问题描述：

在ubuntu14.04上安装完MYSQL后，MYSQL默认给分配了一个默认密码，但当自己在终端上使用默认密码登录的时候，总会提示一个授权失败的错误。

报错信息：Access denied for user 'root'@'localhost' (using password: YES)

执行过程：

sue@suepc:/usr/local/mysql$ mysql -u root -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决方案：

重置root密码。

步骤如下：

1. 把mysql停止。

$ sudo /etc/init.d/mysql stop

[ ok ] Stopping mysql (via systemctl): mysql.service.

2. 启动mysqld_safe。

$ sudo mysqld_safe --skip-grant-tables &

[1] 28860

(如果遇到错误

sue@suepc:~$ sudo mysqld_safe --skip-grant-tables &

[1] 7002

sue@suepc:~$ 2018-04-19T07:16:59.386922Z mysqld_safe Logging to '/var/log/mysql/error.log'.

2018-04-19T07:16:59.475648Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql或mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

则执行下面命令后再次启动mysqld_safe。

(sudo mkdir -p /var/run/mysqld

sudo chown mysql:mysql /var/run/mysqld

)

3. 进入mysql，执行两条命令：

$ mysql -u root

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('123456') , password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 1

mysql> update mysql.user set plugin="mysql_native_password";

Query OK, 1 row affected (0.00 sec)

Rows matched: 3  Changed: 1  Warnings: 0

注意Mysql 5.7版本要修改的字段为authentication_string，而不是Password。另外，plugin字段的值要置成"mysql_native_password"。

4. 退出mysql

5. 停止Mysql

$ sudo /etc/init.d/mysql stop

[ ok ] Stopping mysql (via systemctl): mysql.service.

$ sudo kill -9 $(pgrep mysql)

6. 再次启动mysql

$ sudo /etc/init.d/mysql start

[ ok ] Starting mysql (via systemctl): mysql.service.

再次用密码可以登录。登陆后退出mysql.

$ mysql -uroot -p

Enter password:

mysql> quit

Bye