Remote Backup einer Datenbank mysqldump klappt nicht

cabriofahrer

Well-Known Member
Ich will eine Datenbank (Name: ejemplo) per mysqldump von einem Rechner auf den anderen transferieren, beide Rechner haben FreeBSD mit mysql. Das von mir verwendete Kommando müsste eigentlich klappen (Ich nehme auch an, dass ich laut Ausgabe nach beiden Passwörtern gefragt werde, die ich nacheinander mit einem Abstand eigeben muss?)

Code:
$ mysqldump -u root -p ejemplo | mysql --host=192.168.1.55 -u root -p -C ejemplo
Enter password: Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.55' (61)


mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
$ ping 192.168.1.55
PING 192.168.1.55 (192.168.1.55): 56 data bytes
64 bytes from 192.168.1.55: icmp_seq=0 ttl=64 time=20.777 ms
64 bytes from 192.168.1.55: icmp_seq=1 ttl=64 time=1.582 ms
64 bytes from 192.168.1.55: icmp_seq=2 ttl=64 time=1.498 ms
64 bytes from 192.168.1.55: icmp_seq=3 ttl=64 time=46.276 ms
64 bytes from 192.168.1.55: icmp_seq=4 ttl=64 time=1.380 ms
^C
--- 192.168.1.55 ping statistics ---
5 packets transmitted, 5 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 1.380/14.303/46.276/17.646 ms
$

Aber auch, wenn ich die Passwörter direkt in den Befehl eingebe, klappt es nicht:

Code:
$ mysqldump --user=root --password=passwort_local ejemplo | mysql --user=root --password=passwort_host --host=192.168.1.55 -C 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 2003 (HY000): Can't connect to MySQL server on '192.168.1.55' (61)
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
$
 
Der root-User hat in der Regel keinen Zugriff übers Netz.
Ich empfehle dir, einen Backup-User anzulegen, mit dem du den Dump ziehst.

Also auf dem Host vom dem du das Backup ziehen willst:
Code:
# mysql -u root
mysql> CREATE USER 'backup'@'zielhost-ip' IDENTFIED BY 'langes-passwort';
mysql> GRANT SHOW VIEW, SELECT, LOCK TABLES ON *.* to 'backup'@'zielhost-ip';

Danach kannst du vom Zielhost dumpen und importieren:

Code:
$ mysqldump -u backup -h quellhost-ip -p dbname > dump.sql
$ mysql -u root -p dbname < dump.sql

Rob
 
Du greifst mit root@localhost auf den remote Server zu, das geht natürlich nach hinten. Du musst einen eigenen User für den zugreifenden Host anlegen - also z.b. root@192.168.1.123.

So wie du das machst geht alles unverschlüsselt übers Netz, das würde ich nur machen, wenn du dem Netz unengeschrenkt vertraust, ansonsten wäre vielleicht schöner:

Vom Ziel-Host (192.168.1.55)
ssh root@qeuellhost mysqldmp -uroot -p"myPW" ejemplo |mysql -uroot -p"myPw"

Das macht das ganze über SSH.
 
Danke erstmal. Wenn ich die Antwort von @KobRheTilla richtig verstehe, beschreibt das den Fall, dass man von dem Zielrechner aus, auf dem das Backup in mysql eingespielt werden soll, sich das Backup "zieht". D.h., man muss sich auf diesem Rechner den neuen User anlegen, richtig?
Was ist aber, wenn ich aber ausgehend vom Wortlaut der manpage

Code:
mysqldump is also very useful for populating databases by copying data
       from one MySQL server to another:

           shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

eine Datenbank zum Zielrechner "hinschieben" (populate) will? Also die Datenbank liegt vor dem | und der remote_host ist der Zielrechner? Wo muss ich dann den neuen user@ip_adresse anlegen?

Und gilt die Aussage
Der root-User hat in der Regel keinen Zugriff übers Netz.
für beide Richtungen (rausschicken und beziehen) an beiden Enden? Müssten dann nicht beide Rechner am Netz einen speziellen User haben?
 
