Adding SphinxSE to MySQL on ubuntu server

Unfortunately installing sphinx search on Ubuntu using apt-get does not install the SphinxSE engine in MySQL.  To do that we need to build it from source.  We can do that using the following steps: Ensure that the build-essential packages are installed, as well as all the dependencies required for building mysql-server

apt-get install build-essential
apt-get build-dep mysql-server

Next step get the mysql-server source code:

cd /tmp
dpkg --list | grep mysql-server

In my case this gives:

ii  mysql-server                        5.5.43-0ubuntu0.14.04.1             all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                    5.5.43-0ubuntu0.14.04.1             amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.5               5.5.43-0ubuntu0.14.04.1             amd64        MySQL database server binaries

So we can see we are using mysql-server version 5.5.43. Now we need to grab the source code:

wget 'http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.5/mysql-5.5.43.tar.gz'

Next we need the source code for sphinxsearch

wget 'http://sphinxsearch.com/files/sphinx-2.2.9-release.tar.gz'

Now we need to extract the source code for both programs

tar xvfz mysql-5.5.43.tar.gz 
tar xvfz sphinx-2.2.9-release.tar.gz

Next we need to copy the mysqlse engine code to the mysql source tree:

cp -R sphinx-2.2.9-release/mysqlse/ mysql-5.5.43/storage/sphinx

Then we need to compile mysql:

cd mysql-5.5.43
./BUILD/autorun.sh
./configure
make

Now go and get yourself a coffee – this may take some time 😉 OK so now you are caffined up and mysql has been build we need to copy a couple of files to the mysql you have on you system:

cp storage/sphinx/ha_sphinx.* /usr/lib/mysql/plugin/

And now we need to login to mysql and enable the engine:

mysql -uroot -p mysql
mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | 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         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| SPHINX             | YES     | Sphinx storage engine 2.2.9-release                            | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

As you can see the engine is now listed along with the standard ones. To remove the engine you can do this.

 mysql> UNINSTALL PLUGIN sphinx;