linux下mysql5.7忘记密码修改_Linux下mysql5.7忘记root密码的解决方法

一、首先更改my.cnf的配置文件，并重启mysql

在my.cnf文件中的[mysqld] 下加入下面一行，其余不做改变。

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables

[root@localhost ~]# systemctl restart mysqld

二、登录MySQL,此时不需要输入密码，直接回车即可

mysql -uroot -p

三、切换到mysql数据库，查询user表的结构，这里有需要的字段。

在MySQL5.7版本中mysql数据库下已经没有password这个字段了，password字段改成了authentication_string字段。

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> desc user;

+------------------------+-----------------------------------+------+-----+-----------------------+------- +

| Field | Type | Null | Key | Default | Extra |

+------------------------+-----------------------------------+------+-----+-----------------------+------- +

| Host | char(60) | NO | PRI | | |

| User | char(32) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| Create_tablespace_priv | enum('N','Y') | NO | | N | |

| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |

| ssl_cipher | blob | NO | | NULL | |

| x509_issuer | blob | NO | | NULL | |

| x509_subject | blob | NO | | NULL | |

| max_questions | int(11) unsigned | NO | | 0 | |

| max_updates | int(11) unsigned | NO | | 0 | |

| max_connections | int(11) unsigned | NO | | 0 | |

| max_user_connections | int(11) unsigned | NO | | 0 | |

| plugin | char(64) | NO | | mysql_native_password | |

| authentication_string | text | YES | | NULL | |

| password_expired | enum('N','Y') | NO | | N | |

| password_last_changed | timestamp | YES | | NULL | |

| password_lifetime | smallint(5) unsigned | YES | | NULL | |

| account_locked | enum('N','Y') | NO | | N | |

+------------------------+-----------------------------------+------+-----+-----------------------+------- +

45 rows in set (0.00 sec)

把这个复制下来，修改密码时会用到，当然也可以手打。

四、修改mysql的root密码并退出mysql

mysql> update user

-> set authentication_string=password('ABCabc123!')

-> where user='root' and host='loacalhost';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 1

mysql> \q

Bye

五、再次修改my.cnf配置文件，将第一步添加的语句注释或删除,然后重启mysql。

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

#skip-grant-tables

[root@localhost ~]# systemctl restart mysqld

六、用新密码登录mysql

[root@localhost ~]# mysql -uroot -pABCabc123!

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 2

Server version: 5.7.14 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>

注意：如果不行的话，你可能还需要执行下面这一步。我用的 MySQL5.7 要执行下面一步。

mysql> alter user root@localhost identified by '123';