Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to know how can I backup my MySQL databases on a periodic basis and have the backups downloaded to my local server. I'm using navicat and it has an easy backup interface and tasks schedule facility but it backs up the db and stores the backup on the server itself. I would like to have a way to download the backups once made to my local system rather than have them on the same server as the database.

share|improve this question
add comment

6 Answers

up vote 3 down vote accepted

As the others have noted - you will need to install some software on the MySQL machine to do these backups. Navicat is just a client - it can not run a scheduled backup on the server.

There is much software out there that can do this for you - some of them backup directly from the MySQL DB.

The free solution is to 'roll your own' database backup. This largely depends on your operating system.

Linux - create a shell script to do mysqldump on the db you wish to backup, then send this backup to your home PC via scp, ftp, or e-mail (if you do not have the ssh and ftp infrastructure at home).

Windows - create a scheduled task to backup the mysql db to a shared directory, or set up a command line script to backup the DB and email, ftp or scp it to your local pc.

If you have PHP installed on the server you can use the following directions for either OS:

http://www.sematopia.com/?p=61

There are many ways to skin this cat!

share|improve this answer
add comment

Smart move - if you have a local linux server, ssh/scp, shared keys, and mysqldump are an obvious way to go.

Even if your local backup server is a windows box, it shouldn't be too tough.

If the backups are already made on the server(?) then all you need is to scp them off nightly, unless of couyrse they are enormous and you are looking to do differential backups, which is a bit tougher with SQL.

share|improve this answer
add comment

I'd set up an rsync server on a remote host.

If it's an internal network simple rsync with a password option should be enough

If it goes thru a public link either go thru ssh (with public key authentication) or OpenVPN with the remote rsync server listening only on secure IPs

share|improve this answer
    
On another note: You might want to try nilfs2 which creates mountable checkpoints automagically. I however think that it might not quite generate consistent backups for MySQL (this way would be very experimental) –  Server Horror Jun 8 '09 at 8:50
add comment

If you can you could install backupninja it will dump and can send it to another host via rsync, scp and ftp.. It will also send detailed email reports.

share|improve this answer
add comment

You can also execute backups using the mysqldump command locally, and piping the output to a file. It's not a true binary copy of the data, but it's a copy of all the data in the table.

share|improve this answer
    
don't take this as a disadvantage; an "sql copy" is generally far more valuable, and usually more robust than a binary copy of the tables. –  Tom Newton Jun 9 '09 at 7:07
add comment

I've got a cron job that dumps the databases with mysqldump into a directory that is backed up by TSM. If you've already got a good network backup system I'd say to tie into that somehow.

If not, I'd go with rsync + mysqldump. You can set up mysqldump to dump to a directory, and then run rsync to move the dump over to the other server.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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