Compiling MySQL for InnoDB Storage Engine Use

MySQL distributions that intend on using the InnoDB Storage Engine need to be compiled in order to do so. I wanted to use this storage engine, but my binaries were not compiled with the correct option.

If your binaries aren't compiled with this option and you try to simply alter the my.cnf option file as appropriate, you will receive the following error upon startup:

/opt/mysql/5.1.23/libexec/mysqld: unknown variable 'innodb_data_home_dir=/opt/mysql/datafiles'

The solution is to recompile the binaries with the --with-innodb option:

build_mysql.bsh from the source's home directory

#!/bin/bash
MYSQL_BASEDIR=/opt/mysql/5.1.23
MYSQL_TCP_PORT=3307
MYSQL_SOCKET_FILE=$MYSQL_BASEDIR/socket/mysql-5123.sock
CFLAGS="-O3 -march=pentiumpro" CXX=gcc CXXFLAGS="-O3 -march=pentiumpro \
-felide-constructors -fno-exceptions -fno-rtti" ./configure \
--prefix=$MYSQL_BASEDIR --enable-assembler \
--with-mysqld-ldflags=-all-static --with-tcp-port=$MYSQL_TCP_PORT \
--with-unix-socket-path=$MYSQL_SOCKET_FILE --with-innodb

make
make install

./build_mysql.bsh

After the configure, make, and make install operations are complete, update your option file to reflect (at a minimum) the following variables (customized for your environment, of course):

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /opt/mysql/datafiles
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/datafiles
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Restart your MySQL Server:

service mysql-5.1.23 start

Run a couple of queries to verify that MySQL's InnoDB Storage Engine is now available:

mysql> show engines ;

It should now include the following row:

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

Also, check the following:

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

Labels: MySQL