Wie konvertiert man alle Tabellen von MyISAM in InnoDB?
Ich weiß, dass ich eine Änderungstabelle einzeln ausgeben kann, um den Tabellenspeicher von MyISAM in InnoDB zu ändern.
Ich frage mich, ob es eine Möglichkeit gibt, alle schnell auf InnoDB umzustellen?
<?php
// connect your database here first
//
// Actual code starts here
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE = 'MyISAM'";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
mysql_query($sql);
}
?>
Führen Sie diese SQL-Anweisung aus (im MySQL-Client, phpMyAdmin oder wo auch immer), um alle MyISAM-Tabellen in Ihrer Datenbank abzurufen.
Ersetzen Sie den Wert der Variablen name_of_your_db durch Ihren Datenbanknamen.
SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;
Kopieren Sie dann die Ausgabe und führen Sie sie als neue SQL-Abfrage aus.
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';
Klappt wunderbar.
Dadurch erhalten Sie eine Liste aller Tabellen mit den Änderungsabfragen, die Sie in einem Stapel ausführen können
Ersetzen Sie in den nachstehenden Skripts <Benutzername>, <Kennwort> und <Schema> durch Ihre spezifischen Daten.
Um die Anweisungen anzuzeigen, die Sie in eine MySQL-Client-Sitzung kopieren und einfügen können, geben Sie Folgendes ein:
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
Um die Änderung einfach auszuführen, verwenden Sie Folgendes:
echo 'SHOW TABLES;' \
| mysql -u <username> --password=<password> -D <schema> \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -u <username> --password=<password> -D <schema>
KREDIT: Dies ist eine Variation dessen, was in diesem Artikel skizziert wurde.
Eine Linie:
mysql -u root -p dbName -e
"show table status where Engine='MyISAM';" | awk
'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' |
mysql -u root -p dbName
Verwenden Sie dies als SQL-Abfrage in Ihrem phpMyAdmin
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';
Sie können diese Anweisung im Befehlszeilenprogramm von mysql ausführen:
echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
FROM Information_schema.TABLES
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql
Möglicherweise müssen Sie Benutzername und Kennwort angeben: mysql -u Benutzername -p Das Ergebnis ist ein SQL-Skript, das Sie in mysql zurückleiten können:
mysql name-of-database < convert.sql
Ersetzen Sie "Datenbankname" in der obigen Anweisung und Befehlszeile.
Es ist sehr einfach, es gibt nur zwei Schritte, einfach kopieren und einfügen:
schritt 1.
SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;
(Kopieren und Einfügen aller Ergebnisse in der SQL-Registerkarte)
schritt 2: (alle Ergebnisse in die SQL-Registerkarte kopieren) und unten in die Zeile einfügen
START TRANSACTION;
VERPFLICHTEN;
zB . START TRANSACTION;
ALTER TABLE admin_files ENGINE = InnoDB;
VERPFLICHTEN;
Es wurde noch nicht erwähnt, also schreibe ich es für die Nachwelt:
Wenn Sie zwischen DB-Servern migrieren (oder aus einem anderen Grund Ihr Dta sichern und erneut laden), können Sie die Ausgabe von mysqldump einfach ändern:
mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;
Laden Sie es dann erneut:
mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql
(In meiner begrenzten Erfahrung kann dies auch ein viel schnellerer Prozess sein als das Ändern der Tabellen "live". Dies hängt wahrscheinlich von der Art der Daten und Indizes ab.)
Hier ist eine Möglichkeit für Django-Benutzer:
from Django.core.management.base import BaseCommand
from Django.db import connections
class Command(BaseCommand):
def handle(self, database="default", *args, **options):
cursor = connections[database].cursor()
cursor.execute("SHOW TABLE STATUS");
for row in cursor.fetchall():
if row[1] != "InnoDB":
print "Converting %s" % row[0],
result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
print result
Fügen Sie das zu Ihrer App unter den Ordnern management/command/hinzu. Anschließend können Sie alle Ihre Tabellen mit dem Befehl manage.py konvertieren:
python manage.py convert_to_innodb
Um ALTER-Anweisungen für alle Tabellen in allen Nicht-System-Schemas zu generieren, die nach diesen Schemata/Tabellen geordnet sind, führen Sie Folgendes aus:
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, table_name DESC;
Führen Sie danach diese Abfragen über einen Client aus, um die Änderung durchzuführen.
- Die Antwort basiert auf den obigen Antworten, verbessert jedoch die Schemabehandlung.
In mysql können Sie Suchen/Ersetzen mit einem Texteditor verwenden:
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
Hinweis: Sie sollten information_schema und mysql wahrscheinlich ignorieren, da "die Datenbanken mysql und information_schema, die einige der MySQL-Interna implementieren, immer noch MyISAM verwenden. Insbesondere können Sie die Grant-Tabellen nicht zur Verwendung von InnoDB wechseln." ( http://dev.mysql.com/doc/refman/5.5/de/innodb-default-se.html )
Beachten Sie in jedem Fall die Tabellen, die ignoriert und ausgeführt werden sollen:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
Kopieren Sie einfach diese Liste in Ihren Texteditor und suchen/ersetzen Sie "|" mit "ALTER TABLE" usw.
Sie haben dann eine Liste wie diese, die Sie einfach in Ihr mysql-Terminal einfügen können:
ALTER TABLE arth_commentmeta ENGINE=Innodb;
ALTER TABLE arth_comments ENGINE=Innodb;
ALTER TABLE arth_links ENGINE=Innodb;
ALTER TABLE arth_options ENGINE=Innodb;
ALTER TABLE arth_postmeta ENGINE=Innodb;
ALTER TABLE arth_posts ENGINE=Innodb;
ALTER TABLE arth_term_relationships ENGINE=Innodb;
ALTER TABLE arth_term_taxonomy ENGINE=Innodb;
ALTER TABLE arth_terms ENGINE=Innodb;
ALTER TABLE arth_usermeta ENGINE=Innodb;
Wenn dies für Ihren Texteditor nicht einfach ist, finden Sie hier eine weitere Lösung, mit der Sie eine ähnliche Liste (die Sie in mysql einfügen können) für nur ein Präfix Ihrer Datenbank (vom Linux-Terminal) erhalten:
mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"
Eine einfache MySQL-Version.
Sie können einfach die ausführbare Datei von mysql starten, die Datenbank verwenden und die Abfrage kopieren und einfügen.
Dadurch werden alle MyISAM-Tabellen in der aktuellen Datenbank in INNODB-Tabellen umgewandelt.
DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
WHERE `TABLE_SCHEMA` LIKE DATABASE()
AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
IF @convertTable IS NULL THEN
LEAVE mainloop;
END IF;
SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
PREPARE convertTables FROM @sqltext;
EXECUTE convertTables;
DEALLOCATE PREPARE convertTables;
SET @convertTable = NULL;
END LOOP mainloop;
END//
DELIMITER ;
CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;
Einige Korrekturen an diesem util-Skript
SET @DATABASE_NAME = 'Integradb';
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;
Versuchen Sie dieses Shell-Skript
DBENGINE='InnoDB' ;
DBUSER='your_db_user' ;
DBNAME='your_db_name' ;
DBHOST='your_db_Host'
DBPASS='your_db_pass' ;
mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done
Ich habe gerade einen anderen (einfachen?) Weg getestet und für mich gearbeitet.
Exportieren Sie Ihre Datenbank einfach als .sql-Datei, bearbeiten Sie sie mit gedit oder Notepad.
Ersetzen Sie ENGINE=MyISAM durch ENGINE=INNODB und speichern Sie die bearbeitete Datei
Anzahl oder Ersatz sollte die Anzahl Ihrer Tabellen sein
Importiere es in MySQL (phpMyAdmin oder Kommandozeile)
Und Voila!
Sie können ein Skript schreiben, um es in Ihrer bevorzugten Skriptsprache auszuführen. Das Skript würde Folgendes tun:
- Ausgabe
SHOW FULL TABLES. - Prüfen Sie für jede zurückgegebene Zeile, ob in der zweiten Spalte
'BASE TABLE'und nicht'VIEW'angegeben ist. - Wenn es nicht
'VIEW'ist, geben Sie den entsprechendenALTER TABLE-Befehl aus.
In meinem Fall habe ich von einer MySQL-Instanz mit einem Standardwert von MyISAM zu einer MariaDB-Instanz mit einem DEFAULT von InnoDB migriert.
Per MariaDB-Migrationsdokumente.
Beim alten Serverlauf:
mysqldump -u root -p --skip-create-options --all-databases > migration.sql
Die --skip-create-options stellen sicher, dass der Datenbankserver beim Laden der Daten anstelle von MyISAM die Standardspeicher-Engine verwendet.
mysql -u root -p < migration.sql
Dies hat einen Fehler beim Erstellen von mysql.db ausgelöst, aber jetzt funktioniert alles super :)
Ich bin ein Neuling und musste meine eigene Lösung finden, da Mysql-Befehle im Web normalerweise mit Schreibfehlern durchsetzt sind, die einen echten Alptraum für die Menschen schaffen, die gerade erst anfangen. Hier ist meine Lösung ....
Anstelle von 1 Befehl pro Tabelle bereitete ich Dutzende von Befehlen (bereit zum Kopieren und Einfügen) auf einmal mit Excel vor.
Wie? Erweitern Sie Ihr PuTTY-Fenster, geben Sie mysql ein und führen Sie den Befehl "SHOW TABLE STATUS;" aus. und das Kopieren/Einfügen der Ausgabe in Microsoft Excel. Gehen Sie zur Registerkarte Daten und verwenden Sie die Funktion "Text in Spalten", um die Spalten mit einem Leerzeichen zu begrenzen. Sortieren Sie dann die Spalten nach der jeweiligen Spalte, in der Ihre Tabellentypen angezeigt werden, und löschen Sie alle Zeilen, für die die Tabellen bereits im InnoDb-Format vorliegen (da keine Befehle für sie ausgeführt werden müssen, sind sie bereits erledigt). Fügen Sie dann 2 Spalten links von der Tabellenspalte und 2 Spalten rechts ein. Fügen Sie dann den ersten Teil des Befehls in Spalte-1 ein (siehe unten). Spalte 2 sollte nur ein Leerzeichen enthalten. Spalte 3 ist Ihre Tabellenspalte. Spalte 4 sollte nur ein Leerzeichen enthalten. Spalte 5 ist der letzte Teil Ihres Befehls. Es sollte so aussehen:
column-1 column-2 column-3 column-4 column-5
ALTER TABLE t_lade_tr ENGINE=InnoDB;
ALTER TABLE t_foro_detail_ms ENGINE=InnoDB;
ALTER TABLE t_ljk_ms ENGINE=InnoDB;
Dann kopiere und füge jeweils etwa 5 Zeilen in mysql ein. Dies wird etwa 5 gleichzeitig konvertieren. Ich bemerkte, wenn ich mehr als einmal tat, würden die Befehle fehlschlagen.
<?php
// connect your database here first
mysql_connect('Host', 'user', 'pass');
$databases = mysql_query('SHOW databases');
while($db = mysql_fetch_array($databases)) {
echo "database => {$db[0]}\n";
mysql_select_db($db[0]);
$tables = mysql_query('SHOW tables');
while($tbl = mysql_fetch_array($tables)) {
echo "table => {$tbl[0]}\n";
mysql_query("ALTER TABLE {$tbl[0]} ENGINE=MyISAM");
}
}
<?php
// Convert all MyISAM tables to INNODB tables in all non-special databases.
// Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.
if($argc < 4)
exit("Usage: {$argv[0]} <Host> <username> <password>\n");
$Host = $argv[1];
$username = $argv[2];
$password = $argv[3];
// Connect to the database.
if(!mysql_connect($Host, $username, $password))
exit("Error opening database. " . mysql_error() . "\n");
// Get all databases except special ones that shouldn't be converted.
$databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
if($databases === false)
exit("Error showing databases. " . mysql_error() . "\n");
while($db = mysql_fetch_array($databases))
{
// Select the database.
if(!mysql_select_db($db[0]))
exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
printf("Database: %s\n", $db[0]);
// Get all MyISAM tables in the database.
$tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
if($tables === false)
exit("Error showing tables. " . mysql_error() . "\n");
while($tbl = mysql_fetch_array($tables))
{
// Convert the table to INNODB.
printf("--- Converting %s\n", $tbl[0]);
if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
}
}
mysql_close();
?>
verwenden Sie diese Zeile, um das Datenbankmodul für eine einzelne Tabelle zu ändern.
ALTER TABLE table_name ENGINE = INNODB;
Dies ist ein einfaches PHP-Skript.
<?php
@error_reporting(E_ALL | E_STRICT);
@ini_set('display_errors', '1');
$con = mysql_connect('server', 'user', 'pass');
$dbName = 'moodle2014';
$sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
$rs = mysql_query($sql, $con);
$count = 0;
$ok = 0;
while($row = mysql_fetch_array($rs)){
$count ++;
$tbl = $row[0];
$sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
$resultado = mysql_query($sql);
if ($resultado){
$ok ++;
echo $sql."<hr/>";
}
}
if ($count == $ok){
echo '<div style="color: green"><b>ALL OK</b></div>';
}else{
echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
}
Noch eine andere Option ... Hier ist, wie man es in Ansible macht. Es wird davon ausgegangen, dass sich der Name Ihrer Datenbank in dbname befindet und Sie den Zugriff bereits konfiguriert haben.
- name: Get list of DB tables that need converting to InnoDB
command: >
mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
register: converttables
check_mode: no
changed_when: False
- name: Convert any unconverted tables
command: >
mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
with_items: "{{ converttables.stdout_lines }}"
für mysqli connect;
<?php
$Host = "Host";
$user = "user";
$pass = "pss";
$database = "db_name";
$connect = new mysqli($Host, $user, $pass, $database);
// Actual code starts here Dont forget to change db_name !!
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db_name'
AND ENGINE = 'MyISAM'";
$rs = $connect->query($sql);
while($row = $rs->fetch_array())
{
$tbl = $row[0];
$sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
$connect->query($sql);
} ?>