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!

Answer 1

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)

Answer 2

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

Answer 3

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.