[Skip to content](#main)

[→](/)

[![Avunu](/brand/avunu-logo.svg)![Avunu](/brand/avunu-logo-light.svg)](/)[Services](/services/)[Consulting](/services/consulting/)[ERP](/services/erp/)[Odoo](/services/erp/odoo/)[ERPNext](/services/erp/erpnext/)[Custom software](/services/custom-software/)[Devices & IoT](/services/devices-iot/)[Websites](/services/websites/)[Infrastructure](/services/infrastructure/)[Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

[About Avunu](/about/)[Team](/about/team/)[Insights](/insights/)[Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

- [Services](/services/)[Consulting](/services/consulting/)
- [ERP](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)
- [Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

- [About Avunu](/about/)
- [Team](/about/team/)
- [Insights](/insights/)
- [Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

[Knowledge base](/kb/)[Databases](/kb/databases/)Convert Tables to a Different Storage Engine

Search the knowledge basePress/

ESC

Jump to

[Browse the knowledge base](/kb/)[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

↵select↑↓navigateescclose

Search the knowledge base

[Skip to the article](#kb-article)[Databases](/kb/databases/)3 articles

- [Overview](/kb/databases/)
- [Convert Tables to a Different Storage Engine](/kb/databases/convert-table-storage-engines/)
- [MariaDB and MySQL Cheatsheet](/kb/databases/mariadb-mysql-cheatsheet/)
- [Recover Data from a MariaDB Server That Won't Start](/kb/databases/emergency-recovery-mode/)

Other sections[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

In Databases: 3 articles

[Overview](/kb/databases/)[Convert Tables to a Different Storage Engine](/kb/databases/convert-table-storage-engines/)[MariaDB and MySQL Cheatsheet](/kb/databases/mariadb-mysql-cheatsheet/)[Recover Data from a MariaDB Server That Won't Start](/kb/databases/emergency-recovery-mode/)

Other sections[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

DatabasesConvert Tables to a Different Storage Engine

Generate and run ALTER TABLE statements that move every table in a MariaDB database from MyISAM or Aria to InnoDB or another engine.

UpdatedOct 1, 2026

Applies toMariaDB 10.11MariaDB 11.4

Tagsmariadbmysqlinnodbstorage-engines

Reading time3 min

On this page[1. Back Up the Database](#1-back-up-the-database)[2. Find Tables That Need Converting](#2-find-tables-that-need-converting)[3. Generate the ALTER Statements](#3-generate-the-alter-statements)[4. Run the Conversion](#4-run-the-conversion)[5. Verify](#5-verify)[Converting to Other Engines](#converting-to-other-engines)

Older databases, especially ones migrated from legacy WordPress or PHP applications, often still have MyISAM or Aria tables. Converting them to InnoDB gives you transactions, row-level locking, and crash recovery. The same method works for any target engine, such as MyRocks.

## 1. Back Up the Database

Converting rewrites every table, so start with a full dump:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">mariadb-dump</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--single-transaction</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--routines</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--triggers</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--databases</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">DB_NAM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">E</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">DB_NAM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">E</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">-before-convert.sql</span>
```

## 2. Find Tables That Need Converting

List the tables that aren't already on the target engine:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_NAME, ENGINE</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">information_schema</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">TABLES</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_SCHEMA </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'<DB_NAME>'</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">  </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_TYPE </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'BASE TABLE'</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">  </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ENGINE </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'InnoDB'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

## 3. Generate the ALTER Statements

Have MariaDB write the conversion statements for you, and review them before running:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">CONCAT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'ALTER TABLE `'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, TABLE_SCHEMA, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'`.`'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, TABLE_NAME, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'` ENGINE=InnoDB;'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">information_schema</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">TABLES</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_SCHEMA </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'<DB_NAME>'</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">  </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_TYPE </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'BASE TABLE'</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">  </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ENGINE </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'InnoDB'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

```text
ALTER TABLE `<DB_NAME>`.`wp_options` ENGINE=InnoDB;
ALTER TABLE `<DB_NAME>`.`wp_posts` ENGINE=InnoDB;
```

The backticks handle table names that contain spaces or reserved words.

## 4. Run the Conversion

Once you're happy with the list, pipe the generated statements straight back into the server from the shell:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">mariadb</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-N</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-e</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"SELECT CONCAT('ALTER TABLE </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">\`</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">', TABLE_SCHEMA, '</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">\`</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">.</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">\`</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">', TABLE_NAME, '</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">\`</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069"> ENGINE=InnoDB;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<DB_NAME>' AND TABLE_TYPE = 'BASE TABLE' AND ENGINE <> 'InnoDB'"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">|</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">mariadb</span>
```

`-N` drops the column header, so only the statements reach the second `mariadb` process.

Warning

Each `ALTER TABLE ... ENGINE` copies the whole table and locks it for writes while it runs. Large tables can take a long time and need free disk space roughly equal to their size. Run the conversion during a maintenance window.

## 5. Verify

Confirm every table now reports the new engine:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_NAME, ENGINE</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">information_schema</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">TABLES</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> TABLE_SCHEMA </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'<DB_NAME>'</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ORDER BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ENGINE, TABLE_NAME;</span>
```

You don't need a separate `OPTIMIZE TABLE` afterward. Changing the engine already rebuilds each table from scratch.

## Converting to Other Engines

Replace `InnoDB` in the queries above with the target engine's name. Some engines are plugins that must be installed first. MyRocks, for example, ships as a separate package:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">apt</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">install</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">mariadb-plugin-rocksdb</span>
```

```
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">SHOW ENGINES;</span>
```

Check that the engine is listed as `YES` before converting. Also check that the target engine supports the features your tables use, such as full-text indexes, foreign keys, or spatial columns.

Sources

- [mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table)
- [mariadb.com/docs/server/server-usage/storage-engines/myrocks](https://mariadb.com/docs/server/server-usage/storage-engines/myrocks)

Need help with your databases?

Our notes can take you a long way, but sometimes you'd rather have someone who's done it before.

[Managed infrastructure](/services/infrastructure/)

[← PreviousOverview](/kb/databases/)[Next →MariaDB and MySQL Cheatsheet](/kb/databases/mariadb-mysql-cheatsheet/)

CC0This article is in the public domain ([CC0 1.0](https://creativecommons.org/publicdomain/zero/1.0/)), code samples included. Use it however helps you.

On this page

- [1. Back Up the Database](#1-back-up-the-database)
- [2. Find Tables That Need Converting](#2-find-tables-that-need-converting)
- [3. Generate the ALTER Statements](#3-generate-the-alter-statements)
- [4. Run the Conversion](#4-run-the-conversion)
- [5. Verify](#5-verify)
- [Converting to Other Engines](#converting-to-other-engines)

[Back to top↑](#top)

Services

- [ERP implementation](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)

Resources

- [Our work](/work/)
- [Open source](/open-source/)
- [Knowledge base](/kb/)
- [Insights](/insights/)

Company

- [About](/about/)
- [Team](/about/team/)
- [Partners](/partners/)
- [Contact](/contact/)

Clients

- [Project portal](https://erp.avunu.net/projects)
- [Invoices](https://erp.avunu.net/)
- [Support](https://erp.avunu.net/)

[![Avunu](/brand/avunu-logo-light.svg)](/)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M20.400 20.500h-3.600v-5.600c0-1.300 0-3-1.800-3s-2.100 1.400-2.100 2.900v5.700H9.400V9h3.400v1.600c.5-.9 1.600-1.900 3.400-1.900 3.600 0 4.300 2.400 4.300 5.500v6.300zM5.300 7.400a2.100 2.100 0 1 1 0-4.100 2.100 2.100 0 0 1 0 4.100zm1.800 13.100H3.600V9h3.500v11.500zM22.200 0H1.800C.8 0 0 .8 0 1.700v20.600c0 .9.800 1.700 1.800 1.700h20.400c1 0 1.800-.8 1.800-1.700V1.700C24 .8 23.200 0 22.200 0z">\</path>\</svg>\</av-icon>](https://www.linkedin.com/company/avunu)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M12 .3a12 12 0 0 0-3.8 23.4c.6.1.8-.3.8-.6v-2c-3.3.7-4-1.6-4-1.6-.6-1.4-1.4-1.8-1.4-1.8-1-.7.1-.7.1-.7 1.2.1 1.8 1.2 1.8 1.2 1 1.8 2.8 1.3 3.5 1 .1-.8.4-1.3.7-1.6-2.7-.3-5.5-1.3-5.5-5.9 0-1.3.5-2.4 1.2-3.2-.1-.3-.5-1.5.1-3.2 0 0 1-.3 3.3 1.2a11.500 11.500 0 0 1 6 0c2.300-1.500 3.300-1.200 3.300-1.200.6 1.700.2 2.900.1 3.200.8.8 1.200 1.900 1.200 3.200 0 4.600-2.800 5.600-5.500 5.900.4.400.8 1.100.8 2.200v3.300c0 .3.2.7.8.6A12 12 0 0 0 12 .3">\</path>\</svg>\</av-icon>](https://github.com/Avunu)[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

Avunu LLC · 948 East Philadelphia Street, York, PA 17403 · (717) 814-8540Knowledge base articles are in the public domain[Privacy](/privacy/)[Terms](/terms/)
