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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

在博客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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
关系型数据库 MySQL 网络安全
DBeaver连接MySQL提示Access denied for user ‘‘@‘ip‘ (using password: YES)
“Access denied for user ''@'ip' (using password: YES)”错误通常与MySQL用户权限配置或网络设置有关。通过检查并正确配置用户名和密码、用户权限、MySQL配置文件及防火墙设置,可以有效解决此问题。希望本文能帮助您成功连接MySQL数据库。
320 4
|
6月前
|
SQL 关系型数据库 MySQL
mysql密码错误-ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using passwor:yes)
这篇文章提供了解决MySQL数据库"Access denied for user 'root'@'localhost' (using password: YES)"错误的方法,通过跳过密码验证、修改root密码,然后重启服务来解决登录问题。
mysql密码错误-ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using passwor:yes)
|
7月前
|
安全 关系型数据库 MySQL
【Python】已解决:pymysql.err.OperationalError:(2003 “Can’t connect to MySQL server on ‘localhost’ ([WinEr
【Python】已解决:pymysql.err.OperationalError:(2003 “Can’t connect to MySQL server on ‘localhost’ ([WinEr
930 1
|
7月前
|
关系型数据库 MySQL 数据库连接
Cant‘t connect to MySQL server on ‘localhost‘ (10038)
Cant‘t connect to MySQL server on ‘localhost‘ (10038)
|
7月前
|
关系型数据库 MySQL 数据库
mysql 中登录报错:ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)ERROR
mysql 中登录报错:ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)ERROR
|
22天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
8天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
79 42
|
26天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
204 0
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
72 3
|
2月前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
116 3