Wie MySQL-InnoDB Tabellen auf einen anderen Server übertragen?

[Klaus1]

Hallo,

ich habe in einer Datenbank 37 Tabellen, 6 sind vom Typ MyISAM, der Rest InnoDB, 2 Views und 3 Prozeduren. Der Quellserver läuft unter Windows, der Zielserver unter Linux.

Ich versuche die Datenbank mittels mysqldump zu sichern und dann auf dem Zielserver wieder zu importieren.

Den mysqldump rufe ich wie folgt auf:

mysqldump --single-transaction --add-drop-database --triggers --routines --events --databases MYDB --user=backup --password >c:\tmp\MYDB.sql

Der Export sieht für mich auch erstmal ok aus.

Beim Import auf dem Zielserver bekomme ich aber diverse Meldungen:

# mysql --user=backup --password=pssst

> use MYDB;
> source MYDB.sql;

Z.B. solche Fehler:

ERROR 1813 (HY000): Tablespace '`MYDB`.`answer_variants`' exists.
Query OK, 0 rows affected (0.00 sec)

ERROR 1146 (42S02): Table 'MYDB.answer_variants' doesn't exist
ERROR 1146 (42S02): Table 'MYDB.answer_variants' doesn't exist
ERROR 1146 (42S02): Table 'MYDB.answer_variants' doesn't exist
ERROR 1146 (42S02): Table 'MYDB.answer_variants' doesn't exist
Query OK, 0 rows affected (0.00 sec)

So sieht es für die genannte Tabelle im SQL-Script aus:

--
-- Table structure for table `answer_variants`
--

