Wow this is a lot to take in, I have to go over it.

Couple questions...

So if I backup my database today for example:

backup-jan28-2010.sql 800mb

and then the backup script runs again tomorrow it'll backup to backup-jan29-2010.sql but only transfer what has risen above 800mb?

Another question... if I backed up over S3 how would I restore it? What about the other solutions?

The problem is that bandwidth and storage space on S3 isn't cheap.

Say I want to back up my Linode, which has 20GB of data.

Assume my incremental backups weekly are only 5% the size of a full backup on average. Assume that I only keep the previous/current week of data. That's two full backups and 12 incrementals, or 52GB of data.

A full month schedule would involve transferring roughly four full and 24 incrementals, let's say. 104GB to transfer.

Total costs:
S3 storage, 52GB @ $0.15/GB: $7.80/mth
S3 transfer, 104GB @ $0.10/GB: $10.40/mth
Linode transfer, 104GB @ $0.10/GB: $10.40/mth
Total: $28.60/mth

That's not cheap to back up a $30 linode!

The same approach when backing up to home would only cost the last bit, $10.40 total, assuming you already have the storage to spare. If you want to account for the cost of home storage, my costs for building my file server are actually somewhat similar to Amazon's... $0.10/GB (including drives/hardware) without redundancy, $0.12 per gig with...

It depends on what you want to do. If you run a rdiff-backup (or any rsyc-like backup solution) you could name you file the current date, then delete it the next day. Assuming you ran rdiff-backup before it was deleted you will always be able to restore that file. You could also simply just keep deleting and recreating backup.sql. If you run an rdiff-backup daily you can revert it back to any date you want.

How rdiff-backup works is that you can pull any file from any date, assuming you make backups between changes.

With rdiff-backup, if you name each file uniquely (ie backup-jan28-2010.sql) you will have to redownload all 800mb (compression?) once a day. If you simply overwrite backup.sql every time you dump your database, rdiff-backup will only transfer the changed date.

Basically rdiff-backup works like this: The first time it sees a file it HAS to download the whole thing, after that it will only download the changes. If you move a file, it will have to redownload the entire thing at its new location. If the file hasnt changed, it'll simply make a note in its internal tracking system that the file has not been changed and download nothing.

You can run all kinds of commands on your rdiff-backup such as the following. It checks my home directory for all the files that have been changed in the past 5 days. Note that these are all run on the virtual machine where the backups are kept and require no communication with my actual linode.

root@linode-backup:/backups# rdiff-backup --list-changed-since 1D linode_current/home/smark/
changed home/smark/.bash_history
changed home/smark/.lastip
changed home/smark/codinguniverse.com/logs/access_log
changed home/smark/files.spectralcoding.com/logs/access_log
changed home/smark/psybnc/psybnc-oftc/log/psybnc.log
changed home/smark/spectralcoding.com/logs/access_log
changed home/smark/wackyfeedback.com/logs/access_log
changed home/smark/wiki.spectralcoding.com/logs/access_log

Or the following which lists all my backups since I recreated the VM:

root@linode-backup:/backups# rdiff-backup --list-increments --list-increment-sizes linode_current/
        Time                       Size        Cumulative size
