How can I restore the MySQL root user’s full privileges?

Question

I accidentally removed some of the privileges from my MySQL root user, including the ability to alter tables. Is there some way I can restore this user to its original state (with all privileges)?

UPDATE mysql.user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE User = 'root';
# MySQL returned an empty result set (i.e. zero rows).
FLUSH PRIVILEGES ;
# MySQL returned an empty result set (i.e. zero rows).


#1045 - Access denied for user 'root'@'localhost' (using password: YES)
GRANT ALL ON *.* TO 'root'@'localhost'

Bluebottle

The second error looks like a password problem. Probably my fault for assuming you had a 'root'@'localhost' account already. Re-run mysqld with --skip-grant-tables, and: "DROP USER 'root'@'localhost'; GRANT ALL PRIVILEGES ON . TO 'root'@'%';" instead? The best way to find out which account you should be granting access to is to run "SELECT User, Host FROM mysql.user WHERE User='root';"... that will give you the hostname that should be used after the @ symbol in the GRANT command. – Kopeck

It's bad form for the mods to mark the topic as off-topic when it is not off-topic except that there may be another Stack Exchange site more suited for the topic in which case it should be identified. (Shame on the mods.) Here is where this might go: dba.stackexchange.com – Fuchsin

^ agreed, I voted to reopen. if it was closed due to the existence of the DBA site, that should at least have been mentioned and the OP directed there. (Or potentially closed as a duplicate of another question here) – Which

Answer 1

If the GRANT ALL doesn't work, try:

1. Stop mysqld and restart it with the --skip-grant-tables option.
2. Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).

3. Issue the following commands in the mysql client:

   UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

   FLUSH PRIVILEGES;

After that, you should be able to run GRANT ALL ON *.* TO 'root'@'localhost'; and have it work.

Kopeck

1.How to connect to the mysqld server with just:mysql 2.When I issued UPDATE mysql.user SET Grant_priv='1' WHERE User='root'; FLUSH PRIVILEGES; I got Query ok, 0 rows affected(0.00 sec) rows matched:2 changed:0 warnings:0 Query ok, 0 rows affected(0.00 sec). When I logged in to phpMyAdmin as root user, I still see "No privileges". – Bluebottle

Sorry, after the steps above you should be able to run the GRANT ALL command. To connect to mysqld, I meant you won't need a password -- I can't remember whether any username will work, or whether it will need to be "root". – Kopeck

Also note that I've updated the solution to grant the SUPER privilege too, and to provide an alternative syntax. Don't forget to remove the "skip-grant-tables" option before testing! – Kopeck

This does not work. As Steven said, the update to the user table effects 0 records. The root user remains unable to grant. – Bludgeon

This worked for me (very thankful) however the last step "GRANT ALL ON * . * to me@localhost;" gives me "0 rows affected". Not sure if that means I already have the privileges. – Muffin

Yes, it does work. And if you're on windows, just temporarily add skip-grant-tables to the [mysqld] section of your mysql configuration file to access mysql on the command line without password. – Hunkydory

