Migration from MyISAM to InnoDB: Difference between revisions
From MoodleDocs
m (Added batch conversion shell commands link) |
|||
(One intermediate revision by one other user not shown) | |||
Line 1: | Line 1: | ||
{{Managing a Moodle site}} | {{Managing a Moodle site}} | ||
This page only applies to administrators using Moodle with a MySQL database | This page only applies to administrators using Moodle with a MySQL database as it's about migrating the [http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html MySQL storage engine]. | ||
== Why migrate to InnoDB? == | |||
InnoDB is highly recommended rather than MyISAM because it works more robustly, performs better with big sites and allows better data integrity features (transactions). | |||
It is required if you want to use Moodle's [[Web services]] (for the [[Mobile app]] for example), and possibly for more features in future. | |||
== MySQL Configuration == | == MySQL Configuration == | ||
Line 16: | Line 13: | ||
* '''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. | * '''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 | == Various Methods described in Moodle forums (choose one)== | ||
* run the innodb script as a webpage <nowiki>http://www.YOUR_MOODLE_SITE/admin/tool/innodb</nowiki> (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 <nowiki>http://www.YOUR_MOODLE_SITE/admin/innodb.php</nowiki> | * run the innodb script as a webpage <nowiki>http://www.YOUR_MOODLE_SITE/admin/tool/innodb</nowiki> (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 <nowiki>http://www.YOUR_MOODLE_SITE/admin/innodb.php</nowiki> | ||
* 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 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) | ||
Line 22: | Line 19: | ||
* 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.''' | * 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 | * Follow the process detailed here - https://sites.google.com/site/moodlemayhem/tcea2011-moodle-server-setup-basics/moodleacidtrip | ||
* Use the php script (Tested on 1.9 and 2.0/2.2) http://noveckg.blogspot.com/2012/04/moodle-php-script-to-convert-mysql.html | * Use the php script (Tested on 1.9 and 2.0/2.2) http://noveckg.blogspot.com/2012/04/moodle-php-script-to-convert-mysql.html | ||
* Batch Convert using the linux shell (Tested using MySQL 5.0 and CentOS 5.x, works on Moodle 1.9 and 2.x) http://www.woblag.com/2012/10/batch-convert-myisam-tables-to-innodb.html | |||
==See also== | ==See also== | ||
* [http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB MySQL Engines: MyISAM vs. InnoDB] article by Tag1 Consulting | * [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] | |||
* [http:// | Using Moodle forum discussions: | ||
*[http://moodle.org/mod/forum/discuss.php?d=162871 Documentation for migration from MyISAM to InnoDB] | |||
*[http://moodle.org/mod/forum/discuss.php?d=117913 New moodle.org] | |||
[[fr:Migration de MyISAM vers InnoDB]] | [[fr:Migration de MyISAM vers InnoDB]] |
Latest revision as of 13:10, 27 November 2012
This page only applies to administrators using Moodle with a MySQL database as it's about migrating the MySQL storage engine.
Why migrate to InnoDB?
InnoDB is highly recommended rather than MyISAM because it works more robustly, performs better with big sites and allows better data integrity features (transactions).
It is required if you want to use Moodle's Web services (for the Mobile app for example), and possibly for more features in future.
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/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 [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). 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
- Use the php script (Tested on 1.9 and 2.0/2.2) http://noveckg.blogspot.com/2012/04/moodle-php-script-to-convert-mysql.html
- Batch Convert using the linux shell (Tested using MySQL 5.0 and CentOS 5.x, works on Moodle 1.9 and 2.x) http://www.woblag.com/2012/10/batch-convert-myisam-tables-to-innodb.html
See also
- MySQL Engines: MyISAM vs. InnoDB article by Tag1 Consulting
- An easy migration of MyISAM mysqldump to InnoDB
Using Moodle forum discussions: