MySQL 在 Ubuntu 上进行新安装后，用户 'root'@'localhost'（使用密码：YES）的访问被拒绝

Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu

提问by SQL-Neuling

Today I did a login as root into Ubuntu 14.04.1 LTS ll

今天我以 root 身份登录到 Ubuntu 14.04.1 LTS ll

and then apt-get install mariadb-server(without sudo but as root).

然后apt-get install mariadb-server（没有 sudo 但作为 root）。

With mySQL -h localhost -u root --password=<PW>I got

随着mySQL -h localhost -u root --password=<PW>我得到

Access denied for user 'root'@'localhost' (using password: YES)

用户 'root'@'localhost' 访问被拒绝（使用密码：YES）

With mySQL -u root -pI logged into the DB and did

随着mySQL -u root -p我登录数据库并做了

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<PW>';
FLUSH ALL PRIVILEGES;

But this did not help. Have you got any idea? I did not find the answer for the similar questions.

但这没有帮助。你有什么想法吗？我没有找到类似问题的答案。

回答by DorianFM

TL;DR:To access newer versions of mysql/mariadb after as the root user, after a new install, you need to be in a root shell (ie sudo mysql -u root, or mysql -u rootinside a shell started by su -or sudo -ifirst)

TL;DR：要以 root 用户身份访问较新版本的 mysql/mariadb，在新安装后，您需要在 root shell 中（即sudo mysql -u root，或mysql -u root在由su -或sudo -ifirst启动的 shell 中）

---

---

Having just done the same upgrade, on Ubuntu, I had the same issue.

刚刚完成相同的升级，在 Ubuntu 上，我遇到了同样的问题。

What was odd was that

奇怪的是

sudo /usr/bin/mysql_secure_installation

Would accept my password, and allow me to set it, but I couldn't log in as rootvia the mysqlclient

会接受我的密码，并允许我设置它，但我无法root通过mysql客户端登录

I had to start mariadb with

我不得不启动 mariadb

sudo mysqld_safe --skip-grant-tables

to get access as root, whilst all the other users could still access fine.

以 root 身份访问，而所有其他用户仍然可以正常访问。

Looking at the mysql.usertable I noticed for root the plugincolumn is set to unix_socketwhereas all other users it is set to 'mysql_native_password'. A quick look at this page: https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/explains that the Unix Socket enables logging in by matching uidof the process running the client with that of the user in the mysql.usertable. In other words to access mariadb as rootyou have to be logged in as root.

查看mysql.user表，我注意到 root 的plugin列设置为，unix_socket而所有其他用户都设置为“mysql_native_password”。快速浏览此页面：https: //mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/解释说 Unix Socket 通过匹配uid运行客户端的进程与用户的进程来启用登录mysql.user桌子。换句话说，要访问 mariadb，root您必须以 root 身份登录。

Sure enough restarting my mariadb daemon with authentication required I can login as root with

果然重新启动我的 mariadb 守护进程需要身份验证我可以用 root 登录

sudo mysql -u root -p

or

或者

sudo su -
mysql -u root -p

Having done this I thought about how to access without having to do the sudo, which is just a matter of running these mysql queries

完成此操作后，我考虑如何访问而不必执行 sudo，这只是运行这些 mysql 查询的问题

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

(replacing <password>with your desired mysql root password). This enabled password logins for the root user.

（替换<password>为您想要的 mysql root 密码）。这为 root 用户启用密码登录。

Alternatively running the mysql query:

或者运行 mysql 查询：

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Will change the root account to use password login without changing the password, but this may leave you with a mysql/mariadb install with no root password on it.

将更改 root 帐户以使用密码登录而不更改密码，但这可能会让您安装 mysql/mariadb，而没有 root 密码。

After either of these you need to restarting mysql/mariadb:

在其中任何一个之后，您都需要重新启动 mysql/mariadb：

sudo service mysql restart

And voila I had access from my personal account via mysql -u root -p

瞧，我可以通过我的个人帐户访问 mysql -u root -p

PLEASE NOTE THAT DOING THIS IS REDUCING SECURITYPresumably the MariaDB developers have opted to have root access work like this for a good reason.

请注意，这样做会降低安全性据推测，MariaDB 开发人员选择让这样的 root 访问工作是有充分理由的。

Thinking about it I'm quite happy to have to sudo mysql -u root -pso I'm switching back to that, but I thought I'd post my solution as I couldn't find one elsewhere.

