Mysql – Can’t open the thesql.plugin table error when installing MySQL on Mac OS Sierra

installationmac os xMySQLmysql-5.7upgrade

I am attempting to install MySQL on macOS Sierra 10.12.5. I had a previous (non-functioning) install of MySQL so I followed the steps here to install a clean copy with Homebrew.

I had to create a var/log/mysql/error.log as it wasn't created automatically, however now it is logging I get the following error when I do a mysql.server start:

2017-07-24T13:36:28.554840Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-07-24T13:36:28.563492Z 0 [ERROR] unknown variable 'key_buffer=16M'
2017-07-24T13:36:28.563534Z 0 [ERROR] Aborting

This is how I initialised MySQL:

mysqld -initialize --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql

When I start MySQL with homebrew I get no error:

CRMPiccos-MacBook:~ crmpicco$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

Version info:

CRMPiccos-MacBook:log crmpicco$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using  EditLine wrapper

Best Answer

In my case mysql failed after the update because the user running mysqld (The user mysql) being unable to open the mysql data directory with write permission. The files were instead owned by myself. I fixed this with:

sudo chown -R mysql /usr/local/mysql/*

The orignal cause (for me) was not the Sierra upgrade itelf, but the overall process of updating applications in response. Specifically other instructions online for updating PHP with brew after the Sierra update had included a chown:

sudo chown -R $(whoami):admin /usr/local # DO NOT DO THIS

This had unfortunately altered the file permissions within /usr/local/mysql within /usr/local, causing the error.

Related Solutions

MySQL 5 on Mac 10.5: “problem connecting to the database”

I have found the problem:
Somehow, MySQL decided to create mysql.sock in /private/tmp rather than in /var/mysql.

So, my solution was to create a symlink:

cd /var
sudo mkdir mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

Now everything works fine.

Mysql – Can’t remote access MySQL server running on Mac OS X

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf files all of which specified bind-address=0.0.0.0. I even removed and reinstalled MySQL following these instructions and then reinstalled using brew install mysql. Still no remote connections allowed.

It wasn't until I ran ps -ax | grep mysql and noticed that the bind address was being passed in the launch command (thus overriding any my.cnf files) that I dug some more and found out that Homebrew binds MySQL to 127.0.0.1 by default.

Editing ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist to change --bind-address=127.0.0.1 to --bind-address=0.0.0.0 solved my problem (the latter should be changed to a specific IP address if this isn't just a development machine).

I feel this is a vital piece of information that was lacking from most of the resources I consulted so hopefully posting this here will help someone else!

EDIT: As LeandroCR indicated in the comments, running brew services restart mysql will overwrite the plist file in LaunchAgents with the default one, leading to MySQL mysteriously refusing connections again. So better advice than what I originally wrote is the following:

Edit /usr/local/Cellar/mysql/<yourversion>/homebrew.mxcl.mysql.plist and replace --bind-address=127.0.0.1 with bind-address=* or --bind-address=0.0.0.0 (see MySQL documentation on bind-address)
Restart mysql using brew services restart mysql

Then MySQL should continue to accept non-local connections from then on - until you reinstall it, presumably.

Edit (Sep 2019) Timothy Zorn points out that this problem no longer occurs for MySQL 8.x installed and run via Homebrew, so my answer above, written in 2016, may only be relevant to 5.x.

Best Answer

Related Solutions

MySQL 5 on Mac 10.5: “problem connecting to the database”

Mysql – Can’t remote access MySQL server running on Mac OS X

Related Question