Skip navigation
Currently Being Moderated

Convert MySQL to use InnoDB Tables

Created on: Sep 14, 2009 12:23 PM by Noel Brockett - Last Modified:  Sep 14, 2009 12:23 PM by Noel Brockett

Using InnoDB tables with Zenoss and MySQL


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.

Comments (1)