How To Investigate/Restore MySQL Permissions? MySQL ERROR 1045 (28000): Access denied for user

Question

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.

Answer 1

Basically from https://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.