Ubuntu下Mysql 5.7.19 root密码重置

在阿里云ECS下的Ubuntu 16.04安装Mysql，紧接着就用mysql_secure_installation安装，并且设置了root的密码。但是完毕后居然不能登录，百思不得其解。

$ mysql -uroot -p

Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

 

无奈只好重置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

 

（如果遇到错误

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，执行两条命令（PASSWORD('NewPassword')里的NewPassword替换成你的密码）：

$ mysql -u root

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('NewPassword') , 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

mysql> quit
Bye

 

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 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

 

参考文档：

https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

https://www.linuxquestions.org/questions/linux-software-2/mysql-mariadb-install-root-password-not-working-4175602071/

http://www.jb51.net/article/77858.htm