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

Question

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 ?

its all statement cause the root@localhost dont have any privilege https://ibb.co/3S2Vz6r — Maszgalang, Jun 16 '21 at 17:24
so you need to run the database with `--skip-grant-tables` and add these... when do you get an error `ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement` ? — Flash Thunder, Jun 16 '21 at 17:28

score 2 · Accepted Answer · answered Jun 16 '21 at 17:52

2

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.

answered Jun 16 '21 at 17:52

Flash Thunder

11,672
8
47
91

is this no problem ? or should be change ? https://ibb.co/bvnHjrr the grant is not updateable and i ty to change or create other user is the same https://ibb.co/nbCKyhJ – Maszgalang Jun 16 '21 at 17:56
I don't understand. – Flash Thunder Jun 16 '21 at 17:56
did you follow my steps? you should do it on command line, not PHPMyAdmin – Flash Thunder Jun 16 '21 at 18:01
i do in command line check the first link that prove i do it in commandline – Maszgalang Jun 16 '21 at 18:02
and did you add `skip-grant-tables` to ini file and restart the database before? – Flash Thunder Jun 16 '21 at 18:03
yes already add and comment it after change and restart the database, but that message is Grant_priv is not updatable is the problem, can that field forced to update ? – Maszgalang Jun 16 '21 at 18:05
I don't know... sorry :( – Flash Thunder Jun 16 '21 at 18:06
oke thats no problem ill search it later, hope somebody can help Thanks its form to make it normal but nor for the grant, btw i need 15 rep up vote ur post lol – Maszgalang Jun 16 '21 at 18:07
what I can think of is to just make a dump of databases with `mysqldump -u[yourusername] -p[yourpassword] --all-databases > mydump.sql` command line, reinstall everything and then bring the data back with `mysql -u[yourusername] -p[yourpassword] < mydump.sql` – Flash Thunder Jun 16 '21 at 18:11
Man! U r my hero! o/ – Nowdeen Aug 19 '23 at 15:33

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

1 Answers1