程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Linux mysql 5.6: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO),28000denied

Linux mysql 5.6: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO),28000denied

編輯:MySQL綜合教程

Linux mysql 5.6: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO),28000denied


最近操作mysql 5.6, 出現了以下問題。

分享,感謝原著:

案例環境:

             操作系統 :Red Hat Enterprise Linux Server release 5.7 (Tikanga) 64 bit

          數據庫版本 : Mysql 5.6.19 64 bit

案例介紹:

今 天開始學習mysql,遂先安裝了Mysql 5.6.19 64bit 版本的數據庫,結果安裝成功了,但是使用root登錄時遇到了ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)錯誤. 如下所示

[root@DB-Server tmp]# rpm -ivh MySQL-server-5.6.19-1.rhel5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
[root@DB-Server tmp]# rmp -ivh MySQL-client-5.6.19-1.rhel5.x86_64.rpm 
-bash: rmp: command not found
[root@DB-Server tmp]# rpm -ivh MySQL-client-5.6.19-1.rhel5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
[root@DB-Server tmp]# /etc/rc.d/init.d/mysql status
MySQL is not running[FAILED]
[root@DB-Server tmp]# /etc/rc.d/init.d/mysql start
Starting MySQL.[  OK  ]
[root@DB-Server tmp]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@DB-Server tmp]# mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

安裝過程中沒有設置過root密碼,不清楚root密碼,搜索了網上一些資料,大致有下面一些情況:

1:有些博客說root的隨機密碼位於/root/.mysql_secret中,但是我這個版本根本沒有/root/.mysql_secret文件。搜索到一篇文章,大意如下所示(沒有確認)。到現在也沒有明白,我這個版本為什麼沒有生成這個文件?

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) :表示沒有生成root的臨時密碼

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) :表示生成了root的臨時密碼。

2:有些資料說root的默認密碼為空,經過確認那是以前的老版本,Mysql 5.6及以後版本出處於安全考慮,root密碼已經不為空了。

另外,我在官網論壇居然發現有很多人遇到類似問題ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

解決方法:

首先停掉mysql服務器

[root@DB-Server init.d]# /etc/rc.d/init.d/mysql stop
 
 Shutting down MySQL..[ OK ]
 

然後使用mysqld_safe命令在啟動mysql,更新root賬號的密碼

--skip-grant-tables:不啟動grant-tables(授權表),跳過權限控制。

--skip-networking :跳過TCP/IP協議,只在本機訪問(從網上有些資料看,這個選項不是必須的。可以不用)

[root@DB-Server init.d]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[2] 9396
[root@DB-Server init.d]# 140722 14:59:46 mysqld_safe Logging to '/var/lib/mysql/DB-Server.err'.
140722 14:59:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

執行上面命令後,此會話窗口會出現無反應的狀態,需要使用CTRL+C中斷會話,檢查/var/lib/mysql/DB-Server.err日志,發現也無其它異常信息。沒有弄明白該會話窗口為啥會出現無響應狀態。

tail -200 /var/lib/mysql/DB-Server.err | more
 
2014-07-22 14:59:41 9346 [Note] Shutting down plugin 'binlog'
2014-07-22 14:59:41 9346 [Note] /usr/sbin/mysqld: Shutdown complete
 
140722 14:59:41 mysqld_safe mysqld from pid file /var/lib/mysql/DB-Server.pid ended
140722 14:59:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-07-22 14:59:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timesta
mp server option (see documentation for more details).
2014-07-22 14:59:47 9516 [Note] Plugin 'FEDERATED' is disabled.
2014-07-22 14:59:47 9516 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-07-22 14:59:47 9516 [Note] InnoDB: The InnoDB memory heap is disabled
2014-07-22 14:59:47 9516 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-07-22 14:59:47 9516 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-07-22 14:59:47 9516 [Note] InnoDB: Using Linux native AIO
2014-07-22 14:59:47 9516 [Note] InnoDB: Using CPU crc32 instructions
2014-07-22 14:59:47 9516 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-07-22 14:59:47 9516 [Note] InnoDB: Completed initialization of buffer pool
2014-07-22 14:59:47 9516 [Note] InnoDB: Highest supported file format is Barracuda.
2014-07-22 14:59:47 9516 [Note] InnoDB: 128 rollback segment(s) are active.
2014-07-22 14:59:47 9516 [Note] InnoDB: Waiting for purge to start
2014-07-22 14:59:47 9516 [Note] InnoDB: 5.6.19 started; log sequence number 1626087
2014-07-22 14:59:47 9516 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.19'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server (GPL)

-> from user

    -> where user='root' and host='root' or host='localhost';
+-----------+------+-------------------------------------------+------------------+
| Host      | User | Password                                  | password_expired |
+-----------+------+-------------------------------------------+------------------+
| localhost | root | *A848DE7CCD839E924921BEE41711991DDA0D529E | Y                |
+-----------+------+-------------------------------------------+------------------+
1 row in set (0.00 sec)
 
mysql> update user set password=PASSWORD('p12#456')
    -> where user='root' and host='root' or host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

新設置用戶或更改密碼後需用flush privileges刷新MySQL的系統權限相關表,否則會出現拒絕訪問,還有一種方法,就是重新啟動mysql服務器,來使新設置生效。

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye
[root@DB-Server init.d]# /etc/rc.d/init.d/mysql restart
Shutting down MySQL..140722 15:02:27 mysqld_safe mysqld from pid file /var/lib/mysql/DB-Server.pid ended
[  OK  ]
Starting MySQL.[  OK  ]
[2]-  Done                    mysqld_safe --user=mysql --skip-grant-tables --skip-networking
[root@DB-Server init.d]# mysql -u root p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@DB-Server init.d]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19
 
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> 

另外,如果登錄mysql數據庫後執行腳本遭遇 ERROR 1820 (HY000): You must SET PASSWORD before executing this statement,可以使用重新設置一次密碼即可解決問題.

mysql>set password = password('p12#456'); 

 

至此問題解決,但是還有不少地方有些疑惑,為啥出現這個錯誤? 其實這麼多資料都只是說了解決方法,但是都回避了問題的原因。

參考資料:

http://huangyifa163.blog.163.com/blog/static/262875752011127102215790/

http://sundful.iteye.com/blog/704337

http://wenku.baidu.com/view/735ffa41be1e650e52ea995a.html

http://www.cnblogs.com/sunson/articles/2172086.html

http://blog.163.com/eric1945@126/blog/static/164934572201081494343373/

http://www.cnblogs.com/likai198981/archive/2013/04/06/3002518.html

作者:潇湘隱者 出處:http://www.cnblogs.com/kerrycode/

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved