0

New to databases and accidentally removed privileges from root user; only privileges I currently have are USAGE and PROXY. This is a MySQL database that I access through HeidiSQL. Currently cannot access my data tables; is there a way to get these privileges restored? Did not create any other user accounts.

Improve this question
1
  • What privileges did the root user have and do you have the name of the root user? Since it is a root user i assume it has the highest of privileges so you can run mysql code to grant privileges: GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION; See: stackoverflow.com/questions/5016505/… for more information – Raidenlee Jun 7 '17 at 15:18
1

You can use following steps to regrant root priviledge.

  1. Shutdown mysqld.
  2. Start mysqld with --skip-grant-tables.
  3. Use mysql to connect mysqld. (Type mysql only, No need to input password -p, maybe No need to input username root, either)

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

    FLUSH PRIVILEGES;

  5. Restart mysqld normally.
Improve this answer
3
  • How do I shutdown mysqld? – Eric Cooks Jun 8 '17 at 23:23
  • dev.mysql.com/doc/mysql-startstop-excerpt/5.7/en – Su Jun-Ming Jun 8 '17 at 23:26
  • I don't have shutdown privileges so I can't use the mysqladmin shutdown. I tried turning off MySQL using the "Services" menu on my PC, but when I try to restart with 'mysqld_safe --skip-grant-tables -skip-networking' it tells me that it can't connect to server. Any ideas? – Eric Cooks Sep 26 '17 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.