119

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'
hexacyanide
  • 88,222
  • 31
  • 159
  • 162
Steven
  • 24,410
  • 42
  • 108
  • 130
  • 1
    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. – DMI Nov 11 '09 at 08:36
  • 1
    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: http://dba.stackexchange.com/ – Jon Davis Aug 28 '14 at 18:16
  • ^ 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) – indivisible Sep 28 '16 at 15:07

10 Answers10

165

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.

DMI
  • 6,843
  • 2
  • 24
  • 25
  • 7
    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". – Steven Nov 10 '09 at 16:22
  • 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". – DMI Nov 10 '09 at 16:45
  • 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! – DMI Nov 10 '09 at 16:49
  • 3
    This does not work. As Steven said, the update to the user table effects **0** records. The root user remains unable to grant. – Cerin May 17 '12 at 17:06
  • 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. – PJ Brunet Jul 04 '13 at 07:33
  • 3
    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. – markus Nov 16 '13 at 21:44
  • 1
    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 – Ali SH Nov 27 '17 at 19:13
  • 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: https://stackoverflow.com/questions/4963171/mysql-server-startup-error-the-server-quit-without-updating-pid-file 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. – ALisboa Apr 13 '18 at 19:23
  • Thanks. I was looking for solution many days. It really helped! – karmeljuk Jul 29 '22 at 09:34
94

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');
Scaramouche
  • 3,188
  • 2
  • 20
  • 46
Bipin Bahuguna
  • 1
  • 2
  • 2
  • 2
    @Bipin, Weird there's only `mysqld.exe` in the `bin` folder, not `mysqld_safe.exe`. What do you mean by "mysqld_safe"? – Pacerier May 07 '15 at 09:46
  • 4
    For MySQL 5.5, you need two more privs, Event_priv and Trigger_priv. – Greg Bell Feb 02 '16 at 23:05
  • I imagine this breaks in 5.6.5 – Otheus Feb 16 '16 at 15:07
  • 1
    @Pacerier - according to [this post](http://forums.devshed.com/mysql-help-4/mysqld_safe-631108.html) you don't really need `mysqld_safe`, just run the normal mysqld binary: `mysqld.exe --skip-grant-tables` – bkwdesign Mar 23 '16 at 15:25
  • This does not have "ALL PRIVILEGES" -- This one worked better: http://stackoverflow.com/questions/6085455/restoring-deleted-root-user-and-password-for-mysql – ParoX Jan 29 '17 at 21:47
  • 3
    `ERROR 1054 (42S22): Unknown column 'Password' in 'field list'` – Jamie Hutber Feb 23 '17 at 18:52
  • 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) – Scaramouche Oct 07 '18 at 04:37
  • With MySQL, same error as @JamieHutber. Unknown Password column. – Apache Oct 21 '18 at 08:20
  • This still worked for me today with MariaDB 10.3.16, thank you so much, saved some precious time for me. – Marçal Torroella Jul 01 '19 at 09:43
  • 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. – devius Nov 21 '19 at 10:45
  • 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. – jan_tera May 19 '22 at 10:38
26

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

Didier Ghys
  • 30,396
  • 9
  • 75
  • 81
Prabhu
  • 1
  • 2
  • 2
  • 1
    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'; – Nick Woodhams Dec 19 '12 at 09:54
10

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
Varun Kumar
  • 2,543
  • 1
  • 23
  • 22
  • 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 – lunix15 Jul 03 '20 at 15:14
  • 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. – jerome Apr 04 '21 at 09:36
3

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

miken32
  • 42,008
  • 16
  • 111
  • 154
user1316095
  • 1
  • 2
3
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;
Rohit Arya
  • 6,751
  • 1
  • 26
  • 40
Rashendra - Rashen
  • 139
  • 4
  • 13
  • 8
    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. – Terungwa Dec 27 '17 at 10:56
3

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!!!!

Terungwa
  • 395
  • 3
  • 18
1
  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
Taha Saber
  • 43
  • 1
  • 7
1

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.

Martin Zeitler
  • 1
  • 19
  • 155
  • 216
0

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.

DPalharini
  • 413
  • 6
  • 16