Playing With MySQL Storage Engines in Ubuntu

To see which tables are using which storage engine, you may run the following command on your database

show table status from databasename;

Now you can decide how to manage your tables. A useful comparison of storage engines in MySQL is also available.

Set InnoDB as Default Storage Engine

If you want to use InnoDB as the default storage engine for MySQL in Ubuntu 7.10 (Gutsy Gibbon), you may do so easily. A forum post (Re: default table type = innodb is stable??) helped a lot in learning about this. First, stop the server

sudo /etc/init.d/mysql stop

Read the MySQL configuration file

cat /etc/mysql/my.cnf

Look for the following part in the configure file

# * Basic Settings
user = mysql

Edit the file

sudo vim /etc/mysql/my.cnf

And change the text (we need to look for) to

# * Basic Settings
user = mysql
default-storage-engine = InnoDB

Start the server

sudo /etc/init.d/mysql start

Now whenever you create a new table, it will use the InnoDB storage engine.

Change Storage Engine for Tables

A very good post (Convert a bunch of tables to InnoDB) helped me gain this knowledge. If you want to change the storage engine for tables, just run the following query on your database. If you want the table to use InnoDB use

alter table tablename type=InnoDB;

If you want the table to use MyISAM use

alter table tablename type=MyISAM;

Create a Table With Storage Engine

If you want to create a table and specify which storage engine to use, you may do so in the following way

create table dummytable1 (id int, name varchar (10)) type = InnoDB;
create table dummytable2 (id int, name varchar (10)) type = MyISAM;

Type or Engine?

You can replace type = InnoDB with engine = InnoDB. They are both the same (I think). It is up to you to decide.


One Response to Playing With MySQL Storage Engines in Ubuntu

  1. Adnan Raza says:


    I have configured clustering successfully, But I want default engine to be innodb which I have set. Now Whenever I create a new database it automatically replicates on other nodes , It had to be INNODB only. How can I restrict it also??

%d bloggers like this: