Convert MySQL to use InnoDB Tables

When I initially installed Zenoss, my MySQL installation did not support the InnoDB Engine. Here's what I did to enable InnoDB, in MySQL and convert the tables.

Backup your existing events database, just incase.

As root:

mysqldump events > events.sql

Stop Zenoss and MySQL

/etc/init.d/zenoss stop
/etc/init.d/mysql stop

Verify your existing tables for the Zenoss events database

mysql events -e 'show table status \G'
*************************** 1. row ***************************
        Name: alert_state
        Engine: MyISAM
...
...

Edit your appropriate (existing or not) my.cnf file and enable Innodb tables. I use my-huge.cnf as I have plenty of memory in my system (8 gigs). The cnf files can be found in the support folder of the mysql source tar file.

Un-comment the lines below in /etc/my.cnf.

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var/
innodb_log_arch_dir = /usr/local/mysql/var/

Start MySQL

/etc/init.d/mysql start

Verify that InnoDB Engine is supported

mysql -e 'show engines'
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | NO      | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+

If InnoDB support is set to 'NO', you'll need to remove the ibdata* and ib_log* files and restart MySQL. The restart might take a while due to the initial index build.

/etc/init.d/mysql stop
cd /path/to/mysql/var
rm ib_log*
rm ibdata*
/etc/init.d/mysql start

Copy the text below and save it to a file on your zenoss server. Call it something like innodb_events.

alter table alert_state type = innodb;
alter table detail type = innodb;
alter table heartbeat type = innodb;
alter table history type = innodb;
alter table log type = innodb;
alter table status type = innodb;

As root:

mysql events < innodb_events

This will alter the tables and convert them to InnoDB.

Restart Zenoss

/etc/init.d/zenoss start

Verify that the tables are now innodb

mysql events -e 'show table status \G'
*************************** 1. row ***************************
        Name: alert_state
        Engine: InnoDB
        Version: 10
...
...

You're all set! If you mess up, you can always revert with your backed up events.sql file.