mysql legt normal immer nur lokale user an. Wenn du irgenwas übers Netz machen willst - egal ob ziehen oder schieben - brauchst du also Nutzer die übers Netz zugreifen können.
@KobRheTilla hat die Reihenfolge umgedreht, da so der potentiel unsichere Netzuser nur leserechte braucht. In deinem Weg, also das "hinschieben" braucht der Netzuser schreibrechte. Prinzipiell funktioniert das aber natürlich auch anders, da musst du dann eben den neuen user am Zielhost anlegen.
Die Manpage kann natürlich nicht alle Anwendungsfälle abdecken und schreibt nur exemplarisch ein paar Beispiele auf ;)

Bei der SSH-Variante von mir kommst du gänzlich ohne neue mysql-user aus. Du brauchst halt ssh-Zugriff.
 
Bei der SSH-Variante von mir kommst du gänzlich ohne neue mysql-user aus. Du brauchst halt ssh-Zugriff.

Aber oben hast Du doch gesagt

Du musst einen eigenen User für den zugreifenden Host anlegen - also z.b. root@192.168.1.123.

Also doch zuerst den user root@192.168.1.34 (IP vom Quellhost in meinem konkreten Fall) anlegen oder nur, wenn ich KEIN ssh verwende?

Könntest Du dann bitte nochmal genau die für mein Vorhaben gewollte Konstellation (Ich möchte vom Quellhost (192.168.1.34) aus die Datenbank zum Zielhost (192.168.1.55) verschieben.) mit genauen Schritten schildern? Also wenn ich ssh verwende, brauche ich keinen neuen User anlegen? Und wie dann, wenn ich das ohne ssh machen will?

Dies wäre schließlich auch die einzige praktikable Möglichkeit, wenn man aus irgendeinem Grund auf dem Zielhost keine körperliche Zugriffsmöglichkeit hätte oder einen bestimmten Weg zurücklegen müsste oder auch die bessere Möglichkeit, wenn man die gleiche Datenbank auf mehreren Rechnern verteilen müsste.
 
Wenn du SSH verwendest genügt der lokale root-user, der automatisch bei der Installation angelegt wird. Ich hatte das nur für den von dir anfangs geposteten Weg geschrieben - da würdest du auf dem Ziel-Host (.1.55) einen User für deinen Quell-Host benötigen. Also root@192.168.1.34. Wie du so einen User anlegst gibts genug mysql-tutorials.

Für den von mir weiter beschriebenen Weg über SSH ist das nicht nötig. Allerdings musst du die Verbindung vom Ziel-Host aus starten. Du loggst dich also per SSH auf dem Ziel-Host ein, und führst

ssh root@192.168.1.34 mysqldmp -uroot -p"myPW" ejemplo |mysql -uroot -p"myPw"

Da musst du dann das SSH - Passwort eingeben und das Ding läuft. Was es macht: Es dumpt dir die DB ejemplo, und überschreibt die DB auf dem Zielhost.

Ohne SSH funktioniert es prinzipiell genau so wie du, bzw. KobRheTilla beschreiben haben. Eben einmal vom Quellhost aus, und einmal vom Zielhost aus. In beiden Fällen wäre ein neuer mysql - user am nicht lokalen Server nötig.
 
Ach ja, auf keinen Fall User nach dem Muster root@% anlegen. Würde die Sache zwar kurzfristig vereinfachen, langfristig ist es jedoch eher ein Albtraum...
 
ssh root@192.168.1.34 mysqldmp -uroot -p"myPW" ejemplo |mysql -uroot -p"myPw"

Klappt nicht. Es geht noch nichtmal ssh root@192.168.1.34, auch wenn ich das Passwort für root eingebe. Ich kann nur über meinen User "werner" mich per ssh mit dem Quellhost verbinden, also ssh werner@192.168.1.34. Und jetzt ist mir nicht ganz klar, wo in dieser Konstellation mit ssh
ssh root@192.168.1.34 mysqldmp -uroot -p"myPW" ejemplo |mysql -uroot -p"myPw"
jetzt Quell- und Zielrechner sind, weil ich jeweils verschiedene Mysql-Root-Passwörter habe, sonst wäre es egal. Also ist in diesem Fall der Quellhost (192.168.1.34) der erste in der Syntax vor dem | und der Ziehost (192.168.1.55), von dem ich den Befehl eingebe, der zweite nach dem | oder umgekehrt?

