.... I revoked all the privileges to the root@local host, now I can't access any of the databases.  How can I reset the password or privileges on root@localhost to the default i.e. mysql -u root   no password?  ....

Ouch!

Revoking all privileges from root  (or changing and forgetting the new password - I'm unsure which you have done) is rather like throwing the master keys away .... and if it was easy to get them back through a backdoor, then it would be equally easy for some hacker to gain root access in the same way.

Three thoughts:

a)  MySQL has two different accounts - root@localhost and root@% - set up for admin by default.  The second of these is for doing MySQL work away from the machine on which you're running the MySQL service.   If you have not altered / damaged that account, you might be able to use it to fix the localhost account.

b) If root@% isn't available, the next best approach would be for you to copy (at operating system level) the tables in the mysql database (i.e. the system tables) onto another computer also running a mysql service BUT UNDER A DIFFERENT DATABASE NAME.   You could then handle the tables as data on that other system, and copy them back.  Not an easy procedure, but it can be done. You'll need operating system admin rights on both computers to do this, and you should do it with the mysql daemon stopped.

c) When (prior to revoking the access) did you last take a backup of your database?  If it's recent enough, the most practical solution for you might be to clear out all the old data and revert to backup.


you could use a "my.cnf" file

In your MySQL directory (mines /var/lib/mysql/) create at file call "my.cnf".

open and create the content:
[mysqld]
skip-grant-tables

then close.

restart MySQL and you should get in with no password on the command line as this does what it says....skips the grant tables.

You can then do a mysqldump of your lastest data before either removing MySQL completely and rebuilding from scratch, or edit the Table user of the mysql database to grant privileges manually.

It goes wihtout saying that the my.cnf file should be removed as soon as is possible as having no users or passwords for your db is a security hole the size of a cow.

Cheers
jfp

---