Fast, Reliable, Proven transactional storage for MySQL

InnoDB

Creating InnoDB tables

Read more

First check that InnoDB is not disabled in your my.cnf or my.ini file by typing:

 mysql> show variables like '%inno%';
 _________________________________ ________________________ 
| Variable_name                   | Value                  |
 _________________________________ ________________________ 
| have_innodb                     | YES                    |
…

You can create tables in the InnoDB format by specifying ENGINE=INNODB after the table creation statement:

CREATE TABLE t (a INT NOT NULL, b INT, PRIMARY KEY (a)) ENGINE=INNODB;

To alter a MyISAM table to the InnoDB format do:

ALTER TABLE yourtablename ENGINE=INNODB;

Downloading InnoDB

Starting with version 3.23 (first released in May 12, 2001), all binary and source downloads of MySQL contain InnoDB. Select an appropriate version from the MySQL download page. On Windows, use mysqld-max-nt.exe.

Compiling MySQL with InnoDB from source

Read more

If you do not want to use a binary distribution, but want to compile MySQL/InnoDB yourself, download a source tarball from the MySQL download page. After extracting the files, go to the main directory of MySQL (usually named something like mysql-5.0.27), and type:

$ ./configure --with-innodb
$ make

On MySQL 5.1, ./configure expects a different parameter:


$ ./configure --with-plugins=innobase
$ make

Finally, you will have to install MySQL. For information on this, please consult the MySQL Reference Manual.

MySQL/InnoDB-4.1 embedded server library

Binary .rpm packages for various GNU/Linux distributions are available for download on the MySQL 4.1 downloads page. There is no embedded server library based on later versions of MySQL/InnoDB.

InnoDB Plugin

Downloading InnoDB Plugin 1.0 for MySQL 5.1

you can download source and binary versions of the InnoDB Plugin here.

Recovering After a System Crash During ALTER TABLE

The InnoDB Plugin can create primary key (clustered) and secondary indexes more quickly than the built-in InnoDB, by sorting the data rather than inserting rows one at a time into an empty index. No special recovery is needed if the server crashes while creating a secondary index. However, some manual steps are needed to complete recovery if a system crash while rebuilding the clustered (primary key) index of a table.

Please read Chapter 2 of the User’s Guide for the InnoDB Plugin for background on the process of creating indexes, and specifically section 2.5 on Crash Recovery for an overview of the crash recovery scenario. Detailed steps for completing recovery following a crash are presented here.

InnoDB Hot Backup

Fixing corrupted InnoDB data file pages

Sometimes the operating system or the hardware can corrupt a data file page. If the page is not used in InnoDB tables, then mysqld may run ok. But when you try to back up the tablespace with ibbackup, you get errors like below and ibbackup refuses to make the backup.

The link below is to a little C program, innodb_page_checksum_reset.c, that you can use to reset the lsn and the checksum fields in one page, so that ibbackup will no longer complain. Of course, your main task is to find out what is wrong with the OS or the hardware that causes corrupt pages to appear. This C program will not fix the corruption!

innodb_page_checksum_reset.c