Note: You are currently viewing documentation for Moodle 2.9. Up-to-date documentation for the latest stable version of Moodle may be available here: Migration from MyISAM to InnoDB.

Migration from MyISAM to InnoDB: Difference between revisions

From MoodleDocs
Line 13: Line 13:
* 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]
* 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)
* 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)
* [[https://sites.google.com/site/moodlemayhem/tcea2011-moodle-server-setup-basics/moodleacidtrip|Follow the process detailed here]]
* Follow the process detailed here - https://sites.google.com/site/moodlemayhem/tcea2011-moodle-server-setup-basics/moodleacidtrip


== Difference between MyISAM and InnoDB ==
== Difference between MyISAM and InnoDB ==

Revision as of 14:54, 30 November 2010


See Documentation for migration from MyISAM to InnoDB for the current discussion which prompted the creation of this page. --Frank Ralf 11:41, 24 November 2010 (UTC)

MySQL Configuration

Before attempting to change the table type, you should:

  • check InnoDB is enabled. Log in to MySQL and run the command SHOW ENGINES. Make sure that support for InnoDB shows either "YES" or "DEFAULT".
  • set innodb_file_per_table if required. 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/innodb.php (there might be timeout issues on large databases and it only works with mysql)
  • use the CLI script in admin/cli/mysql_engine.php (requires access to shell, mysql only, 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 [1]
  • 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)
  • Follow the process detailed here - https://sites.google.com/site/moodlemayhem/tcea2011-moodle-server-setup-basics/moodleacidtrip

Difference between MyISAM and InnoDB

  • read [2]
  • 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)