当前位置: 首页 >数据库 > mysql5.7.x修改密码

mysql5.7.x修改密码

今天访问数据库提示密码错误:

[root@node ~]# mysql -uroot -hlocalhost -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@node ~]# mysql -uroot -hlocalhost -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@node ~]# 

 思来想去,一直想不起密码,最后只有关闭数据库通过添加参数"--skip-grant-tables"来重启启动:

[root@node ~]# /usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf --skip-grant-tables &[1] 14254[root@node ~]# 2018-05-06T07:54:11.083543Z mysqld_safe Logging to '/data/mysql/error.log'.2018-05-06T07:54:11.107761Z mysqld_safe Starting mysqld daemon with databases from /data/mysql[root@node ~]# ps axu|grep mysqlroot  14254  0.5  0.0 113252  1648 pts/0S15:540:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tablesmysql 15564  1.9 14.5 2265704 272520 pts/0  Sl15:540:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --skip-grant-tables --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=node.pid --socket=/tmp/mysql.sock --port=3306root  15606  0.0  0.0 112644948 pts/0S+15:540:00 grep --color=auto mysql[root@node ~]# 

 

重启MySQL服务后,就开始着手修改密码:

 1 [root@node ~]# mysql 2 Welcome to the MySQL monitor.  Commands end with ; or \g. 3 Your MySQL connection id is 2 4 Server version: 5.7.22-log MySQL Community Server (GPL) 5  6 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 7  8 Oracle is a registered trademark of Oracle Corporation and/or its 9 affiliates. Other names may be trademarks of their respective10 owners.11 12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.13 14 root@db 15:56:  [(none)]> use mysql;15 Database changed16 root@db 15:56:  [mysql]> update user set password=password('root@123') where user='root';17 ERROR 1054 (42S22): Unknown column 'password' in 'field list'18 root@db 15:57:  [mysql]> 

奇怪了,难道我记错了,后来再一次执行该命令,发现仍然爆出同样的错误,user表中居然没有password这个字段,看了下MySQL版本:5.7.22看来跟5.5 5.6有些不同,进一步查看user表结构:

mysql5.7.x修改密码 _ JavaClub全栈架构师技术笔记
 1 root@db 15:57:  [mysql]> desc user; 2 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 3 | Field  | Type  | Null | Key | Default| Extra | 4 +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 5 | Host| char(60)  | NO| PRI ||| 6 | User| char(32)  | NO| PRI ||| 7 | Select_priv| enum('N','Y') | NO| | N || 8 | Insert_priv| enum('N','Y') | NO| | N || 9 | Update_priv| enum('N','Y') | NO| | N ||10 | Delete_priv| enum('N','Y') | NO| | N ||11 | Create_priv| enum('N','Y') | NO| | N ||12 | Drop_priv  | enum('N','Y') | NO| | N ||13 | Reload_priv| enum('N','Y') | NO| | N ||14 | Shutdown_priv  | enum('N','Y') | NO| | N ||15 | Process_priv| enum('N','Y') | NO| | N ||16 | File_priv  | enum('N','Y') | NO| | N ||17 | Grant_priv | enum('N','Y') | NO| | N ||18 | References_priv| enum('N','Y') | NO| | N ||19 | Index_priv | enum('N','Y') | NO| | N ||20 | Alter_priv | enum('N','Y') | NO| | N ||21 | Show_db_priv| enum('N','Y') | NO| | N ||22 | Super_priv | enum('N','Y') | NO| | N ||23 | Create_tmp_table_priv  | enum('N','Y') | NO| | N ||24 | Lock_tables_priv| enum('N','Y') | NO| | N ||25 | Execute_priv| enum('N','Y') | NO| | N ||26 | Repl_slave_priv| enum('N','Y') | NO| | N ||27 | Repl_client_priv| enum('N','Y') | NO| | N ||28 | Create_view_priv| enum('N','Y') | NO| | N ||29 | Show_view_priv | enum('N','Y') | NO| | N ||30 | Create_routine_priv| enum('N','Y') | NO| | N ||31 | Alter_routine_priv | enum('N','Y') | NO| | N ||32 | Create_user_priv| enum('N','Y') | NO| | N ||33 | Event_priv | enum('N','Y') | NO| | N ||34 | Trigger_priv| enum('N','Y') | NO| | N ||35 | Create_tablespace_priv | enum('N','Y') | NO| | N ||36 | ssl_type| enum('','ANY','X509','SPECIFIED') | NO| |||37 | ssl_cipher | blob  | NO| | NULL  ||38 | x509_issuer| blob  | NO| | NULL  ||39 | x509_subject| blob  | NO| | NULL  ||40 | max_questions  | int(11) unsigned  | NO| | 0 ||41 | max_updates| int(11) unsigned  | NO| | 0 ||42 | max_connections| int(11) unsigned  | NO| | 0 ||43 | max_user_connections| int(11) unsigned  | NO| | 0 ||44 | plugin | char(64)  | NO| | mysql_native_password ||45 | authentication_string  | text  | YES  | | NULL  ||46 | password_expired| enum('N','Y') | NO| | N ||47 | password_last_changed  | timestamp | YES  | | NULL  ||48 | password_lifetime  | smallint(5) unsigned  | YES  | | NULL  ||49 | account_locked | enum('N','Y') | NO| | N ||50 +------------------------+-----------------------------------+------+-----+-----------------------+-------+51 45 rows in set (0.00 sec)52 53 root@db 16:01:  [mysql]> 
View Code

确实没看到password名字的字段,但是发现一个可疑字段"authentication_string",于是尝试用该字段再执行修改操作:

