How to set default storage engine in MySQL or MariaDB

Setting a default storage engine controls what engine new tables use when no ENGINE clause is provided in CREATE TABLE. Standardizing this avoids accidental creation of non-transactional tables, reduces surprises during restores or migrations, and keeps behavior consistent across deployments.

Both MySQL and MariaDB support multiple storage engines (for example InnoDB, MyISAM, and vendor-specific engines). The server decides which engine to use by consulting the global system variable default_storage_engine at table-creation time, unless an explicit ENGINE=… is specified in the statement.

The default engine change affects only newly created tables that omit ENGINE. Existing tables keep their current engine until an explicit conversion is performed, and setting an engine that is not available (or is disabled) can prevent expected table creation behavior. The workflow below assumes local administrative access on Linux with systemd for restarts, plus privileges to run administrative SQL statements.

1. Show the current default_storage_engine value.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
   +------------------------+--------+
   | Variable_name          | Value  |
   +------------------------+--------+
   | default_storage_engine | InnoDB |
   +------------------------+--------+

   If sudo mysql is not configured for administrative login, use mysql -u root -p (or an equivalent privileged account) for the same commands.

2. List available storage engines on the server.

   $ mysql --table -u root -p -e "SHOW ENGINES;"
   +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
   | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
   +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
   | ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
   | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
   | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
   | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
   ##### snipped #####

   Choose an engine that shows DEFAULT or YES under Support, or table creation may fail when the default is applied.

3. Open the mysqld option file used for server settings.

   $ sudoedit /etc/mysql/conf.d/engine.cnf

   Common paths include /etc/mysql/mysql.conf.d/mysqld.cnf (MySQL on Debian or Ubuntu) and /etc/mysql/mariadb.conf.d/50-server.cnf (MariaDB on Debian or Ubuntu), plus /etc/my.cnf or /etc/my.cnf.d/*.cnf (many RHEL-style installs).

4. Add a default_storage_engine line under the [mysqld] section.

   [mysqld]
   default_storage_engine=InnoDB

   A typo in an option file can prevent mysqld from starting, which can take the database offline until the configuration is corrected.

5. Restart the database service to load the updated configuration.

   $ sudo docker restart sg-mysql
   sg-mysql

   If the unit name is not mysql, restart the installed unit (commonly sudo systemctl restart mariadb on MariaDB-based installs).

6. Check the service status for an active (running) state.

   $ docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}' | grep -E '^sg-mysql[[:space:]]'
   sg-mysql                mysql:8.0           Up 10 seconds

7. Confirm the new default_storage_engine value after the restart.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
   +------------------------+--------+
   | Variable_name          | Value  |
   +------------------------+--------+
   | default_storage_engine | InnoDB |
   +------------------------+--------+

   This value is applied only when CREATE TABLE omits ENGINE=…, and existing tables keep their current engine until changed explicitly.

8. Create a temporary database for an isolated verification.

   $ mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS sg_engine_test;"

9. Drop the verification table if it already exists.

   $ mysql -u root -p -e "USE sg_engine_test; DROP TABLE IF EXISTS t_engine_default;"

10. Create a new table without specifying ENGINE.

    $ mysql -u root -p -e "USE sg_engine_test; CREATE TABLE t_engine_default (id INT PRIMARY KEY);"

11. Verify the created table engine matches the configured default.

    $ mysql -u root -p -e "USE sg_engine_test; SHOW TABLE STATUS LIKE 't_engine_default'\\G"
    *************************** 1. row ***************************
               Name: t_engine_default
             Engine: InnoDB
    ##### snipped #####

    MySQL 8.0+ supports persisting runtime changes with SET PERSIST default_storage_engine='InnoDB';, which writes to the server’s persisted configuration (often /var/lib/mysql/mysqld-auto.cnf) without editing the main option file.

12. Remove the temporary verification database.

    $ mysql -u root -p -e "DROP DATABASE sg_engine_test;"