如何恢复MySQL根用户的全部特权?

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)?

我不小心从我的MySQL根用户中删除了一些特权，包括修改表的能力。是否有某种方法可以将该用户恢复到原始状态(具有所有特权)?

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'

7 个解决方案

#1

134

If the GRANT ALL doesn't work, try:

如果所有的资助都不起作用，试着:

Stop mysqld and restart it with the --skip-grant-tables option.
停止mysqld并使用—skip-grant-tables选项重新启动它。
Connect to the mysqld server with just: mysql (i.e. no -p option, and username may not be required).
使用以下命令连接到sqmyld服务器(即不需要-p选项，用户名可能不需要)。
Issue the following commands in the mysql client:

在mysql客户端中发出以下命令:

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

更新mysql。用户设置Grant_priv='Y'， Super_priv='Y'，其中user ='root';

FLUSH PRIVILEGES;

冲洗特权;

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

之后，您应该能够运行GRANT ALL ON *。*“根”@“localhost”;和它的工作。

#2

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

如果你误删了你的根用户，你可以做一件事:

Stop MySQL service
停止MySQL服务
Run mysqld_safe --skip-grant-tables &
运行mysqld_safe——skip-grant-tables &
Type mysql -u root -p and press enter.
键入mysql -u root -p并按enter。
Enter your password
输入您的密码
At the mysql command line enter: use mysql;
在mysql命令行输入:使用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

然后重新启动mysqld

#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;

我还删除了root用户在mysql控制台中不显示的root和database的特权，因此通过mysql>改变用户mysql -u 'userName' -p;和密码;

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

谢谢

#4

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.

只需使用相应的密码从root登录，并简单地运行上面的命令到任何用户。

For example:

例如:

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

#5

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

只需插入或更新mysql即可。在每个列中具有值Y的用户特权。

Same above.

以上相同。

Full answer : http://www.myee.web.id/?p=869

完整的答案:http://www.myee.web.id/?p=869

#6

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

我拒绝向root用户提供insert和reload特权。因此，在更新权限后，刷新特权无效(由于缺少reload特权)。因此，我在Ubuntu 16.04上使用debian-sy -maint用户来恢复用户。根特权。您可以在这个文件中找到user.debian-sys-maint的密码

sudo cat /etc/mysql/debian.cnf

#7

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:

如果您正在本地计算机上使用WAMP (mysql版本5.7.14)步骤1:打开my。ini文件步骤2:删除分号3:重启mysql服务器步骤4:启动mysql控制台步骤5:

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

Step 6: Problem solved!!!!

第六步:问题解决了! ! ! !

#1

134