Taking periodic backups of a MySQL database

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.

Solution 1:

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!

Solution 2:

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.