用户 root 的访问被拒绝 - MAC OS 上的 mysql

Access denied for user root - mysql on MAC OS

提问by Wordica

I know how do skip this problem on ubuntu, but how can i do it on MAC OS?

我知道如何在 ubuntu 上跳过这个问题，但我如何在 MAC OS 上做到这一点？

How can i set password for mysql on MAC?

如何在 MAC 上为 mysql 设置密码？

1) Doesn't work

1）不起作用

mysqladmin -u root password NEWPASSWORD

2)Doesn't work

2）不起作用

mysqld --skip-grant-tables --skip-networking &

3) This works:

3）这有效：

mysql root password forgotten

mysql root密码忘记了

回答by Suren Konathala

You can do the following on Mac (El Capitan)

您可以在 Mac (El Capitan) 上执行以下操作

1. Open a Terminal window, use the command below to stop mysql if it's already running.

   sudo /usr/local/mysql/support-files/mysql.server stop

   You can also check System Preferences > MySQL to see if it is running

2. Start MySQL with this command:

   sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

3. Open a new terminal window/tab:

   sudo /usr/local/mysql/bin/mysql -u root

   This should open "mysql" prompt. Execute the following command:

   $mysql> UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root';

   Troubleshooting tips:

   A) The command for MySql versions before 5.7 was:

   $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';

   B) If you see ERROR 1046 (3D000): No database selected, then run this command first:

   use mysql;

   C) If you see unknown "Password" field error, then run this command:

   UPDATE USER SET AUTHENTICATION_STRING=password('NewPassword') WHERE user='root'; $mysql> FLUSH PRIVILEGES; $mysql> EXIT

4. Stop MySql server

   sudo /usr/local/mysql/support-files/mysql.server stop

5. Restart MySQL, either through System Preferences > MySql or using a command.

1. 打开终端窗口，如果 mysql 已经在运行，请使用下面的命令停止它。

   sudo /usr/local/mysql/support-files/mysql.server stop

   您还可以检查 System Preferences > MySQL 以查看它是否正在运行

2. 使用以下命令启动 MySQL：

   sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

3. 打开一个新的终端窗口/选项卡：

   sudo /usr/local/mysql/bin/mysql -u root

   这应该打开“mysql”提示。执行以下命令：

   $mysql> UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root';

   故障排除提示：

   A) 5.7 之前的 MySql 版本的命令是：

   $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';

   B) 如果您看到ERROR 1046 (3D000): No database selected，请先运行此命令：

   use mysql;

   C) 如果您看到未知的“密码”字段错误，请运行以下命令：

   UPDATE USER SET AUTHENTICATION_STRING=password('NewPassword') WHERE user='root'; $mysql> FLUSH PRIVILEGES; $mysql> EXIT

4. 停止 MySql 服务器

   sudo /usr/local/mysql/support-files/mysql.server stop

5. 通过 System Preferences > MySql 或使用命令重新启动 MySQL。

回答by E. Brown

The solution of

的解决方案

UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root';

wasn't working for me, but I did

不适合我，但我做了

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';

And was able to proceed. I'm using Ver 8.0.12.

并且能够继续。我使用的是 8.0.12 版。

回答by Ruchin Somal

You can do the following on iMac or Mac (High Sierra)

您可以在 iMac 或 Mac (High Sierra) 上执行以下操作

Open a Terminal window, and stop the mysql if it's already running. You can also check this System Preferences > MySQL > see if it is running.

打开终端窗口，如果 mysql 已经在运行，则停止它。您还可以检查此系统偏好设置 > MySQL > 查看它是否正在运行。

Start MySQL with this command for skipping the main table

使用此命令启动 MySQL 以跳过主表

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

Open a new terminal window/tab..

打开一个新的终端窗口/选项卡..

sudo /usr/local/mysql/bin/mysql -u root

This should open "mysql" prompt. Execute the below command:

这应该打开“mysql”提示。执行以下命令：

A ) MySQL 5.6 and below

A) MySQL 5.6 及以下

UPDATE mysql.user SET password=PASSWORD('NewPassord') WHERE user='root';

