MySQL forgot password

MySQL 忘记密码

注：vim 修改文件 i 保存退出 :wq ， 不修改退出 :q!

关闭服务

[root@Cc ~]# ps -ef |grep mysql
root      3658  8508  0 12:47 pts/1    00:00:00 grep --color=auto mysql
mysql    20039     1  0 12:39 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
[root@Cc ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@Cc ~]#

修改配置文件

在配置文件中添加 skip-grant-tables

[root@Cc ~]# vim /etc/my.cnf
.....
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
[client]
default-character-set=utf8
...
:wq

重启服务

[root@Cc ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@Cc ~]#

进入 mysql

输入 mysql -u root -p 直接 Enter、Enter

[root@Cc ~]# mysql -u root -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> 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> update mysql.user set authentication_string=password('password') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> exit
Bye

进入数据库 修改表

update mysql.user set authentication_string=password('这里是填写mysql密码') where user='root';

删除修改的配置文件

skip-grant-tables

[root@Cc ~]# vim /etc/my.cnf
...
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
[client]
default-character-set=utf8
...
:wq

重启 mysqld 服务

[root@Cc ~]# service mysqld restart

出现错误

确认问题

• 重启服务还是没有用
• 确认过 update 语句也执行过了（检查 执行 sql语句看下执行结果，影响行数）
• 确认输入的 mysql -u root -p 之后输入的密码是否是自己 update的密码

查看 user表

下面默认值是 N 使用 update 语句设置为 Y ，个人使用 navicat 工具改的，是把密码去掉然后再用给你工具改

mysql> select * from user where user ="root"\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *A021A79F3EE5D774BF4197B804D3FDAAE6511167
      password_expired: N
 password_last_changed: 2020-04-18 13:15:34
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

修改成功

[root@Cc filebrowser]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 105
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>

登陆成功

也可以使用其他方式更改密码