Migration von MyISAM zu InnoDB

Aus MoodleDocs
Version vom 14. Juni 2012, 10:55 Uhr von Gisela Hillenbrand (Diskussion | Beiträge) (Die Seite wurde neu angelegt: „{{Moodle administrieren}} Dieser Artikel ist für Moodle-Administrator/innen gedacht, die eine MySQL-Datenbank verwenden. Es geht um die Umwandlung der [http://de…“)
(Unterschied) ← Nächstältere Version | Aktuelle Version (Unterschied) | Nächstjüngere Version → (Unterschied)
Wechseln zu:Navigation, Suche

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