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.
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:
There are many ways to skin this cat!
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.
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
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) Jun 8, 2009 at 8:50
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.
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.
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. Jun 9, 2009 at 7:07
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.