Remote Backup einer Datenbank mysqldump klappt nicht

Achso, ja das ist dann aber wirklich dumm. Auf dem Zielserver benutzt man einfach den User, der sowieso die Rechte an seiner DB hat.

Rob

Der wird nur @'localhost' sein. Daher braucht er einen neuen. Für mysql ist sowieso der ganze String 'USER'@'HOST' immer ein eigener User somit ist egal wie dass dann benannt wird.
 
Er legt den Backupuser am ZIEL Server an

Also wenn ich @SolarCatcher richtig verstanden habe, wird der user backup am Quellhost angelegt, von dem aus ich dumpen will.

Was ich jetzt am Quellhost gemacht habe:

Code:
root@localhost [(none)]> create user 'backup'@'%' identified by 'backup';
Query OK, 0 rows affected (0.04 sec)

root@localhost [(none)]> create user 'backup'@'localhost' identified by 'backup';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> grant all on *.* 'backup'@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''backup'@'%'' at line 1
root@localhost [(none)]> grant all on *.* to 'backup'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> grant all on *.* to 'backup'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>

Und wenn ich weiterhin die Syntax von mysqldump richtig verstehe, für den Fall, dass man vom Quellhost verschieben will, kommt mysqldump ... Quellhost | mysql ... Zielhost.

So habe ich also eingegeben:

Code:
$ mysqldump -u backup -pbackup -C ejemplo | mysql -h 192.168.1.55 -u root -pwerner ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'amd64.local' is not allowed to connect to this MySQL server
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Got errno 32 on write
$

amd64.local ist der Quellhost mit der IP 192.168.1.34. Wieso klappt das jetzt dennoch nicht?
 
Oder man speichert den Dump lokal zwischen und macht es in zwei Schritten. Sooo groß wird die DB ja nicht sein und dann wird sicher deutlich verständlicher. :)
 
Oder man speichert den Dump lokal zwischen und macht es in zwei Schritten. Sooo groß wird die DB ja nicht sein und dann wird sicher deutlich verständlicher. :)

Die Datenbank ist 5,6 GB groß und es soll in einem Schritt gemacht werden.

Weil das nicht klappen kann. Du brauchst den User am Zielhost, und musst auch dort die mysql auf 0.0.0.0 setzen.

Oder du verbindest dich vom Zielhost aus zum Quellhost.. Aber das hatten wir ja jetzt alles schon..

Hatte bereits beide Rechner auf 0.0.0.0 gestetzt in der /etc/rc.conf. War doch richtig so, oder? War die danach von mir angegebene telnet-Ausgabe korrekt?

Eine weitere Sache ist hier, wie ich festgestellt habe, auch nicht beachtet worden: Offensichtlich muss auf dem Zielhost, auf dem die Datenbank landen soll, zuerst eine leere Datenbank erstellt werden?
Ich habe jetzt auf beiden Rechnern einen user 'backup'@'%' mit allen grants erstellt.

Hier jetzt meine letzten Ausgaben vom Quellhost vor und nach dem Erstellen einer leeren Datenbank auf dem Zielhost.

Zunächst nocheinmal das, was nicht klappen kann:


Code:
$ mysqldump -u backup -pbackup -C ejemplo | mysql -h 192.168.1.55 -u root -pwerner ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'amd64.local' is not allowed to connect to this MySQL server
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Got errno 32 on write

(Zielhost ist wie gesagt 192.168.1.55, Syntax also richtig?)

Danach das Gleiche beide Male mit jeweiligem user backup. Hier schon anders, Fehlermeldung, dass Datenbank ejemplo nicht existiert. Kann nur auf dem Zielhost der Fall sein, denn da soll sie ja hin.

Code:
$ mysqldump -u backup -pbackup -C ejemplo | mysql -h 192.168.1.55 -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
ERROR 1049 (42000): Unknown database 'ejemplo'
mysqldump: Got errno 32 on write

Der Parameter --add-drop-database ändert daran nichts:

