暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL忘记密码处理方式 | 5.7及8.0

MySQL忘记密码处理方式 | 5.7及8.0

原创 谢辉元 2021-12-31
1138

MySQL 5.7 忘记密码

1 ) 增加参数 --skip-grant-tables 跳过授权表方式启动数据库

mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --skip-grant-tables &

mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --skip-grant-tables &
[root@ora11g1 ~]# ps -ef|grep mysql
root      3259  3049  0 01:46 pts/3    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --skip-grant-tables
mysql     4317  3259  0 01:46 pts/3    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/u01/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --skip-grant-tables --log-error=/u01/mysql/mysql3306/logs/mysql-error-log.err --open-files-limit=65535 --pid-file=ora11g1.pid --socket=/u01/mysql/mysql3306/run/mysql.sock --port=3306
2 ) 使用空密码连接数据库
[root@ora11g1 ~]# mysql -uroot -S /u01/mysql/mysql3306/run/mysql.sock -P3306 -p
Enter password: 这里直接回车
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.
3 ) 使用更新权限表mysql.user方式修改密码

update mysql.user set authentication_string=PASSWORD(“654321”) where user=‘root’;

[root@3306][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-------------------------------------------+
| user | host | authentication_string                     |
+------+------+-------------------------------------------+
| root | %    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)
-- 直接使用set和alter方式修改密码都是失败

[root@3306][(none)]>>set PASSWORD = '654321';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
[root@3306][(none)]>>alter user 'root'@'localhost' identified by '654321';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

-- 但是先flush privileges后就可以使用alter方式修改密码了
[root@3306][(none)]>>flush privileges;
Query OK, 0 rows affected (0.06 sec)

[root@3306][(none)]>>alter user 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

使用update修改mysql.user表成功，这里注意需要使用PASSWORD函数，否则写入的authentication_string串直接是原文，登录是登录失败的
[root@3306][(none)]>>update mysql.user set authentication_string="654321" where user='root';           
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@3306][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-----------------------+
| user | host | authentication_string |
+------+------+-----------------------+
| root | %    | 654321                |
+------+------+-----------------------+
1 row in set (0.00 sec)

[root@3306][(none)]>>update mysql.user set authentication_string=PASSWORD("654321") where user='root'; 
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

[root@3306][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-------------------------------------------+
| user | host | authentication_string                     |
+------+------+-------------------------------------------+
| root | %    | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)


[root@3306][(none)]>>flush privileges;
Query OK, 0 rows affected (0.18 sec)
使用刚修改的密码登录：
[root@ora11g1 ~]# mysql -uroot -S /u01/mysql/mysql3306/run/mysql.sock -P3306 -p654321
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 3
Server version: 5.7.26-log MySQL Community Server (GPL)

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

[root@3306][(none)]>>

MySQL 8 忘记密码

1 ) 增加参数 --skip-grant-tables 跳过授权表方式启动数据库
/u01/mysql/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/u01/mysql/mysql3308/conf/my.cnf --user=mysql --skip-grant-tables &

 [root@ora11g1 ~]# ps -ef|grep mysql
root      6982  3124  0 02:37 pts/4    00:00:00 /bin/sh /u01/mysql/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/u01/mysql/mysql3308/conf/my.cnf --user=mysql --skip-grant-tables
mysql     8104  6982  1 02:37 pts/4    00:00:01 /u01/mysql/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/u01/mysql/mysql3308/conf/my.cnf --basedir=/u01/mysql/mysql-8.0.26-linux-glibc2.12-x86_64 --datadir=/u01/mysql/mysql3308/data --plugin-dir=/u01/mysql/mysql-8.0.26-linux-glibc2.12-x86_64/lib/plugin --user=mysql --skip-grant-tables --log-error=/u01/mysql/mysql3308/logs/mysql-error-log.err --open-files-limit=65535 --pid-file=ora11g1.pid --socket=/u01/mysql/mysql3308/run/mysql.sock --port=3308
2 ) 使用空密码连接数据库
mysql8 -uroot -S /u01/mysql/mysql3308/run/mysql.sock -P3308 -hlocalhost -p
Enter password: 这里直接回车
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

