开发者社区> 潇湘隐者> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)的真正原因

简介:
+关注继续查看

在博客Linux mysql 5.6: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)里 面,我介绍了一下安装MySQL后登陆MySQL时会遇到ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 这个错误,当时不知道真正的原因,搜索了一些网上的资料,测试验证了如何解决这个问题,但是一直不知道具体的原因。今天终于把这个问题给彻底搞清楚了。

下面我以MySQL 5.6.20为例子,刨析一下这个错误的真正原因,如下所示

[root@DB-Server tmp]# rpm -ivh MySQL-server-advanced-5.6.20-1.rhel5.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server-advanced  ########################################### [100%]
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
2015-03-26 10:49:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-26 10:49:07 5082 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-26 10:49:07 5082 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-26 10:49:07 5082 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-26 10:49:07 5082 [Note] InnoDB: Memory barrier is not used
2015-03-26 10:49:07 5082 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-26 10:49:07 5082 [Note] InnoDB: Using Linux native AIO
2015-03-26 10:49:07 5082 [Note] InnoDB: Using CPU crc32 instructions
2015-03-26 10:49:07 5082 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-03-26 10:49:07 5082 [Note] InnoDB: Completed initialization of buffer pool
2015-03-26 10:49:07 5082 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2015-03-26 10:49:07 5082 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2015-03-26 10:49:07 5082 [Note] InnoDB: Database physically writes the file full: wait...
2015-03-26 10:49:07 5082 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-03-26 10:49:07 5082 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-03-26 10:49:08 5082 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-03-26 10:49:08 5082 [Warning] InnoDB: New log files created, LSN=45781
2015-03-26 10:49:08 5082 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-03-26 10:49:08 5082 [Note] InnoDB: Doublewrite buffer created
2015-03-26 10:49:08 5082 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-26 10:49:08 5082 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-03-26 10:49:08 5082 [Note] InnoDB: Foreign key constraint system tables created
2015-03-26 10:49:08 5082 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-03-26 10:49:08 5082 [Note] InnoDB: Tablespace and datafile system tables created.
2015-03-26 10:49:08 5082 [Note] InnoDB: Waiting for purge to start
2015-03-26 10:49:08 5082 [Note] InnoDB: 5.6.20 started; log sequence number 0
2015-03-26 10:49:08 5082 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-03-26 10:49:08 5082 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
A random root password has been set. You will find it in '/root/.mysql_secret'.
2015-03-26 10:49:09 5082 [Note] Binlog end
2015-03-26 10:49:09 5082 [Note] InnoDB: FTS optimize thread exiting.
2015-03-26 10:49:09 5082 [Note] InnoDB: Starting shutdown...
2015-03-26 10:49:10 5082 [Note] InnoDB: Shutdown completed; log sequence number 1625977
 
 
2015-03-26 10:49:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-26 10:49:10 5104 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-26 10:49:10 5104 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-26 10:49:10 5104 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-26 10:49:10 5104 [Note] InnoDB: Memory barrier is not used
2015-03-26 10:49:10 5104 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-26 10:49:10 5104 [Note] InnoDB: Using Linux native AIO
2015-03-26 10:49:10 5104 [Note] InnoDB: Using CPU crc32 instructions
2015-03-26 10:49:10 5104 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-03-26 10:49:10 5104 [Note] InnoDB: Completed initialization of buffer pool
2015-03-26 10:49:10 5104 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-26 10:49:10 5104 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-26 10:49:10 5104 [Note] InnoDB: Waiting for purge to start
2015-03-26 10:49:10 5104 [Note] InnoDB: 5.6.20 started; log sequence number 1625977
2015-03-26 10:49:10 5104 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-03-26 10:49:10 5104 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-03-26 10:49:10 5104 [Note] Binlog end
2015-03-26 10:49:10 5104 [Note] InnoDB: FTS optimize thread exiting.
2015-03-26 10:49:10 5104 [Note] InnoDB: Starting shutdown...
2015-03-26 10:49:12 5104 [Note] InnoDB: Shutdown completed; log sequence number 1625987
 
 
 
 
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
 
You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.
 
Also, the account for the anonymous user has been removed.
 
In addition, you can run:
 
  /usr/bin/mysql_secure_installation
 
which will also give you the option of removing the test database.
This is strongly recommended for production servers.
 
See the manual for more instructions.
 
Please report any problems at http://bugs.mysql.com/
 
The latest information about MySQL is available on the web at
 
  http://www.mysql.com
 
Support MySQL by buying support/licenses at http://shop.mysql.com
 
WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.

 

MySQL 的安装日志信息中,你会看到MySQL生成了root用户的随机密码(如下截图所示),并将这个随机密码放置在/root/.mysql_secret 中。并且强制在第一次登陆时修改root用户的密码。Mysql 5.6及以后版本出处于安全考虑,root密码不为空。所以在MySQL的安装过程中,会生成一个随机密码,但是对于我们这样的初学者,不了解情况,于是 便从网上搜索如何解决问题的资料,而问题关键在此,很多做技术的人,了解不够深入,又喜欢随波逐流,停留在问题的表层,并未深入研究。而高手又不屑于这类 简单的问题。

clip_image001

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !

You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,

no other statement but 'SET PASSWORD' will be accepted.

See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

知道了错误的前因后果,那么很容易就搞定这个问题了。找到随机密码,登录数据库后修改密码。问题解决

image

 

[root@DB-Server tmp]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.20-enterprise-commercial-advanced
 
Copyright (c) 2000, 2014, 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> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('wer@34');
Query OK, 0 rows affected (0.03 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

clip_image002

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql_异常_01_Access denied for user 'root'@'192.168.1.13' (using password: YES)
一、异常现象 使用navicat premuim 连接 虚拟机mysql数据库时 ,抛出如下错误: Access denied for user 'root'@'192.168.1.13' (using password: YES)     二、异常原因  这是因为虽然用户名和密码正确,但是却没有在其他ip地址上访问的权限。
3492 0
MySQL用户权限(Host,User,Password)管理(mysql.user)
1:新增用户: 注:mysql数据库下user表中,Host和User为两个主键列(primary key),已经各版本下非空未设置默认字段。 登录后,切换db: mysql> use mysql; Reading table information for completion of table and column names You can turn off this f
5288 0
#1045 - Access denied for user 'root'@'localhost' (using password: NO)
看到phpadmin说root帐号没有设置密码,不安全,设置了之后就不能登录了,想起来需要修改phpadmin中的某个文件来修改响应的密码, 有人说要修改phpMyAdmin\config.inc.php中的$cfg['Servers'][$i]['password'],有人说要修改phpMyAdmin\libraries\config.default.php中的$cfg['Servers'][$i]['password'],经过我的实验发现是前者。
769 0
mysql ERROR 1045 (28000): Access denied for user解决方法
  问题重现(以下讨论范围仅限Windows环境): C:\AppServ\MySQL> mysql -u root -pEnter password:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)   编辑mysql配置文件my.ini(不知道在哪请搜索),在[mysqld]这个条目下加入  skip-grant-tables保存退出后重启mysql 1.点击“开始”->“运行”(快捷键Win+R)。
1090 0
Java Swing LookAndFeel Demo
不说什么了,直接贴代码吧!呀的也太直接了点这博客写的   import java.awt.BorderLayout; import java.awt.GridBagConstraints; import java.
636 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
777
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载