mysql5.7.x修改密码 _ JavaClub全栈架构师技术笔记
 1 root@db 16:03:  [mysql]> use mysql; 2 Database changed 3 root@db 16:03:  [mysql]> update user set authentication_string=password('root@123') where user='root'; 4 Query OK, 0 rows affected, 1 waing (0.00 sec) 5 Rows matched: 1  Changed: 0  Waings: 1 6  7 root@db 16:04:  [mysql]> flush privileges; 8 Query OK, 0 rows affected (0.39 sec) 9 10 root@db 16:04:  [mysql]> exit11 Bye
View Code

居然可以了,进一步登陆了认证:

mysql5.7.x修改密码 _ JavaClub全栈架构师技术笔记
 1 [root@node ~]# mysql -uroot -hlocalhost -proot@123 2 mysql: [Waing] Using a password on the command line interface can be insecure. 3 Welcome to the MySQL monitor.  Commands end with ; or \g. 4 Your MySQL connection id is 3 5 Server version: 5.7.22-log MySQL Community Server (GPL) 6  7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 8  9 Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective11 owners.12 13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.14 15 root@db 16:04:  [(none)]> show databases;16 +--------------------+17 | Database|18 +--------------------+19 | information_schema |20 | mysql  |21 | performance_schema |22 | sys|23 +--------------------+24 4 rows in set (0.00 sec)25 26 root@db 16:05:  [(none)]> 
View Code

看来MySQL5.7跟MySQL5.6 MySQL 5.5在密码修改方面也做了改动,作为笔记仅此而已

 如果新安装的MySQL5.7.x 是不允许直接登录:

 1 [root@node bin]# pwd 2 /usr/local/mysql/bin 3 [root@node bin]#  4 [root@node bin]# ./mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/ --basedir=/usr/local/mysql --user=mysql --initialize 5 [root@node bin]#  6 [root@node bin]#  7 [root@node bin]# ./mysqld_safe --defaults-file=/etc/my.cnf & 8 [1] 19041 9 [root@node bin]# 2018-05-25T21:21:30.360117Z mysqld_safe Logging to '/data/mysql/error.log'.10 2018-05-25T21:21:30.425323Z mysqld_safe Starting mysqld daemon with databases from /data/mysql11 12 [root@node bin]# 13 [root@node mysql]# ps xua|grep mysql14 root  19041  0.6  0.0 113252  1660 pts/0S05:210:00 /bin/sh ./mysqld_safe --defaults-file=/etc/my.cnf15 mysql 20336  2.2 14.0 2332432 262536 pts/0  Sl05:210:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=node.pid --socket=/tmp/mysql.sock --port=330616 root  20387  0.0  0.0 112644952 pts/1S+05:210:00 grep --color=auto mysql17 [root@node mysql]# 18 19 [root@node bin]# ./mysql20 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)21 [root@node bin]# ./mysql -uroot -hlocalhost22 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)23 [root@node bin]# 

从这里发现MySQL5.7.x已经对访问权限做了限制,从前面的提示中可以发现初始密码在error.log中

1 [root@node bin]# grep password /data/mysql/error.log 2 2018-05-25T21:21:07.429395Z 1 [Note] A temporary password is generated for root@localhost: U7zvi&##Ti<A3 2018-05-25T21:25:10.090750Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)4 2018-05-25T21:25:17.410963Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)5 [root@node bin]# 

所以这里可以用该密码就行登录:

 1 [root@node bin]# mysql: [Waing] Using a password on the command line interface can be insecure. 2 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 3  4 [2]+  Exit 1  ./mysql -uroot -hlocalhost -pU7zvi 5 [root@node bin]# ./mysql -uroot -hlocalhost -p 6 Enter password:  7 Welcome to the MySQL monitor.  Commands end with ; or \g. 8 Your MySQL connection id is 5 9 Server version: 5.7.22-log10 11 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.12 13 Oracle is a registered trademark of Oracle Corporation and/or its14 affiliates. Other names may be trademarks of their respective15 owners.16 17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.18 19 root@db 05:27:  [(none)]> show databases;20 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.21 root@db 05:27:  [(none)]> 

发现初始密码可以登录,但是不能操作数据库,比如show databases命令,所以修改初始密码:

 1 root@db 05:27:  [(none)]> show databases; 2 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. 3 root@db 05:27:  [(none)]> set PASSWORD='root@123'; 4 Query OK, 0 rows affected (0.00 sec) 5  6 root@db 05:29:  [(none)]> flush privileges; 7 Query OK, 0 rows affected (0.01 sec) 8  9 root@db 05:29:  [(none)]>10 root@db 05:29:  [(none)]> show databases;11 +--------------------+12 | Database|13 +--------------------+14 | information_schema |15 | mysql  |16 | performance_schema |17 | sys|18 +--------------------+19 4 rows in set (0.00 sec)20 21 root@db 05:29:  [(none)]> 

所以对于MySQL5.7.x安装后必须修改密码然后才能进行其他操作,另外初始密码是保存在错误日志中。

 

作者:kindnull
来源链接:https://www.cnblogs.com/kindnull/p/8998530.html

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。





本文链接:https://www.javaclub.cn/database/118521.html

分享给朋友:

“mysql5.7.x修改密码” 的相关文章

JDBC工具类(DButil) 2022年05月13日 09:13:25
数据库之事务及事务的 ACID 性质 2022年05月21日 11:37:16
mysql查询最新的一条记录 2022年06月06日 16:04:12
MySQL学习(4)︱数据库的查询 2022年06月07日 01:52:58
mysql 查询表中前10条数据 2022年06月08日 04:35:17
mysql查询给某个字段赋值 2022年06月10日 21:43:53