Code:
$ mysqldump -u backup -pbackup -C --add-drop-database ejemplo | mysql -h 192.168.1.55 -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
ERROR 1049 (42000): Unknown database 'ejemplo'
mysqldump: Got errno 32 on write

Danach habe ich die Datenbank auf dem Zielhost erstellt (create database ejemplo;), und jetzt neue Fehlermeldung:

Code:
$ mysqldump -u backup -pbackup -C ejemplo | mysql -h 192.168.1.55 -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysqldump: Got errno 32 on write

Das Gleiche, wenn ich es vom Zielhost aus versuche:

Code:
$ mysqldump -h 192.168.1.34 -u backup -pbackup -C ejemplo | mysql -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'ejemplo'
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Got errno 32 on write
$ mysqldump -h 192.168.1.34 -u backup -pbackup -C --add-drop-database ejemplo | mysql -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'ejemplo'
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Got errno 32 on write

Nach Erstellen der Datenbank auf dem Zielhost (siehe oben) auch auf dem Zielhost wieder:

Code:
$ mysqldump -h 192.168.1.34 -u backup -pbackup -C --add-drop-database ejemplo | mysql -u backup -pbackup ejemplo
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysqldump: Got errno 32 on write

Nachtrag: Habe noch das hier gefunden, da kann ich leider nichts mit anfangen:

https://bugs.mysql.com/bug.php?id=68038
 
mysqldump -h 192.168.1.34 -u backup -pbackup -C --add-drop-database --databases ejemplo | mysql -u backup -pbackup

Wenn er dann noch meckert hast du vielleicht vor lauter rumprobieren was zerschossen :D
 
mysqldump -h 192.168.1.34 -u backup -pbackup -C --add-drop-database --databases ejemplo | mysql -u backup -pbackup

Wenn er dann noch meckert hast du vielleicht vor lauter rumprobieren was zerschossen :D

Ich habe es mit verschiedenen Datenbanken versucht, es klappt gar keine! Immer die gleiche Meldung (Error 1840...). Das ist nun echt besorgniserregend, weil da doch ein anderer Fehler zu sein scheint?
 
Hast du die mysql auf beiden Servern ganz normal mit pkg installiert? Und bis auf die bind-options nichts verändert? GTIDs sollten da eigentlich nichts zu melden haben..
 
Hast du die mysql auf beiden Servern ganz normal mit pkg installiert? Und bis auf die bind-options nichts verändert? GTIDs sollten da eigentlich nichts zu melden haben..

Ja. Was bedeutet denn die Erklärung, auf die ich oben verlinkt habe?

Code:
[10 Jan 2013 12:43] Jon Stephens

Fixed in 5.6+, documented as follows in the 5.6.11 and 5.7.1 changelogs:

        In order to provision or to restore a server using GTIDs, it is
        possible to set @@GLOBAL.GTID_PURGED to a given GTID set
        specifying the transactions that were imported. This operation
        requires that @@GLOBAL.GTID_EXECUTED as well as
        @@GLOBAL.GTID_PURGED are empty, in order to avoid possible
        actions that would override server executed GTIDs. When this
        requirement was not met, the error message -GTID_PURGED can only
        be set when GTID_EXECUTED is empty- did not specify the scope of
        the affected variables.

        In order to avoid confusion, error messages that refer to
        variables relating to GTIDs now include the variable scope.

Closed.
 
Das sagt nur, dass du in so einem Fehlerfall ne verständlichere Fehlermeldung bekommst. Die Frage ist wieso deine Server überhaupt mit GTIDs rumscheißen, die sollten ihnen egal sein.
Was du probieren kannst:

Beim mysqldump noch --set-gtid-purged=OFF angeben.

oder/und

auf dem Zielserver (ACHTUNG!) "reset master;" ausführen. Das sollte dort alle Logs löschen und die GTIDs resetten.
 
Danke! Mit dem Parameter --set-gtid-purged=OFF hat es endlich geklappt! Was ich nicht ganz verstehe ist
auf dem Zielserver (ACHTUNG!) "reset master;" ausführen. Das sollte dort alle Logs löschen und die GTIDs resetten.

