How to fix MySQL permissions in SQL

Question

I screwed-up the permissions in MySQL, and now I'm trying to fix it. Here is the code that I use to fix:

    DELETE FROM mysql.user where User='root';
    CREATE USER 'root'@'localhost' IDENTIFIED BY '390mdij230';
    CREATE USER 'root'@'%' IDENTIFIED BY '390mdij230';
    UPDATE mysql.user SET Password=PASSWORD('390mdij230') WHERE User='root';
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
            WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
            WITH GRANT OPTION;
    FLUSH PRIVILEGES;

The problem is that when I log back using the new user and password, something seems very amiss:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> show grants for root@localhost ;
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*B3C29E97F480197F60635DF16FFA83C382697C2C' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye

That is, not even the mysql database shows-up, 'USAGE' looks to me kind of limited, and well the other databases don't show-up either. How do I get a functional root user again?

EDIT:

Here is the output of 'tree' over MySQL's datadir:

/mnt/d1/mysqldata/
├── antcorr [error opening dir]
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── mysql [error opening dir]
├── performance_schema [error opening dir]
├── phpmyadmin [error opening dir]
└── test [error opening dir

the 'error opening dir' is because the user that executes the 'tree' command doesn't have access... but if I use root or mysql I effectively see the files with 'tree'... it is just more convenient to use the short version to post here. Furthermore, when I do 'SHOW VARIABLES' I see the expected value for 'datadir'.

EDIT 2:

If I start the server this way

mysqld_safe   --init-file=`pwd`/resetpass.sql --skip-grant-tables &

I can effectively see all the databases and tables.

Have you considered that you accidentally removed those tables? Or started the wrong mysql server? — Ulrich Thomas Gabor, Mar 19 '14 at 08:03
What is this "error opening dir" meant to say? Is this Windows or Linux? Have you stopped mysql, restarted the machine and tried to access the folders then? Have you restarted mysql then and taken a look at its error file? Have you tried starting you server with [--skip-grant-tables](http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_skip-grant-tables) to access it plainly and have the ability to take a look? — Ulrich Thomas Gabor, Mar 19 '14 at 08:18
@GhostGambler Good tip that about --skip-grant-tables! Thanks. Check my edits — dsign, Mar 19 '14 at 08:46

How to fix MySQL permissions in SQL

0 Answers0