Ich habe es auch ohne ssh versucht, indem ich auf dem Zielhost einen neuen user mittels

create user 'backup'@'192.168.1.34' identified by 'backup'; erstellt habe.

Aber als ich dann versucht habe, vom Quellhost die Datenbank zum Zielhost zu verschieben, klappte es auch nicht:

Code:
$ mysqldump -u root -ppasswd -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.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.55' (61)
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
$

Brauche ich für den user 'backup' auf dem Zielhost noch spezielle grants?
 
Für mich sieht das so aus, dass der User root einmal keinen Zugriff auf die lokale MySQL-DB hat, daher der Fehler root@localhost. Man muss beachten, das für MySQL ein Unterschied zw. localhost und 127.0.0.1 besteht. Außerdem hat er keinen Zugriff von dem Quellrechner aus, also da wo mysqldump ausgeführt wird, auf die MySQL-DB auf 192.168.1.55.

Ich würde die beiden Zugriffe erstmal fixen und dann weitersehen.
 
Mit telnet kenne ich mich gar nicht aus. So etwa? ping funktioniert.

Code:
$ telnet 192.168.1.55:3306
192.168.1.55:3306: hostname nor servname provided, or not known
$ ping 192.168.1.55
PING 192.168.1.55 (192.168.1.55): 56 data bytes
64 bytes from 192.168.1.55: icmp_seq=0 ttl=64 time=194.803 ms
64 bytes from 192.168.1.55: icmp_seq=1 ttl=64 time=1.197 ms
64 bytes from 192.168.1.55: icmp_seq=2 ttl=64 time=1.467 ms
64 bytes from 192.168.1.55: icmp_seq=3 ttl=64 time=1.676 ms
^C
--- 192.168.1.55 ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 1.197/49.786/194.803/83.726 ms
$
 
Klappt nicht. Es geht noch nichtmal ssh root@192.168.1.34, auch wenn ich das Passwort für root eingebe. Ich kann nur über meinen User "werner" mich per ssh mit dem Quellhost verbinden, also ssh werner@192.168.1.34. Und jetzt ist mir nicht ganz klar, wo in dieser Konstellation mit ssh

jetzt Quell- und Zielrechner sind, weil ich jeweils verschiedene Mysql-Root-Passwörter habe, sonst wäre es egal. Also ist in diesem Fall der Quellhost (192.168.1.34) der erste in der Syntax vor dem | und der Ziehost (192.168.1.55), von dem ich den Befehl eingebe, der zweite nach dem | oder umgekehrt?

Ich habe es auch ohne ssh versucht, indem ich auf dem Zielhost einen neuen user mittels

create user 'backup'@'192.168.1.34' identified by 'backup'; erstellt habe.

Aber als ich dann versucht habe, vom Quellhost die Datenbank zum Zielhost zu verschieben, klappte es auch nicht:

Code:
$ mysqldump -u root -ppasswd -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.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.55' (61)
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
$

Brauche ich für den user 'backup' auf dem Zielhost noch spezielle grants?

Quellhost: Der Host, vondem du die Daten kopieren möchtest. Zielhost: Da wo du sie einspielen magst.

Das mit SSH sollte auch mit dem user werner funktionieren. So wie du das beschrieben hast passt das auch mit Quell und Ziel. Einfach mal probieren und sonst nochmal schreiben was er ausspuckt.

Hier wollen dir viele mit unterschiedlichen Ansätzen helfen, ich hoffe du kommst nicht durcheinander :D
 
Was sagt denn deine ``my.cnf''? Wenn ``bind-address=127.0.0.1'' gesetzt ist, kannst Du nicht von remote per tcp zugreifen. Hier muesstest Du ``bind-address=0.0.0.0'' setzen.
 
Mit telnet kenne ich mich gar nicht aus. So etwa? ping funktioniert.

