在 MySQL 命令行中拒绝 root 用户的访问

Access denied for root user in MySQL command-line

提问by gavin

I've just installed xampp, and am using command line to write mySQL. I am using 'root' with no password and can connect to mysql but cannot CREATE DATABASEas I get the error 1044 access denied for user '' @ 'localhost'. I am logged in as -uroot.

我刚刚安装了 xampp，并且正在使用命令行编写 mySQL。我正在使用没有密码的“root”，可以连接到 mysql，但不能，CREATE DATABASE因为我收到错误1044 access denied for user '' @ 'localhost'。我以-uroot.

I have privileges in phpMyadmin to do what I want, but, in command line I seem to have no write privileges. I've looked at all the other related posts on this topic but to no avail. I cannot GRANT privileges as I have none anyway.

我在 phpMyadmin 中有权限做我想做的事，但是，在命令行中我似乎没有写权限。我已经查看了有关此主题的所有其他相关帖子，但无济于事。我不能授予特权，因为我没有任何特权。

采纳答案by gavin

I re-installed the ODBC connector msi and re-installed mySQL directly (aside from xampp) and it now works. It was a connector problem I think, as SHOW DATABASES wasn't actually showing my databases at all.

我重新安装了 ODBC 连接器 msi 并直接重新安装了 mySQL（除了 xampp），现在它可以工作了。我认为这是一个连接器问题，因为 SHOW DATABASES 实际上根本没有显示我的数据库。

My 'root' login wasn't getting access to the DB, which made it seem like it had limited priviliges but it actually wasn't connected properly.

我的“root”登录无法访问数据库，这使得它看起来权限有限，但实际上并没有正确连接。

回答by Jacob

Are you logging into MySQL as root? You have to explicitly grant privileges to your "regular" MySQL user account while logged in as MySQL root.

您是否以 root 身份登录 MySQL？在以 MySQL 根用户身份登录时，您必须明确授予“常规”MySQL 用户帐户的权限。

First set up a root account for your MySQL database.

首先为您的 MySQL 数据库设置一个 root 帐户。

In the terminal type:

在终端类型中：

mysqladmin -u root password 'password'

To log into MySQL, use this:

要登录 MySQL，请使用以下命令：

mysql -u root -p

To set the privileges manually start the server with the skip-grant-tables option, open mysql client and manually update the mysql.user table and/or the mysql.db tables. This can be a tedious task though so if what you need is an account with all privs I would do the following.

要设置权限，使用 skip-grant-tables 选项手动启动服务器，请打开 mysql 客户端并手动更新 mysql.user 表和/或 mysql.db 表。不过，这可能是一项乏味的任务，因此如果您需要的是一个具有所有权限的帐户，我将执行以下操作。

Start the server with the skip-grant-tablesoption

使用skip-grant-tables选项启动服务器

Start mysql client (without a username/password)

启动 mysql 客户端（没有用户名/密码）

Issue the command

发出命令

flush privileges;

which forces the grant tables to be loaded.

这会强制加载授权表。

