-1

Hi I'm really having trouble with this. I have MySQL running (or at least I think I do) on Mac OS Mojave 10.14.6 and I cannot connect to it for love nor money. I have tried various solutions posted online to try and gain access or try and reset the password and absolutely nothing works.

I just repeatedly get the error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

In Terminal I can shut MySQL down with:

/usr/local/bin/mysql.server stop

and I get a SUCCESS! message but when I try

mysql> FLUSH PRIVILEGES;

I get

ERROR 1045 (28000): Access denied for user..... (using password: NO)

When I try

mysqld --skip-grant-tables

I get a whole heap of repeated lines of this:

2021-04-13T22:28:20.129955Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35

finishing with this:

2021-04-13T22:28:43.206148Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 35 in a file operation.
2021-04-13T22:28:43.207017Z 1 [ERROR] [MY-012596] [InnoDB] Error number 35 means 'Resource temporarily unavailable'
2021-04-13T22:28:43.208735Z 1 [ERROR] [MY-012215] [InnoDB] Cannot open datafile './ibdata1'
2021-04-13T22:28:43.209668Z 1 [ERROR] [MY-012959] [InnoDB] Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2021-04-13T22:28:43.210520Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Cannot open a file.
2021-04-13T22:28:43.211795Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-04-13T22:28:43.213680Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-04-13T22:28:43.215284Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-13T22:28:43.215696Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.23/bin/mysqld: Shutdown complete (mysqld 8.0.23)  Homebrew.

I have tried shutting mysql down in my system preferences as instructed in another post but I do not have any mysql system preferences.

I have tried this:

sudo mysqld_safe —skip-grant-tables

but I get this message and nothing else:

2021-04-13T22:34:44.6NZ mysqld_safe Logging to '/usr/local/var/mysql/my-macbook-pro.local.err'.
2021-04-13T22:34:44.6NZ mysqld_safe A mysqld process already exists

I have spent all day following solutions for this error but MySQL will belligerently refuse to allow me any kind of access to it.

What are my options? Should I format my mac and reinstall everything from scratch? I really have hit a brick wall with this. Any help would be much appreciated.

Thanks.

Ok so here's an edit because I'm told that this post is a duplicate of this other post: MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

So I need to explain more detail.

  • I cannot create or update user as suggested on that post because I get an error that the command not found.

  • I have tried export PATH=$PATH:/usr/local/mysql/bin/ but nothing happened.

  • I do not have an anonymous user as suggested earlier on in that post.

  • I have tried various ports in case that was the problem but no luck.

  • My dbname, username, password do not exceed the values set out by https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html#grant-tables-scope-column-properties

  • Re the answer about C:\MySQL_5.5.12>mysql -urol -Dtest -h127.0.0.1 - I'm using mysql 8 (I think) and I'm not using Windows.

  • I've tried eliminating whitespace between the -p and password but no luck.

  • On answer no #20 about follow these steps to reset your password - on step 1 I get command not found. I can stop the server with a different command as indicated further up this post but then step 2 doesn't work either and I can go no further with that.

  • My Password I have tried with double quotes, single quotes and without quotes.

  • I've tried accessing with and without sudo.

  • GRANT ALL ON... command not found.

  • I've commented out bind-address = 127.0.0.1 in my.cnf file but doesn't work.

  • Require SSL isn't checked for the user privileges on phpmyadmin.

  • My password isn't using special characters.

  • On point #4 about mysql 8 and passwords don't work. I get to step 3 and it doesn't work.

  • I'm not using ubuntu / linux

That covers quite a lot of that post but if anyone who would like more clarity then let me know.

Ken White
  • 123,280
  • 14
  • 225
  • 444
cannon303
  • 403
  • 2
  • 12

1 Answers1

0

I fixed this problem. It seems there was at least two problems to resolve. Firstly it seemed there was two installations of MySQL, one I had installed through Homebrew and one that was installed with MAMP. I didn't know I had two versions until it became apparent later during the steps I took. Secondly I could not connect to my Homebrew installation of MySQL because of incorrect credentials even though technically I was trying to connect to the wrong installation all along.

Here are the steps I took (PLEASE NOTE THAT THESE STEPS DELETED ALL DATABASE DATA):

  1. I updated Mac OS to Big Sur 11.2.3. This didn't fix anything at all.

  2. I uninstalled my Homebrew installation of MySQL following these instructions https://gist.github.com/vitorbritto/0555879fe4414d18569d although I had to stop MySQL first with this command /usr/local/bin/mysql.server stop. Not all of the steps in that uninstallation guide worked but it was enough.

  3. I reinstalled Homebrew installation of MySQL and tried to connect to it. I didn't get the Access denied error any more so thought I was making progress, however I did get a different error Could not connect to MySQL: Unknown database 'mydatabase'.

  4. I installed the System Preferences MySQL control panel - this kept stopping and starting MySQL flashing red and green. It was at this point I realised MAMP was displaying MySQL version 5.7 while I had version 8 of MySQL through my system preferences.

  5. I uninstalled my Homebrew version of MySQL again.

  6. Over to MAMP and Apache wouldn't run any more so I uninstalled MAMP completely.

  7. I reinstalled MAMP

  8. Now trying to connect to MySQL gave this error failed: SQLSTATE[HY000] [2002] Connection refused

  9. After searching this error I found this post https://craftcms.stackexchange.com/questions/23113/craft-3-command-setup-failing which on point 4 said: Enable "Allow network access to MySQL" -> "only from this Mac" I didn't know where this was but after searching found it on the MySQL side tab of MAMP. I selected that tick box and then I was back to the original error at the top of this post ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).

  10. As I was warming up my car's engine ready to take my computer for a one way trip to the nearby estuary I tried one last time to use the password "root" for user "root" (something I had tried several times at the very start) and this time it connected. I was then able to proceed with my Craft CMS installation.

Perhaps this post won't be useful to many people out there but if it saves just one person the headache I've encountered over the last few days then my time putting this answer together would have been worth it.

cannon303
  • 403
  • 2
  • 12