16

I am trying to run MySQL from a Homebrew installation, but it keeps failing when trying mysql.server start.

My /usr/local/var/mysql/<name>.local.err reads as so, and keeps sending this error message every few seconds.

2018-06-20T20:38:54.6NZ mysqld_safe Logging to '/usr/local/var/mysql/<computer name>.local.err'.
2018-06-20T20:38:54.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2018-06-20T20:38:54.379228Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.11) starting as process 6271
2018-06-20T20:38:54.383128Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2018-06-20T20:38:54.449869Z 1 [ERROR] [MY-012526] [InnoDB] InnoDB: Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.20. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2018-06-20T20:38:54.449947Z 1 [ERROR] [MY-012930] [InnoDB] InnoDB: Plugin initialization aborted with error Generic error.
2018-06-20T20:38:54.919197Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2018-06-20T20:38:54.919769Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-06-20T20:38:54.919814Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-06-20T20:38:54.920978Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Homebrew.
2018-06-20T20:38:54.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/bae.local.pid ended
17

I had the exact same errors in my log. Just to build off Nipponese' answer.

Remove all versions of MySQL (I had 5.7.21, 5.7.22, and 8.0.11) :

brew uninstall --force mysql

Confirm Removal and Repair :

brew services list and then brew doctor

Rename original MySQL :

mv /usr/local/var/mysql /usr/local/var/old.mysql

Install latest MySQL (currently 8.0.11) :

brew install mysql

MySQL Secure Install :

/usr/local/bin/mysql_secure_installation

I initially received this error: Error: Access denied for user 'dbadmin'@'localhost' (using password: YES)

I'm using a user level config. So I renamed .my.cnf and ran the secure install again.

mv /Users/[your username]/.my.cnf /Users/[your username]/old.my.cnf /usr/local/bin/mysql_secure_installation

On the install prompts I input the following options Y, 0, Y, N, N, Y, Y.

Upgrade MySQL Workbench After that I still couldn't connect with MySQL Workbench (GA 6.3.10) although it did appear to be starting MySQL finally. I was getting the following error prompt.

"Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found"

To resolve this I downloaded MySQL Workbench 8.0.11 rc (the Development Release version). I used the GUI to add the users I had before adhering to the new password rules. I updated and renamed ~/.my.cnf with the more secure password. Then restored my DBs.

At this point I was able to connect with workbench using root and the new password I setup during the secure install.

Clean Up (remove renamed directory and it's contents) :

rm -r /usr/local/var/old.mysql

| improve this answer | |
  • 1
    This was clearly already self-answered by the original poster yesterday. – JakeGould Jun 22 '18 at 16:31
  • 4
    Perhaps it was already self-answered, but not with such essential detail for those of us looking for the exact steps needed. – Sturm Jun 27 '18 at 17:59
  • What is the purpose of renaming /usr/local/var/mysql rather than simply removing it at the outset? old.mysql isn't used anywhere except during the clean up step. I ask because it would be helpful to reinstall MySQL while preserving the databases. – user2763030 Jul 6 '18 at 23:55
  • The reason I did it was just in case I wanted any of the data later. All my local databases were/are clones of our production environment so I didn't back anything up and just synced it all after the upgrade process. I believe you could use the databases from previous versions although mysql 8 has updated encryption rules for passwords. I know you can add a line to your .my.cnf to use the older auth method instead of sha2. Sorry I'm not super knowledgable on that part. I hope this helps. – Hunter Jul 9 '18 at 14:24
  • If you get Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) while running /usr/local/bin/mysql_secure_installation, you may need to simply start the MySql server like I did. You can do this by running "mysql.server start". – Richie Thomas Dec 4 '18 at 1:16
12

tldr; A new version of mysql was installed via Homebrew. Revert to the previously installed version.

brew switch mysql X.X.XX` and `brew services restart mysql 

The full story is that you likely installed a newer version of mysql with brew upgrade. Review the output of brew info mysql. You may see more than one version.

In my case, I saw both of the following:

/usr/local/Cellar/mysql/5.7.17
/usr/local/Cellar/mysql/8.0.11

I checked mysql --version and the newer 8.0.11 was running.

Homebrew includes the switch command that allows you to do just that between services.

Execute this replacing the version with your own:

brew switch mysql 5.7.17

Restart mysql:

brew services restart mysql

All should be back in working order.

| improve this answer | |
  • 1
    How does this differ in end-purpose from the already self-answered and accepted answer? – JakeGould Jul 29 '18 at 19:01
  • 4
    @JakeGould Answer 167207 suggests you delete all your database content. My answer suggests using brew switch which does not. – johnsampson Jul 30 '18 at 20:45
  • Fair enough. Good advice. +1 – JakeGould Jul 30 '18 at 21:11
  • 1
    Good answer. Worked for me – Kuppuraj Oct 5 '18 at 5:30
  • 1
    Too bad I didn't find this before I uninstalled every version of mysql. FML – andrewtweber Apr 26 '19 at 3:03
2

I looks like the issue was a previous installation in /usr/local/var/mysql.

After uninstalling via homebrew, removing /usr/local/var/mysql, running brew doctor, and re-installing via homebrew, the issue went away.

| improve this answer | |
  • Will removing /usr/local/var/mysql remove all your database content? – Nicodemuz Jul 27 '18 at 5:56
  • @Nicodemuz yup. – nipponese Jul 27 '18 at 17:56
  • 9
    you should probably highlight that as a warning! – Nicodemuz Jul 29 '18 at 10:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.