-- or --

- 或者 -

B) MySQL 5.7+

B) MySQL 5.7+

UPDATE mysql.user SET authentication_string=PASSWORD('NewPassord') WHERE user='root';

Restart MySQL, either through System Preferences > MySql or using a command.

通过 System Preferences > MySql 或使用命令重新启动 MySQL。

回答by Jaime Montero

MACOS 10.14 MOJAVE || MYSQL 8.0.15

MACOS 10.14 莫哈韦|| MYSQL 8.0.15

This didn't work on my mac:

这在我的 Mac 上不起作用：

sudo /usr/local/mysql/support-files/mysql.server stop

须藤/usr/local/mysql/support-files/mysql.server 停止

BUT THIS ACTUALLY WORKED:

但这确实有效：

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/support-files/mysql.server stop

The installation folder might vary per user, BE AWARE!

安装文件夹可能因用户而异，请注意！

Or just Check > System preferences > MySQL > if the server is running, stop it.

或者只是检查 > 系统首选项 > MySQL > 如果服务器正在运行，请停止它。

then,

然后，

Start MySQL with this command:

使用以下命令启动 MySQL：

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/bin/mysqld_safe --skip-grant-tables

Open a new terminal window/tab:

打开一个新的终端窗口/选项卡：

sudo /usr/local/mysql-8.0.15-macos10.14-x86_64/bin/mysql -u root

