在重新设置LINUX密码后，用户“root”@“localhost”(使用密码:Yes)的访问被拒绝

I have a MySQL installed on my linux server, I forgot it's password so I went and changed it using the methods I found on the web. What I did was as follows:

我在linux服务器上安装了MySQL，我忘了它是密码，所以我用我在网上找到的方法修改了它。我所做的是:

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql --user root mysql
SELECT * FROM user; // I checked if I could access the user table or not
update user SET password = PASSWORD('new_pass') WHERE user = 'root';
flush privileges;
exit

The update query did change the password as it showed me the number of rows affected and Query OK etc.

更新查询确实更改了密码，因为它显示了受影响的行数和查询OK等。

Then I restarted mysql

然后我重新启动mysql

/etc/init.d/mysql stop
/etc/init.d/mysql start

Now when I logged in with the new password

现在我用新的密码登录

mysql -u root -p new_pass

it still gives me errors "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Yes)"

它仍然会给我错误“错误1045(28000):用户'root'@'localhost'的访问被拒绝(使用密码:Yes)”

Is there something that I am missing?

有什么东西我遗漏了吗?

10 个解决方案

#1

I was able to solve this problem by executing this statement

通过执行这个语句，我解决了这个问题

sudo dpkg-reconfigure mysql-server-5.5

Which will change the root password.

它将更改根密码。

#2

You can recover MySQL database server password with following five easy steps. Here are commands you need to type for each step (login as the root user):

您可以通过以下五个简单的步骤恢复MySQL数据库服务器密码。以下是您需要为每个步骤输入的命令(作为根用户登录):

Step # 1 : Stop mysql service

/etc/init.d/mysql stop

Output:

输出:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

mysqld_safe --skip-grant-tables &

Output:

输出:

[1] 5988

Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

mysql -u root

Output:

输出:

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

Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

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

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

mysql> flush privileges;

mysql> quit

Step # 5: Stop MySQL Server:

/etc/init.d/mysql stop

Output:

输出:

Stopping MySQL database server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

/etc/init.d/mysql start

mysql -u root -p

#3

You have to reset the password! steps for mac osx(tested and working) and ubuntu

你必须重置密码!mac osx(测试和工作)和ubuntu的步骤

Stop MySQL

停止MySQL

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

以安全模式启动:

$ sudo mysqld_safe --skip-grant-tables

(above line is the whole command)

(上面一行是整个命令)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

这将是一个持续的命令，直到过程完成，所以打开另一个shell/终端窗口，没有密码登录:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Start MySQL

启动MySQL

sudo /usr/local/mysql/support-files/mysql.server start

your new password is 'password'.

你的新密码是“password”。

#4

I had the same problem. You have to write mysql -u root -p

我也有同样的问题。你必须写mysql -u root -p

NOT mysql or mysql -u root -p root_password

不是mysql或mysql -u root -p root_password

#5

I meet the same problem, @progrAmmar enlightened me, "took a closer look at the user table in mysql database".

我遇到了同样的问题，@progrAmmar启发了我，“仔细看看mysql数据库中的用户表”。

My problem is not ssl_type, instead of the first field：Host. I changed the value by using

我的问题不是ssl_type,而不是第一个字段:主机。我通过使用

update user set Host='localhost' where User='root' and Host='%';

更新用户集主机='localhost'，其中用户='root'，主机='%';

in mysqld_safe --skip-grant-tables model.

在mysqld_safe skip-grant-tables模型。

Then it works well.

那么它工作得很好。

#6

You may need to clear the plugin column for your root account. On my fresh install, all of the root user accounts had unix_socket set in the plugin column. This was causing the root sql account to be locked only to the root unix account, since only system root could login via socket.

您可能需要清除根帐户的插件列。在我的新安装中，所有根用户帐户都在插件列中设置了unix_socket。这导致根sql帐户仅被锁定到根unix帐户，因为只有系统根可以通过套接字登录。

If you update user set plugin='' where User='root';flush privileges;, you should now be able to login to the root account from any localhost unix account (with a password).

如果您更新user set plugin= " where user ='root';flush privileges;，您现在应该能够从任何localhost unix帐户(使用密码)登录到根帐户。

See this AskUbuntu question and answer for more details.

请查看这个AskUbuntu的问题并回答更多的细节。

#7

On mac os, please follow below steps:

在mac os上，请按以下步骤操作:

Stop MySQL

停止MySQL

$ sudo /usr/local/mysql/support-files/mysql.server stop Start it in safe mode:

$ sudo /usr/local/mysql/support-files / mysql。服务器停止启动，进入安全模式:

$ sudo mysqld_safe --skip-grant-tables (above line is the whole command)

$ sudo mysqld_safe—skip-grant-tables(上面一行是整个命令)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

这将是一个持续的命令，直到过程完成，所以打开另一个shell/终端窗口，没有密码登录:

$ mysql -u root

美元mysql - u根

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; Start MySQL

mysql >更新mysql。用户设置密码=密码(' Password ')，其中用户='root';启动MySQL

sudo /usr/local/mysql/support-files/mysql.server start your new password is 'password'.

sudo /usr/local/mysql/support-files / mysql。服务器启动您的新密码是“密码”。

#8

In my case:

在我的例子中:

1. /etc/init.d/mysql stop
2. /etc/init.d / mysql停止
3. mysqld_safe --skip-grant-tables &
4. mysqld_safe——skip-grant-tables &

(in new window)

(在新窗口)

3. mysql -u root
4. mysql - u根
5. mysql> use mysql;
6. mysql >使用mysql;
7. mysql> update user set authentication_string=password('password') where user='root';
8. 更新用户设置authentication_string=password('password')，其中user='root';
9. mysql>flush privileges;
10. mysql >冲洗特权;
11. mysql> quit
12. mysql >退出
13. /etc/init.d/mysql restart
14. /etc/init.d / mysql重启

#9

You can try this solution :-

你可以试试这个办法:-

To have mysql asking you for a password, you also need to specify the -p-option: (try with space between -p and password)

要让mysql要求您输入密码，还需要指定-p选项:(在-p和密码之间使用空格)

mysql -u root -p new_password

MySQLl access denied

MySQLl拒绝访问

In the Second link someone has commented the same problem.

在第二个链接中，有人评论了同样的问题。

#10

Actually I took a closer look at the user table in mysql database, turns out someone prior to me edited the ssl_type field for user root to SSL.

实际上，我仔细查看了mysql数据库中的user表，发现在我之前有人为用户root用户编辑了ssl_type字段到SSL。

I edited that field and restarted mysql and it worked like a charm.

我编辑了这个字段，重新启动了mysql，它运行得非常好。

Thanks.

谢谢。