Access denied for user root - mysql on MAC OS

Question

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

How can i 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) This works:

mysql root password forgotten

Has a password you're unaware of already been set, or do you just need to set an initial password? — John Parker, Aug 20 '13 at 16:01
i do not need this right now. mysql stop working at all right now and any uninstallation doesnt repair it. — Wordica, Aug 20 '13 at 16:32
Soo How can i set password for root. I only see access denied for user root'@'localhost .. i try with every ubuntu method and nothing - i cant do mysql -u root — Wordica, Aug 20 '13 at 16:48

score 61 · Accepted Answer · edited Jul 06 '22 at 06:19

61

You can do the following on Mac (El Capitan)

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

Start MySQL with this command:

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 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

D) If you see - ERROR 1064 (42000): You have an error in your SQL syntax; It is because password function was removed in version 8.0.11. Use bare string:

UPDATE USER SET AUTHENTICATION_STRING='NewPassword' WHERE user='root';
Stop MySql server

sudo /usr/local/mysql/support-files/mysql.server stop
Restart MySQL, either through System Preferences > MySql or using a command.

edited Jul 06 '22 at 06:19

jerrymouse

16,964
16
76
97

answered Mar 27 '16 at 20:06

Suren Konathala

3,497
5
43
73

2

MySQL 5.7, the password field was renamed to 'authentication_string'. Command below should be used instead: $mysql> UPDATE user SET authentication_string=PASSWORD("my_password") WHERE User='root'; – Starchand Oct 22 '17 at 15:38
Seems like this no longer works, I am getting `ERROR 1046 (3D000): No database selected` – Sudara Oct 27 '17 at 21:54
2

work perfectly. I wonder why it's so complicated to install and start a database in the beginning. does it design for kick out some new users? – MobileDev Feb 10 '18 at 02:39
9

I'm getting this error: `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('mypassword') WHERE user='root'' at line 1` Any idea what could be the problem? – rraallvv Jun 25 '18 at 05:11
I've tried every single variant of this you can imagine and literally nothing will let me get in to reset the mysql root password, in spite of the fact that it's localhost only and I carefully noted the password I chose (I have also uninstalled & reinstalled at least six times with different passwords and even tried a Brew install with NO password -- I'm on Mac Mojave and trying to get MySQL Community Server to work here.). Something is messing up the password I set on install so I can't get in as root. I'm tearing out hair (figuratively only! but still!) at this point. Can ANYONE help? – code-sushi Jun 07 '19 at 20:26
@code-sushi I have the exact problem. I have even re-installed mysql. Still the root password seems to be garbled. Anyone have solved this issue? – Nambi Feb 13 '20 at 21:43
2

@rraallvv It is not a syntax problem. There is an issue with the function password. Function [PASSWORD](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password) was removed from version 8.0.11. – Roman Kazanovskyi Dec 11 '20 at 14:44
1

@rraallvv i got that error too. like Roman said, it's because of the PASSWORD function. just use a string like so: `UPDATE user SET authentication_string='my_password' WHERE user='root';` – Kat Mar 31 '21 at 15:04
Thanks, that really helped! However, I've had the following problem of password being "expired", this https://stackoverflow.com/a/38448822/13864934 post helped to solve it. – Giasod May 17 '22 at 08:16

score 26 · Answer 2 · edited Oct 02 '19 at 11:56

26

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.

edited Oct 02 '19 at 11:56

Rory O'Kane

29,210
11
96
131

answered Aug 17 '18 at 14:53

E. Brown

361
3
2

1

This is the only solution that did not give me errors -- however, upon stopping and restarting nothing had changed; I'm still getting this whole "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)" business. – code-sushi Jun 07 '19 at 20:43
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("my_password") WHERE User='root'' at line 1 – Draif Kroneg Jun 25 '20 at 09:23
1

FYI: Function [PASSWORD](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password) was removed from version 8.0.11. – Roman Kazanovskyi Dec 11 '20 at 14:42
@DraifKroneg i got that error too. like Roman said, it's because of the PASSWORD function. just use a string like so: `UPDATE user SET authentication_string='my_password' WHERE user='root';` – Kat Mar 31 '21 at 15:06
1

Perfecto! `FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';` – vintagexav Dec 27 '22 at 08:46
but actually with MacOS It worked only by clicking on "Initialise Database" in Mysql (accessible from System settings of Mac settings) and then set as password my new password – vintagexav Dec 27 '22 at 09:35

score 7 · Answer 3 · edited Oct 12 '18 at 01:51

7

You can do the following on iMac or 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.

