MySQL密码破解或没有密码情况重设用户密码

MySQL环境信息情况：

# MySQL版本信息
[root@joa-cdep-cdh04 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using  EditLine wrapper
[root@joa-cdep-cdh04 ~]# 
# 3306端口正常开启中
[root@joa-cdep-cdh04 ~]# netstat -tnlpu|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      2245/mysqld         
[root@joa-cdep-cdh04 ~]# 
# mysqld服务正常运行中
[root@joa-cdep-cdh04 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-12-01 13:55:25 CST; 1min 30s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2243 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 2223 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 2245 (mysqld)
    Tasks: 27
   Memory: 170.3M
   CGroup: /system.slice/mysqld.service
           └─2245 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Dec 01 13:55:24 joa-cdep-cdh04 systemd[1]: Starting MySQL Server...
Dec 01 13:55:25 joa-cdep-cdh04 systemd[1]: Started MySQL Server.
[root@joa-cdep-cdh04 ~]# 

模拟忘记密码场景：

# 登录mysql, mysql -uroot -p 使用直接回车或者密码错误模拟mysql没有密码登录不上
[root@joa-cdep-cdh04 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@joa-cdep-cdh04 ~]# 

开启跳过用户名密码验证功能：

# 1. 停止mysqld服务
[root@joa-cdep-cdh04 ~]# systemctl stop mysqld
[root@joa-cdep-cdh04 ~]# 
# 使用mysqld --skip-grant-tables启动mysqld ( 让mysql跳过用户名密码验证功能 )
[root@joa-cdep-cdh04 ~]# mysqld --skip-grant-tables
2021-12-01T05:57:53.512906Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-12-01T05:57:53.515226Z 0 [Note] mysqld (mysqld 5.7.29) starting as process 2421 ...
2021-12-01T05:57:53.517855Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2021-12-01T05:57:53.517907Z 0 [ERROR] Aborting
2021-12-01T05:57:53.517939Z 0 [Note] Binlog end
2021-12-01T05:57:53.520137Z 0 [Note] mysqld: Shutdown complete

# **** 报错ERROR **** #
# 仔细看一下报错信息：[ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
# 此处 mysql是出于安全考虑，默认拒绝用root账号启动mysql服务，官方给出的解决方式是指定root登录，这样避免不指定默认root情况

[root@joa-cdep-cdh04 ~]# 
[root@joa-cdep-cdh04 ~]# mysqld --skip-grant-tables --user=root

# [ 注意 ] 复制一个连接窗口，或者再启一个连接窗口，当前前台启动任务不要去中断
# [ 注意 ] 复制一个连接窗口，或者再启一个连接窗口，当前前台启动任务不要去中断
# [ 注意 ] 复制一个连接窗口，或者再启一个连接窗口，当前前台启动任务不要去中断

新建会话无验证登录MySQL：

# 在新窗口中尝试登录mysql ；Enter password: 不要输入密码直接回车
[root@joa-cdep-cdh04 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> update mysql.user set password=password('123456') where user='root' and host='localhost';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> 
# **** 报错ERROR **** #
# 仔细看一下报错信息：ERROR 1054 (42S22): Unknown column 'password' in 'field list'
# 此处错误的原因是 mysql 在5.7版本下的mysql数据库下已经没有password这个字段了，password字段对应改成 authentication_string
mysql> 
# 再次尝试

mysql> update mysql.user set authentication_string=password('123456') where user='root' and host='localhost'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1
# 本次成功

# 刷新配置即时生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 退出mysql交互式
mysql> quit
Bye
[root@joa-cdep-cdh04 ~]# 

使用新设密码验证登录MySQL：

# 验证登录mysql
[root@joa-cdep-cdh04 ~]# mysql -uroot -p123456
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.29 MySQL Community Server (GPL)

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

# 成功使用新密码123456登录成功
mysql>