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.
leave a comment