如何在 MySQL 8.0 中授予 root 用户所有权限

How to grant all privileges to root user in MySQL 8.0

提问by Praveen

Tried

试过

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

Getting

得到

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'root' WITH GRANT OPTION' at line 1.

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“IDENTIFIED BY 'root' WITH GRANT OPTION”附近使用的正确语法。

Note: The same is working when tried in previous versions.

注意：在以前的版本中尝试时也是如此。

Also tried

也试过

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Getting

得到

ERROR 1410 (42000): You are not allowed to create a user with GRANT

ERROR 1410 (42000): 不允许您创建具有 GRANT 的用户

MySQL (8.0.11.0) username/password is root/root.

MySQL (8.0.11.0) 用户名​​/密码是 root/root。

回答by Mike Lischke

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANTcommand. Use CREATE USERinstead, followed by the GRANTstatement:

从 MySQL 8 开始，您不再可以（隐式）使用该GRANT命令创建用户。使用CREATE USER代替，后跟GRANT语句：

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Cautionabout the security risks about WITH GRANT OPTION, see:

关于 的安全风险注意事项WITH GRANT OPTION，请参见：

• Grant allprivileges on database

• 授予对数据库的所有权限

回答by J. Doe

1) This worked for me. First, create a new user. Example: User foowith password bar

1）这对我有用。首先，创建一个新用户。示例：foo有密码的用户bar

> mysql> CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';

2) Replace the below code with a username with 'foo'.

2) 用带有'foo'的用户名替换下面的代码。

> mysql> GRANT ALL PRIVILEGES ON database_name.* TO'foo'@'localhost';

Note: database_name is the database that you want to have privileges, .means all on all

注意：database_name 是你想拥有权限的数据库，. 意味着一切

3) Login as user foo

3) 以用户 foo 登录

mysql> mysql -u foo -p

Password: bar

密码：bar

4) Make sure your initial connection from Sequelize is set to foo with pw bar.

4) 确保您从 Sequelize 的初始连接设置为 foo 和 pw bar。

回答by Nebulastic

I see a lot of (wrong) answers, it is just as simple as this:

我看到很多（错误的）答案，就这么简单：

USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';
GRANT ALL ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Note: instead of a self-created useryou can use rootto connect to the database. However, using the default root account to let an application connect to the database is not the preferred way.

注意：user您可以使用而不是自创建root来连接到数据库。但是，使用默认的 root 帐户让应用程序连接到数据库并不是首选方式。

Alternative privileges (be careful and remember the least-privilege principle):

替代特权（小心并记住最小特权原则）：

-- Grant user permissions to all tables in my_database from localhost --
GRANT ALL ON my_database.* TO 'user'@'localhost';

-- Grant user permissions to my_table in my_database from localhost --
GRANT ALL ON my_database.my_table TO 'user'@'localhost';

-- Grant user permissions to all tables and databases from all hosts --
GRANT ALL ON *.* TO 'user'@'*';

If you would somehow run into the following error:

如果您以某种方式遇到以下错误：

ERROR 1130 (HY000): Host ‘1.2.3.4' is not allowed to connect to this MySQL server

ERROR 1130 (HY000): Host '1.2.3.4' is not allowed to connect to this MySQL server

You need add/change the following two lines in /etc/mysql/my.cnfand restart mysql:

您需要添加/更改以下两行/etc/mysql/my.cnf并重新启动mysql：

bind-address           = 0.0.0.0
skip-networking

回答by Sergey Podushkin

The specified user just doesn't exist on your MySQL (so, MySQL is trying to create it with GRANT as it did before version 8, but fails with the limitations, introduced in this version).

指定的用户在您的 MySQL 中不存在（因此，MySQL 尝试使用 GRANT 创建它，就像它在版本 8 之前所做的那样，但由于此版本中引入的限制而失败）。

MySQL's pretty dumb at this point, so if you have 'root'@'localhost' and trying to grant privileges to 'root'@'%' it treats them as different users, rather than generalized notion for root user on any host, including localhost.

MySQL 在这一点上非常愚蠢，所以如果你有 'root'@'localhost' 并试图向 'root'@'%' 授予权限，它会将它们视为不同的用户，而不是任何主机上的 root 用户的通用概念，包括本地主机。

The error message is also misleading.

错误消息也具有误导性。

So, if you're getting the error message, check your existing users with something like this

因此，如果您收到错误消息，请使用以下内容检查现有用户

