Trouble running mysql on OSX 10.6 development machine: "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)"

Question

I've installed MySQL on my personal/development machine using the .dmg package, according to the instructions here: http://dev.mysql.com/doc/refman/5.5/en/macosx-installation-pkg.html including installing the startup item and the preferences pane. And yet, I can't seem to use MySQL at all.

running:

/Library/StartupItems/MySQLCOM/MySQLCOM start

or

/Library/StartupItems/MySQLCOM/MySQLCOM restart

"appears" to work -- in that, it gives me a message like "Starting MySQL database server" -- but afterward, I still can't go into mysql at the command-line, or connect to it in a Rails 2.3.8 application running in script/server. I get the error denoted in the question title.

Also, the MySQL preferences pane doesn't seem to work either. If I click the "Start MySQL Server" button, I'm asked for my password, but then nothing happens -- the pane continues to say that the server is stopped.

(I believe I had a MacPorts version of MySQL installed previously, and it's also possible that there was one built from source at some time in the past -- but I'm reasonably sure I've uninstalled these and deleted all the files having to do with it that I could find.)

I'm also trying mysqld start in terminal. here's the output:

110127 15:40:28 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86_64/data/Lucky-Charm.lower-test
110127 15:40:28 [Warning] Can't create test file /usr/local/mysql-5.5.8-osx10.6-x86_64/data/Lucky-Charm.lower-test
110127 15:40:28 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
110127 15:40:28 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110127 15:40:28  InnoDB: Initializing buffer pool, size = 128.0M
110127 15:40:28  InnoDB: Completed initialization of buffer pool
110127 15:40:28  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

Tried following the message about running mysql_upgrade but that just gives me my original error again.

---

UPDATE:

OK I've been pursuing the theory that it's a permissions problem. Seeing that the datadir was owned by root, I chown -R it to _mysql. In response to Mike, here's where that now stands:

$ ls -al /usr/local/mysql-5.5.8-osx10.6-x86_64
total 296
drwxr-xr-x  16 root    wheel     544 Dec  3 12:53 .
drwxrwxr-x  12 root    staff     408 Jan 27 14:38 ..
-rw-r--r--   1 root    wheel   17987 Dec  3 11:58 COPYING
-rw-r--r--   1 root    wheel   12388 Dec  3 11:58 INSTALL-BINARY
-rw-r--r--   1 root    wheel  113534 Dec  3 11:58 README
drwxr-xr-x  44 root    wheel    1496 Dec  3 12:53 bin
drwxr-xr-x   9 _mysql  wheel     306 Jan 27 16:46 data
drwxr-xr-x   4 root    wheel     136 Dec  3 12:53 docs
drwxr-xr-x  47 root    wheel    1598 Dec  3 12:53 include
drwxr-xr-x  12 root    wheel     408 Jan 27 14:38 lib
drwxr-xr-x   4 root    wheel     136 Dec  3 12:53 man
drwxr-xr-x  19 root    wheel     646 Jan 27 14:38 mysql-test
drwxr-xr-x   3 root    wheel     102 Dec  3 12:53 scripts
drwxr-xr-x  32 root    wheel    1088 Dec  3 12:53 share
drwxr-xr-x  28 root    wheel     952 Dec  3 12:53 sql-bench
drwxr-xr-x  16 root    wheel     544 Dec  3 12:53 support-files

I was trying to do mysqld start in the Terminal because it was the only thing giving me anything that seemed like meaningful error message output (see https://gist.github.com/799436) but I'm told by folks in #mysql that that's not intended to be run directly (and if I try sudo mysqld start i get a message bitching me out for trying to run mysql as root).

I seem to have something working now: mysqld_safe & successfully gets a MySQL server running. What still doesn't work is the "normal" method of starting up the server (the Startup Item or Preferences Pane)

... leading someone in #mysql to tell me that apparently MySQL is fine, it's the startup item that's borked.

Answer 1

Ok there were several things, mostly having to do with permissions/ownership, that were tried to make the binary-installed MySQL work nicely.

You may need to make sure that the startup item is owned by root:

sudo chown -R root:wheel /Library/StartupItems/MySQLCOM

Maybe you need a /etc/my.cnf file with this in it:

[mysqld]
socket=/tmp/mysql.sock
datadir=/usr/local/mysql/data

You might need to fill in these variables in /usr/local/mysql/support-files/mysql.server (the line will be there with blank values):

basedir=/usr/local/mysql 
datadir=/usr/local/mysql/data 

(see can't start MySql in Mac OS 10.6 Snow Leopard regarding the above)

That may be enough to do it, but if not, try making sure the mysql user (_mysql) can write to the data directory (owns it and has write permissions to everything in it).

Anyway, now the Preferences Panel and Startup Item actually seem to work for me.

---

After going over this a second time on another machine, I've made some edits and removed some unnecessary bit from what I answered yesterday.

Overall here's what I suggest you do to get the binary-installed MySQL working nice in OSX 10.6. Warning, you might end up deleting any databases you already had in the first couple steps, but as this is intended to be for your development machine, that shouldn't be any big deal. Back stuff up with mysqldump first if you must.

1. Make sure you don't have a mysql server running right now: ps aux | grep mysql will show you their processes. Stop it with mysqladmin shutdown or if that won't work because something is borked, sudo kill the process numbers.
2. Remove any prior installed versions of mysql -- check port list installed, check for a homebrew-installed one, sudo find / -name mysql looking for compiled-from-source ones and delete them, whatever it takes. You could even remove the startup item by deleting the /Library/StartupItems/MySQLCOM directory if you want.
3. Run the mysql-whatever-version.pkg install package
4. Test it by typing sudo mysqld_safe & at the terminal. If you get "command not found," add /usr/local/mysql/bin to your path and try again. If you get any scary error messages, check for a /etc/my.cnf file as described above and try again. If it still doesn't work, then maybe try recursively chowning and chmoding the /usr/local/mysql/data directory to make sure _mysql can write to it. Once you get it to appear to start up OK, enter mysql at terminal. If you get a MySQL command prompt, all is well (enter exit to get out of it) -- in fact, if you get anything other than the ol' "Can't connect to local MySQL server through socket" then you can conclude that the MySQL server works -- shut down or kill the server and move on.
5. Next we'll install the startup item. Run MySQLStartupItem.pkg
6. Test the startup item at the terminal by entering sudo /Library/StartupItems/MySQLCOM/MySQLCOM start. It will give you a message claiming that it is starting up the server, but if it's unsuccessful it won't give you any indication, so try going into mysql again to test that the server is running. If so, enter sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop to stop the server (and to test that the startup item can stop the server as well as start it :D) If it didn't work, try making the settings described above in /usr/local/mysql/support-files/mysql.server. If that still doesn't do it, try the bit up at the top about sudo chown -R root:wheel /Library/StartupItems/MySQLCOM.
7. Once that works, run/install the MySQL.prefPane. This should give you a MySQL item in your System Preferences near the bottom, and if you go in there, you should see a button that you can click which will stop/start the MySQL server. Try it, and if it doesn't work by now, I'm not sure what else I can tell you.