- MariaDB Cookbook
- Daniel Bartholomew
- 335字
- 2021-07-16 12:19:26
Migrating a table from MyISAM or Aria to InnoDB or XtraDB
The default storage engine of MariaDB is XtraDB, which is an enhanced version of InnoDB.
Getting ready
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in this chapter.
How to do it...
- Open the
mysqlcommand-line client and connect to theisfdbdatabase. - Run the following command line:
ALTER TABLE awards ENGINE=InnoDB; - After the command line gets executed, a message similar to the following will be displayed:
Query OK, 33102 rows affected (5.37 sec) Records: 33102 Duplicates: 0 Warnings: 0
- If our system is older or is under heavy load and it takes longer than 5 seconds for the
ALTER TABLEcommand line execution to complete, we'll see a progress message letting us know how much of the task has been completed. The message gets updated every 5 seconds, until the task is finished.
How it works...
The ALTER TABLE command converts the table over in two stages. First, it creates a new table, identical in every way to the old table, except that the new table uses the InnoDB or XtraDB storage engine (whichever we have configured as the active one) and copies all of the data to the new table.
In the second stage, ALTER TABLE removes the old table and renames the new table with the name of the old table.
On a table like awards that only has thirty thousand or more rows, the conversion is quick and easy; however, on a table with several billion rows, the conversion process will take significantly longer.
There's more...
Before converting all of the tables in a database from MyISAM or Aria to InnoDB/XtraDB, be aware that InnoDB/XtraDB uses more memory when running for the same amount of activity. Make sure our server has the memory capacity to handle it.
See also
- The full documentation for InnoDB and XtraDB can be found at https://kb.askmonty.org/en/xtradb-and-innodb/
- Full documentation of the
ALTER TABLEcommand can be found at https://kb.askmonty.org/en/alter-table/