MySQL 不用sudo连接mysql服务器

Connect to mysql server without sudo

提问by Riyafa Abdul Hameed

The command:

命令：

mysql -u root -p

gives the error:

给出错误：

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But running sudo privileges, works:

但是运行 sudo 权限，有效：

sudo mysql -u root -p

Is it possible to get rid of the sudo requirementbecause it prevents me from opening the database in intellij? I tried the following as in the answer to this question Connect to local MySQL server without sudo:

是否有可能摆脱 sudo 要求，因为它阻止我在 intellij 中打开数据库？我在这个问题的答案中尝试了以下内容Connect to local MySQL server without sudo：

sudo chmod -R 755 /var/lib/mysql/

which did not help. The above question has a different error thrown

这没有帮助。上面的问题抛出了不同的错误

回答by Riyafa Abdul Hameed

Only the root user needs sudo requirement to login to mysql. I resolved this by creating a new user and granting access to the required databases:

只有 root 用户需要 sudo 要求才能登录 mysql。我通过创建一个新用户并授予对所需数据库的访问权限解决了这个问题：

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

now newusercan login without sudo requirement:

现在newuser可以在没有 sudo 要求的情况下登录：

mysql -u newuser -p

回答by Hemant Thorat

You need to change algorithm. Following work for me,

你需要改变算法。以下为我工作，

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql > FLUSH PRIVILEGES;

回答by Keet Sugathadasa

You can use the same ROOT user, or a NEW_USER and remove the SUDO privileges. Below example shows how to remove connect using ROOT, without SUDO.

您可以使用相同的 ROOT 用户或 NEW_USER 并删除 SUDO 权限。下面的示例显示了如何使用 ROOT 删除连接，而无需使用 SUDO。

Connect to MY-SQL using SUDO

使用 SUDO 连接到 MY-SQL

sudo mysql -u root

Delete the current Root User from the User Table

从用户表中删除当前根用户

DROP USER 'root'@'localhost';

Create a new ROOT user (You can create a different user if needed)

创建一个新的 ROOT 用户（如果需要，您可以创建一个不同的用户）

CREATE USER 'root'@'%' IDENTIFIED BY '';

Grant permissions to new User (ROOT)

授予新用户权限（ROOT）

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

Flush privileges, so that the Grant tables get reloaded immediately. (Why do we need to flush privileges?)

刷新特权，以便立即重新加载 Grant 表。（为什么需要刷权限？）

FLUSH PRIVILEGES;

Now it's all good. Just in case, check whether a new root user is created.

现在一切都好了。以防万一，检查是否创建了新的 root 用户。

SELECT User,Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

Exit mysql. (Press CTRL + Z). Connect to MySQL without SUDO

退出mysql。（按 CTRL + Z）。无需 SUDO 即可连接到 MySQL

mysql -u root

Hope this will help!

希望这会有所帮助！

回答by Mahmood Shahbazian

first login to your mysql with sudo.

首先使用 sudo 登录到您的 mysql。

then use this code to change "plugin" coloumn value from "unix_socket" to "mysql_native_password" for root user.

然后使用此代码将 root 用户的“plugin”列值从“unix_socket”更改为“mysql_native_password”。

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

FLUSH PRIVILEGES;

finally restart mysql service. that's it.

最后重启mysql服务。就是这样。

if you want more info, check thislink

如果您想了解更多信息，请查看此链接

回答by SREEKANTH KC

You can use the below query:

您可以使用以下查询：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

This query is enough.

这个查询就够了。

回答by Atequer Rahman

I have solved this problem using following commands.

我已经使用以下命令解决了这个问题。

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';
FLUSH PRIVILEGES;

Here, username= any user name you like.

这里， username=您喜欢的任何用户名。

and password= any password you like.

和password= 您喜欢的任何密码。

回答by user1877106

This answerneeds to be slightly adapted for mariaDB instead of mysql.

这个答案需要稍微适应 mariaDB 而不是 mysql。

First login as root using sudo:

首先使用 sudo 以 root 身份登录：

$ sudo mysql -uroot

Then alter the mariadb root user:

然后更改 mariadb root 用户：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password USING PASSWORD('mypassword');
FLUSH PRIVILEGES;

From now on sudo is not longer needed:

从现在起不再需要 sudo：

$ mysql -uroot -p

Version used: mysql Ver 15.1 Distrib 10.4.13-MariaDB, for osx10.15 (x86_64) using readline 5.1

使用的版本：mysql Ver 15.1 Distrib 10.4.13-MariaDB，用于使用 readline 5.1 的 osx10.15 (x86_64)

回答by user1877106

In the comment of the question you answer you referenced, it reads

在你回答你引用的问题的评论中，它写着

Ok, just try to analyze all of the directories down in the path of the socket file, they need to have o+rx and the sock file too (it's not a good idea to make it modifiable by others).

You can also try to remove mysql.sock and then restart mysqld, the file should be created by the daemon with proper privileges.

好的，只需尝试分析套接字文件路径中的所有目录，它们也需要有 o+rx 和 sock 文件（让它被其他人修改不是一个好主意）。

您也可以尝试删除 mysql.sock 然后重新启动 mysqld，该文件应该由具有适当权限的守护进程创建。

This seemed to work for this question(the one you said you looked at) so it may work for you as well

这似乎适用于这个问题（你说你看过的那个）所以它也适用于你

回答by Bernd Buffen

The error Message:

错误信息：

"ERROR 1698 (28000): Access denied for user 'root'@'localhost'"

“错误 1698 (28000)：用户 'root'@'localhost' 的访问被拒绝”

means that the Server not allow the connect for this user and not that mysql cant access the socket.

意味着服务器不允许此用户的连接，而不是 mysql 无法访问套接字。

try this to solve the problem:

试试这个来解决问题：

Login in your DB

登录您的数据库

sudo mysql -u root -p

then make these modifications:

然后进行这些修改：

MariaDB []>use mysql;
MariaDB [mysql]>update user set plugin=' ' where User='root';
MariaDB [mysql]>flush privileges;
MariaDB [mysql]>exit

try login again without sudo

尝试在没有 sudo 的情况下再次登录