linux下mysql5.7.12忘记密码

一. 碰到问题

问题：mac上安装的mysql5.7.12，长时间不用，忘记密码，navicat连接时提示：
Access denied for user ‘root’@’localhost’ (using password: YES)

解决：重置密码

二. 重置密码详情:

2.1 关闭mysql服务

mac下可在系统偏好设置中关闭或在终端中使用kill强制关闭,进入root用户

xiaoxiangdeMacBook-Pro:bin root# ps -ef|grep mysql
   74   838     1   0 10:17AM ??         0:00.53 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid
    0  1124   814   0 10:36AM ttys000    0:00.00 grep mysql
xiaoxiangdeMacBook-Pro:bin root# kill -9 838
xiaoxiangdeMacBook-Pro:bin root# ps -ef|grep mysql
    0  1133   814   0 10:37AM ttys000    0:00.00 grep mysql

2.2 启动mysql的安全模式

命令：./mysqld_safe –user=root –skip-grant-tables –skip-networking &

xiaoxiangdeMacBook-Pro:bin root# pwd
/usr/local/mysql/bin
xiaoxiangdeMacBook-Pro:bin root# ./mysqld_safe --user=root --skip-grant-tables --skip-networking & 
[1] 1184
xiaoxiangdeMacBook-Pro:bin root# 2016-08-09T02:40:37.6NZ mysqld_safe Logging to '/usr/local/mysql/data/xiaoxiangdeMacBook-Pro.local.err'.
2016-08-09T02:40:37.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

xiaoxiangdeMacBook-Pro:bin root# ps -ef|grep mysql
    0  1184   814   0 10:40AM ttys000    0:00.02 /bin/sh ./mysqld_safe --user=root --skip-grant-tables --skip-networking
    0  1296  1184   0 10:40AM ttys000    0:00.25 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --skip-grant-tables --skip-networking --log-error=/usr/local/mysql/data/xiaoxiangdeMacBook-Pro.local.err --pid-file=/usr/local/mysql/data/xiaoxiangdeMacBook-Pro.local.pid
    0  1303   814   0 10:41AM ttys000    0:00.00 grep mysql

2.3 使用root用户免密码登陆mysql

命令：mysql -u root -p

xiaoxiangdeMacBook-Pro:bin root# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

提示：如果在mysql正常运行的情况下，输入mysql提示：
mysql command not found
遇上-bash: mysql: command not found，是因为/usr/local/bin目录下没有mysql，需要建立软链接:把mysql安装目录，比如/usr/local/mysql/bin/mysql，映射到/usr/local/bin目录下：

# cd /usr/local/bin
# ln -fs /usr/local/mysql/bin/mysql mysql

还有其它常用命令mysqladmin、mysqldump等不可用时候都可按用此方法解决。创建后可查看:

xiaoxiangdeMacBook-Pro:~ root# ls /usr/local/bin
bbdiff      idea        prl_convert prlcore2dmp prlsrvctl
bbedit      mysql       prl_disk_tool   prlctl
bbfind      mysqladmin  prl_perf_ctl    prlexec

2.4 选择使用mysql数据库

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

2.5 重置密码

查看当前密码，已加密的。注意mysql的版本不同，密码存储的字段不同，首先查看mysql版本是5.7.12，密码存储字段为authentication_string；

xiaoxiangdeMacBook-Pro:bin root# mysql --version
mysql  Ver 14.14 Distrib 5.7.12, for osx10.11 (x86_64) using  EditLine wrapper

mysql> select user,host,authentication_string from user where user="root";
+------+-----------+-------------------------------------------+
| user | host      | authentication_string                     |
+------+-----------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 

重置密码，并查看,刷新权限，并退出mysql

mysql> update user set authentication_string=password('123456') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select user,host,authentication_string from user where user="root";
+------+-----------+-------------------------------------------+
| user | host      | authentication_string                     |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;

2.6 退出安全模式

xiaoxiangdeMacBook-Pro:bin root# ps -ef|grep mysql
    0  1184   814   0 10:40AM ttys000    0:00.02 /bin/sh ./mysqld_safe --user=root --skip-grant-tables --skip-networking
    0  1296  1184   0 10:40AM ttys000    0:00.54 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=root --skip-grant-tables --skip-networking --log-error=/usr/local/mysql/data/xiaoxiangdeMacBook-Pro.local.err --pid-file=/usr/local/mysql/data/xiaoxiangdeMacBook-Pro.local.pid
    0  1755   814   0 10:57AM ttys000    0:00.00 grep mysql
xiaoxiangdeMacBook-Pro:bin root# kill -9 1184
xiaoxiangdeMacBook-Pro:bin root# kill -9 1296
xiaoxiangdeMacBook-Pro:bin root# ps -ef|grep mysql
    0  1781   814   0 10:58AM ttys000    0:00.00 grep mysql

2.7 重新启动mysql，重启不了可以先重启一下机器，用navicat连接成功

以上，有问题请留言