SELECT CONCAT("'", user, "'@'", host, "'") FROM mysql.user;

and then create missing user (as Mike advised) or adjust your GRANT command to the actual exisiting user specificaion.

然后创建缺少的用户（如 Mike 建议的那样）或将您的 GRANT 命令调整为实际存在的用户规范。

回答by Nuel Makara

My Specs:

我的规格：

mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

What worked for me:

什么对我有用：

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'desired_password';
mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' WITH GRANT OPTION;

Response in both queries:

两个查询中的响应：

Query OK, O rows affected (0.10 sec*)

N.B: I created a database (db_name) earlier and was creating a user credential with all privileges granted to all tables in the DB in place of using the default root user which I read somewhere is a best practice.

注意：我之前创建了一个数据库 (db_name)，并且正在创建一个用户凭证，该凭证具有授予数据库中所有表的所有权限，而不是使用我在某处读取的默认 root 用户，这是最佳做法。

回答by zenglong chen

Check out your username and domain is the same as created before. Mysql select account by the two colums in user table.If it is different, mysql may think you want to create a new account by grant,which is not supported after 8.0 version.

检查您的用户名和域是否与之前创建的相同。Mysql通过user表中的两列选择账号，如果不同，mysql可能认为你想通过grant创建一个新账号，8.0版本以后不支持了。

回答by Armando Juarez

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I'm running a bitnami-mysql virtual machine to set up a local sandbox for development.

只是我在这个主题上的 2 美分。我在尝试从 MySQL Workbench 连接时遇到了完全相同的问题。我正在运行一个 bitnami-mysql 虚拟机来设置一个用于开发的本地沙箱。

Bitnami's tutorial said to run the 'Grant All Privileges' command:

Bitnami 的教程说要运行“授予所有权限”命令：

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke's answer.

这显然不起作用，我终于使用 Mike Lischke 的回答让它工作了。

What I think happened was that the root@% user had the wrong credentials associated to it. So if you've tried to modify the user's privileges and with no luck try:

我认为发生的事情是 root@% 用户的凭据错误。因此，如果您尝试修改用户的权限并且没有运气，请尝试：

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your my.cnf config file. In my case I've commented the line out since it's just for a sandbox environment.

1. 删除用户。
2. 再次创建用户。
3. 确保您的 my.cnf 配置文件具有正确的绑定。就我而言，我已注释掉该行，因为它仅适用于沙箱环境。

From Mysql Console:

从 Mysql 控制台：

List Users (helpful to see all your users):

列出用户（有助于查看所有用户）：

select user, host from mysql.user;

Drop Desired User:

删除所需用户：

drop user '{{ username }}'@'%';

Create User and Grant Permissions:

创建用户并授予权限：

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

运行此命令：

FLUSH PRIVILEGES;

Locate your mysql config file 'my.cnf' and look for a line that looks like this:

找到您的 mysql 配置文件“my.cnf”并查找如下所示的行：

bind-address=127.0.0.1

and comment it using a '#':

并使用“#”对其进行评论：

#bind-address=127.0.0.1

Then restart your mysql service.

然后重启你的mysql服务。

Hope this helps someone having the same issue!

希望这可以帮助有同样问题的人！

回答by ZERO

This worked for me:

这对我有用：

mysql> FLUSH PRIVILEGES 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES

回答by devo chen

1. grant privileges

1. 授予权限

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'WITH GRANT OPTION;

mysql> 授予所有权限。'root'@'%'with GRANT OPTION;

mysql> FLUSH PRIVILEGES

mysql> 刷新特权

2. check user table:

2.查看用户表：

mysql> use mysql

mysql> 使用 mysql

mysql> select host,user from user

mysql> 从用户中选择主机、用户

3.Modify the configuration file

3.修改配置文件

mysql default bind ip：127.0.0.1, if we want to remote visit services,just delete config

mysql默认绑定ip：127.0.0.1，如果要远程访问服务，删除config即可

#Modify the configuration file
vi /usr/local/etc/my.cnf

#Comment out the ip-address option
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1

4.finally restart the services

4.最后重启服务

brew services restart mysql

brew 服务重启 mysql

回答by u11270882

My Specs:

我的规格：

mysql --version
mysql  Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

What worked for me:

什么对我有用：

mysql> USE mysql;
mysql> UPDATE User SET Host='%' WHERE User='root' AND Host='localhost';