Search code examples
mysqlxampp

i accidently change my root privilege, how to fix it?

so i accidently change my privilege of my root user and i cant do nothing right now the database is hidden cause dont have global privilege,

already try to change some line in the [mysqld] and add

skip-grant-tables

in my.ini but its cant change the privilage again bac to normal just say

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

any solution of my problem ?

Solution

  • Open C:\xampp\mysql\bin\my.ini (MySQL config file)

    Find the line [mysqld] and right below it add skip-grant-tables. Example:

    # The MySQL server
[mysqld]
skip-grant-tables
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
[...etc...]

    This should allow you to access MySQL if you don't know your password.

    Stop and start MySQL from XAMPP to make this change take effect.

    Next, in command line, connect to MySQL:

    C:\xampp\mysql\bin\mysql.exe --user=root

    Then run these commands:

    UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';
exit

    Bring back your my.ini to normal by removing skip-grant-tables, restart the mysql server.

    Done.