MySQL 5.5 on Lion not working

Question

I've installed MySQL 5.5 via the disk image on Mac OS X 10.7. I've also added /usr/local/mysql/bin to my path in .bash_profile.

which mysql returns /usr/local/mysql/bin/mysql

However, I can't seem to get the server running, no matter whatever I try.

mysql -u root returns:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

sudo /usr/local/mysql/bin/mysqld_safe starts and then immediately stops the daemon:

120130 23:18:57 mysqld_safe Logging to '/usr/local/mysql/data/Bryans-Macbook-Pro.local.err'.
120130 23:18:57 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
120130 23:18:59 mysqld_safe mysqld from pid file /usr/local/mysql/data/Bryans-Macbook-Pro.local.pid ended

I feel that I have tried every possible solution that can be found, and I am out of ideas now. I've even tried installing an older version of MySQL (5.1) and got the same results and efforts invain.

FURTHER INFORMATION

Running mysqld results in:

$ mysqld
120209  0:02:23 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.5.20-osx10.6-x86_64/data/ is case insensitive
120209  0:02:23 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
120209  0:02:23 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120209  0:02:23 InnoDB: The InnoDB memory heap is disabled
120209  0:02:23 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120209  0:02:23 InnoDB: Compressed tables use zlib 1.2.3
120209  0:02:23 InnoDB: Initializing buffer pool, size = 128.0M
120209  0:02:23 InnoDB: Completed initialization of buffer pool
120209  0:02:23 InnoDB: highest supported file format is Barracuda.
120209  0:02:23 InnoDB: 1.1.8 started; log sequence number 1595675
120209  0:02:23 [ERROR] mysqld: unknown option '--skip-locking'
120209  0:02:23 [ERROR] Aborting

120209  0:02:23  InnoDB: Starting shutdown...
120209  0:02:24  InnoDB: Shutdown completed; log sequence number 1595675
120209  0:02:24 [Note] mysqld: Shutdown complete

UPDATE

Well, I removed mysql completely from my system, re-installed an older version (5.1), and it is actually starting up now. However, I still can't run rails server. I get the following error:

/Users/bricker/.rvm/gems/ruby-1.8.7-p352/gems/mysql2-0.2.7/lib/mysql2/mysql2.bundle: dlopen(/Users/bricker/.rvm/gems/ruby-1.8.7-p352/gems/mysql2-0.2.7/lib/mysql2/mysql2.bundle, 9): Library not loaded: /opt/local/lib/mysql5/mysql/libmysqlclient_r.16.dylib (LoadError)

So, I've run this command:

sudo install_name_tool -change libmysqlclient_r.16.dylib /opt/local/lib/mysql5/mysql/libmysqlclient_r.16.dylib ~/.rvm/gems/ruby-1.8.7-p352/gems/mysql2-0.2.7/lib/mysql2/mysql2.bundle

But still receive the error when attempting to start the 'rails server'.

UPDATE 2

Okay, final update: After everything, reinstalling mysql numerous times, seeing all these errors - it turns out that perhaps the original problem was a conflict between gems mysql2-0.2.7 and mysql2-0.3.11. To ultimately fix the problem:

1. Removed all mysql-related files from my system (see bash script below)

2. Installed MySQL 5.1.61 (64-bit) from the Disk Image provided on the MySQL website

3. Ran the following lines:

sudo install_name_tool -change libmysqlclient_r.16.dylib /opt/local/lib/mysql5/mysql/libmysqlclient_r.16.dylib ~/.rvm/gems/ruby-1.8.7-p352/gems/mysql2-0.2.7/lib/mysql2/mysql2.bundle

sudo install_name_tool -change libmysqlclient.16.dylib /usr/local/mysql/lib/libmysqlclient.16.dylib ~/.rvm/gems/ruby-1.8.7-p352/gems/mysql2-0.2.7/lib/mysql2/mysql2.bundle

1. ran gem uninstall mysql2, and selected the version 0.3.11 - the only version left on this RVM section is mysql2-0.2.7.

Now everything seems to be working! Thanks again for all the help.

Bash script to remove mysql-related files from Mac OS X (10.6 & 10.7).

#!/bin/bash
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*
echo "Done."

Copy into remove-mysql.sh, make it executable (chmod +x remove-mysql.sh), and run it.

Answer 1

1. Run mysql_upgrade
2. Remove skip-locking option from my.cnf: MySQL - what does skip-locking in my.cnf do?

If you cannot find skip-locking in your my.cnf, that means that MySQL loads it from path different to your expectations. Double-check that.

P.S. It seems that you upgraded your MySQL from some old version, aren't you?

Answer 2

In one of the lines in the error log, this caught my attention

120209  0:02:23 [ERROR] mysqld: unknown option '--skip-locking'

I think --skip-locking is a pretty old option.

You might want to replace that in my.cnf with skip-external-locking instead

Since you removed --skip-locking go find datadir in my.cnf and make sure you are pointing that variables where the mysql data is located.

Answer 3

Sounds (to me) like you have a permissions issue.

Check the perms on the following directories:

/usr/local/mysql/bin
/usr/local/mysql/data
/usr/local/mysql/include
/usr/local/mysql/lib
/usr/local/mysql/scripts
/usr/local/mysql/share
...
/usr/local/mysql/*

They should all be read/write/excute for the mysql user and mysql group

Read more on install layout: http://dev.mysql.com/doc/refman/5.5/en/macosx-installation-pkg.html#mysql-installation-layout-macosx

mysql_safe script: I think it might do a user switch to the "user" mysql. Read more on that: http://dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html

Answer 4

Check to ensure your data directory is readable/writable by mysql.

Try to run mysqld_safe --skip-locking &

That should start the server in the background. Then login with "mysql" .. it shouldn't ask for any credentials.

Check to see if you have any users defined within the user table on the mysql database.

You may have to manually add a user (as the grants system is bypassed above) using :

use mysql
insert into users <standard insert record stuff here>

When done, come out of the mysql client and do a killall -s1 mysqld .. you may need to do a killall -s 15 mysqld to get the backgrounded job to finish cleanly. Please dont do a -9 on the poor old process.

Jobs/kill %% etc can be used as well.