无法使用在 mysql 中创建的用户登录

Cannot log in with created user in mysql

提问by Brian G

Using this command

使用这个命令

GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';

I try to login with:

我尝试登录：

 mysql -u brian -ppassword

The error is:

错误是：

ERROR 1045 (28000): Access denied for user 'brian'@'localhost' (using password: YES)

I am doing this as root and I did try to flush privileges.

我以 root 身份执行此操作，并且确实尝试刷新权限。

I tried this with countless users but it does not seem to work. I can create a user with no password and login works. Command line and from phpmyadmin

我与无数用户一起尝试过，但似乎不起作用。我可以创建一个没有密码的用户并且登录有效。命令行和来自 phpmyadmin

Also check to see if the user was in mysql.user which it is.

还要检查用户是否在 mysql.user 中。

Show grants for brian shows:

布赖恩节目的节目资助：

| GRANT ALL PRIVILEGES ON *.* TO 'brian'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |

回答by chaos

You probably have this perpetual MySQL problem where one of the default users in the user table is '' @ localhost, which winds up denying all localhostusers later in the table. What I would do is mysqldumpthe mysqldatabase and look for this entry in the Usertable; if found, delete it and flush privileges.

您可能会遇到这个永久的 MySQL 问题，其中用户表中的默认用户之一是'' @ localhost，最终拒绝localhost表中的所有用户。我会做的是mysqldump将mysql在此项目数据库并查看User表; 如果找到，将其删除并刷新权限。

For more details see https://dev.mysql.com/doc/refman/5.5/en/connection-access.html.

有关更多详细信息，请参阅https://dev.mysql.com/doc/refman/5.5/en/connection-access.html。

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from h1.example.net by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

一种常见的误解是认为对于给定的用户名，当服务器尝试为连接查找匹配项时，首先使用明确命名该用户的所有行。这不是真的。前面的示例说明了这一点，其中 jeffrey 来自 h1.example.net 的连接首先不是由包含 'jeffrey' 作为 User 列值的行匹配，而是由没有用户名的行匹配。结果，jeffrey 被认证为匿名用户，即使他在连接时指定了用户名。

回答by user2509314

This is a problem caused by the anonymous users. Once I install MySQL I always run

这是匿名用户造成的问题。一旦我安装 MySQL，我总是运行

shell> mysql_secure_installation 

and select to set/change the root password, remove anonymous users, disallow remote root login, remove the test database. This will remove the anonymous user and secure your installation. It should also solve the problem you have.

并选择设置/更改root密码、删除匿名用户、禁止远程root登录、删除测试数据库。这将删除匿名用户并保护您的安装。它还应该解决您遇到的问题。

回答by zinking

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

回答by cortopy

None of the solutions provided here worked. After loads of error and trial I realised that I had special characters in the password. Changing password without special characters solved the issue

此处提供的解决方案均无效。经过大量的错误和试验后，我意识到密码中有特殊字符。更改没有特殊字符的密码解决了问题

回答by Russell Silva

The mysql docs have this to say: (from http://dev.mysql.com/doc/refman/5.1/en/adding-users.html):

mysql 文档有这样的说法：（来自http://dev.mysql.com/doc/refman/5.1/en/adding-users.html）：

Two of the accounts have a user name of montyand a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost'account can be used only when connecting from the local host. The 'monty'@'%'account uses the '%'wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for montyto be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_dbwould take precedence when monty connects from the local host. As a result, montywould be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Hostcolumn value than the 'monty'@'%'account and thus comes earlier in the user table sort order.

其中两个帐户的用户名是monty，密码是some_pass。这两个帐户都是具有执行任何操作的完全权限的超级用户帐户。该'monty'@'localhost'帐户只能在从本地主机连接时使用。该'monty'@'%'帐户'%'对主机部分使用通配符，因此它可用于从任何主机进行连接。

必须同时拥有两个帐户monty才能从任何地方作为monty. 如果没有 localhost 帐户，mysql_install_db当 monty 从本地主机连接时，由 localhost 创建的匿名用户帐户将优先。因此，monty将被视为匿名用户。其原因是匿名用户帐户具有Host比'monty'@'%'帐户更具体的列值，因此在用户表排序顺序中更早。

With this in mind I would recommend you create a 'brian'@'localhost'user with the same privileges.

考虑到这一点，我建议您创建一个'brian'@'localhost'具有相同权限的用户。

回答by Asaph

You forgot the quotes around brian in your grant statement. Try it like this:

您忘记了授权声明中有关 brian 的引号。像这样尝试：

GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';

GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';

回答by Chenxiao

I think 'Russell Silva' is right...

我认为“拉塞尔席尔瓦”是对的......

I created an user by

我创建了一个用户

CREATE USER 'username'@'%' PASSWORD='userpassword';

But I cannot login in this account.The console told me that

但是我无法登录这个帐户。控制台告诉我

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

So I created an user with the same username except that changing '%' to 'localhost',and I could finally login in as 'username'. It's quite weird for me though.

所以我创建了一个用户名相同的用户，只是将“%”更改为“localhost”，我最终可以以“用户名”身份登录。不过这对我来说很奇怪。

回答by Ari

In my case it was due to me clicking "SSL: REQUIRE SSL" (in phpmyadmin). When I changed it to "REQUIRE NONE" I could log in.

就我而言，这是由于我单击了“SSL：REQUIRE SSL”（在 phpmyadmin 中）。当我将其更改为“REQUIRE NONE”时，我可以登录。

回答by user1717662

Change to native password using this command:

使用以下命令更改为本地密码：

ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

回答by theINtoy

I had a similar problem attempting to connect to a Maria DB running on Ubuntu after upgrading to 17.04.

升级到 17.04 后，我在尝试连接到在 Ubuntu 上运行的 Maria DB 时遇到了类似的问题。

The default was to listen only on localhost, 127.0.0.1.

默认值是仅在 localhost 127.0.0.1 上侦听。

To make MySQL/Maria listen on all available ports and interfaces I needed to explicitly specify bind-address=0.0.0.0. I added this line to the end of the file /etc/mysql/my.cnf, i.e.

为了让 MySQL/Maria 监听所有可用的端口和接口，我需要明确指定 bind-address=0.0.0.0。我将此行添加到文件的末尾/etc/mysql/my.cnf，即

...
[client-server]

# Import all .cnf files from configuration directory

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
bind-address=0.0.0.0

Then...

然后...

sudo /etc/init.d/mysql restart