This should open "mysql" prompt. Execute the following command (*scroll right if you don't the full query):

这应该打开“mysql”提示。执行以下命令（如果没有完整查询，请向右滚动）：

UPDATE mysql.user SET authentication_string='your-password-goes-here' WHERE user='root' and host='localhost';

REMEMBER THAT

请记住

mysql-8.0.15-macos10.14-x86_64

mysql-8.0.15-macos10.14-x86_64

(in my case) is the installation folder on your local machine, and it might or might not be different than mine because of OS versions, mysql versions, installation methods used, etc.

（就我而言）是本地计算机上的安装文件夹，由于操作系统版本、mysql 版本、使用的安装方法等，它可能与我的不同，也可能与我的不同。

回答by S.Mishra

Very Simple Fix for MariaDB version: 10.4.6-MariaDon MojavemacOS

MariaDB 版本的非常简单的修复：10.4.6-MariaD在MojavemacOS 上

I have gone through all the answers. Some of them worked for me some of them not. I found one simple way to fix this on macOS or OSX. Here are the steps:

我已经浏览了所有的答案。他们中的一些人为我工作，其中一些人没有。我找到了一种在 macOS 或 OSX 上解决此问题的简单方法。以下是步骤：

Prerequisites:

先决条件：

Homebrew should be installed. Use the following linkto install homebrew on macOS or OSX.

应该安装 Homebrew。使用以下链接在 macOS 或 OSX 上安装自制软件。

Install mariadb:

安装 mariadb：

1. brew install mariadb
2. Start MySQL Server: mysql.server startor run brew services start mariadb to start MySQL Server at login to the computer.
3. Get into MySQL instance sudo mysql -u root

1. brew install mariadb
2. 启动 MySQL 服务器：mysql.server start或运行brew services start mariadb 以在登录计算机时启动 MySQL 服务器。
3. 进入MySQL实例 sudo mysql -u root

NOTE: mysql -u rootwill throw error ERROR 1698 (28000): Access denied for user 'root'@'localhost'so use sudoto run this command.

注意：mysql -u root会抛出错误，ERROR 1698 (28000): Access denied for user 'root'@'localhost'所以sudo用来运行这个命令。

1. Now to change the password of the rootuser I tried the following commands:

   1. UPDATE user SET password=PASSWORD("mypassword") WHERE User='root';
      • This has thrown an error: ERROR 1348 (HY000): Column 'Password' is not updatable
   2. UPDATE user SET authentication_string=PASSWORD("mypassword") WHERE User='root';
      • This has thrown an error: ERROR 1348 (HY000): Column 'authentication_string' is not updatable

2. But the following command worked:

   • ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypassword';
     • The response was: Query OK, 0 rows affected (0.009 sec)

4. 现在要更改root用户的密码，我尝试了以下命令：

   1. UPDATE user SET password=PASSWORD("mypassword") WHERE User='root';
      • 这引发了一个错误： ERROR 1348 (HY000): Column 'Password' is not updatable
   2. UPDATE user SET authentication_string=PASSWORD("mypassword") WHERE User='root';
      • 这引发了一个错误： ERROR 1348 (HY000): Column 'authentication_string' is not updatable

5. 但以下命令有效：

   • ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypassword';
     • 回应是： Query OK, 0 rows affected (0.009 sec)

So, it was a simple fix for me for the version 10.4.6-MariaDinstalled through brew. Hope this will help you too.

因此，对于10.4.6-MariaD通过 brew 安装的版本，这对我来说是一个简单的修复。希望这也能帮助你。

回答by Starchand

For MySQL 5.7 I had to use:

对于 MySQL 5.7，我必须使用：

UPDATE user SET authentication_string = PASSWORD('YourNewPassword'), password_expired = 'N' WHERE User = 'root';

更新用户 SET authentication_string = PASSWORD('YourNewPassword'), password_expired = 'N' WHERE User = 'root';

回答by youurayy

sudo mysql -uroot
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
flush privileges;
Ctrl+D
mysql -uroot -pnew_password  # will work now

Notice the sudoon the first line.

注意sudo第一行。

回答by Reena Upadhyay

I had a very hard time in fixing this issue on MAC Sierra, 10.12.6, MySql version 5.7.17

我很难在 MAC Sierra、10.12.6、MySql 版本 5.7.17 上解决这个问题

Following steps worked for me:

以下步骤对我有用：

Open a Terminal window, use the command below to stop mysql if it's already running.

打开终端窗口，如果 mysql 已经在运行，请使用下面的命令停止它。

sudo /usr/local/mysql/support-files/mysql.server stop

须藤/usr/local/mysql/support-files/mysql.server 停止

Start MySQL with this command:

使用以下命令启动 MySQL：

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

须藤/usr/local/mysql/bin/mysqld_safe --skip-grant-tables

Open a new terminal window/tab:

打开一个新的终端窗口/选项卡：

sudo /usr/local/mysql/bin/mysql -u root

须藤/usr/local/mysql/bin/mysql -u root

This will open "mysql" prompt. Execute following command in mysql prompt one by one:

这将打开“mysql”提示。在mysql提示符下一一执行以下命令：

use mysql;

UPDATE user SET authentication_string = PASSWORD('my_new_password'), password_expired = 'N' WHERE User = 'root';

FLUSH PRIVILEGES;

EXIT

Now Stop MySql server first then start it using below commands

现在先停止 MySql 服务器，然后使用以下命令启动它

sudo /usr/local/mysql/support-files/mysql.server stop

须藤/usr/local/mysql/support-files/mysql.server 停止

sudo /usr/local/mysql/support-files/mysql.server start

须藤/usr/local/mysql/support-files/mysql.server 启动

Hope this solves your issue.

希望这能解决您的问题。

回答by code-sushi

I discovered that in Mac Mojave, at least if you do the install straight from downloading MySQL Community Package rather than through brew, apparently you still need to insert the password you choose for 'root' through the System Preferences screen after stopping, restarting with safe mode (--skip-grant-tables), and flushing privileges. Then you can log in as root in phpMyAdmin. This was after trying at least 20 different sets of advice/instruction for fixing this, including the ones listed above on this page. Hope it helps someone!

我发现在 Mac Mojave 中，至少如果您直接通过下载 MySQL 社区包而不是通过 brew 进行安装，显然您仍然需要在停止后通过系统首选项屏幕插入您为“root”选择的密码，并以安全方式重新启动模式（--skip-grant-tables）和刷新权限。然后您可以在 phpMyAdmin 中以 root 身份登录。这是在尝试了至少 20 组不同的建议/说明来解决这个问题之后，包括本页上面列出的那些。希望它可以帮助某人！