1

I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?

  • I'm assuming Unix - cron a job to ftp the file 5 minutes after the dump. – Vérace Jan 12 '15 at 14:45
  • It's unix, the problem is space on local machine. – aF. Jan 12 '15 at 15:06
  • Tar and gzip? Buy a disk? Even a memory stick? – Vérace Jan 12 '15 at 15:28
  • @Vérace I just want to know if it's possible to do it remotely and not the solutions to be able to do it locally. – aF. Jan 14 '15 at 11:59
  • @added a couple of edits. – Vérace Jan 14 '15 at 14:51
3

What about piping a gzipped mysql dump over ssh.

1

@aF I thought that the solution mentioned by @tersmitten was a Unix NAMED FIFO PIPE - so you actually store a minimum of data on your system. Take a look at the mknod/mkfifo named pipes here. Try a few of the links from Googling this (intermachine IPC mkfifo example). I vaguely remember doing this for Oracle redo logs years ago.

A couple of thoughts - maybe you could mount a network drive from the other machine on your server and pipe your backup to that? Or the cloud - how big is it? Dropbox and other services offer this - Google drive?

[EDIT] (Pity I'm on the laptop today - no access to a machine to check out my theories about this fascinating question (gave you +1).

Take a look here - some results which jumped out at me were these (1, 2, 3 & 4).

[EDIT2] (My brain is really not working today! :-) )

Why not replicate to the remote machine and then backup the replica? Quick and easy replication is _THE_ reason why MySQL won the F\LOSS database wars. Backup (at quiet times) allow the replica to fall behind at these quiet times quiet times and then catching back up won't be a problem. What storage engine are you using - MyISAM or InnoDB? What version of MySQL?

1

You are better off just getting the necessary root access to ssh straight in and scp the file or dump.

If you cannot ssh into the server with root access but you simply interested in dumping the file inside the local DB Server, you have very limited options. In fact, I can only think of one option.

The only way to mysqldump data locally is not to use mysqldump itself but duplicate what mysqldump can do. Since mysqldump is capable of creating CSV files but you do not have OS access, you need to take matters into your own hands.

Login to mysql and run SELECT ... INTO OUTFILE .

Here is a sample for the MySQL Documentation on SELECT ... INTO OUTFILE

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

This requires secure_file_priv be set to a specific folder. You also need the FILE privilege.

To fully clarify the aforementioned query, keep in mind that the target system of SELECT ... INTO OUTFILE is the DB Server, not the client side. It says so in the MySQL Documentation:

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host's file system.

  • so it's not possible to do it directly to the remote machine right? – aF. Jan 14 '15 at 10:06
  • But the OP's problem is that he doesn't have the space to create this /tmp/result.txt file. – Vérace Jan 14 '15 at 14:31
  • @Vérace SELECT INTO OUTFILE only writes to the DB Server. It can never write to a client server like mysqldump can. – RolandoMySQLDBA Jan 14 '15 at 15:39
  • @RolandoMySQLDBA Isn't the OP's whole problem that he can't use mysqldump to another server? He has to generate the dump on the server before transferring it, but he doesn't have the space to do so - or have I misunderstood something? – Vérace Jan 14 '15 at 18:05
  • @Vérace Yes, that is his problem. My answer says The only way to mysqldump data locally is not to use mysqldump itself but duplicate what mysqldump can do. The origin of the dump is the DB server itself. It could never be the client. I am just showing that the only way to dump data in the DB server is from within the DB server. I also mentioned in my answer You are better off just getting the necessary root access to ssh straight in and scp the file or dump.. – RolandoMySQLDBA Jan 14 '15 at 18:09
0

It's not possible to do the mysqldump directly to a remote location (like in other sgdb's).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.