在 Ubuntu 上新安装后，用户 'root'@'localhost' 的访问被拒绝(使用密码:YES)
今天我以 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"访问(使用密码:YES)
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，在新安装后，您需要在根 shell 中(即 sudo mysql -u root 或 mysql -u root 在由 su - 或 sudo 启动的 shell 中-i 首先)
TL;DR: To access newer versions of mysql/mariadb 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
我必须使用 mariadb 启动
I had to start mariadb with
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 Socket 通过将运行客户端的进程的 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;
(将 <password> 替换为您想要的 mysql root 密码).这为 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.
相关文章