如何将所有权限恢复给 MySQL 中的 root 用户？

How to get all privileges back to the root user in MySQL?

提问by Steven

I have logged in to MySQL with the --skip-grant-tables option. But I don't know how to get all privileges back to the root user.

我已经使用 --skip-grant-tables 选项登录到 MySQL。但我不知道如何将所有权限恢复给 root 用户。

Error

SQL query:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';

MySQL said: Documentation

1290 - The MySQL server is running with the --skip-grant-tables option so

it cannot execute this statement

Error

SQL query: Edit

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL said: Documentation

1290 - The MySQL server is running with the --skip-grant-tables option so

it cannot execute this statement

错误

SQL查询：

授予所有特权 * 。* TO 'root'@'localhost';

MySQL 说： 文档

1290 - MySQL 服务器正在使用 --skip-grant-tables 选项运行，因此

它无法执行此语句

错误

SQL 查询：编辑

授予所有特权 * 。* TO 'root'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL 说： 文档

1290 - MySQL 服务器正在使用 --skip-grant-tables 选项运行，因此

它无法执行此语句

回答by cyb0k

If you cant acces to mysql server as a root you should delete or cannot restore all root records in mysql.user table, delete all of them and add a new record.

如果您不能以 root 身份访问 mysql 服务器，则应删除或无法恢复 mysql.user 表中的所有 root 记录，删除所有记录并添加新记录。

You should use mysql PASSWORD() function to hash your cleartext password. check for more information http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

您应该使用 mysql PASSWORD() 函数来散列您的明文密码。检查更多信息http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

First stop mysql server and launch mysqld with --skip-grant-tables.

首先停止mysql服务器并使用--skip-grant-tables启动mysqld。

[root@mysql ~]# service mysqld stop; mysqld_safe --skip-grant-tables &

Hash your cleartext password.

散列您的明文密码。

mysql> select PASSWORD('CLEARTEXT_PASSWORD');
+-------------------------------------------+
| PASSWORD('CLEARTEXT_PASSWORD')            |
+-------------------------------------------+
| *1EADAEB11872E413816FE51216C9134766DF39F9 |
+-------------------------------------------+
1 row in set (0.00 sec)

Use mysql database to apply changes.

使用 mysql 数据库应用更改。

mysql> use mysql;

Delete all root records

删除所有根记录

mysql> delete from user where User='root';

Add new record that root user can access with all privileges from your ip adress.

添加 root 用户可以使用您的 IP 地址的所有权限访问的新记录。

mysql> insert into `user` VALUES('YOUR_IP_ADDRESS','root','*1EADAEB11872E413816FE51216C9134766DF39F9','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','Y','Y','Y','','','','',0,0,0,0,'','');

Flush changes.

冲洗变化。

mysql> FLUSH PRIVILEGES;

Exit from mysql command line .

从 mysql 命令行退出。

mysql> exit;

Restart mysqld service.

重启mysqld服务。

[root@mysql ~]# /etc/init.d/mysqld restart

if you run this mysql commands/ queries you will get a new access to mysql server.

如果您运行此 mysql 命令/查询，您将获得对 mysql 服务器的新访问权限。

回答by John

For mysql 8.0.12+
I've tried the documentation (not working) I've tried various other options, all failed.

对于 mysql 8.0.12+
我试过文档（不工作）我试过各种其他选项，都失败了。

PASSWORD() is deprecated, SET PASSWORD is disabled, ALTER USER is disabled as well.
1. stop mysql service
2. Start it this way: /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. Login (mysql) and exec: update user set authentication_string='' where
User='root'; 4. killall mysqld
5. Start service again

不推荐使用 PASSWORD()，禁用 SET PASSWORD，也禁用 ALTER USER。
1. 停止 mysql 服务
2. 以这种方式启动：/usr/local/mysql/bin/mysqld_safe --skip-grant-tables
3. 登录（mysql）和执行：更新用户集 authentication_string='' where
User='root '; 4.killall mysqld
5.再次启动服务

Now you can use "set password" or "alter user" after logging in within a password

现在您可以在密码内登录后使用“设置密码”或“更改用户”

回答by Mansur Ali

First, stop the MySQL server and then start it with the --skip-grant-tablesoption.

首先，停止 MySQL 服务器，然后使用--skip-grant-tables选项启动它。

[root@backups1 mysql5.7]# /etc/init.d/mysqld stop
[root@backups1 mysql5.7]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql

Then, connect to your instance without a password:

然后，无需密码即可连接到您的实例：

[root@backups1 mysql5.7]# mysql -u root

Then, reset the password for the root user.

然后，重置 root 用户的密码。

mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;

Switch to normal mode of MySQL then connect without password.

切换到 MySQL 的正常模式，然后无需密码即可连接。

回答by user18099

If the goal of your --skip-grant-tables is insert a new Grant, you can do so by inserting a line in mysql.user (see other answers)

如果您的 --skip-grant-tables 的目标是插入一个新的 Grant，您可以通过在 mysql.user 中插入一行来实现（参见其他答案）

If your challenge is in giving this new Grant all the required privileges (many Y/N colums), then you can copy an existing root grant. And adjust only what you require.

如果您的挑战是为这个新的 Grant 授予所有必需的权限（许多 Y/N 列），那么您可以复制现有的 root 授权。并仅调整您需要的内容。

CREATE TEMPORARY TABLE mysql.tmpUser SELECT * FROM mysql.user WHERE host="old" and user="root";
UPDATE mysql.tmpUser SET host="new" WHERE user = "root";
INSERT INTO mysql.user SELECT * FROM mysql.tmpUser;

回答by RageZ

When you run mysql using --skip-grant-tablesmysql won't check any permissions. So basically you can do anything.

当您使用--skip-grant-tablesmysql运行 mysql时，不会检查任何权限。所以基本上你可以做任何事情。

To get back the root privileges you would need to run a query in mysqlDB like this

要取回 root 权限，您需要mysql像这样在DB 中运行查询

select * from user where user = 'root'

just to check if the root user is still there if ok:

只是为了检查 root 用户是否还在那里，如果没问题：

UPDATE user SET Grant_priv = 1, Super_priv = 1 WHERE user = 'root'

after you can restart mysql without the --skip-grant-tablesand the root user should be able to do some grant so your query should work

在您可以在没有 的情况下重新启动 mysql 之后--skip-grant-tables，root 用户应该能够进行一些授权，因此您的查询应该可以工作

回答by Alex Martelli

To run a GRANTquery, you just don'trun mysql with skip-grant-tables-- what's complicated about that...?

要运行GRANT查询，您只是不运行 mysql skip-grant-tables－ 这有什么复杂的……？