Migration from MyISAM to InnoDB

2012-01-25T12:09:45Z

Agsteele: /* Various Methods described in Moodle Forums (choose one) */ Updating innodb script path

{{stub}}
This page only applies to administrators using Moodle with a MySQL database.
It's about migrating [http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html MySQL storage engine].

See [http://moodle.org/mod/forum/discuss.php?d=162871 Documentation for migration from MyISAM to InnoDB] for the current discussion which prompted the creation of this page. --[[User:Frank Ralf|Frank Ralf]] 11:41, 24 November 2010 (UTC)

== Difference between MyISAM and InnoDB ==
* read [http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB]
* InnoDB is best for data integrity, not so good for searching large files, and uses up more CPU cycles and storage space than MyISAM equivalent
* InnoDB is much younger than MyISAM but is becoming increasingly attractive ... but because it is more complex, some utilities written for MyISAM like mysqlhotcopy may not work
* It is perfectly OK to select InnoDB for certain tables, and MyISAM for others (based on above criteria)

== MySQL Configuration ==
Before attempting to change the table type, you should:
* '''check InnoDB is enabled'''. Log in to MySQL and run the command [http://dev.mysql.com/doc/refman/5.1/en/show-engines.html SHOW ENGINES]. Make sure that support for InnoDB shows either "YES" or "DEFAULT".
* '''set innodb_file_per_table if required'''. [http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html This setting] can be useful for large installations, but it only affects newly created tables and can't be applied retrospectively.

== Various Methods described in Moodle Forums (choose one)==
* run the innodb script as a webpage http://www.YOUR_MOODLE_SITE/admin/tool/innodb (v2.2.1 You should be logged into your Moodle site as an administrator - there might be timeout issues on large databases) In earlier versions of Moodle 2.x the script was at http://www.YOUR_MOODLE_SITE/admin/innodb.php
* use the CLI script in admin/cli/mysql_engine.php (requires access to shell, and the config.php must only have one moodle instance, but has no timeout issues)
* use PHPMyAdmin entering Structure view of a table and clicking Operations tab. Look for Storage Engine parameter. This is a tedious task due to the number of tables [http://moodle.org/mod/forum/discuss.php?d=162002]
* perform a database dump (e.g. mysqldump moodle_database > dump.SQL), use an editor or sed/perl/awk command to find/replace , replacing MyISAM with InnoDB. Finally, restore back in the server. (e.g. mysql < dump.SQL or equivalent). '''If using this method, be very careful to use the same file encoding on both input and output.'''
* Follow the process detailed here - https://sites.google.com/site/moodlemayhem/tcea2011-moodle-server-setup-basics/moodleacidtrip

==See also==

* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=117913 New moodle.org] forum discussion

* [http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB MySQL Engines: MyISAM vs. InnoDB] article by Tag1 Consulting

* [http://code.openark.org/blog/mysql/useful-sed-awk-liners-for-mysql An easy migration of MyISAM mysqldump to InnoDB]

[[fr:Migration de MyISAM vers InnoDB]]

MoodleDocs - User contributions [en]

Migration from MyISAM to InnoDB