在Ubuntu上进行新安装后，拒绝用户'root'@'localhost'的访问(使用密码:是) [英] Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu

问题描述

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

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

，然后是apt-get install mariadb-server(没有sudo，但作为root).

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

有了mySQL -h localhost -u root --password=<PW>我得到了

拒绝用户'root'@'localhost'的访问(使用密码:是)

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

使用mySQL -u root -p我登录到数据库并完成

With mySQL -u root -p I logged into the DB and did

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.

推荐答案

TL; DR::要以root用户身份访问较新版本的mysql/mariadb，在完成新安装后，您需要在根外壳中(即sudo mysql -u root或mysql -u root在以su -或sudo -i首先启动的外壳中)

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 root inside a shell started by su - or sudo -i first)

在Ubuntu上完成相同的升级后，我遇到了相同的问题.

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

奇怪的是

sudo /usr/bin/mysql_secure_installation

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

Would accept my password, and allow me to set it, but I couldn't log in as root via the mysql client

我必须以

sudo mysqld_safe --skip-grant-tables

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

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

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

Looking at the mysql.user table I noticed for root the plugin column is set to unix_socket whereas 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 uid of the process running the client with that of the user in the mysql.user table. In other words to access mariadb as root you have to be logged in as root.

确保通过身份验证重新启动我的mariadb守护程序，我可以使用root身份登录

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

sudo mysql -u root -p

或

sudo su -
mysql -u root -p

完成此操作后，我考虑了如何在无需执行sudo的情况下进行访问，这仅是运行这些mysql查询的问题

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

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

(用所需的mysql根密码替换<password>).为root用户启用了密码登录.

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

或者运行mysql查询:

Alternatively running the mysql query:

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

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

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.

在这两个方法中的任何一个之后，您都需要重新启动mysql/mariadb:

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

sudo service mysql restart

瞧，我可以通过mysql -u root -p

请注意这样做会降低安全性大概是因为MariaDB开发人员选择进行这样的root访问是有充分理由的.

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

考虑到这一点，我很高兴必须执行sudo mysql -u root -p，所以我切换回该位置，但是我认为我会发布解决方案，因为我找不到其他解决方案.

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

这篇关于在Ubuntu上进行新安装后，拒绝用户'root'@'localhost'的访问(使用密码:是)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！