Mysql: Access denied for user 'root'@'localhost' right after installation on macOS

Question

I just installed MySQL v8.0.11 on my macOS High Sierra v10.13.4 from the dmg package downloaded from the MySQL website. The installer did not ask for any privileges or access settings during installation. After installation finished, I tried running:
$ mysql -u root I was returned the error ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO). I then tried running the same command with sudo but I was returned the same error.

Going through answers to similar questions, I tried running:
sudo /etc/init.d/mysql stop
It returns the error sudo: /etc/init.d/mysql: command not found

If then I go ahead and try the next step which is
sudo mysqld_safe --skip-grant-tables
I am returned the output
[2] 27806 [2] + 27806 suspended (tty output) sudo mysqld_safe --skip-grant-tables

Also, running
mysqld --skip-grant-tables
returns me the following errors:
mysqld: Can't change dir to '/usr/local/mysql-8.0.11-macos10.13-x86_64/data/' (OS errno 13 - Permission denied) 2018-04-20T14:29:23.579709Z 0 [System] [MY-010116] [Server] /usr/local/mysql-8.0.11-macos10.13-x86_64/bin/mysqld (mysqld 8.0.11) starting as process 29470 2018-04-20T14:29:23.593533Z 0 [Warning] [MY-010091] [Server] Can't create test file /usr/local/mysql-8.0.11-macos10.13-x86_64/data/Faheems-MacBook-Air.lower-test 2018-04-20T14:29:23.593582Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.11-macos10.13-x86_64/data/ is case insensitive 2018-04-20T14:29:23.593863Z 0 [ERROR] [MY-010172] [Server] failed to set datadir to /usr/local/mysql-8.0.11-macos10.13-x86_64/data/ 2018-04-20T14:29:23.593889Z 0 [ERROR] [MY-010119] [Server] Aborting 2018-04-20T14:29:23.594332Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.11-macos10.13-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.11) MySQL Community Server - GPL.

It still doesn't solve the original error or lets me run mysql. I have tried almost every answer out there on related questions but nothing works.

Thanks to anyone who can help!

Es

You should check out Homebrew for managing packages like MySQL. It’s a great package manager and you wouldn’t be running into errors like this. – Sox

@CoryKleiser do you suggest I uninstall this and then reinstall with Homebrew? – Es

Absolutely. Homebrew is Mac’s missing package manager. It will perform all your updates for you and keep everything organized. – Sox

I had issues like this on my old MacBook when I wasn’t using Homebrew. When I got a new one I started using Homebrew and I never had any issues. I’m not sure if it will fix your problem or not but it is a best practice to be using a great package manager. – Sox

@CoryKleiser I do use Homebrew for literally everything and have been using it for a while. Just thought downloading MySQL from its official website shouldn't be a problem. I am trying reinstalling it with Homebrew right now but I read in some other answers that this issue isn't of Homebrew per say and might not fix it. I'm hoping it does. – Es

I use homebrew for MySQL and it works great. You are right though. It may not fix the problem. I wish you luck. – Sox

@CoryKleiser I reinstalled using Homebrew and now am running into a new issue. I've raised the question about it here: https://mcmap.net/q/839404/-mysql-error-authentication-plugin-39-caching_sha2_password-39-cannot-be-loaded/5668901 – Es

@CoryKleiser I'm running into this problem using Homebrew... – Meteoroid

I tried using Homebrew first and couldn't get it to work, so uninstalled it, and now installed using Oracle's dmg installer, and am having the same issues as the OP. Tempted to wipe my entire disk and reinstall from the OS up! – Hellbox

@SurfingSanta You could try uninstalling MySQL and installing MariaDB. MySQL keeps giving me some random issues once in a while. – Es

Answer 1

After trying a few fixes, I started the MySQL server with
$ sudo mysql.server start
then went ahead with $ mysql_secure_installation to set the password for the root user.

This worked for me.

Note: Homebrew asks you to first do mysql_secure_installation before starting the MySQL server for the first time, but that resulted in the above-mentioned error.

Es

what do you mean by "After trying a few fixes"? Without sharing what fixes you tried this sentence does not help us :/ – Tevis

Answer 2

I spent quite some time trying to install MySQL and successfully logging in to it from teminal. I first used the official dmg file (mysql-8.0.29-macos12-arm64.dmg), and then with brew (brew install mysql), but got 'access denied' no matter what I tried.

The steps that solved it for me:

First uninstall from brew with the following commands in the terminal

1. launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
2. brew remove mysql
3. brew cleanup

When it is removed it can be installed again using the official dmg file. After following the installer, I set a password from System Preferences with 'Initialize Database' and then click 'Start MySQL Server'. When the marks beside the instances are green, we are ready.

I then opened my terminal and wrote mysql -u root -p but this just threw 'Command Not Found'. I then navigated to the directory inside terminal, the directory is listed inside 'MySQL' in 'System Preferences' which for me is '/usr/local/mysql-8.0.29-macos12-arm64'. Here I tried to run the command again, but it only worked when i used ./mysql -u root -p, the last thing to do then, was to add this path to my terminal profile, which is done with: nano ~/.zshrc or nano ~/.bash_profile depending on which one you use, and adding: export PATH=${PATH}:/usr/local/mysql-8.0.29-macos12-arm64/bin to the bottom of the file.

After these steps, I can now use the terminal and enter MySQL by simply writing: mysql -u root -p and then entering the password I set inside System Preferences.

(My system: MacBook Air M1, MacOS 12.4)

Appleton

Answer 3

Its true that

Homebrew asks you to first do mysql_secure_installation before starting the MySQL server for the first time, but that resulted in the above-mentioned error.

Solution:

• Remove mysql complete from your computer

• Download and Install mysql without brew. Specify your desire password here or based on the version the installer might mention you a password

• set the path for for mysql

  export PATH=$PATH:/usr/local/mysql/bin

• Check whether its installed properly mysql --version

• mysql -uroot p to login

• change the password if required mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root')

Tevis