Access denied for root user - mysql on MAC OS

Question

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

How to set password for mysql on MAC?

1) Doesn't work

mysqladmin -u root password NEWPASSWORD 

2) Doesn't work

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

3) It works:

forgot mysql user password

Answer 1

You can do the following on a Mac (El Capitan)

1. Open a terminal window, use the command below to stop mysql if it is already running.

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

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

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 the mysql prompt. Run the following command:

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

   Troubleshooting Tips:

   A) The command for versions of MySql 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 first run this command:

   use mysql;

   C) If you see an unknown error in the Password field, 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 the command.

Answer 2

Decision

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

didn't work for me but i did

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

And he could continue. I am using Ver 8.0.12.

Answer 3

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

Open a terminal window and stop MySQL if it is already running. You can also check this System Preferences> MySQL> to see if it works.

Start MySQL with this command to skip the main table

 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 the mysql prompt. Run the following command:

A) MySQL 5.6 and below

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

-- or --

B) MySQL 5.7+

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

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

Answer 4

For MySQL 5.7, I had to use:

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

Answer 5

MACOS 10.14 MOJAVE || MYSQL 8.0.15

This did not work on my Mac:

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

BUT IT REALLY WORK:

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

Installation folder may vary depending on the user, be careful!

Or just check> System Preferences> MySQL> if the server is running, stop it.

then,

Start MySQL with this command:

 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 the mysql prompt. Run the following command (* scroll right if you don’t have a complete request):

 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

(in my case) this is the installation folder on your local computer, and it may differ from mine depending on OS versions, MySQL versions, installation methods used, etc.

Answer 6

A very simple fix for MariaDB version: 10.4.6-MariaD on Mojave macOS

I went through all the answers. Some of them helped me, some of them did not. I found one easy way to fix this on macOS or OSX. Here are the steps:

Prerequisites:

Homebrew must be installed. Use the following link to install homebrew on macOS or OSX.

Install mariadb:

1. brew install mariadb
2. Start MySQL Server: mysql.server start or run brew services start mariadb to start MySQL Server when you log on to the computer.
3. Get MySQL instance of sudo mysql -u root

NOTE: mysql -u root will throw an error ERROR 1698 (28000): Access denied for user 'root'@'localhost' , so use sudo to run this command.

4. Now, to change the root password, I tried the following commands:

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

5. But the following command worked :

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

So for me it was a simple fix for version 10.4.6-MariaD installed via brew. Hope this helps you too.

Answer 7

It was very difficult for me to fix this problem on MAC Sierra, 10.12.6, MySql version 5.7.17

The following steps helped me:

Open a terminal window, use the command below to stop mysql if it is already running.

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

Start MySQL with this command:

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

Open a new terminal window / tab:

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

This will open the mysql prompt. Run the following command at the mysql prompt one by one:

 use mysql; UPDATE user SET authentication_string = PASSWORD('my_new_password'), password_expired = 'N' WHERE User = 'root'; FLUSH PRIVILEGES; EXIT 

Now first stop the MySql server, then start it using the commands below

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

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

Hope this solves your problem.

Answer 8

I found that in Mac Mojave, at least if you are installing directly from the MySQL Community Package, and not through brew, obviously you still need to insert the password that you selected for “root” through the “System Preferences” screen "after stopping by restarting with safe mode (--skip-grant-tables) and reset privileges. You can then log in as root in phpMyAdmin. This was done after you tried to fix at least 20 different tips / instructions, including those listed above on this page. Hope this helps someone!

Answer 9

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

Pay attention to sudo in the first line.