How to Set Default Storage Engine in MySQL Easily

To set the default storage engine in MySQL, use the default_storage_engine system variable in the MySQL configuration file or set it dynamically with SET GLOBAL default_storage_engine = 'engine_name';. This controls which storage engine MySQL uses when creating new tables without an explicit engine specified.

Syntax

You can set the default storage engine in MySQL in two main ways:

• In the configuration file: Use default_storage_engine=engine_name under the [mysqld] section.
• Dynamically at runtime: Use the SQL command SET GLOBAL default_storage_engine = 'engine_name'; to change it without restarting the server.

Replace engine_name with the desired storage engine like InnoDB, MyISAM, etc.

ini

[mysqld]
default_storage_engine=InnoDB

Example

This example shows how to set the default storage engine to InnoDB dynamically and verify the change.

mysql

SET GLOBAL default_storage_engine = 'InnoDB';
SHOW VARIABLES LIKE 'default_storage_engine';

Output

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| default_storage_engine | InnoDB  |
+------------------------+---------+

Common Pitfalls

Common mistakes when setting the default storage engine include:

• Trying to set the variable without GLOBAL, which only changes it for the current session.
• Not restarting MySQL after changing the configuration file, so the change does not take effect.
• Using an unsupported or misspelled storage engine name.

Always check the available storage engines with SHOW ENGINES; before setting.

mysql

/* Wrong: changes only current session */
SET default_storage_engine = 'MyISAM';

/* Right: changes globally until restart */
SET GLOBAL default_storage_engine = 'MyISAM';

Quick Reference

Action	Command or Setting	Notes
Set in config file	[mysqld] default_storage_engine=InnoDB	Requires MySQL restart
Set dynamically	SET GLOBAL default_storage_engine = 'InnoDB';	Takes effect immediately, but resets on restart
Check current engine	SHOW VARIABLES LIKE 'default_storage_engine';	Shows current default engine
List available engines	SHOW ENGINES;	Verify supported engines

Key Takeaways

Set default storage engine in MySQL using the configuration file or SET GLOBAL command.

Changes in the config file require a server restart to take effect.

Use SET GLOBAL to change the engine immediately but temporarily.

Always verify available storage engines with SHOW ENGINES before setting.

Avoid session-only changes by including GLOBAL in the SET command.