linux中ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

今天开始学习mysql，于是在Linux的centos6.7安装了Mysql 5.6.1964bit 版本的数据库，结果安装成功了：

[root@lp ~]# rpm -e mysql-libs-5.1.73-5.el6_6.x86_64 --nodeps

[root@lp ~]# rpm -ivh MySQL-client-advanced-5.6.19-1.el6.x86_64.rpm

Preparing...               ########################################### [100%]

  1:MySQL-client-advanced ########################################### [100%]

[root@lp ~]# rpm -ivh MySQL-server-advanced-5.6.19-1.el6.x86_64.rpm

Preparing...               ########################################### [100%]

  1:MySQL-server-advanced ########################################### [100%]

但是使用root登录时遇到了ERROR 1045(28000): Access denied for user 'root'@'localhost' (using password: NO)错误. 如下所示：

[root@lp ~]# mysql -u root -p

Enter password:

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

或者通过下面方法设置新密码也设置不了！

[root@lp ~]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALLMySQL

      SERVERS INPRODUCTION USE!  PLEASE READ EACH STEPCAREFULLY!

 

In order to log into MySQL to secure it, we'll need thecurrent

password for the root user. If you've just installed MySQL, and

you haven't set the root password yet, the password will beblank,

so you should just press enter here.

 

Enter current password for root (enter for none):

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

Enter current password for root (enter for none):

ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using password: YES)

Enter current password for root (enter for none):

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

Unable to connect to the server as root user, giving up.

Cleaning up...

解决方法：

安装过程中没有设置过root密码，所以怎么登陆啊？？？？

第一步：

首先关闭MySQL服务器：（如果服务器已经启动）

 [root@lp ~]#/etc/rc.d/init.d/mysql stop

Shutting down MySQL..[确定]

第二步：

然后使用mysqld_safe命令在启动mysql，更新root账号的密码，其中：

--skip-grant-tables：不启动grant-tables(授权表),跳过权限控制。

--skip-networking ：跳过TCP/IP协议，只在本机访问。

[root@lp ~]# mysqld_safe --user=mysql --skip-grant-tables--skip-networking &

[1] 4154

[root@lp ~]# 170109 15:19:53 mysqld_safe Logging to'/var/lib/mysql/lp.err'.

170109 15:19:54 mysqld_safe Starting mysqld daemon withdatabases from /var/lib/mysql

注意：执行上面命令后，此会话窗口会出现无反应的状态，需要使用CTRL+C中断会话。

第三步：

[root@lp ~]# mysql -u root mysql

Reading table information for completion of table and columnnames

You can turn off this feature to get a quicker startup with-A

 

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.19-enterprise-commercial-advanced MySQLEnterprise Server - Advanced Edition (Commercial)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. Allrights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

 

mysql> select Host, User, Password,password_expired

    ->      from user

    ->      where user='root' and host='root' orhost='localhost';

+-----------+------+-------------------------------------------+------------------+

| Host      | User |Password                                  |password_expired |

+-----------+------+-------------------------------------------+------------------+

| localhost | root |*9D62D7794A3F574E452C67E0C9913832B5F00298 | Y                |

+-----------+------+-------------------------------------------+------------------+

1 row in set (0.03 sec)

 

mysql>  update userset password=PASSWORD('root')

    ->      where user='root' and host='root' orhost='localhost';

Query OK, 1 row affected (0.06 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@lp ~]# /etc/rc.d/init.d/mysql restart

Shutting down MySQL..170109 15:25:04 mysqld_safe mysqld frompid file /var/lib/mysql/lp.pid ended

[确定]

Starting MySQL.[确定]

[1]+  Done                    mysqld_safe --user=mysql--skip-grant-tables --skip-networking

第五步：（特别注意容易遗忘）

如果第一次登录mysql数据库后执行脚本遭遇 ERROR 1820(HY000): You must SET PASSWORD before executing this statement,可以使用重新设置一次密码即可解决问题.

例如：

[root@lp ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.19-enterprise-commercial-advanced

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. Allrights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

 

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executingthis statement

 

再次设置密码：

mysql>set password = password('root');

QueryOK, 0 rows affected (0.00 sec)

 

mysql>show databases;

+--------------------+

|Database           |

+--------------------+

|information_schema |

|mysql              |

|performance_schema |

|test               |

+--------------------+

4rows in set (0.00 sec)