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

user_202335 0
Viewed 219835

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'
mysql-error-1045 privileges mysql
9 Answers

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_65126930

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

user_13160950
  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
user_87355740

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.

user_5493720
Related