Installing MySQL 5.1.29-RC & Sphinx SE 0.9.8.1 on OS X Leopard

Sphinx 0.9.8.1 was recently released, and I still didn't have MySQL re-installed on OS X. Therefore, it was a good time to install both Sphinx and MySQL. I prefer using the SphinxSE MySQL storage engine in conjunction with Sphinx since I'm comfortable with SQL and like being able to join directly with the SphinxSE tables in queries. SphinxSE also provides other benefits when developing an app with an ORM. For this task, I followed these useful posts:

You can setup your path according to Dan Benjamin's post under "Setting the Path" by adding the following to your .bash_profile, .bash_login, or .profile:

export PATH="/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:$PATH"

Following another Dan Benjamin post under "Setting Up", make a src directory for storing the source code:

sudo mkdir /usr/local/src
sudo chgrp admin /usr/local/src
sudo chmod -R 775 /usr/local/src

Download and extract the MySQL 5.1.29-RC source and Sphinx 0.9.8.1 source:

cd /usr/local/src
mv ~/Downloads/mysql-5.1.29-rc.tar .
chgrp admin mysql-5.1.29-rc.tar
tar -xf mysql-5.1.29-rc.tar
curl -C - -O http://sphinxsearch.com/downloads/sphinx-0.9.8.1.tar.gz
tar -xvf sphinx-0.9.8.1.tar.gz

Make a sphinx directory in the MySQL storage directory:

cd mysql-5.1.29-rc
mkdir storage/sphinx

Copy the Sphinx sphinxse files to mysql storage/sphinx directory:

cp -R ../sphinx-0.9.8.1/mysqlse/ storage/sphinx

Run autorun.sh:

sudo sh BUILD/autorun.sh

Run configure with the Sphinx plugin and InnoDB options:

sudo ./configure --prefix=/usr/local/mysql \
--with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=complex \
--enable-thread-safe-client --enable-local-infile \
--with-plugins=sphinx --with-innodb

Run make and make install:

sudo make
sudo make install

Copy one of the sample MySQL my.cnf files found in /usr/local/mysql/share/mysql and edit it on your desktop as needed, paying close attention to the socket file and tmpdir (I've seen many installation issues caused by an unintended value in my.cnf):

[client]
socket                = /tmp/mysql.sock

[mysqld]
socket                = /tmp/mysql.sock
tmpdir                = /tmp

Copy your my.cnf to /etc/my.cnf:

sudo cp ~/Desktop/my.cnf /etc/my.cnf

Change the mysql directory ownership:

cd /usr/local/mysql
sudo chown -R mysql .
sudo chgrp -R mysql .

Take a MySQL install tip from Mike, and edit bin/mysql_install_db to change hostname='localhost' so that you won't have strange "Access denied" for localhost issues:

# Try to determine the hostname
#hostname=`/bin/hostname`
hostname='localhost'

Install the system tables, and change the file & directory ownership again:

sudo ./bin/mysql_install_db --user=mysql
sudo chown -R root .
sudo chown -R mysql var

Now to start MySQL on boot, create a file called com.mysql.mysqld.plist on your Desktop as provided by Dan under "Auto-Starting MySQL": [code="xml"] KeepAlive Label com.mysql.mysqld Program /usr/local/mysql/bin/mysqld_safe RunAtLoad UserName mysql WorkingDirectory /usr/local/mysql [/code] Move the plist file to the LaunchDaemons directory, and change its owner:

sudo mv ~/Desktop/com.mysql.mysqld.plist /Library/LaunchDaemons
sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist

Start MySQL:

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

Check that MySQL is running:

ps -ef | grep mysql
   74 19729     1   0   0:00.02 ??         0:00.03 /bin/sh /usr/local/mysql/bin/mysqld_safe
   74 19868 19729   0   0:00.16 ??         0:00.39 /usr/local/mysql/libexec/mysqld 
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/var 
--log-error=/usr/local/mysql/var/Al.local.err --pid-file=/usr/local/mysql/var/Al.local.pid 
--socket=/tmp/mysql.sock --port=3306
  503 19870   457   0   0:00.00 ttys003    0:00.00 grep mysql

Note: It may take some time for your InnoDB data files to be created if you are not using a file per table (innodb_file_per_table) and if you specified large InnoDB data file sizes. You can always check the var directory for the InnoDB data file sizes as the files are being created:

sudo ls -al var

Finally, login to MySQL, and check for the Sphinx storage engine:

Al:mysql-5.1.29-rc roberto$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.29-rc Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                    | Transactions | XA  | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| CSV        | YES     | CSV storage engine                                         | NO           | NO  | NO         | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO  | NO         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO  | NO         | 
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES | YES        | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO  | NO         | 
| SPHINX     | YES     | Sphinx storage engine 0.9.8                                | NO           | NO  | NO         | 
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
6 rows in set (0.00 sec)

mysql>

Wrap up by installing Sphinx:

cd /usr/local/src/sphinx-0.9.8.1
sudo ./configure --with-mysql
sudo make
sudo make install

You can check to see that searchd and indexer are installed by executing the commands and seeing the failure response and help output:

Al:sphinx-0.9.8.1 roberto$ searchd
Sphinx 0.9.8.1-release (r1533)
Copyright (c) 2001-2008, Andrew Aksyonoff

FATAL: no readable config file (looked in /usr/local/etc/sphinx.conf, ./sphinx.conf).
Al:sphinx-0.9.8.1 roberto$ indexer
Sphinx 0.9.8.1-release (r1533)
Copyright (c) 2001-2008, Andrew Aksyonoff

Usage: indexer [OPTIONS] [indexname1 [indexname2 [...]]]

Options are:
--config                 read configuration from specified file
                        (default is sphinx.conf)
--all                        reindex all configured indexes
--quiet                        be quiet, only print errors
--noprogress                do not display progress
                        (automatically on if output is not to a tty)
--rotate                send SIGHUP to searchd when indexing is over
                        to rotate updated indexes automatically
--buildstops  
                        build top N stopwords and write them to given file
--buildfreqs                store words frequencies to output.txt
                        (used with --buildstops only)
--merge  
                        merge 'src-index' into 'dst-index'
                        'dst-index' will receive merge result
                        'src-index' will not be modified
--merge-dst-range   
                        filter 'dst-index' on merge, keep only those documents
                        where 'attr' is between 'min' and 'max' (inclusive)

Examples:
indexer --quiet myidx1        reindex 'myidx1' defined in 'sphinx.conf'
indexer --all                reindex all indexes defined in 'sphinx.conf'
Al:sphinx-0.9.8.1 roberto$

All finished! :-)