Installing MySQL and gem on Snow Leopard 10.6.8

When I start an old Rails app with Ruby 1.8.7, Rails 2.1.2 on my MacBook laptop, I got error with MySQL connection, check the log:

WARNING: You're using the Ruby-based MySQL library that ships with Rails. This library is not suited for production. Please install the C-based MySQL library instead (gem install mysql).

Before that, I had installed mysql-5.5.21-osx10.6-x86.dmg

I tried several ways to fix that like gem install mysql2 or sudo env ARCHFLAGS="-arch i386" gem install mysql --with-mysql-dir=/usr/local/mysql --with-mysql-lib=/usr/local/mysql/lib   --with-mysql-include=/usr/local/mysql/include  --with-mysql-config=/usr/local/mysql/bin/mysql_config

...

But it still didn't work. So I decided to remove MySQL and install it from source as others suggested.

Remove MySQL

- First, backup databases.

- Stop MySQL (then check & kill exist MySQL processes)

- Follow instructions

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*

Download & install MySQL
Follow instructions
(Note that the mysql 5.5 use cmake to configure, so just need 5.1 for now)

cd ~/Downloads
curl -O http://mysql.mirrors.pair.com/Downloads/MySQL-5.1/mysql-5.1.70.tar.gz
tar xvzf mysql-5.1.70.tar.gz
cd mysql-5.1.70
./configure --prefix=/usr/local/mysql --with-extra-charsets=complex \
--enable-thread-safe-client --enable-local-infile --enable-shared \
--with-plugins=innobase
make
sudo make install
cd /usr/local/mysql
sudo chown -R mysql ./var

sudo ./bin/mysql_install_db --user=mysql

#error!
Installing MySQL system tables...
ERROR: 1004  Can't create file '/var/tmp/#sql1599d_1_0.frm' (errno: 9)
130725  8:00:57 [ERROR] Aborting

130725  8:00:57 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

#fix error
#clean up data, must! - if not, then the mysql table is still empty, can't login as root
sudo rm -Rf var
#create tmp dir
sudo mkdir -p var/tmp
sudo chown -R mysql var
sudo ./bin/mysql_install_db --user=mysql --tmpdir=/usr/local/mysql/var/tmp
sudo /usr/local/mysql/libexec/mysqld --skip-grant --user=mysql
(checking:
mysql -uroot
mysql > use mysql;
mysql > select * from user;
mysql > exit)

sudo /usr/local/mysql/bin/mysqladmin -u root shutdown

ps aux | grep mysql
kill all exist mysql processes

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

sudo /usr/local/mysql/bin/mysqladmin -u root password 'rootXYZ0'

sudo /usr/local/mysql/bin/mysqladmin -u root -p shutdown

Starting (and Auto-Starting) MySQL

Because the com.mysql.mysqld.plist link from hivelogic.com died, so I found another

echo "<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>com.mysql.mysqld</string>
<key>Program</key>
<string>/usr/local/mysql/bin/mysqld_safe</string>
<key>RunAtLoad</key>
<true/>
<key>UserName</key>
<string>mysql</string>
<key>WorkingDirectory</key>
<string>/usr/local/mysql</string>
</dict>
</plist>" >> ~/com.mysql.mysqld.plist

sudo mv ~/com.mysql.mysqld.plist /Library/LaunchDaemons/com.mysql.mysqld.plist

sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist

#start
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist

(#stop
sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist)

Gem install
gem list
gem uninstall mysql
env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

Note: don't use sudo env ARCHFLAGS="-arch i386" gem install mysql... That will not work


Add path/alias to ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH
alias mysqlstart="sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist"
alias mysqlstop="sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist"
alias mysqlstatus="ps aux | grep mysql | grep -v grep"

Launch app
cd ~/rails/myapp
ruby script/server