考虑一下，我很高兴不得不sudo mysql -u root -p这样做，所以我又回到了那个，但我想我会发布我的解决方案，因为我在其他地方找不到。

回答by Curycu

In clean Ubuntu 16.04 LTS, MariaDB rootlogin for localhostchanged from password style to sudo login style...

在干净的 Ubuntu 16.04 LTS 中，本地主机的MariaDB根登录从密码样式更改为 sudo 登录样式...

so, just do

所以，就做

sudo mysql -u root

since we want to login with password, create another user 'user'

因为我们想用密码登录，创建另一个用户'user'

in MariaDB console... (you get in MariaDB console with 'sudo mysql -u root')

在 MariaDB 控制台中...（您可以使用“sudo mysql -u root”进入 MariaDB 控制台）

use mysql
CREATE USER 'user'@'localhost' IDENTIFIED BY 'yourpassword';
\q

then in bash shell prompt,

然后在 bash shell 提示符下，

mysql-workbench

and you can login with 'user' with 'yourpassword' on localhost

您可以在本地主机上使用“您的密码”使用“用户”登录

回答by IsraelM17

Try the command

试试命令

sudo mysql_secure_installation

press enter and assign a new password for rootin mysql/mariadb.

按 Enter 并在mysql/mariadb 中为root分配一个新密码。

If you get an error like

如果你得到这样的错误

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

错误 2002 (HY000)：无法通过套接字'/var/run/mysqld/mysqld.sock' 连接到本地 MySQL 服务器

enable the service with

启用服务

service mysql start

now if you re-enter with

现在如果你重新输入

mysql -u root -p

if you follow the problem enter with sudo suand mysql -u root -pnow apply permissions to root

如果您按照问题输入sudo su并mysql -u root -p现在将权限应用到root

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';

this fixed my problem in MariaDB.

这解决了我在MariaDB 中的问题。

Good luck

祝你好运

回答by sailfish009

from superuser accepted answer:

来自超级用户接受的答案：

sudo mysql -u root
use mysql;
update user set plugin='' where User='root';
flush privileges;
exit;

回答by Joe C

I had to be logged into Ubuntu as root in order to access Mariadb as root. It may have something to do with that "Harden ..." that it prompts you to do when you first install. So:

我必须以 root 身份登录 Ubuntu 才能以 root 身份访问 Mariadb。它可能与“强化...”有关，它会在您第一次安装时提示您这样做。所以：

$ sudo su
[sudo] password for user: yourubunturootpassword
# mysql -r root -p
Enter password: yourmariadbrootpassword

and you're in.

你在。

回答by Sylvain

The new command to flush the privileges is:

刷新权限的新命令是：

FLUSH PRIVILEGES

FLUSH PRIVILEGES

The old command FLUSH ALL PRIVILEGESdoes not work any more.

旧命令FLUSH ALL PRIVILEGES不再起作用。

You will get an error that looks like that:

你会得到一个看起来像这样的错误：

MariaDB [(none)]> FLUSH ALL PRIVILEGES; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALL PRIVILEGES' at line 1

MariaDB [(none)]> FLUSH ALL PRIVILEGES; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALL PRIVILEGES' at line 1

Hope this helps :)

希望这可以帮助 ：）

回答by Interlated

Run mysql_upgrade.

运行 mysql_upgrade。

Check that

检查一下

SHOW GRANTS FOR 'root'@'localhost';

says

说

GRANT ALL PRIVILEGES ON ... WITH GRANT OPTION 

Check that the table exists _mysql.proxies_priv_.

检查表是否存在_mysql.proxys_priv_。

Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?

尝试授予权限时，用户 'root'@'localhost' 的访问被拒绝。如何授予权限？

回答by nguyen

System like Ubuntu prefers to use auth_socketplugin. It will try to authenticate by comparing your username in DB and process which makes mysql request; it is described in here

像 Ubuntu 这样的系统更喜欢使用auth_socket插件。它将尝试通过比较您在数据库中的用户名和发出 mysql 请求的进程来进行身份验证；它在这里描述

The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server, and permits the connection only if the names match.

socket插件检查socket用户名（操作系统用户名）是否与客户端程序向服务器指定的MySQL用户名匹配，只有匹配时才允许连接。

Instead you may want to back with the mysql_native_password, which will require user/password to authenticate.

相反，您可能希望使用mysql_native_password 返回，这将需要用户/密码进行身份验证。

About the method to achieve that, I recommend thisinstead.

关于实现这一点的方法，我推荐这个。