Linux mysql 5.6: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using pass...
时间 2014-07-22 23:33:00
案例环境:
操作系统 : 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.rpmPreparing... ########################################### [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.rpmPreparing... ########################################### [100%]1:MySQL-client ########################################### [100%][root@DB-Server tmp]# /etc/rc.d/init.d/mysql statusMySQL is not running[FAILED][root@DB-Server tmp]# /etc/rc.d/init.d/mysql startStarting MySQL.[ OK ][root@DB-Server tmp]# mysql -u root -pEnter password:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@DB-Server tmp]# mysql -u root mysqlERROR 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密码已经不为空了。
解决方法:
首先停掉mysql服务器[root@DB-Server init.d]# /etc/rc.d/init.d/mysql stopShutting 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 | more2014-07-22 14:59:41 9346 [Note] Shutting down plugin 'binlog'2014-07-22 14:59:41 9346 [Note] /usr/sbin/mysqld: Shutdown complete140722 14:59:41 mysqld_safe mysqld from pid file /var/lib/mysql/DB-Server.pid ended140722 14:59:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql2014-07-22 14:59:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp 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 pages2014-07-22 14:59:47 9516 [Note] InnoDB: The InnoDB memory heap is disabled2014-07-22 14:59:47 9516 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2014-07-22 14:59:47 9516 [Note] InnoDB: Compressed tables use zlib 1.2.32014-07-22 14:59:47 9516 [Note] InnoDB: Using Linux native AIO2014-07-22 14:59:47 9516 [Note] InnoDB: Using CPU crc32 instructions2014-07-22 14:59:47 9516 [Note] InnoDB: Initializing buffer pool, size = 128.0M2014-07-22 14:59:47 9516 [Note] InnoDB: Completed initialization of buffer pool2014-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 start2014-07-22 14:59:47 9516 [Note] InnoDB: 5.6.19 started; log sequence number 16260872014-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)
[root@DB-Server init.d]# mysql -u root mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.19 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select Host, User, Password,password_expired-> 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> quitBye[root@DB-Server init.d]# /etc/rc.d/init.d/mysql restartShutting 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 pERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)[root@DB-Server init.d]# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.19Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
至此问题解决,但是还有不少地方有些疑惑,为啥出现这个错误? 其实这么多资料都只是说了解决方法,但是都回避了问题的原因。