mysqlsqldirectadmin

Can access mySQL only with --skip-grant-tables argument (all other threads checked)

So while started with --skip-grant-tables I tried the following:

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

This gives:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Then tried to bypass that I found a threat that told me to do: CREATE USER 'root'@'localhost' IDENTIFIED BY 'root'; FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;

That worked, so then I tried to change the password for root, restarted mysqld without --skip-grant-tables and tried to login ... Access denied with the password I had just set.

Then I tried to recreate the whole root user so first I repeated the above steps to bypass the limitations of --skip-grant-tables, deleted the root user and then did:

INSERT INTO mysql.user 
SET user = 'root', 
    host = 'localhost', 
    authentication_string = 'nDSExgwoz4ZknHdeSgBPq8Pw2L',
    Select_priv = 'y',
    Insert_priv = 'y',
    Update_priv = 'y',
    Delete_priv = 'y',
    Create_priv = 'y',
    Drop_priv = 'y',
    Reload_priv = 'y',
    Shutdown_priv = 'y',
    Process_priv = 'y',
    File_priv = 'y',
    Grant_priv = 'y',
    References_priv = 'y',
    Index_priv = 'y',
    Alter_priv = 'y',
    Show_db_priv = 'y',
    Super_priv = 'y',
    Create_tmp_table_priv = 'y',
    Lock_tables_priv = 'y',
    Execute_priv = 'y',
    Repl_slave_priv = 'y',
    Repl_client_priv = 'y',
    Create_view_priv = 'y',
    Show_view_priv = 'y',
    Create_routine_priv = 'y',
    Alter_routine_priv = 'y',
    Create_user_priv = 'y',
    Event_priv = 'y',
    Trigger_priv = 'y',
    ssl_cipher = 'y',
    x509_issuer = 'y',
    Create_tablespace_priv = 'y',
    x509_subject = 'y';

Restarted mysqld again without --skip-grant-tables.. still access denied.. I don't know what else to try.

Solution

  • I gave up and completely reinstalled mysql. The misconfiguration was inside the sys and sql databases..