mysqldump on remote server

Question

If there are two machines client and server .From client how to do a mysqldump to the server such that the dump is avaliable on the client and not stored in the server

Thanks..

Answer 1

Do this in two steps:

• dump data on server
• transfer to client (possibly compress first)

If you need to do it often, then write a script on the server that dumps, compresses and copies the data to the client (don't forget to archive/delete old backups on server, as neded)

Answer 2

You could write a simple script, that could run in your crontab to create such dump and move it to some particular area of your file system, like an http accessible folder, or an ftp folder.

Then you could write an script to run in your clients that would fetch such dumps if you need this to be automatic too.

Answer 3

• Either you do the backup serverside (if you have access to the server), using mysqldump to dump it, gzip or bzip2 to zip the file, and ftp/sftp/scp to transfer the file to the client afterwards. You can later script this, and afterwards crontab it to have it run automatically each X time. Checkout logrotate to avoid storing too many backups.
• Or you use a tool on the client to fetch the data. The default (free) MySQL Workbench can back-up an entire database, or you can select which tables to backup (and interestingly, afterwards which tables to restore - nice if you only need to reset 1 table)