MySQL - how to backup database to a different server?

Question

Hi,

I have 2 databases with MyISAM tables which are updated once a week. They are quite big in size (one DB is 2GB and the other is 6GB). I currently back them up once a week with mysqldump and keep the last 2 weeks' worth of .sql dumps on the same server where the DBs are running.

I would like, however, to be able to dump the backups to another server, as they are taking up server space unnecessarily. What is the best way to achieve this? If possible, I would like to keep the databases running during the backup. (no inserts or updates take place during the backup process, just selects).

Thanks in advance, Tim

Answer 1

Were I you, I would create a script that did the backup and then sent the backup elsewhere. I know that is kind of what you are asking how to so, but you left out some things that would be good to know, such as what OS are your two systems running?

Of they are both windows, you could mount a network drive and have the backup dump there (or copy the dump there). If they are linux servers I would recommend copying it across using the scp command. If it is a mix then it gets fun and tricky.

If you are working with linux servers, the following guide should walk you through the process of backup. Click me!

If you are still scratching your head after reading that, let me know what kind of OSes you are rolling with and I can provide more detailed instructions.

Good luck!