CODEWITHSUNDEEP
mysqlmacos
Wordica
Wordica

Reputation: 2597

Access denied for user root - mysql on MAC OS

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

Upvotes: 26

Views: 116671

Answers (12)

Suren Konathala
Suren Konathala

Reputation: 3597

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

  1. Start MySQL with this command:

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

  2. 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';

  3. Stop MySql server

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

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

Upvotes: 64

Chris Mcnelis
Chris Mcnelis

Reputation: 1

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.

Upvotes: 0

Hoang Subin
Hoang Subin

Reputation: 7400

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

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

  2. using mysqld_safe will not help. Try this syntax mysqld --skip-grant-tables & enter image description here

  3. type mysql

  4. type FLUSH PRIVILEGES;

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

Upvotes: 0

Dmitriy Chizhov
Dmitriy Chizhov

Reputation: 21

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

Upvotes: 2

E. Brown
E. Brown

Reputation: 406

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.

Upvotes: 28

S.Mishra
S.Mishra

Reputation: 3644

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:

  1. brew install mariadb
  2. Start MySQL Server: mysql.server start or run brew services start mariadb to start MySQL Server at login to the computer.
  3. 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.

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

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

Upvotes: 6

youurayy
youurayy

Reputation: 1665

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.

Upvotes: 3

Jaime Montero
Jaime Montero

Reputation: 61

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.

Upvotes: 6

code-sushi
code-sushi

Reputation: 719

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!

Upvotes: 0

Ruchin Somal
Ruchin Somal

Reputation: 1103

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.

Upvotes: 8

Reena Upadhyay
Reena Upadhyay

Reputation: 2017

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.

Upvotes: 0

Starchand
Starchand

Reputation: 714

For MySQL 5.7 I had to use:

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

Upvotes: 3

Related Questions