Skip navigation
Currently Being Moderated

Convert MySQL to use InnoDB Tables

VERSION 1 
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)