如何恢复 MySQL root 用户的全部权限？

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

提问by Steven

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'

回答by DMI

If the GRANT ALLdoesn't work, try:

如果GRANT ALL不起作用，请尝试：

1. Stop mysqldand restart it with the --skip-grant-tablesoption.
2. Connect to the mysqldserver with just: mysql(i.e. no -poption, 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;

1. mysqld使用该--skip-grant-tables选项停止并重新启动它。
2. 连接到mysqld服务器：（mysql即没有-p选项，可能不需要用户名）。

3. 在 mysql 客户端发出以下命令：

   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.

之后，您应该能够运行GRANT ALL ON *.* TO 'root'@'localhost';并使其工作。

回答by Bipin Bahuguna

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

如果您误删除了root用户，您可以做一件事：

1. Stop MySQL service
2. Run mysqld_safe --skip-grant-tables &
3. Type mysql -u root -pand press enter.
4. Enter your password
5. At the mysql command line enter: use mysql;

1. 停止 MySQL 服务
2. 跑 mysqld_safe --skip-grant-tables &
3. 键入mysql -u root -p并按回车键。
4. 输入您的密码
5. 在 mysql 命令行输入： 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

然后重启mysqld

EDIT: October 6, 2018

编辑：2018 年 10 月 6 日

In case anyone else needs this answer, I tried it today using innodb_version 5.6.36-82.0and 10.1.24-MariaDBand it works if you REMOVE THE BACKTICKS (no single quotes either, just remove them):

万一其他人需要这个答案，我今天尝试使用innodb_version 5.6.36-82.0和10.1.24-MariaDB，如果您删除反引号（也没有单引号，只需删除它们），它就可以工作：

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');

回答by Prabhu

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 和数据库的权限，因此更改了用户名 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 .

在这个命令之后，它都在 root 中显示数据库。

Thanks

谢谢

回答by Rashendra - Rashen

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;

回答by Varun Kumar

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 的插入和重新加载权限。因此，更新权限后，FLUSH PRIVILEGES 不起作用（由于缺乏重新加载权限）。所以我在 Ubuntu 16.04 上使用 debian-sys-maint 用户来恢复 user.root 权限。你可以从这个文件中找到 user.debian-sys-maint 的密码

sudo cat /etc/mysql/debian.cnf

回答by user1316095

Just insert or update mysql.userwith value Yin each column privileges.

只需在每列权限中插入或更新mysql.user值Y。

回答by Terungwa

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 步：通过删除分号取消注释“skip-grant-tables”这一行第 3 步：重新启动 mysql服务器步骤 4：启动 mySQL 控制台步骤 5：

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

Step 6: Problem solved!!!!

第 6 步：问题已解决！！！！