2011/02/07

Setting InnoDB as the default MySQL Engine

In order to set InnoDB as the default storage engine in MySQL version 5.0.x the process is simple.

Open the my.cnf file in a text editor such as vi.  If the following line is in the config it should be commented out or deleted:

skip-innodb

Add this line under [mysqld] to make InnoDB the default engine:

default-table-type=innodb

Save the file and restart mysqld.

To verify that the default has been changed from MyISAM  to InnoDB login to MySQL and run the following:

show engines;

You should see that InnoDB is now the default.

+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     | 
| BerkeleyDB | YES     | 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                                        | 
+------------+---------+----------------------------------------------------------------+

No comments:

Post a Comment