Wie kann ich eine Datenbank von einem Server auf einen anderen verschieben?

Meine Lieblingsmethode ist es, einen sqldump-Befehl an einen sql-Befehl weiterzuleiten. Sie können alle Datenbanken oder eine bestimmte Datenbank erstellen. So zum Beispiel

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword 

Sie können alle Datenbanken mit tun

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

Das einzige Problem ist, wenn die Datenbank zu groß ist und die Pipe zusammenbricht. In diesem Fall können Sie Tabelle für Tabelle oder eine der anderen unten genannten Methoden ausführen.

Ich habe kürzlich eine 30-GB-Datenbank mit der folgenden Strategie verschoben:

Alter Server

• Stoppen Sie den MySQL-Server
• Kopieren Sie den Inhalt von datadir an einen anderen Speicherort auf der Festplatte (~/mysqldata/*)
• Starten Sie den MySQL-Server erneut (Ausfallzeit 10-15 Minuten)
• komprimiere die Daten (tar -czvf mysqldata.tar.gz ~/mysqldata)
• kopieren Sie die komprimierte Datei auf einen neuen Server

Neuer Server

• installiere mysql (nicht starten)
• komprimierte Datei entpacken (tar -xzvf mysqldata.tar.gz)
• verschieben Sie den Inhalt von mysqldata in das Datadir
• Stellen Sie sicher, dass Ihre innodb_log_file_size auf dem neuen Server identisch ist. Wenn dies nicht der Fall ist, kopieren Sie nicht die alten Protokolldateien ( mysql generiert diese) )
• Starten Sie MySQL

Gemäß dem MySQL 5.0 Certification Study Guide , Kapitel 32, Abschnitt 32.3.4, Seiten 456.457, werden die Bedingungen für die binäre Portabilität beschrieben Folgendes herausbringen:

Die binäre Portabilität ist wichtig, wenn Sie eine binäre Sicherung, die auf einem Computer erstellt wurde, auf einem anderen Computer mit einer anderen Architektur verwenden möchten. Die Verwendung einer binären Sicherung ist beispielsweise eine Möglichkeit, Datenbanken von einem MySQL-Server auf einen anderen zu kopieren.

Für MyISAM bedeutet binäre Portabilität, dass Sie die Dateien für eine MyISAM-Tabelle direkt von einem MySQL-Server auf einen anderen auf einem anderen Computer kopieren können und der zweite Server auf die Tabelle zugreifen kann.

Für InnoDB bedeutet binäre Portabilität, dass Sie die Tablespace-Dateien direkt von einem MySQL-Server auf einem Computer auf einen anderen Server auf einem anderen Computer kopieren können und der zweite Server auf den Tablespace zugreifen kann. Standardmäßig werden alle von einem Server verwalteten InnoDB-Tabellen zusammen im Tabellenbereich gespeichert. Die Portabilität des Tabellenbereichs hängt also davon ab, ob alle einzelnen InnoDB-Tabellen portierbar sind. Wenn auch nur eine Tabelle nicht portierbar ist, ist auch der Tabellenbereich nicht portierbar.

MyISAM-Tabellen und InnoDB-Tablespaces können binär von einem Host auf einen anderen portiert werden, wenn zwei Bedingungen erfüllt sind:

• Beide Maschinen müssen eine Ganzzahlarithmetik mit zwei Komplementen verwenden
• Beide Maschinen müssen das IEEE-Gleitkommaformat verwenden, andernfalls dürfen die Tabellen keine Gleitkommaspalten enthalten (FLOAT oder DOUBLE).

In der Praxis stellen diese beiden Bedingungen eine geringe Einschränkung dar. Zwei-Komplement-Integer-Arithmetik und IEEE-Gleitkommaformat sind die Norm bei moderner Hardware. Eine dritte Bedingung für die Bino-Portabilität von InnoDB ist, dass Sie für Tabellen und Datenbanken Kleinbuchstaben verwenden sollten. Dies liegt daran, dass InnoDB diese Namen unter Windows intern (in seinem Datenwörterbuch) in Kleinbuchstaben speichert. Die Verwendung von Kleinbuchstaben ermöglicht die binäre Portabilität zwischen Windows und Unix. Um die Verwendung von Kleinbuchstaben zu erzwingen, können Sie die folgenden Zeilen in eine Optionsdatei einfügen:

[mysqld]
lower_case_table_names=1

Wenn Sie InnoDB so konfigurieren, dass Tabellenbereiche pro Tabelle verwendet werden, werden die Bedingungen für die binäre Portabilität erweitert, um auch die .ibd-Dateien für InnoDB-Tabellen einzuschließen. (Die Bedingungen für die gemeinsam genutzten Tablespaces gelten weiterhin, da sie das Datenwörterbuch enthalten, in dem Informationen zu allen InnoDB-Tabellen gespeichert sind.)

Wenn die Bedingungen für die binäre Portabilität nicht erfüllt sind, können Sie MyISAM- oder InnoDB-Tabellen von einem Server auf einen anderen kopieren, indem Sie sie in einem Textformat (z. B. mit mysqldump) sichern und erneut auf den Zielserver laden.

Basierend auf der Speicher-Engine gibt es zwei Hauptmethoden zum Verschieben einzelner Tabellen.

Für das gegebene Beispiel nehmen wir Folgendes an:

1. datadir ist/var/lib/mysql
2. datenbank namens mydb
3. tabelle in mydb Datenbank namens mytable.

MyISAM-Tabellen

Wenn mydb.mytable die MyISAM-Speicher-Engine verwendet, wird die Tabelle physisch als drei separate Dateien dargestellt

1. /var/lib/mysql/mydb/mytable.frm (.frm-Datei)
2. /var/lib/mysql/mydb/mytable.MYD (.MYD-Datei)
3. /var/lib/mysql/mydb/mytable.MYI (.MYI-Datei)

Die .frm enthält die Tabellenstruktur
Die .MYD enthält die Tabellendaten
Die .MYI enthält die Tabellenindexseite

Diese Dateien werden voneinander abhängig verwendet, um die Tabelle von einem logischen Standpunkt in MySQL aus darzustellen. Da diesen Dateien keine weitere logische Zuordnung zugeordnet ist, wird eine Tabelle von einem DB-Server auf einen anderen migriert. Sie können dies sogar von einem Windows-Server zu einem Linux-Server oder einem MacOS tun. Natürlich können Sie MySQL herunterfahren und die 3 Tabellendateien kopieren. Sie könnten Folgendes ausführen:

LOCK TABLES mydb.mytable READ;
SELECT SLEEP(86400);
UNLOCK TABLES;

in einer ssh-Sitzung, um die Tabelle als schreibgeschützt zu halten und die Sperre 24 Stunden lang zu halten. Führen Sie eine Sekunde später die Kopie in einer anderen SSH-Sitzung durch. Beenden Sie dann die MySQL-Sitzung mit der 24-Stunden-Sperre. Sie müssen nicht 24 Stunden warten.

InnoDB-Tabellen

Basierend auf dem oben genannten Zitat aus dem Zertifizierungsbuch gibt es viele Faktoren, die bestimmen, wie eine bestimmte InnoDB-Tabelle gesichert wird. Führen Sie der Einfachheit, Klarheit und Kürze halber einfach einen mysqldump der gewünschten Tabelle mit den Parametern --single-transaction durch, um einen perfekten Zeitpunkt-Dump der Tabelle zu erhalten. Sie müssen sich nicht mit der InnoDB-Semantik befassen, wenn Sie nur eine Tabelle möchten. Sie können dieses Dumpfile auf einen beliebigen MySQL-Server Ihrer Wahl laden.

Da hier zwei Fragen zusammengeführt wurden (jcolebrand): EDIT

Wenn Sie mehr als bereit sind, mit einer langsamen DB-Leistung zu leben, können Sie eine Reihe von Rsyncs vom alten Server (ServerA) zum neuen Server (ServerB) ausführen, auch wenn MySQL noch auf ServerA ausgeführt wird.

Schritt 01) Installieren Sie dieselbe Version von MySQL auf ServerB wie ServerA

Schritt 02) Führen Sie auf ServerA SET GLOBAL innodb_max_dirty_pages_pct = 0; von MySQL und ca. 10 Minuten (Dies löscht schmutzige Seiten aus dem InnoDB-Pufferpool. Es hilft auch dabei, MySQL schneller herunterzufahren.) Wenn Ihre Datenbank ausschließlich MyISAM ist, können Sie diesen Schritt überspringen.

Schritt 03) rsync --archive --verbose --stats --partial --progress --human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql

Schritt 04) Wiederholen Sie Schritt 03, bis eine Synchronisierung weniger als 1 Minute dauert

Schritt 05) service mysql stop auf ServerA

Schritt 06) Führen Sie eine weitere Synchronisierung durch

Schritt 07) scp ServerA:/etc/my.cnf ServerB:/etc/

Schritt 08) service mysql start auf ServerB

Schritt 08) service mysql start auf ServerA (optional)

Versuche es !!!

VORBEHALT

Sie können einen solchen Replikations-Slave erstellen. Denken Sie daran, die Server-ID explizit im Master /etc/my.cnf und eine andere Nummer für die Server-ID im Slave /etc/my.cnf festzulegen

Sie benötigen mysqldump nicht einmal, wenn Sie ein ganzes Datenbankschema verschieben, und Sie sind bereit, die erste Datenbank zu stoppen (damit sie bei der Übertragung konsistent ist).

1. Stoppen Sie die Datenbank (oder sperren Sie sie)
2. Wechseln Sie in das Verzeichnis, in dem sich die MySQL-Datendateien befinden.
3. Übertragen Sie den Ordner (und seinen Inhalt) in das MySQL-Datenverzeichnis des neuen Servers
4. Starten Sie die Datenbank
5. Geben Sie auf dem neuen Server den Befehl 'Datenbank erstellen' aus.
6. Erstellen Sie die Benutzer neu und erteilen Sie Berechtigungen.

Ich kann mich nicht erinnern, ob mysqldump Benutzer und Berechtigungen oder nur die Daten verarbeitet ... aber selbst wenn dies der Fall ist, ist dies way schneller als das Ausführen und Ausführen eines Dumps. Ich würde das nur verwenden, wenn ich eine MySQL-Datenbank sichern und dann erneut in ein anderes RDBMS einfügen müsste, wenn ich die Speicheroptionen ändern müsste (innodb vs. myisam) oder wenn ich die wichtigsten Versionen von MySQL ändern würde (aber Ich glaube, ich habe das zwischen 4 und 5 gemacht.

Wenn Sie nur eine bestimmte Tabelle verschieben möchten, versuchen Sie:

mysqldump -u username -ppassword databasename tablename > databasename.tablename.sql

Sie können oben im selben Befehl weitere Tabellennamen angeben. Verschieben Sie nach Abschluss des Befehls die Datei databaseasename.tabellenname.sql auf den anderen Server und stellen Sie sie mit folgenden Daten wieder her:

mysql -u username -ppassword databasename < databasename.tablename.sql

Beachten Sie, dass die hintere SQL-Datei mit dem Programm mysqldump erstellt wird und die Wiederherstellung direkt in mysql erfolgt.

1. Wenn Sie SSH-Zugriff haben, können Sie mysqldump über die Befehlszeile verwenden
2. Wenn Sie keinen SSH-Zugriff haben, aber über einen phpMyAdmin-Zugriff verfügen, können Sie ihn zum Exportieren/Importieren verwenden
3. Wenn Sie keinen phpMyAdmin-Zugriff haben, gibt es einige praktische PHP-Skripte, die ausgegeben und importiert werden (aus eigener Erfahrung habe ich jedoch nie eines gefunden, das so zuverlässig ist wie phpMyAdmin).

Möglicherweise gibt es diese Möglichkeit, in der Sie die eigentlichen Datenbankdateien verschieben (für meine Installation befinden sie sich unter/var/lib/mysql), aber ich bin mir nicht sicher, wie sie sich verhalten/funktionieren werden.

Sie müssen eine Ausfallzeit nehmen. Es wird eine Weile dauern, abhängig von Ihrer Netzwerkgeschwindigkeit. Ich gehe davon aus, dass Sie MySQL unter Linux/Unix ausführen. Hier ist der Prozess, den ich benutze:

1. Stoppen Sie den MySQL-Daemon auf dem Quellhost.
2. Erstellen Sie einen tmp-Ordner auf Ihrem Zielhost, um die Dateien zu empfangen.
3. Verwenden Sie Bildschirm, um eine Shell-Sitzung zu erstellen, die überlebt, wenn Ihr SSH getrennt wird.
4. Verwenden Sie rsync, um die Dateien zwischen Hosts zu übertragen. So etwas wie: rsync -avhP Quellbenutzer @ targethost:/path/to/folder /
5. Führen Sie Ihre Testfälle aus, um sicherzustellen, dass Sie bei der Übertragung nichts verloren haben.

Fahren Sie dann wie gewohnt fort, um das lokale MySQL einzurichten.

* Hinweis: Sie können den Parameter -c auch mit rsync verwenden, um der Übertragung eine Prüfsumme hinzuzufügen. Dies ist jedoch abhängig von der CPU-Geschwindigkeit langsam.

Ich denke, dass alle früheren Antworten wahrscheinlich gut funktionieren, aber das Problem des Festlegens eines Datenbanknamens während der Übertragung nicht wirklich ansprechen.

So habe ich es gerade mit bash gemacht:

Möglicherweise ist es besser, rsync als scp zu verwenden und die Datei nicht zu komprimieren, wenn Sie dies häufig tun.

Auf meinem Quellserver:

[email protected]:~$ d=members
[email protected]:~$ mysqldump $d | gzip > $d.sql.gz
[email protected]:~$ scp -i .ssh/yourkeynamehere $d.sql.gz $sbox:$d.sql.gz

Auf meinem Zielserver:

[email protected]:~$ d1=members
[email protected]:~$ d2=members_sb
[email protected]:~$ mysqladmin create $d2
[email protected]:~$ cat $d1.sql.gz | gunzip |  mysql $d2

Auf beiden Computern, um den Fortschritt zu sehen:

[email protected]:~$ ls *.gz 
[email protected]:~$ cat $d.sql.gz | gunzip |  less

Dies alles setzt voraus, dass Sie auf beiden Computern die Datei MySQL-Konfiguration in Ihrem Home-Verzeichnis haben und die Berechtigungen festlegen:

$ echo "
[client]
user=drupal6
password=metoknow
Host=ord-mysql-001-sn.bananas.com
[mysql]
database=nz_drupal" > .my.cnf
$ chmod 0600 ~/.my.cnf

Ich kann bestätigen, dass die Methode von DTest auch zum Kopieren zwischen Ubuntu und Osx funktioniert.

So kopieren Sie alle Datenbanken, ohne Dumping oder ähnliches durchführen zu müssen:

Stellen Sie sicher, dass Sie ein sauberes MySQL von MySQL haben (installiert das von MySQL heruntergeladene dmg http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.63-osx10.6-x86_64.dmg ), dass (SEHR WICHTIG) noch nie ausgeführt wurde.

Kopieren Sie den Inhalt des Ordners/var/lib/mysql/vom Ubuntu-Computer über/usr/local/mysql/data/content auf dem Mac. Um Zugriff auf den Ordner auf dem Ubuntu-Computer zu erhalten, musste ich Sudo verwenden, d.h.

Sudo cp /var/lib/mysql /home/foouser/mysql_data_folder
Sudo chown -R foouser /home/foouser/mysql_data_folder

Ich habe den Ordner mit scp kopiert.

Bevor Sie beginnen, nehmen Sie eine Kopie des MySQL-Ordners auf dem Mac, um sicherzustellen, dass Sie nichts vermasseln.

Gehen Sie nach dem Kopieren des Ordners auf dem Mac wie folgt vor:

Sudo chown -R _mysql /usr/local/mysql/data/
Sudo chgrp -R wheel /usr/local/mysql/data/
Sudo chmod -R g+rx /usr/local/mysql/data/

Starten Sie den MySQL-Server zum ersten Mal (im Einstellungsbereich unter Systemeinstellungen-> MySQL). Alle Benutzer und Datenbanken sollten jetzt korrekt eingerichtet sein.

Dies funktionierte mit MySQL 5.1.61 unter Ubuntu 64 Bit 11.10 und MySQL 5.1.63 unter Osx Lion (MacBook Pro).

verschieben Sie es auf eine andere MySQL-Server-Datenbank? Wenn ja, exportieren Sie es

# mysqldump -u username -ppassword database_name > FILE.sql

Generische Linux-Methode:

/etc/init.d/mysqld stop
rsync -avz source_files destination
vi /etc/my.cnf

bearbeiten Sie dann den Datenverzeichnis (und den Socket) für mysqld und mysqld_safe (falls zutreffend), um auf den neuen Speicherort zu verweisen

/etc/init.d/mysql start

Ich habe dies gepostet, weil niemand einfach die geringste Anzahl von Schritten aufzulisten schien und ich denke, dass dies der einfachste Weg persönlich ist.

Ich würde die zwei einfachen Schritte vorschlagen, um die gesamte Datenbank von einem Server auf einen anderen zu übertragen.

Schritt 1 : Führen Sie eine vollständige Sicherung der Datenbanken im Quellserver mit mysqldump .

Schritt 2 : Mit dem Befehl rsync können Sie die gesamten Datenbanken übertragen der Zielserver.

Vielleicht ist dies eine bessere Möglichkeit:

Version 1: Kopie der Datendateien (nur MYISAM)

ssh server1
service mysql stop
cd $mysql-data-dir
rsync -avz dirs-or-files server2:$mysql-data-dir
service mysql start

ssh server2 service starte mysql neu

• Wenn Ihre Datenbankdateien schreibgeschützt sind, können Sie das Stoppen des Servers überspringen.

Version 2: mysqldump

Installieren Sie pigz - auf modernen Xeon- oder Opteron-Prozessoren, insbesondere wenn Sie zwei oder mehr CPUs haben, ist es VIEL schneller als gzip.

ssh server1 
mysqldump ... | pigz > backup-YYMDD.sql.gz
rsync backup-YYMDD.sql.gz server:location

ssh server2
pigz -dc location/backup-YYMDD.sql.gz | mysql ..

Version: Master/Slave + Mysqldump/Dateikopie

In HA environment you should use the following trick:
setup slave server & do all backups from it
before backups - do "slave stop"; 
then do version 1 or version 2

skript:

touch full.start
mysqladmin -h slave-db stop-slave
echo "show slave status \G" | mysql -h slave-db > FULL/comfi-$NOW.master-position
/usr/bin/mysqldump -h slave-db --default-character-set=utf8 -A --opt --skip-lock-tables | pigz > "FULL/XXXX-$NOW.sql.gz"
mysqladmin -h slave-db start-slave
touch full.end

ln -fs "FULL/XXXX-$NOW.sql.gz" FULL.sql.gz

PS:

um kleine Tabellen zu kopieren, verwenden Sie:

ssh server1 mysqldump schematabelle | SSH Server2 MySQL-Schema