Create MySQL database with default InnoDB tables?

Question

I've been working on writing a SQL statement to create a MySQL database with several default options, including default character set and default collate. Is it possible to add syntax to make the default engine type for tables in this database to be InnoDB?

I've been looking through the MySQL manual for v.5.1 and I've found the statement ENGINE=innodb which would be appended to a CREATE TABLE statement... but I haven't found anything related to a CREATE DATABASE statement.

Is there a normal way to do this as part of the database creation, or does it need to be specified on a table-by-table basis?

Answer 1

Quoting the Reference Manual (Setting the Storage Engine):

If you omit the ENGINE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.

You may also want to change the default storage engine just for the current session. You can do this by setting the storage_engine variable:

SET storage_engine=INNODB;

Answer 2

you need to specify the default storage engine when starting mysqld. for example:

mysqld --default-storage-engine=InnoDB

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_default-storage-engine