DROP TABLE IF EXISTS `answer_variants`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `answer_variants` (
  `id` int(11) NOT NULL,
  `variant_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `answer_variants`
--

LOCK TABLES `answer_variants` WRITE;
/*!40000 ALTER TABLE `answer_variants` DISABLE KEYS */;
INSERT INTO `answer_variants` VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H');
/*!40000 ALTER TABLE `answer_variants` ENABLE KEYS */;
UNLOCK TABLES;

Es werden nur die MyISAM-Tabellen erzeugt.

Kann mir jemand sagen, was ich falsch mache?

LG Klaus

[TS]

Hello,

auf die Gefahr, hier wieder mal eine Schlacht zu eröffnen:

InnoDB nur komplett übertragen, oder mittels sehr tiefer Kenntnisse über die Relationen mittels Extract- und Insert-Methoden.

Ohne totalen Zugriff auf den Quelldatenbankserver sehe ich nur Probleme.

Da war MyISAM noch wesentlich pflegeleichter.

Glück Auf
Tom vom Berg

[dedlfix]

Tach!

InnoDB nur komplett übertragen, oder mittels sehr tiefer Kenntnisse über die Relationen mittels Extract- und Insert-Methoden.

Er geht doch über einen Dump mit Statements drin, kein Kopieren von Dateien.

Ohne totalen Zugriff auf den Quelldatenbankserver sehe ich nur Probleme.

Bei so einem Dump mit Statements darf es eigentlich keine gravierend anderen Probleme geben als beim ganz normalen Arbeiten mit Statements.

---

Allerdings, Frage an den OP: Welche bei Google zu findenden Antworten hast du schon probiert und mit welchem Ergebnis? Das meiste da bezieht sich auf den Tablespace. Sind vielleicht die Versionen von MySQL auf den Systemen grundlegend unterschiedlich, so dass der Dump vielleicht noch keine Ahnung vom Tablespace auf dem Zielsystem hat? Muss man da vielleicht auf dem Zielsystem per Hand einen bereits vorhandenen Tablespace löschen?

dedlfix.

[TS]

Hello,

"Dump mit Statements" führt schlussendlich immer zu einer Komplettübertragung.

Bei InnoDB habe ich bisher noch nicht das passende Tool gefunden (außer abgesicherter Komplettübertragung). Tools, die ein Intime-Copying ermöglichen, sind mir da total fremd.

Glück Auf
Tom vom Berg

[dedlfix]

Tach!

"Dump mit Statements" führt schlussendlich immer zu einer Komplettübertragung.

Ich habe den Eindruck, ich verstehe dich grade nicht. Vielleicht beziehst du dich ja auf mysqlhotcopy, was aber beim OP nicht im Einsatz ist. Mit mysqldump kann man jedenfalls recht fein steuern, was im Dump enthalten sein soll.

dedlfix.

[Klaus1]

Hallo,

möglicherweise liegt es tatsächlich an unterschiedlichen MySQL-Versionen?

Die Quelle ist in Version 5.6.11, das Ziel in Version 5.7.20.

Ich hatte auf dem Zielsystem zunächst die gesamte Datenbank manuell (über phpmyadmin) gelöscht, aber theroetisch sollte das Script die DB auch vorher löschen.

Ich habe alle angezeigten Fehlermeldungen durchgesucht, es sind ausschließlich

ERROR 1813 (HY000): Tablespace '`MYDB`.`tabellenname`' exists.

gefolgt von mehreren

ERROR 1146 (42S02): Table 'MYDB.tabellenname' doesn't exist

[Glaskugelinterpreter]

Ich hatte auf dem Zielsystem zunächst die gesamte Datenbank manuell (über phpmyadmin) gelöscht,

Es ist nur schwer vorstellbar, dass dann

use MYDB;

keinen Fehler ausgeworfen haben soll.

Versuch es mal mit:

mysql -u backup -p MYDB < MYDB.sql >> output.log 2>> output.log

und zeige uns dann output.log vollständig.

[Klaus1]

Mit Deiner Kommandozeile bekomme ich ganz andere Ergebnisse 😕

In der SQL-Datei steht am Anfang:

DROP DATABASE IF EXISTS `MYDB`;
CREATE DATABASE IF NOT EXISTS `MYDB` DEFAULT CHARACTER SET utf8;
USE `MYDB`;

Dennoch steht in der output.log nur:

ERROR 1049 (42000): Unknown database 'MYDB'

Danach habe ich auf Linux-Ebene das Verzeichnis /var/lib/mysql/MYDB gelöscht und im mysql die Datenbank angelegt (create database MYDB;)

Jetzt wurden plötzlich alle Tabellen erzeugt und sind über phpmyadmin anzeigbar.

Was auch immer da vorlag, jetzt habe ich alles drüben. Danke Euch für die Hilfe.

LG Klaus

[Glaskugelinterpreter]

vermutlich war es so, wie dedlfix schrieb:

Es scheint, dass da bereits ein Tablespace existiert, der nun im Weg liegt.

Mit dem Löschen des Verzeichnisses /var/lib/mysql/MYDB hast Du auch die zu diesem gehörenden Dateien gelöscht. Allerdings ist dieses Vorgehen sehr unsauber, denn womöglich könnte jetzt

select * from INFORMATION_SCHEMA.TABLESPACES

und

select * from INFORMATION_SCHEMA.FILES

zeigen, dass es für den Tablespace "MYDB" womöglich noch Einträge gibt. Die müss(t)en dann noch weg, weil das zu zukünftigen Fehlern führen kann. Die Informationen dazu, wie MYSQL damit umgeht, wenn dessen Dateien derart wild auf Dateisystemebene und womöglich bei laufenden Server gelöscht werden, sind zumindest im Handbuch "rar". Wahrscheinlich haben die Autoren ein solch wildes Vorgehen nicht mal vorhergesehen…

Andererseits sollte das

DROP DATABASE IF EXISTS `MYDB`;

eigentlich auch den zugehörigen Tablespace MYDB.tabellenname gelöscht haben… Nur weiß hier niemand, was genau Du wann getan hast…

[dedlfix]

Tach!

Ich hatte auf dem Zielsystem zunächst die gesamte Datenbank manuell (über phpmyadmin) gelöscht, aber theroetisch sollte das Script die DB auch vorher löschen.

Beachte, dass Tablespace etwas anderes meint als Database oder Table. Es scheint, dass da bereits ein Tablespace existiert, der nun im Weg liegt. Mir ist so ein Problem noch nicht untergekommen, so dass ich keine konkreten Hinweise geben kann. Probier es mit DROP TABLESPACE.

dedlfix.

[Glaskugelinterpreter]

Kann mir jemand sagen, was ich falsch mache?

Ich jedenfalls nicht. Grund: Bei meinem Test lief das von Dir gezeigte SQL klaglos durch.

Aber die Glaskugel brummt:

> ERROR 1813 (HY000): Tablespace '`MYDB`.`answer_variants`' exists.

Query OK, 0 rows affected (0.00 sec)

Das ist nur ein Auszug aus dem Log. Fehlt da eventuell ein wichtiger Eintrag?

mysql --user=backup --password=pssst

Nämlich der, welcher besagt, dass answer_variants mangels Rechten nicht angelegt wurde? Immerhin hat der Benutzer den Name "backup". Einem solchen würde ich kein Recht zu irgendwelchen Veränderungen einräumen. Lesen darf der aber alles...

Ich habe für meinen Test einen Benutzer genommen, der in der Test-Datenbank "alles" darf außer Rechte vergeben.

[Klaus1]

Sorry, hätte ich vielleicht erwähnen sollen, der Backup-User hat komplett alle Rechte (grant all privileges), da ich mir einen restore-User gespart habe 😉

Ich habe alle angezeigten Fehlermeldungen durchgesucht, es sind ausschließlich

ERROR 1813 (HY000): Tablespace '`MYDB`.`tabellenname`' exists.

gefolgt von mehreren

ERROR 1146 (42S02): Table 'MYDB.tabellenname' doesn't exist

LG Klaus