Create a new account with the GRANT command something like this (but replacing username and password with whatever you want to use.

使用类似这样的 GRANT 命令创建一个新帐户（但用您想要使用的任何内容替换用户名和密码。

GRANT ALL on *.* to 'username'@'localhost' identified by 'password';

Restart the server in normal mode (without skip-grant-tables) and log in with your newly created account.

以正常模式（没有跳过授权表）重新启动服务器并使用您新创建的帐户登录。

Refer thisMySQL docs.

请参阅此MySQL 文档。

回答by Paulo Pinto

navigate do C:\xampp\mysql\bin\ and make sure the file mysql.exe is in that folder.

导航 do C:\xampp\mysql\bin\ 并确保文件 mysql.exe 在该文件夹中。

mysql -uroot -p

if dont have a password just press enter.

如果没有密码只需按回车键。

the prompt changes to

提示更改为

mysql>

do your mysql commands

执行你的 mysql 命令

回答by Vishal

By default there is no password is set for root user in XAMPP.

默认情况下，XAMPP 中没有为 root 用户设置密码。

You can set password for root user of MySQL.

您可以为 MySQL 的 root 用户设置密码。

Navigate to

导航

localhost:80/security/index.php

本地主机：80/security/index.php

and set password for root user.

并为root用户设置密码。

Note:Please change the port number in above url if your Apache in on different port.

注意：如果您的 Apache 在不同的端口，请更改上面 url 中的端口号。

Open XAMPP control panel Click "Shell" button

打开 XAMPP 控制面板 点击“Shell”按钮

Command prompt window will open now in that window type

命令提示符窗口现在将在该窗口类型中打开

mysql -u root -p;

It will ask for password type the password which you have set for root user.

它将要求输入密码，输入您为 root 用户设置的密码。

There you go ur logged in as root user :D Now do what u want to do :P

您以 root 用户身份登录 :D 现在做您想做的事 :P

回答by rubo77

Gain access to a MariaDB 10 database server

访问 MariaDB 10 数据库服务器

After stopping the database server, the next step is to gain access to the server through a backdoor by starting the database server and skipping networking and permission tables. This can be done by running the commands below.

停止数据库服务器后，下一步是通过启动数据库服务器并跳过网络和权限表，通过后门获得对服务器的访问。这可以通过运行以下命令来完成。

sudo mysqld_safe --skip-grant-tables --skip-networking &

Reset MariaDB root Password

重置 MariaDB 根密码

Now that the database server is started in safe mode, run the commands below to logon as root without password prompt. To do that, run the commands below

现在数据库服务器以安全模式启动，运行以下命令以 root 身份登录，无需密码提示。为此，请运行以下命令

sudo mysql -u root

Then run the commands below to use the mysql database.

然后运行下面的命令来使用mysql数据库。

use mysql;

Finally, run the commands below to reset the root password.

最后，运行以下命令以重置 root 密码。

update user set password=PASSWORD("new_password_here") where User='root';

Replace new_password _herewith the new password you want to create for the root account, then press Enter.

将new_password _here替换为您要为 root 帐户创建的新密码，然后按 Enter。

After that, run the commands below to update the permissions and save your changes to disk.

之后，运行以下命令以更新权限并将更改保存到磁盘。

flush privileges;

Exit (CTRL + D) and you're done.

退出（CTRL + D），你就完成了。

Next start MariaDB normally and test the new password you just created.

接下来正常启动 MariaDB 并测试您刚刚创建的新密码。

sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service

Logon to the database by running the commands below.

通过运行以下命令登录到数据库。

sudo mysql -u root -p

source:https://websiteforstudents.com/reset-mariadb-root-password-ubuntu-17-04-17-10/

来源：https : //websiteforstudents.com/reset-mariadb-root-password-ubuntu-17-04-17-10/

回答by A. Gh.

I had the same issue, and it turned out to be that MariaDB was set to allow only root to log in locally via the unix_socket plug-in, so clearing that setting allowed successfully logging in with the user specified on the command line, provided a correct password is entered, of course. See this answer on Ask Ubuntu

我遇到了同样的问题，结果是 MariaDB 设置为仅允许 root 通过 unix_socket 插件在本地登录，因此清除该设置允许使用命令行上指定的用户成功登录，提供了当然，输入正确的密码。在 Ask Ubuntu 上看到这个答案

回答by alpc

Server file only change name folder

服务器文件只更改名称文件夹

etc/mysql
rename
mysql-

回答by Tom

this might help on Ubuntu:

这可能对 Ubuntu 有所帮助：

go to /etc/mysql/my.cnf and comment this line:

转到 /etc/mysql/my.cnf 并注释此行：

bind-address           = 127.0.0.1

Hope this helps someone, I've been searching for this a while too

希望这对某人有所帮助，我也一直在寻找这个

Cheers

干杯

回答by feeela

You mustn't have a space character between -uand the username:

-u和用户名之间不能有空格字符：

mysql -uroot -p
# or
mysql --user=root --password