[root@mysql.sock][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-------------------------------------------+
| user | host | authentication_string                     |
+------+------+-------------------------------------------+
| root | %    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)
3 ) 使用alter和更新权限表mysql.user方式修改密码
在--skip-grant-tables模式下不允许使用set\alter设置密码
[root@mysql.sock][(none)]>>set PASSWORD = '654321';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
[root@mysql.sock][(none)]>>alter user 'root'@'%' identified by '654321';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

但是登录之后是可以使用flush privileges 重新加载权限表的，加载权限表后，再次使用alter设置密码就可以了。
[root@mysql.sock][(none)]>>flush privileges;
Query OK, 0 rows affected (0.05 sec)

[root@mysql.sock][(none)]>>set PASSWORD = '654321';
ERROR 1133 (42000): Can't find any matching row in the user table
[root@mysql.sock][(none)]>>alter user 'root'@'%' identified by '654321';
Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock][(none)]>>flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][(none)]>>exit
Bye
使用刚修改的密码登录：
(base) [root@ora11g1 ~]# mysql8 -uroot -S /u01/mysql/mysql3308/run/mysql.sock -P3308 -hlocalhost -p654321
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 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

[root@mysql.sock][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-------------------------------------------+
| user | host | authentication_string                     |
+------+------+-------------------------------------------+
| root | %    | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)
4 ) 使用更新权限表mysql.user方式修改密码
因为到8已经移除PASSWORD函数了，使用update更新密码就只能将authentication_string 设置为空值才能使用空值时登录，设置任何其他值都是登录不了的。
[root@mysql.sock][(none)]>>update mysql.user set authentication_string=PASSWORD("654321") where user='root'; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("654321") where user='root'' at line 1
[root@mysql.sock][(none)]>>update mysql.user set authentication_string="654321" where user='root'; 
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@mysql.sock][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-----------------------+
| user | host | authentication_string |
+------+------+-----------------------+
| root | %    | 654321                |
+------+------+-----------------------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]>>flush privileges;
Query OK, 0 rows affected (0.04 sec)

[root@mysql.sock][(none)]>>exit
Bye
(base) [root@ora11g1 ~]# mysql8 -uroot -S /u01/mysql/mysql3308/run/mysql.sock -P3308 -hlocalhost -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
(base) [root@ora11g1 ~]# 

[root@ora11g1 ~]# mysql8 -uroot -S /u01/mysql/mysql3308/run/mysql.sock -P3308 -hlocalhost -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

[root@mysql.sock][(none)]>>update mysql.user set authentication_string='' where user='root'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@mysql.sock][(none)]>>select user,host,authentication_string from mysql.user where user='root';
+------+------+-----------------------+
| user | host | authentication_string |
+------+------+-----------------------+
| root | %    |                       |
+------+------+-----------------------+
1 row in set (0.00 sec)

[root@mysql.sock][(none)]>>flush privileges;
Query OK, 0 rows affected (0.04 sec)

[root@mysql.sock][(none)]>>exit
Bye
(base) [root@ora11g1 ~]# mysql8 -uroot -S /u01/mysql/mysql3308/run/mysql.sock -P3308 -hlocalhost -p 直接回车
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

[root@mysql.sock][(none)]>>

总结

MySQL 5.7 忘记密码重置密码方式一:
  1. 关闭数据库
    2）增加 --skip-grant-tables 跳过授权表方式启动数据库
    3）使用空密码登录数据库
    4）直接修改权限表mysql.user 表authentication_string字段设置密码
    update mysql.user set authentication_string=PASSWORD(“654321”) where user=‘root’;
    设置密码后 刷新权限 flush privileges;
MySQL 5.7 忘记密码重置密码方式二:
  1. 关闭数据库
    2）增加 --skip-grant-tables 跳过授权表方式启动数据库
    3）使用空密码登录数据库
    4）刷新权限 flush privileges;
    5）使用 alter user方式设置密码
    alter user ‘root’@’%’ identified by ‘654321’;
    6）使用新密码登录
MySQL 8 忘记密码重置密码方式一，直接update 权限表只能修改为空密码才能成功。
MySQL 8 忘记密码重置密码方式二 同 MySQL 5.7 忘记密码重置密码方式二
墨力计划mysql
最后修改时间：2021-12-31 17:01:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论