I use InnoDB storage engine because of support for transactions and referral integrity rules. However, MySQL still creates new tables as MyISAM by default. It was so annoying to always define storage engine when creating new tables and double check that I didn’t forget it until I found how to set InnoDB by default.
The solution is very easy:
- Open your MySQL configuration file: my.cnf or my.ini depending on your operation system.
- Locate [mysqld] section which usually goes at the top of file.
- Add this setting to the section:
default-storage-engine = InnoDB
- Restart MySQL server.
- Enjoy your new new tables created as InnoDB w/o any additional worry.
The setting is applied to CREATE TABLE queries as well as tables created with phpMyAdmin, navicat and possibly any other MySQL client.
If you can’t change MySQL configuration file you still can change default storage engine used during a session by setting storage_engine system variable:
2 thoughts on “How to set InnoDB as a default storage engine for MySQL tables”