Start MySQL with this command for skipping 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 "mysql" prompt. Execute the below 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 a command.

edited Oct 12 '18 at 01:51

Joshua Pinter

45,245
23
243
245

answered Jan 20 '18 at 16:58

Ruchin Somal

1,073
12
14

1

From MySQL 5.7 the password field in mysql.user table is called "authentication_string", so the query becomes `UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE user='root';` – roibeart Jan 21 '18 at 20:06
Worked for me. Updated the answer to reflect @roibeart's correct comment for MySQL 5.7+. – Joshua Pinter Oct 12 '18 at 01:53
FYI: Function [PASSWORD](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password) was removed from version 8.0.11. – Roman Kazanovskyi Dec 11 '20 at 14:42

Jaime Montero · Answer 4 · 2019-06-26T19:22:22.163

MACOS 10.14 MOJAVE || MYSQL 8.0.15

This didn't work on my mac:

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

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.

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 "mysql" prompt. Execute the following command (*scroll right if you don't the full query):

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) 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.

S.Mishra · Answer 5 · 2019-08-13T15:38:09.050

Very Simple Fix for MariaDB version: 10.4.6-MariaD on Mojave macOS

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:

Prerequisites:

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

Install mariadb:

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

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

Now to change the password of the root user 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
But the following command worked:
- ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypassword';
  - The response was: Query OK, 0 rows affected (0.009 sec)

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

this worked perfectly for mojavae and installation done using homebrew — DeadDjangoDjoker, Sep 22 '19 at 17:42

score 3 · Answer 6 · answered Oct 22 '17 at 15:46

3

For MySQL 5.7 I had to use:

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

answered Oct 22 '17 at 15:46

Starchand

684
1
9
23

4

This executes with MySQL 5.7 on Sierra 10.12.6 but still doesn't allow you to login after restarting the db using "mysql -u root -p admin". What astonishes me is how much time is being wasted trying to do something that should be simple. – rakehell Dec 11 '17 at 20:43

score 3 · Answer 7 · answered Jul 14 '19 at 18:49

3

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

Notice the sudo on the first line.

answered Jul 14 '19 at 18:49

youurayy

1,635
1
18
11

score 2 · Answer 8 · edited Dec 01 '20 at 08:09

2

I used to try all solutions but nothing worked. Finally and suddenly I found the solution! I use 10.5.8-MariaDB Homebrew.

    USE mysql;
    SELECT user, authentication_string, plugin, host FROM mysql.user;

For some reasons authentication_string is invalid and it's what we need to fix

Then just run the command below:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

edited Dec 01 '20 at 08:09

Anatoly

20,799
3
28
42

answered Nov 29 '20 at 22:09

Dmitriy Chizhov

21
2

score 0 · Answer 9 · answered Sep 03 '18 at 08:02

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

Following steps worked for me:

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

Start MySQL with this command:

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 will open "mysql" prompt. Execute following command in 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 Stop MySql server first then start it using below commands

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

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

Hope this solves your issue.

score 0 · Answer 10 · answered Jun 07 '19 at 21:07

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!

Yeah, this solution is based on Mojave. It worked for me. – S.Mishra Aug 13 '19 at 15:37 — S.Mishra, Aug 13 '19 at 15:37

Hoang Subin · Answer 11 · 2021-11-12T04:12:52.713

I am using Mac and my solution is a bit different like above

cd to folder that I installed mysql. In my case it is cd /opt/homebrew/Cellar/mysql/8.0.27/bin because I used brew to install mysql
using mysqld_safe will not help. Try this syntax mysqld --skip-grant-tables &
type mysql
type FLUSH PRIVILEGES;
type ALTER USER 'root'@'localhost' IDENTIFIED BY 'password you want';

If you see ERROR 1819 (HY000): Your password does not satisfy the current policy requirements error. Just type the password with capital letter + number + special character

score 0 · Answer 12 · edited Jan 22 '22 at 18:27

Mac OSX 12.1 (Monterey)

Installed Oracle MySql: mysql Ver 8.0.28 for macos11 on x86_64 (MySQL Community Server - GPL)

Using the package install after installation it appears the root password you set does not get saved in the mysql database within the server. I tried a number of the update user commands from above and ultimately what fixed the issue was System Preferences > MySql > Initialize Database > [set password again].

Initialize DB Screen

Stop the DB and restart was able to create a session with the root user. Now that it is working I kind of want to trash it just for the headache and use mariadb instead.

Access denied for user root - mysql on MAC OS

12 Answers12

Linked