Code:
$ telnet 192.168.1.55:3306
192.168.1.55:3306: hostname nor servname provided, or not known
$ ping 192.168.1.55
PING 192.168.1.55 (192.168.1.55): 56 data bytes
64 bytes from 192.168.1.55: icmp_seq=0 ttl=64 time=194.803 ms
64 bytes from 192.168.1.55: icmp_seq=1 ttl=64 time=1.197 ms
64 bytes from 192.168.1.55: icmp_seq=2 ttl=64 time=1.467 ms
64 bytes from 192.168.1.55: icmp_seq=3 ttl=64 time=1.676 ms
^C
--- 192.168.1.55 ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 1.197/49.786/194.803/83.726 ms
$
Code:
telnet 192.168.1.55 3306
sollte Dir eine Rückmeldung vom MySQL-Server geben, wenn nicht muss der Zielhost noch angepasst werden. Der Parameter in der my.cnf war bind_address oder so ähnlich
 
@cabriofahrer

Du fragtest nach den unterschiedlichen Usern. Dabei fliegen hier einige durcheinander.

Am wichtigsten ist der mysql-user (auf dem Quell-mysql-server), der die Daten exportieren (dumpen) soll. Dieser muss Zugriff auf die Daten haben. Der mysql-user root ist so konfiguriert, dass er nicht über das Netzwerk zugreifen darf, aber lokal. Daher hast Du zwei Optionen:
  1. Du legst einen neuen mysql-user an, der Zugriff auf die Datenbank ejemplo hat und der über das Netz zugreifen darf (für einen User backup würde das dann 'backup'@'%' sein). Mit diesem mysql-user kannst Du über das Netzwerk, wie von Dir eingangs beschrieben, die Daten auf den Ziel-Server dumpen.
  2. Oder: Du verbindest Dich per SSH mit dem Quell-Server - als welcher SSH-User ist dabei egal. Ab hier arbeitest Du quasi lokal auf diesem Server. Daher kannst Du jetzt den root-user des MySQL-Servers verwenden, um die Daten lokal auf diesem Server zu speichern. Anschließend musst Du sie noch auf den Zielrechner übertragen - z.B. per scp oder sftp. Letzteres entweder direkt aus der SSH-Session heraus: Du bist auf dem Quell-Server eingelogged und kopierst die Dump-Datei per sftp oder scp auf den Ziel-Server. Oder Du lädst Dir die Datei auf Deinen lokalen Rechner herunter (Laptop/Desktop) und kopierst sie anschließend von dort auf den Ziel-Server.
 
Um jetzt erstmal auf die telnet-Sache zu antworten:

Code:
$ telnet 192.168.1.55 3306
Trying 192.168.1.55...
telnet: connect to address 192.168.1.55: Connection refused
telnet: Unable to connect to remote host
$ telnet 192.168.1.55 3306
Trying 192.168.1.55...
Connected to 192.168.1.55.
Escape character is '^]'.
N
5.7.29-logVArj.EQ1PpxNtmysql_native_passwordConnection closed by foreign host.
$

Der erste Versuch schlägt fehl, wie man sieht. Ich habe danach in die /etc/rc.conf folgendes eingetragen und mysql-server neugestartet:

mysql_args="--bind-address=0.0.0.0"

Ich nehme an, so wird der entsprechende Eintrag in /usr/local/etc/mysql/my.conf "überritten", richtig? Wie man sieht, ist danach die Ausgabe von telnet anders und endet mit ...closed by foreign host. Inwieweit das jetzt richtig ist und weiterhilft, weiß ich nicht, sieht aber nicht so aus, da die gestern schon eingegebenen Befehle auch nicht funktionieren.

Was aber in die richtige Richtung geht und der von mir gewünschte Lösungsweg zu sein scheint, ist die heutige Erklärung von @SolarCatcher in Nr. 1:

Am wichtigsten ist der mysql-user (auf dem Quell-mysql-server), der die Daten exportieren (dumpen) soll. Dieser muss Zugriff auf die Daten haben. Der mysql-user root ist so konfiguriert, dass er nicht über das Netzwerk zugreifen darf, aber lokal. Daher hast Du zwei Optionen:
  1. Du legst einen neuen mysql-user an, der Zugriff auf die Datenbank ejemplo hat und der über das Netz zugreifen darf (für einen User backup würde das dann 'backup'@'%' sein). Mit diesem mysql-user kannst Du über das Netzwerk, wie von Dir eingangs beschrieben, die Daten auf den Ziel-Server dumpen.

