0

I've made a change in my mysql user permissions which for some inexplicable reason I can no longer see all of my databases with the exception the mysql installation dbs. I don't quite understand why I can't see these DBs and am looking for input on where I should start looking for where permissions could have been screwed up. This is a layer of mysql I'm not terribly familiar with. I know the DBs are there I just can't see them as root for some strange reason.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------------------------------------+
| Grants for root@localhost                                              |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION    | 
| GRANT ALL PRIVILEGES ON `db`.* TO 'root'@'localhost' WITH GRANT OPTION | 
+------------------------------------------------------------------------+
bikedorkseattle
  • 951
  • 3
  • 10
  • 24

1 Answers1

0

first backup your mysql datadir (in ubuntu it is /var/lib/mysql). You find the directory location in the mysql config file, entry datadir. Backup with your favorite packer programm that directory, e.g.tar or zip. Before backup, stop the mysql service. After backup start the mysql service again.

Then try to delete your root user host entry db.* from the db table

use mysql
select host,user,db from db;
delete from db where user="root" and db="db";

I am not sure if it is the right solution and also not testet it yet, so i hope i can you with this idea. But don't forget to backup your datadir first.

MartinF
  • 109
  • 6