"reset master" wo/wie genau? In der mysql-Konsole? Die Datenbank (wie gesagt 5,6 GB groß) ist aber leider nicht ganz auf dem Zielhost (Laptop mit 2 GB RAM und 2 GB swap) angekommen. Liegt aber wahrscheinlich dann am Speicherlimit, obwohl während des ganzen Vorgangs ein "top" auf dem Laptop die swap die ganze Zeit als unbenutzt gemeldet hat, was ich dann nicht verstehe...
 
Bitte genauer? Und macht es einen Unterschied, ob mysql auf FreeBSD oder auf Windows installiert ist? Auf beiden Rechnern habe ich FreeBSD installiert, ich hätte aber auch noch ein Laptop mit Windows.
Das soll heißen, auf dem Quell-Host einloggen. Dann auf den lokalen MySQL-Server mit mysql und dem User (-u) zugreifen, der den Dump durchführen soll und den Host (-h) nutzen, der auch später beim Dump benutzt wird, also localhost oder 127.0.0.1.
Anschließend mit mysql auf den MySQL-Server auf dem Zielhost zugreifen. In beiden Fällen die Datenbank abfragen, die kopiert werden soll. Das muss in beiden Fällen klappen.

Eigentlich sollte das keinen Unterschied machen, ob das FreeBSD oder Windows ist. Wichtig sind die beteiligten Hosts und Datenbanken.
 
Danke! Mit dem Parameter --set-gtid-purged=OFF hat es endlich geklappt! Was ich nicht ganz verstehe ist


"reset master" wo/wie genau? In der mysql-Konsole? Die Datenbank (wie gesagt 5,6 GB groß) ist aber leider nicht ganz auf dem Zielhost (Laptop mit 2 GB RAM und 2 GB swap) angekommen. Liegt aber wahrscheinlich dann am Speicherlimit, obwohl während des ganzen Vorgangs ein "top" auf dem Laptop die swap die ganze Zeit als unbenutzt gemeldet hat, was ich dann nicht verstehe...
Das wurde Dir von mysqldump als Warnung auch empfohlen. :-)
reset master wird, bei Bedarf, in der MySQL Konsole abgesetzt. Da Du aber keine Replikation einsetzt, sollte das nicht nötig sein.
Sagt das Errorlog von MySQL etwas bez. Import? Früher musste man den Server etwas anpassen, wenn man größere Dumps importieren wollte.
https://docs.oracle.com/cd/E19182-01/820-6323/gicxk/index.html
 
Zumindest früher musste man da max_allowed_packet hochschrauben, wenn man eine Zeile hat, die über 4MB an Daten hat. Bei Binary-Blobs ist das schnell mal der Fall.
 
Das wurde Dir von mysqldump als Warnung auch empfohlen. :-)

Habe ich später auch gesehen, richtig... Noch Danke für den Hinweis bezüglich des Artikels von Oracle.

Doch ist die Konfigurationsdatei in der der Wert "max_allowed_packet" geändert werden muss in der "my.ini" oder in der "my.cnf"?

Hier der Inhalt meiner my.cnf, in der dieser Parameter auftaucht:

Code:
$ more /usr/local/etc/mysql/my.cnf
# $FreeBSD: head/databases/mysql57-server/files/my.cnf.sample.in 414707 2016-05-06 14:39:59Z riggs $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:76M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick
$
 
Ich wüsste nicht, dass mysql eine my.ini liest. Aber 64M sollte eigentlich genügen. Oder hast du Dinge wie Videos in deiner DB gespeichert?
Das ist aber definitiv keine default my.cnf wie sie freebsd anlegt, und da ich schon gefragt habe, ob du etwas an der installation verändert hast...
 
Ich wüsste nicht, dass mysql eine my.ini liest.

Sagt der Artikel von Oracle, auf den oben verwiesen wird. Ich vermute, das bezieht sich vielleicht auf eine ältere Version und mittlerweile ist es my.cnf? Wenn das vielleicht noch jemand klären könnte?
  1. To set max_allowed_packet:
  2. Open the "my.ini" file under the MySQL server install directory.
  3. Search for the "max_allowed_packet" parameter. If the file does not have it, add the parameter to the file.
  4. Set the value as needed. To set the value to 1GB, enter the value as one of the following:max_allowed_packet=1073741824 max_allowed_packet=1G
  5. Restart the MySQL Server.
 
