问题描述:
在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