GoAdmio

innodb2myisam

Convert the InnoDB tables of a MariaDB/MySQL database, or a single table, to the MyISAM storage engine

On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also


Overview

Every table in MariaDB/MySQL uses a storage engine. /go/innodb2myisam changes tables from InnoDB to MyISAM. It can convert:

MyISAM and InnoDB behave differently, and the change can affect your site. Read Before you convert first.


Quick start

# Convert all InnoDB tables of a database
/go/innodb2myisam shopdb

# Convert one table
/go/innodb2myisam shopdb orders

Syntax

/go/innodb2myisam [database] [table]
Argument Description
<database> The database name. If you give no arguments at all, the command asks Database Name:.
<table> Optional. One table to convert. Without it, every InnoDB table in the database is converted.

Options

Form What it does
/go/innodb2myisam <database> Finds every table of the database that uses InnoDB, and converts each one.
/go/innodb2myisam <database> <table> Converts only that table. The command does not check its current engine.
/go/innodb2myisam Asks for the database name, then converts all its InnoDB tables.

To go the other way, use myisam2innodb.


Output

For each table, the command prints one line, and then any error from MariaDB:

Converting Table 'orders' to MyISAM...

It prints nothing when the database has no InnoDB tables or does not exist. There is no summary at the end.

The list of table names is saved in /usr/local/goadmio/<database>.txt and is not removed afterwards. Each run also adds a line to /var/log/go/innodb2myisam.log.


Examples

# Back up the database first
mysqldump shopdb > /root/shopdb.sql
# Convert every InnoDB table of the database
/go/innodb2myisam shopdb
# Convert a single table
/go/innodb2myisam shopdb orders
# Check the engine of each table afterwards
mariadb -e "select table_name, engine from information_schema.tables where table_schema='shopdb'"

Troubleshooting

Symptom Likely cause Fix
Nothing is converted, and Access denied or a password prompt appears The list of tables is read with the root password from the MySQLRootPassword variable. The command does not set this variable. Make sure the variable is available in your shell, then run the command again.
Nothing is printed The database has no InnoDB tables, or the name is wrong. Check the name and the engines with the query in the Examples.
An SQL syntax error for one table The table name has a hyphen, a space, or is a reserved word. The name is not quoted. Convert it by hand with the name in backticks.
An error about a foreign key The table has foreign keys, or other tables refer to it. Remove the foreign keys first (see Before you convert).
Table '<db>.<table>' doesn't exist A typo in the table name. Check the table name.
The site is slow or errors appear after the change MyISAM locks the whole table for each write, and has no transactions. Convert back with myisam2innodb.
A table is damaged after a crash MyISAM tables are not crash-safe. Repair it with repair.

Details

Before you convert

Topic What changes
Backup Make a backup first. Converting rewrites every table.
Transactions MyISAM has no transactions. A group of changes cannot be rolled back.
Foreign keys MyISAM does not support foreign keys. Tables that use them may fail to convert, or lose them.
Locking A write locks the whole table, not a row, so busy sites with many writes can become slower.
Crash safety After a crash or power loss, MyISAM tables can be damaged and need repair.
Time and space The table is rewritten and is locked while it runs. Large tables take longer and need free disk space.

What it does, in order

  1. Logs the run to /var/log/go/innodb2myisam.log.
  2. Takes the database and table names from the arguments, and asks for the database name if there are no arguments.
  3. Without a table name, reads the names of all InnoDB tables of the database from information_schema and saves them in /usr/local/goadmio/<database>.txt.
  4. For each table, prints the line and runs ALTER TABLE <database>.<table> ENGINE=MyISAM.

Known issues


See also

Command Description
myisam2innodb Convert MariaDB/MySQL tables from MyISAM to InnoDB
myisam2aria Convert MariaDB/MySQL tables from MyISAM to Aria
repair Repair a MySQL database
dbls List all databases and their sizes
my Optimize the my.cnf configuration for MySQL/MariaDB
mysqlbackup Back up MariaDB/MySQL to a remote server via rsync