mysql 5.7 user表为空_mysql5.7 mysql库下面的user表没有password字段无法修改密码

如题所述，mysql5.7  mysql库下面的user表没有password字段无法修改密码，

5.7版本已经不再使用password来作为密码的字段了  而改成了authentication_string

登陆密码记不起：

[[email protected] ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user [email protected] (using password: NO)

更改配置

vi /etc/my.cnf

#在mysqld模块下添加

skip-grant-tables

重启服务

systemctl restart mysqld

按照传统改密码方式发现没有 password这个字段。

mysql> update user set password=password(‘123‘) where user=‘root‘;

ERROR1054 (42S22): Unknown column ‘password‘ in ‘field list‘

查询user表结构

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 | |

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

网上查了一下据说5.7 版本password 字段改成authentication_string  password函数还是原来的password函数

mysql> update user set authentication_string=password(‘123‘) where user=‘root‘;

Query OK,1 row affected, 1 warning (0.00sec)

Rows matched:1 Changed: 1 Warnings: 1

#更改配置重启mysql  成功登陆

[[email protected] ~]# vi /etc/my.cnf

注释 # skip-grant-tables

[[email protected]~]# systemctl restart mysqld

[[email protected]~]# mysql -uroot -p123

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 connectionid is 2Server version:5.7.21-log

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 clearthe current input statement.

mysql>