Migration von MyISAM zu InnoDB

Dieser Artikel ist für Moodle-Administrator/innen gedacht, die eine MySQL-Datenbank verwenden. Es geht um die Umwandlung der MySQL storage engine.

Unterschied zwischen MyISAM und InnoDB

• read [1]
• 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 Konfiguration

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.

Verschiedene Methoden aus den Moodle-Foren

Wählen Sie eine der folgenden Methoden, um die Umwandlung von MyISAM zu InnoDB durchzuführen:

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

Siehe auch

• New moodle.org - Diskussionsbeitrag im Kurs Using Moodle auf moodle.org
• MySQL Engines: MyISAM vs. InnoDB Artikel von Tag1 Consulting
• An easy migration of MyISAM mysqldump to InnoDB