how to restore access to MySQL after deleting all privileges?

Question

Ok, I know I'm stupid. I deleted all privileges to MySQL, I have access to root account on host. Is there any way to add account to mysql?

Answer 1

First of all stop mysql & backup /var/lib/mysql or wherever the data files are on your system.

Then from a clean mysql install elsewhere tar up /var/lib/mysql/mysql and put it on the broken system.

Then re-add your users & permissions.

Of course if you have backed up the mysql database you can do this more quickly.

Answer 2

I have a nice set of Linux commands you may want to run every night to backup your MySQL permissions:

mysql -u -p --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u -p --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

This will literally spill out the grants for all users as SQL GRANT Commands.

MAATKIT also does this using their perl script mk-show-grants

That way if you hose all your permissions, you can do three(3) things:

1. apply the suggestion by @jamespo
2. login to mysql as root@localhost
3. Run MySQLGrants.sql