I screwed permissions in MySQL, and now I'm trying to fix it. Here is the code I'm using to fix it:
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 return using a new user and password, something seems very uncomfortable:
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, even the mysql database does not appear, "USE" looks at me as limited, and other databases are not displayed either. How to get a functional root user again?
EDIT:
Here is the result of the "tree" over MySQL 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
" dir" , , "tree", ... root mysql, ""... . , "SHOW VARIABLES", "datadir".
2:
mysqld_safe --init-file=`pwd`/resetpass.sql --skip-grant-tables &
.