MySQL数据库忘记密码不用怕

root等账号密码的丢失,影响是致命的。下面给出方法,无密码登录mysql,修改密码。
以下为5.7版本root账号的密码修改方法,5.6版本类似。

1.发现密码忘记了,无法登录数据库

[root@192-168-199-198 /]# /opt/mysql5.7/bin/mysql -uroot -pfander -h127.0.0.1 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

2.关闭数据库

我这里用service关闭。没有service,就只能kill掉了。

[root@192-168-199-198 init.d]# service mysql3307 stop
Shutting down MySQL(mysql3307)............ SUCCESS! 

3.加跳过权限表参数,重启数据库

这样不输入密码,也能进入数据库了。

## 临时加--skip-grant-tables参数启动,跳过权限表
/opt/mysql5.7/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
#无密码登录db
/opt/mysql5.7/bin/mysql -S /tmp/mysql3307.sock

4.设置新密码

MySQL5.7后password字段用authentication_string字段代替了。
use mysql;
update user set authentication_string=password('fander') where user='root' and host='%';
flush privileges;

5.去掉跳过权限表参数,重启数据库

#重启数据库
[root@192-168-199-198 init.d]# service mysql3307 restart
Shutting down MySQL(mysql3307)............ SUCCESS! 
Starting MySQL(mysql3307). SUCCESS! 
[1]+  Done                    /opt/mysql5.7/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables
#登录已无问题
[root@192-168-199-198 init.d]# /opt/mysql5.7/bin/mysql -uroot -pfander -h127.0.0.1 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> 

posted on 2019-01-29 02:59 fanderchan 阅读(...) 评论(...) 编辑 收藏

导航