Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

mysqld is listening on 3306 supposedly

Telnet to 3306 works

Also tried binding it specifically yo localhost and then 127.0.0.1 which made no difference

However:

# netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     78993    /var/run/mysqld/mysqld.sock

# mysql -P3306 -ptest
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Things I've tried:

dpkg-reconfigure mysql-server-5.1  Doesn't help

http://www.debian-administration.org/articles/442 Doesn't help

This command (source):

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Doesn't help, in fact:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

So might the user be deleted? Extremely unlikely as all this started after packages update a colleague did and some separate services started screwing around but my colleague said he removed the offenders.

Theres more: while # mysqld_safe --skip-grant-tables is running one can access the data tables, only with the valid passwords! So there's users and some authentication takes place hence the 0 rows affected above.

Can the privileges tables be damaged somehow and how can I recreate/restore them when my only way of getting a mysql console is to skip them?

Can I spare my reinstall of MySQL? Either way I did get a dump of the DBs now that I could get in with the above mode.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Basically from http://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-users-full-privileges

I went into # mysqld_safe --skip-grant-tables opened a # mysql -u root

Did UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

Did a normal /etc/init.d/mysql start (after killing the special instance)

Again # mysql -u root

then UPDATE mysql.user SET Password=PASSWORD('yrpass') WHERE User='root';

Root has a pass. Same for the other mysql users where needed.

What I found was there was no privileges/grants at all - regardless of --skip-grant-tables.

It was easier with MySQL Workbench to first make root a DBA as it wasn't.

After that I had to restore the few other users. Luckily I have a dump of table_priv from earlier (before all this) so per table restoration should be fine but that's in my case.

Also I had a few root users on '%' 'localhost' '127.0.0.1' some of them had an old pass. without having to start a special instance with --skip-grant-tables I could get in with the debian-sys-maint user, however it had a different password because of imports of dumps of other systems! I got that solved by looking at this article

What caused all this in the first place I don't know, open for discussion.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.