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

Answer 1

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.

Answer 2

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.

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

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

Answer 3

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!