Access denied for user 'mysql'@'localhost' (using password: NO)

完整过程解决 ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)

分类： mysql 2013-10-26 10:06  21791人阅读  评论(1)  收藏  举报

数据库

 ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)
此问题网上大部分都是围绕下面的第二步（修改密码）展开的，很是坑爹的是我怎么都登陆不进去（各种模式登陆均失败），何谈修改密码呢？
本人分心mysql日志文件总结此问题的整体步骤如下：


第一步：修改pid路径
查看日志文件：
 cat /var/log/mysqld.log


2013-10-26 16:39:34 3712 [ERROR] /usr/sbin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory)
2013-10-26 16:39:34 3712 [ERROR] Can't start server: can't create PID file: No such file or directory


原因：
mysql 用户没有操作/var/run目录的权限，所以pid文件无法创建，导致登陆时无法建立 进程信息文件，登陆进程就无法开启，自然无法登陆。


解决：
修改 /etc/my.conf
原来的
 #pid-file=/var/run/mysqld/mysqld.pid
修改为
pid-file=/var/lib/mysql/mysqlid.pid


检查发现，mysql用户根本无法 cd /var/run/。修改为mysql可以有权限的目录后再执行mysql就进入数据库了。


第二步：修改数据库默认密码
/etc/init.d/mysql stop   (service mysqld stop )
/usr/bin/mysqld_safe --skip-grant-tables
另外开个SSH连接
[root@localhost ~]# mysql
mysql>use mysql
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>exit


然后
[mysql@localhost etc]$ ps -A | grep mysql
4532 pts/0    00:00:00 mysqld_safe
5542 pts/0    00:00:00 mysqld
[mysql@localhost etc]$ kill -9 4532 5542 
正常启动 MySQL：/etc/init.d/mysql start   (service mysqld start)


第三步：
登陆ok。 mysql -uroot -p


/*********************下面为本人的完整修复过程信息，含分析过程：*******************************/
myslq status SUCCESS 但是各种尝试登陆均不起效
[mysql@localhost etc]$ service mysql start
Starting MySQL. SUCCESS!
[mysql@localhost etc]$ service mysql status
SUCCESS! MySQL running (4463)
[mysql@localhost etc]$ mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@localhost etc]$ mysql -umysql
ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)
[mysql@localhost etc]$ mysqladmin -uroot -p password
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
想死有木有！！！
冷静查看日志吧-->
[mysql@localhost ~]$ cat /var/log/mysqld.log 
2013-10-26 16:39:34 3712 [ERROR] /usr/sbin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory)
2013-10-26 16:39:34 3712 [ERROR] Can't start server: can't create PID file: No such file or directory
尼玛！！！
[mysql@localhost ~]$ cd /var/run/
-bash: cd: /var/run/: 权限不够
[mysql@localhost ~]$ 
就这样吧！！！
[mysql@localhost ~]$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
pid-file=/var/lib/mysql/mysqlid.pid
重启mysql
[mysql@localhost etc]$ service mysql stop
Shutting down MySQL.. SUCCESS!
[mysql@localhost etc]$ service mysql start
Starting MySQL.. SUCCESS!
尼玛啊！！！
[mysql@localhost etc]$ mysql
ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)
到这一步，如果想执行mysql直接登陆，需要如下操作
[mysql@localhost etc]$ service mysql stop
Shutting down MySQL.. SUCCESS! 
[mysql@localhost etc]$ mysqld_safe --user=mysql --skip-grant-tables --skip-networking
[mysql@localhost ~]$ mysql
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.14


Copyright (c) 2000, 2013, 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> 


但是要想在service mysql start方式下登陆继续往下看吧。
尼玛！！！
[mysql@localhost etc]$ mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)
解释：这里是因为数据库默认的root密码没有设置导致的。
（下面这个是网络上搜索ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO)  大家的解决方法，但是如果
没有修改pid的路径，这里根本登陆不进去，何谈修改密码。因为mysql始终无法创建pid文件才是问题根源）
这种问题需要强行重新修改密码，方法如下：
/etc/init.d/mysql stop   (service mysqld stop )
/usr/bin/mysqld_safe --skip-grant-tables
另外开个SSH连接
[root@localhost ~]# mysql
mysql>use mysql
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>exit


然后
[mysql@localhost etc]$ ps -A | grep mysql
4532 pts/0    00:00:00 mysqld_safe
5542 pts/0    00:00:00 mysqld
[mysql@localhost etc]$ kill -9 4532 5542 
正常启动 MySQL：/etc/init.d/mysql start   (service mysqld start)


终于！！！
[mysql@localhost ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.14


Copyright (c) 2000, 2013, 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>