i stuck at the Grant part(that didn't work) for more than 9 hours and your answer saved me...you are a lifesaver.thanks alot – Sarracenia

For latest versions of mySQL, you may get an error when trying to start mysqld with the --skip-grant-tables flag. This post shows how to get around that: #4963671 Basically, you need kill the process with the appropriate pid, cd into the mysql folder (path may vary slightly, mine was /usr/local/mysql) and move ownership of the files in there to a user that is not root. This command works: sudo chown -R mysql /usr/local/mysql/ . After that, you should be able to safe start the server. – Tjaden

Thanks. I was looking for solution many days. It really helped! – Bucci

@Hunkydory this is the solution that finally worked for me. This technique also works on Ubuntu and is the solution for the latest (2023) edition of MySQL – Ebonyeboracum

Answer 2

If you've deleted your root user by mistake you can do one thing:

1. Stop MySQL service
2. Run mysqld_safe --skip-grant-tables &
3. Type mysql -u root -p and press enter.
4. Enter your password
5. At the mysql command line enter: use mysql;

Then execute this query:

insert into `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

then restart the mysqld

EDIT: October 6, 2018

In case anyone else needs this answer, I tried it today using innodb_version 5.6.36-82.0 and 10.1.24-MariaDB and it works if you REMOVE THE BACKTICKS (no single quotes either, just remove them):

insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Chopine

@Bipin, Weird there's only mysqld.exe in the bin folder, not mysqld_safe.exe. What do you mean by "mysqld_safe"? – Putnem

For MySQL 5.5, you need two more privs, Event_priv and Trigger_priv. – Yokum

I imagine this breaks in 5.6.5 – Bloodthirsty

@Putnem - according to this post you don't really need mysqld_safe, just run the normal mysqld binary: mysqld.exe --skip-grant-tables – Eutherian

This does not have "ALL PRIVILEGES" -- This one worked better: #6085955 – Regalado

ERROR 1054 (42S22): Unknown column 'Password' in 'field list' – Bauske

In case anyone else needs this answer, I tried it today using innodb_version 5.6.36-82.0 and 10.1.24-MariaDB and it works if you REMOVE THE BACKTICKS (no single quotes either, just remove them) – Serf

With MySQL, same error as @JamieHutber. Unknown Password column. – Forestry

This still worked for me today with MariaDB 10.3.16, thank you so much, saved some precious time for me. – Literator

Note that in MySQL 8.0+ (possibly in earlier versions as well) there is no Password column. It has been replaced with authentication_string, so just replace it in the query above. – Amide

To those who had the error ERROR 1054 (42S22): Unknown column 'Password' in 'field list' like me, you can remove Password from the field list, and also remove the empty passwort ('', after 'root',). This will also reset the root password and it should work. – Salvadorsalvadore

Answer 3

i also remove privileges of root and database not showing in mysql console when i was a root user, so changed user by mysql>mysql -u 'userName' -p; and password;

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;

after this command it all show database's in root .

Thanks

Afterworld

I had to do a combination of things. I had to stop the mysqld service, then run mysqld_safe --skip-crant-tables &, then the above UPDATE command, then restart mysql, then run GRANT ALL ON . TO 'user'@'localhost'; – Wagram

Answer 4

I had denied insert and reload privileges to root. So after updating permissions, FLUSH PRIVILEGES was not working (due to lack of reload privilege). So I used debian-sys-maint user on Ubuntu 16.04 to restore user.root privileges. You can find password of user.debian-sys-maint from this file

sudo cat /etc/mysql/debian.cnf

Distefano

You save me! GRANT was not working neither in --skip-grant-tables mode so entering as ubuntu-system allow me to create my root user again, and than I could update all privileges – Kissner

You saved me too (linux Mint 20.1 mysql 8.0.23-0ubuntu0.20.04.1). Same, --skip-grant-tables does not work in Mint, mysql and user attributes have changed (password attr. is now authentication_string, PASSWORD func is defunct..) . better to use phpmyadmin or mysql refs to check the right syntax, since almost everything I red is obsolete or not for my env. – Delanadelancey

Answer 5

If you are using WAMP on you local computer (mysql version 5.7.14) Step 1: open my.ini file Step 2: un-comment this line 'skip-grant-tables' by removing the semi-colon step 3: restart mysql server step 4: launch mySQL console step 5:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;

Step 6: Problem solved!!!!

Milne

no... still get access denied trying to grant any access to root, or for it to select anything. – Zoospore

Answer 6

GRANT ALL ON *.* TO 'user'@'localhost' with GRANT OPTION;

Just log in from root using the respective password if any and simply run the above command to whatever the user is.

For example:

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

Corinthian

but if 'root' user has lost priviledges, then how can this work,does the root user not need elevated privileges to do this? this did not work for me. – Milne

Answer 7

Just insert or update mysql.user with value Y in each column privileges.

Extragalactic

Answer 8

If deleted the root user (let's say trying to set a new user with root privileges), on ubuntu/debian, you should:

1. stop the service systemctl stop mysql.
2. Edit the mysqld.cnf generally on /etc/mysql/mysql.conf.d/mysqld.cnf depending on the distribuition.
3. Add skip-grant-tables right after the [mysqld] session.
4. Restart the service systemctl restart mysql.
5. Log on mysql without user, only with mysql.
6. And as aforementioned set the desired user with root privileges: UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';.

After that don't forget to edit the mysqld.cnf, comment the skip-grant-tables and restart the server again.

Note: for me, add --skip-grant-tables during the mysqld server did not worked. When a tried to connect the server crashed.

Waldowaldon

Answer 9

MariaDB on RHEL:

sudo systemctl stop mariadb.service
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

Then, eg.

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

Sadly, there's no graceful restart when having detached with &:

sudo kill `pidof mysqld`
sudo systemctl start mariadb.service

---

But one could as well set skip-grant-tables and skip-networking inside /etc/my.cnf.

Totally

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

Answer 10

1. "sudo cat /etc/mysql/debian.cnf" to use debian-sys-maint user
2. login by this user throgh "mysql -u saved-username -p;", then enter the saved password.
3. mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
4. mysql> FLUSH PRIVILEGES;
5. mysql> exit
6. reboot Thanks

Theo