MySQL only works with: skip-grant-tables

问题:

I forgot the root password, so followed a few different methods to reset, which eventually worked.

Now I am unable to create new databases on PHPmyAdmin, the message "no privileges" is displayed.

So I try to add all the permissions to ROOT again using:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

But then I get the error:

#1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

So I remove the "skip-grant-tables" from my.ini, then my MySQL based sites stop working.

Have I officially fudged it up?

回答1:

Make sure you flush the privileges:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

From the documentation:

FLUSH PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.