Also wenn ich das richtig verstehe, muss ich den user "backup" auf dem Quellhost, auf dem sich die Datenbank befindet, erstellen?
Und dieser user muss "Zugriff auf die Datenbank" und "Zugriff auf das Netz" haben. Also wie genau muss er dann erstellt werden?

mysql> CREATE USER 'backup'@'welche_IP_hier' IDENTFIED BY 'langes-passwort';

Und welche grants?
 
Erst legst Du den User an:
mysql> CREATE USER 'backup'@'%' IDENTFIED BY 'langes-passwort';

Das % ist wie der Joker für jedwede Verbindungen, lokal oder von irgendeiner IP Adresse. Wenn Du die IP-Adresse kennst, von der aus Du auf den Quell-mysql-server zugreifen willst, kannst Du die auch direkt eingeben. Am besten löschst Du später den backup User wieder, damit niemand damit (remote) Unfug treibt.

Und dann erteilst Du ihm alle Rechte für diese Datenbank
mysql> GRANT ALL ON ejemplo.* TO 'backup'@'%';

Statt "GRANT ALL" kannst Du die Rechte natürlich auch einschränken, allerdings musst Du da sehr aufpassen, weil sowas wie Views, Stored Procedures etc. mehr als nur SELECT-Rechte benötigen. Hier eine entsprechende Antwort dazu auf StackExchange.
 
Da du offensichtlich von allen 3 vorgeschlagenen Möglichkeiten auf der potentiel unsichersten beharrst:

CREATE USER 'backup'@'192.168.1.55' IDENTFIED BY 'langes-passwort';
GRANT ALL ON ejemplo.* TO 'backup'@'192.168.1.55';
FLUSH PRIVILEGES;

Anstatt GRANT ALL könntest du GRANT und dann 5 oder 6 Rechte die er braucht aufführen, aber ganz ehrlich, da ist eh schon alles egal wenn du das so machen willst ;)

Dann sollte dein im ersten Post genannter Befehl funktionieren - Der User muss noch geändert werden:

mysqldump -u root -p ejemplo | mysql --host=192.168.1.55 -u backup -p

Die Datenbank brauchst du am Zielserver nicht angeben weil mysqldump automatisch ein Drop und Create-Statement schreibt wenn man ihm nichts anderes sagt.

Aber ums nochmal klar zu sagen: NUR so machen, wenn du dem Netz zu 100% vertrauen kannst.
Achja und natürlich darf auf Zielserver (192.168.1.55) keine Firewall aktiv sein, die port 3306 blockiert.
 
Die Datenbank brauchst du am Zielserver nicht angeben weil mysqldump automatisch ein Drop und Create-Statement schreibt wenn man ihm nichts anderes sagt.
Das stimmt nicht, --add-drop-database muss explizit angegeben werden und ergibt auch nur Sinn, wenn man --databases nutzt.
Ich würde grundsätzlich beim Import den Zieldatenbanknamen mit angeben.


Man braucht folgende Rechte für einen kompletten Dump:
SELECT, LOCK TABLES, EXECUTE, SHOW VIEW, TRIGGER

Einem Backup-User INSERT, UPDATE oder gar DROP zu erlauben ist unnötig.

Rob
 
Das stimmt nicht, --add-drop-database muss explizit angegeben werden und ergibt auch nur Sinn, wenn man --databases nutzt.
Ich würde grundsätzlich beim Import den Zieldatenbanknamen mit angeben.



Man braucht folgende Rechte für einen kompletten Dump:
SELECT, LOCK TABLES, EXECUTE, SHOW VIEW, TRIGGER

Einem Backup-User INSERT, UPDATE oder gar DROP zu erlauben ist unnötig.

Rob

Ok dann soll er den DB Namen lieber angeben :) Ich hab da ehrlich immer viel mehr Statements im mysqldump, dachte das wäre standard aber kann mich natürlich irren.

Er legt den Backupuser am ZIEL Server an, also braucht er Drop/Insert/Update. Er will das Statement ja vom Quellhost ausführen. Daher auch mein "der blödeste von allen hier aufgezeigten Wegen". Das einzige was er in dieser Konstelation nicht braucht wäre wohl GRANT.
 
Zurück
Oben