-----------------------------------------------------------------------------
Thu Jan 28 01:04:22 2010         11.8 GB           11.8 GB   (current mirror)
Wed Jan 27 12:54:44 2010         2.26 MB           11.8 GB
Mon Jan 25 14:07:15 2010         35.3 MB           11.9 GB
Wed Jan 20 02:22:05 2010         39.1 MB           11.9 GB
Thu Jan 14 13:14:55 2010         42.6 MB           11.9 GB
Tue Jan 12 21:30:25 2010         37.1 MB           12.0 GB
Tue Jan 12 00:57:17 2010         36.7 MB           12.0 GB
Sat Jan  9 10:26:20 2010         35.3 MB           12.0 GB
Mon Jan  4 01:25:00 2010         41.1 MB           12.1 GB
Mon Jan  4 01:12:35 2010         1.18 MB           12.1 GB
Wed Dec 30 12:26:25 2009         49.8 MB           12.1 GB
Tue Dec 29 17:46:06 2009         34.3 MB           12.2 GB
Mon Dec 28 21:21:21 2009         32.7 MB           12.2 GB
Thu Dec 10 11:07:38 2009          305 MB           12.5 GB
Wed Dec  9 14:11:12 2009         16.9 MB           12.5 GB
Tue Dec  8 00:01:27 2009         41.2 MB           12.6 GB
Sun Dec  6 14:59:15 2009         17.0 MB           12.6 GB
Fri Dec  4 11:59:59 2009         16.3 MB           12.6 GB
Thu Dec  3 12:04:18 2009         15.5 MB           12.6 GB
Tue Dec  1 11:54:55 2009         20.0 MB           12.6 GB
Mon Nov 30 11:30:45 2009         22.0 MB           12.6 GB
Sun Nov 29 14:04:16 2009         13.5 MB           12.7 GB
Wed Nov 25 11:32:36 2009         25.4 MB           12.7 GB
Tue Nov 24 12:20:49 2009         20.3 MB           12.7 GB
Mon Nov 23 12:52:05 2009         21.1 MB           12.7 GB
Sun Nov 22 18:59:23 2009         21.2 MB           12.7 GB
Sun Nov 22 15:28:17 2009         1.47 MB           12.7 GB
Sat Nov 21 11:44:32 2009         21.9 MB           12.8 GB
Thu Nov 19 15:57:10 2009         18.0 MB           12.8 GB
Mon Nov 16 11:25:33 2009         16.5 MB           12.8 GB
Fri Nov 13 15:18:25 2009         17.8 MB           12.8 GB
Thu Nov 12 01:33:37 2009         29.1 MB           12.8 GB
Mon Nov  9 13:33:38 2009         29.7 MB           12.9 GB
Fri Nov  6 11:49:26 2009         42.5 MB           12.9 GB
Wed Nov  4 15:02:38 2009         37.6 MB           13.0 GB
Mon Nov  2 12:57:26 2009         35.1 MB           13.0 GB
Wed Oct 28 12:54:22 2009         34.6 MB           13.0 GB
Tue Oct 27 16:13:07 2009         17.9 MB           13.0 GB
Mon Oct 26 12:11:58 2009         15.9 MB           13.1 GB
Mon Oct 26 00:41:27 2009         15.7 MB           13.1 GB
Fri Oct 23 12:04:53 2009         7.58 MB           13.1 GB
Thu Oct 22 12:19:17 2009         6.16 MB           13.1 GB
Wed Oct 21 11:46:55 2009         7.27 MB           13.1 GB
Mon Oct 19 11:33:05 2009         7.30 MB           13.1 GB
Sat Oct 17 16:29:55 2009         8.01 MB           13.1 GB
Fri Oct 16 17:48:02 2009         7.23 MB           13.1 GB
Wed Oct 14 11:14:16 2009         7.47 MB           13.1 GB
Tue Oct 13 11:57:40 2009         7.50 MB           13.1 GB
Mon Oct 12 12:06:24 2009         7.73 MB           13.1 GB
Sun Oct 11 14:10:16 2009         6.34 MB           13.1 GB
Sat Oct 10 17:53:44 2009         6.86 MB           13.1 GB
Fri Oct  9 11:49:11 2009         6.84 MB           13.2 GB
Thu Oct  8 11:47:08 2009         7.78 MB           13.2 GB
Wed Oct  7 13:41:54 2009         8.81 MB           13.2 GB
Tue Oct  6 15:49:47 2009         38.3 MB           13.2 GB
Mon Oct  5 11:22:37 2009         8.22 MB           13.2 GB
Sun Oct  4 17:08:00 2009         8.07 MB           13.2 GB
Sat Oct  3 13:44:13 2009         9.80 MB           13.2 GB
Fri Oct  2 11:39:14 2009         8.07 MB           13.2 GB
Thu Oct  1 17:13:01 2009         7.10 MB           13.2 GB
Wed Sep 30 13:09:17 2009         23.9 MB           13.3 GB
Tue Sep 29 11:59:40 2009         5.95 MB           13.3 GB
Mon Sep 28 15:03:24 2009         5.25 MB           13.3 GB
Sat Sep 26 15:14:45 2009         4.83 MB           13.3 GB
Fri Sep 25 14:05:57 2009         2.23 MB           13.3 GB
Thu Sep 24 11:56:54 2009         3.12 MB           13.3 GB
Wed Sep 23 11:40:27 2009         6.71 MB           13.3 GB
Tue Sep 22 15:55:41 2009         1.24 MB           13.3 GB
Mon Sep 21 21:10:56 2009         1.50 MB           13.3 GB
Sun Sep 20 11:29:00 2009          103 MB           13.4 GB
Sat Sep 19 16:34:42 2009         1.39 MB           13.4 GB
Fri Sep 18 10:52:50 2009         1.17 MB           13.4 GB
Thu Sep 17 10:19:15 2009         1.28 MB           13.4 GB
Tue Sep 15 23:01:54 2009         1.65 MB           13.4 GB
Tue Sep 15 18:32:13 2009          930 KB           13.4 GB

Size = Changed size of the mirror files (?)
Cumulative Size = Total size of the backup

Okay sorry I'm asking so many questions guys, I'm really just trying to absorb all of this and learn. It's been great so far and I really appreciate it.

So I saw someone mentioned that to do proper MySQL dumps I have to lock the db so nothing odd will happen if restored, correct?

Alright, he said if it was running under a certain version, I think MyISAM then I wouldn't need to run a specific command to back it up, otherwise I would?

Firstly, how do I figure out what type of DB MySQL is running?

Second if it's not running that specific type of db then I have to mysqldump with a command to lock the tables, correct?

I found this script really well done, It's way better than mine :

Bash Script: Incremental Encrypted Backups with Duplicity (Amazon S3)