Migration from MyISAM to InnoDB: Difference between revisions
From MoodleDocs
| Line 12: | Line 12: | ||
* 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 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 [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) | |||
[[fr:admin/innodb]] | [[fr:admin/innodb]] | ||
Revision as of 00:20, 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
- 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)