Unter Freebsd nimmt er die /usr/local/etc/mysql/my.cnf bzw. /usr/local/etc/my.cnf. Hängt glaub ich von der Version ab. Natürlich nur wenn du nichts anderes setzt z.b. in der rc.conf.
Dort unter dem [mysqld] Teil die Werte entsprechend setzen. Aber wird halt nur was bringen, wenn du auch wirklich so große Zeilen hast.
Beim Dumpenden-Server den Spass bei [mysqldump] setzen.
 
Die Datenbank ist 5,6 GB groß und es soll in einem Schritt gemacht werden.
Warum in einem Schritt? Wenn es wirklich um ein Backup geht, ist ein mysqldump | mysql konzeptionell völliger Murks und wird fast sicher irgendwann zu Tränen führen. Da einfach unendlich viel bei schiefgehen. Es geht damit los, dass du dein Backup überschreibst, bevor du ein neues Backup gezogen hast. Was ist, wenn gerade während des Backuplaufs der Server stirbt? Oder wenn dir nach dem Backuplauf auffällt, dass auf dem Server etwas kaputt ist und das Backup bräuchtest? Dazu kommt die Möglichkeit eines Netzwerkfehlers, stirbt dir dir Netzwerkverbindung während des Backuplaufs, ist das Backup im Eimer.

Wenn man Offline-Backups machen möchte, sollte man tunlichst ein Script um mysqldumo herumstricken. Das Script führt mysqldump aus und komprimiert die Ausgabe, legt sie im Dateisystem ab und löscht sie erst nach einiger Zeit. So hat man immer eine gewisse Anzahl Backups, über welche man im Problemfall zurückgehen kann. Außerdem berechnet das idealerweise noch eine Prüfsumme auf den Dump, anhand derer man später Bitrot ausschließen kann. Und es prüft die Fehelrcodes, gab es einen Fehler reagiert es entsprechend.

Wenn man ein Online-Backup machen möchte, nimmt man Replikation. Ein Replication Slave ist mit MySQL schnell aufgesetzt und auch einfach zu managen. Er hat den großen Vorteil, dass man Änderungen, die auf den Server geschrieben wurden, spätestens nach einigen Sekunden in der lokalen Kopie hat. Das verkürzt Zeitfenster zwischen letztem Backup und dem Ausfall des Servers massiv.

Wenn es aber nur um eine lokale Kopie zum Testen oder Entwickeln geht, ist alles gut. :)
 
Das ist aber definitiv keine default my.cnf wie sie freebsd anlegt, und da ich schon gefragt habe, ob du etwas an der installation verändert hast...

Ich habe nichts an der Istallation geändert. Da war höchstens ein Upgrade von 5.6 auf 5.7 im Spiel. Aber die my.cnf auf dem Laptop mit einer Frischinstallation von FreeBSD und mysql 5.7 hat genau den gleichen Inhalt.

Und Danke auch für Deinen letzten Beitrag @Yamagi , das war sehr lehrreich. In der Tat wurde auch nicht die gesamte Datenbank von 5,6 GB übertragen, es gab einen Fehler. Aber es ging um eine Aufgabenstellung, die nicht auf meinem Mist gewachsen war. Insgesamt haben alle Beiträge hier sehr weitergeholfen. Wenn ich wirklich mal Backups von Datenbanken in eigenem Interesse machen will, habe ich ja jetzt hier eine Menge an Informationen bekommen. Die Konstellation mysqldump | mysql scheint aber für kleine Datenbanken unproblematisch zu sein.

In dem Zusammenhang hätte ich noch gerne gefragt, welche Overrides für mysql in der /etc/rc.conf generell möglich sind, so wie etwa mysql_args="--bind-address = 0.0.0.0"? Ich habe leider nichts dazu gefunden.
 
Zurück
Oben