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

User · Answer

quot sudo cat  etc mysql debian cnf quot  to use debian-sys-maint user login by this user throgh  quot mysql -u saved-username -p  quot   then enter the saved password  mysql gt  UPDATE mysql user SET Grant priv  Y   Super priv  Y  WHERE User  root   mysql gt  FLUSH PRIVILEGES  mysql gt  exit reboot Thanks

User · Answer

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

User · Answer

If you ve deleted your root user by mistake you can do one thing    Stop MySQL service Run mysqld safe --skip-grant-tables  amp  Type mysql -u root -p and press enter  Enter your password 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

User · Answer

i also remove privileges of root and database not showing in mysql console when i was a root user  so changed user by  mysql gt 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

User · Answer

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

User · Answer

If the GRANT ALL doesn t work  try    Stop mysqld and restart it with the --skip-grant-tables option   Connect to the mysqld server with just  mysql  i e  no -p option  and username may not be required   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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to privileges

Examples related to mysql-error-1045