Access denied for user 'root'@'localho

问题:

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:

/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.

Then I restarted 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)"

Is there something that I am missing?

回答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):

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

Stop 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:

$ mysql -u root

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

Start MySQL

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

your new password is 'password'.

回答4:

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

NOT mysql or 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".

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

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

in mysqld_safe --skip-grant-tables model.

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.

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).

See this AskUbuntu question and answer for more details.

回答7:

On mac os, please follow below steps:

Stop 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:

$ mysql -u root

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

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

回答8:

In my case:

1. /etc/init.d/mysql stop
2. mysqld_safe --skip-grant-tables &

(in new window)

3. mysql -u root
4. mysql> use mysql;
5. mysql> update user set authentication_string=password('password') where user='root';
6. mysql>flush privileges;
7. mysql> quit
8. /etc/init.d/mysql restart

回答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 -u root -p new_password

MySQLl access denied

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.

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

Thanks.