16 August 2010

Compiling Debian package for MySQL 5.1 with SphinxSE

I'd share experiense of compiling MySQL with SphinxSE Debian-way, i.e. making a DEB package from MySQL and SphinxSE sources. I pick an easy way using checkinstall. Checkinstall implies quick-and-dirty solution. If you want package complied Debian policy, you are not on the right spot(go here; this gives an overview). Ubuntu standard packages don't always provide latest releases. At least it regards to both sphinxsearch and mysql-server. If you already have MySQL installed, remove it:
$ sudo apt-get remove --purge mysql-server-5.1
UPDATE 19.08.2010 Otherwise, you should create a group and a user for MySQL as described here After that which mysql command should return an empty reply. Download Sphinx source tarball and untar it. We'll need just it's mysqlse folder. For instance, I'm checking out 1.10 beta release now. So I refer to sphinx-1.10-beta folder below. Download MySQL source for generic Linux platform and untar it:
$ tar -xf mysql-5.1.49.tar.gz
Currently 5.1.49 is the latest version. So I refer to mysql-5.1.49 folder below. Copy Sphinx mysqlse folder to mysql-5.1.49/storage/sphinx/:
$ cp -R sphinx-1.10-beta/mysqlse/ mysql-5.1.49/storage/sphinx/
Configure and precompile MySQL:
$ cd mysql-5.1.49
$ sh BUILD/autorun.sh
$ ./configure \
--prefix=/usr/local/mysql \
--with-plugins=sphinx \
--enable-assembler \
--with-mysqld-ldflags=-all-static \
--with-server-suffix=' SphinxSE 1.10 beta' \
--with-charset=utf8 
$ make && sudo make install 
Instead of sudo make install:
$ sudo checkinstall -D \
--pkgname=mysql-server-5.1.49_sphinxse-1.10b \
--pkgversion=1.0
You should receive output like
...
======================== Installation successful ==========================

Copying documentation directory...
./
./README
./ChangeLog
./Docs/
./Docs/Makefile
./Docs/Makefile.am
./Docs/INSTALL-BINARY
./Docs/mysql.info
./Docs/Makefile.in
./INSTALL-SOURCE
./COPYING
./INSTALL-WIN-SOURCE
grep: /var/tmp/tmp.FFdtipwofv/newfile: No such file or directory

Copying files to the temporary directory...OK

Stripping ELF binaries and libraries...OK

Compressing man pages...OK

Building file list...OK

Building Debian package...OK

Installing Debian package...OK

Erasing temporary files...OK

Writing backup package...OK

Deleting temp dir...OK


**********************************************************************

 Done. The new package has been installed and saved to

 /home/ruslan/now/mysql-5.1.49/mysql-server-5.1.49-sphinxse-1.10b_1.0-1_i386.deb

 You can remove it from your system anytime using: 

      dpkg -r mysql-server-5.1.49-sphinxse-1.10b

**********************************************************************
Save mysql-server-5.1.49-sphinxse-1.10b somewhere, e.g. /usr/src. Now you can install it:
$ sudo dpkg -i mysql-server-5.1.49-sphinxse-1.10b
and see it in the package list:
$ dpkg --list mysql-server-5.1.49-sphinxse-1.10b 
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                        Version                     Description
+++-===========================-===========================-======================================================================
ii  mysql-server-5.1.49-sphinxs 1.0-1                       mysql-5.1.49 + SphinxSE 1.10 beta
Launch mysql server at startup:
$ sudo cp support-files/mysql.server /etc/init.d/mysql
$ sudo chmod +x /etc/init.d/mysql
$ sudo update-rc.d mysql defaults
Copy configuration:
$ sudo cp support-files/my-medium.cnf /etc/my.cnf
If you had MySQL installed before, probably /etc/mysql/my.cnf file still exists with old configuration. In such case overwrite it:
$ sudo cp -f support-files/my-medium.cnf /etc/mysql/my.cnf
or make appropriate changes in it. Make MySQL binaries visible from everywhere. E.g. append the following line to ~/.bashrc:
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/sbin
Now start MySQL daemon:
$ sudo /etc/init.d/mysql start
or, directly,
$ sudo /usr/local/mysql/bin mysqld_safe --user=mysql &
basedir, datadir etc. could be overriden whether in command line arguments, or in /etc/init.d/mysql itself. Now SPHINX should be in the engine list:
mysql> show engines \G
*************************** 1. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: SPHINX
     Support: YES
     Comment: Sphinx storage engine 0.9.9 ($Revision: 2391 $)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.00 sec)

UPDATE 18 AUGUST 2010

BTW, Ubuntu's standard mysql-server-5.1 package has a bug in \s (status) command: it doesn't show current database info(charset, name etc.). There is no such bug after installing from original source. And I'm still figuring out how to fix bug with DELETE key(it prints tilde("~") instead of removing a character). Occasionally, on the first compile a had got rid of it. But in the next install it appeared again. I suspect mysql-common(5.1.41-3ubuntu12.3) package. I believe, it'll be fixed, if I recompile mysql-server, but I've no spare time now.