How to change default-storage-engine in MySQL

When creating a table, the ENGINE option is used, which specifies the storage engine, if this option is not specified in the SQL query, then the default storage engine is used, which is specified in the MySQL server configuration file or when it starts mysqld –default-storage-engine=InnoDB.

I will give an example of viewing the supported storage engines and which is specified by default.

SELECT engine, support, transactions, xa FROM information_schema.engines;
SELECT * FROM information_schema.ENGINES\G;

An example of specifying the default storage engine in the MySQL server configuration file (innodb is usually used by default):


An example of changing the storage engine without restarting the MySQL server (but so that the change is not reset after restarting, it must also be specified in the configuration file, as I showed above):

SET GLOBAL storage_engine=INNODB;
SET storage_engine=INNODB;

An example of converting a table from one mechanism to another (I recommend always making backups before important changes):

ALTER TABLE ixnfo_com ENGINE = InnoDB;

If you specify a storage mechanism that is not supported, then the one that is specified by default will be used, for example, when using replication when some servers support this storage mechanism, while others do not.

See my other articles about MySQL

Leave a comment

Leave a Reply