Difference between revisions of "Migration from MyISAM to InnoDB"

Jump to: navigation, search
m (French link)
(See also)
 
(19 intermediate revisions by 14 users not shown)
Line 1: Line 1:
{{stub}}
+
{{Admin tools}}
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].
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)
+
== Why migrate to InnoDB? ==
 +
 
 +
'''Starting from Moodle 2.9 MyISAM is no longer supported.''' InnoDB works more robustly, performs better with big sites and allows better data integrity features (transactions).
  
 
== MySQL Configuration ==
 
== MySQL Configuration ==
Line 10: Line 11:
 
* '''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 Forums (choose one)==
+
== 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)
+
* RECOMMENDED:  use the CLI script - '''php admin/cli/mysql_engine.php''' - requires access to shell, and the config.php must only have one moodle instance
* 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)
+
* Make sure nobody else is accessing the server while performing the following. Put Moodle into maintenance mode. As an administrator, run the innodb script as a webpage <nowiki>http://www.YOUR_MOODLE_SITE/admin/tool/innodb/index.php</nowiki>. (With large databases, you may experience time-out issues.) Add the following statement to <nowiki>/etc/my.cnf</nowiki> under the [mysqld] heading: "binlog_format=ROW" (without the quotes) and stop/restart MySQL. Without this statement you will encounter the dmlwriteexception error when moodle attempts to write to MySQL. Take Moodle out of maintenance mode.
 
* 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). '''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
  
== Difference between MyISAM and InnoDB ==
+
==See also==
* 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)
 
  
==See also==
+
* [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://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb#9492183 A simple SQL statement to convert all MyISAM tables to InnoDB]
  
* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=117913 New moodle.org] forum discussion
+
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]]
 +
[[de:Migration von MyISAM zu InnoDB]]
 +
[[es:Migración desde MyISAM a InnoDB]]
 +
[[ja:MyISAMからInnoDBへの移行]]

Latest revision as of 09:39, 11 August 2016

(Additional tools may be found elsewhere in the admin settings)

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?

Starting from Moodle 2.9 MyISAM is no longer supported. InnoDB works more robustly, performs better with big sites and allows better data integrity features (transactions).

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)

  • RECOMMENDED: use the CLI script - php admin/cli/mysql_engine.php - requires access to shell, and the config.php must only have one moodle instance
  • Make sure nobody else is accessing the server while performing the following. Put Moodle into maintenance mode. As an administrator, run the innodb script as a webpage http://www.YOUR_MOODLE_SITE/admin/tool/innodb/index.php. (With large databases, you may experience time-out issues.) Add the following statement to /etc/my.cnf under the [mysqld] heading: "binlog_format=ROW" (without the quotes) and stop/restart MySQL. Without this statement you will encounter the dmlwriteexception error when moodle attempts to write to MySQL. Take Moodle out of maintenance mode.
  • 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

See also

Using Moodle forum discussions: