How to fix MySQL permissions in SQL

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 &

.

+4

Source: https://habr.com/ru/post/1532433/


All Articles