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.

   $ sudo mysql -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.

   $ sudo mysql -e "SHOW ENGINES;"
   +--------------------+---------+------------------------------------------------------------+--------------+------+------------+
   | Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
   +--------------------+---------+------------------------------------------------------------+--------------+------+------------+
   | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
   | MyISAM             | YES     | Non-transactional engine with good performance and small   | NO           | NO   | NO         |
   | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | 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/mysql.conf.d/mysqld.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 systemctl restart 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.

   $ sudo systemctl status mysql --no-pager
   ● mysql.service - MySQL Community Server
        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
        Active: active (running) since Fri 2025-12-12 22:24:19 UTC; 6s ago
   ##### snipped #####

7. Confirm the new default_storage_engine value after the restart.

   $ sudo mysql -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.

   $ sudo mysql -e "CREATE DATABASE IF NOT EXISTS sg_engine_test;"

9. Drop the verification table if it already exists.

   $ sudo mysql -e "USE sg_engine_test; DROP TABLE IF EXISTS t_engine_default;"

10. Create a new table without specifying ENGINE.

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

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

    $ sudo mysql -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.

    $ sudo mysql -e "DROP DATABASE sg_engine_test;"