> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Working with MySQL database engines

> Get an introduction to working with database engines in MySQL with this guide including step-by-step instructions, helpful tips and relevant code snippets.

This article is an introduction to working with database engines in MySQL.

<Note>
  To follow some of the procedures in this article, you must have root access to the server.
</Note>

## About MySQL database engines

Database engines provide the underlying functionality for MySQL to work with and process data.

The two most common and popular MySQL database engines are **MyISAM** and **InnoDB**. MyISAM is the default engine for MySQL for versions earlier than 5.5.5, and functions well in most scenarios. However, depending on your needs, there are situations where another database engine, such as InnoDB, may be the better choice. For example, InnoDB supports transactions, whereas MyISAM does not. InnoDB also provides support for foreign keys, whereas MyISAM does not.

If you have root access to your server, you have complete control over how and when MySQL uses the various database engines. You can change the default database engine, change a specific table's database engine, and more.

<Note>
  This article assumes that you already know how to access MySQL from the command line using the *mysql* program. If you do not know how to do this, please read [this article](/docs/connect-to-mysql-from-the-command-line) first.
</Note>

## Determining the default database engine

To determine the default database engine for your installation, type the following command at the **mysql>** prompt:

```sql theme={null}
SHOW ENGINES;
```

A list of supported engines appears, along with a brief description and the supported features for each engine. The default database engine is marked **DEFAULT** in the **Support** column.

## Changing the default database engine

You can change the default database engine for your MySQL installation. After you do this, all new tables that you create will use the new database engine (unless you explicitly set the engine during table creation).

To change the default database engine, follow these steps:

1. Use your preferred text editor to open the *my.cnf* file on your server. The location of the *my.cnf* file depends on your Linux distribution:

   * On AlmaLinux and Fedora, the *my.cnf* file is located in the **/etc** directory.

   * On Debian and Ubuntu, the *my.cnf* file is located in the **/etc/mysql** directory.

2. In the *my.cnf* file, locate the **\[mysqld]** section.

3. Add or modify the following line in the **\[mysqld]** section. Replace *ENGINE* with the name of the engine that you want to use as the default:

   ```
   default-storage-engine=ENGINE
   ```

   > 🚧 Important
   >
   > If you are enabling the InnoDB database engine, depending on your Linux distribution you may have to disable the following line in the *my.cnf* file:
   >
   > ```
   > skip-innodb
   > ```
   >
   > To do this, just add a pound sign (**#** ) to the beginning of the line, as follows:
   >
   > ```
   > #skip-innodb
   > ```

4. Save the changes to the *my.cnf* file, and then exit the text editor.

5. Restart the MySQL server using the appropriate command for your Linux distribution:

   * For AlmaLinux and Fedora, type:

     ```bash theme={null}
     service mysqld restart
     ```

   * For Debian and Ubuntu, type:

     ```bash theme={null}
     service mysql restart
     ```

6. To confirm the new default database engine, use the **SHOW ENGINES** SQL statement as described in the **Determining the default database engine** section.

## Determining a table's current database engine

To determine which engine a database table is currently using, type the following command at the **mysql>** prompt. Replace *database* with the name of the database that you want to check:

```sql theme={null}
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database';
```

This command displays a list of every table in the database, along with the engine each table is using.

## Changing a table's database engine

You can change the database engine for a table that already exists. For example, the following SQL statement shows how to modify a table named *myTable* to use the InnoDB engine:

```sql theme={null}
ALTER TABLE myTable ENGINE = InnoDB;
```

## Creating a new table with a specific database engine

When you create a table in a database, you can explicitly set its database engine (otherwise, MySQL uses the default database engine during table creation). For example, the following SQL statement shows how to create a table named *myTable* that uses the MyISAM database engine:

```sql theme={null}
CREATE TABLE myTable (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       data VARCHAR(20) NOT NULL
) ENGINE MyISAM;
```

Simiarly, to create a table that uses the InnoDB database engine, you could use the following SQL statement:

```sql theme={null}
CREATE TABLE myTable (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       data VARCHAR(20) NOT NULL
) ENGINE InnoDB;
```

## More information

* For more information about the MyISAM engine, please visit [http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html](http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html).

* For more information about the InnoDB engine, please visit [http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html](http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html).

## Related articles

* [Managing MySQL databases, users, and tables from the command line](/docs/managing-mysql-databases-and-users-from-the-command-line)