Need help with a backup plan

[Dave S]

Hi all. I need some advice on a backup plan for my business, as it's growing rapidly and my data storage needs are getting kinda hairy. The particulars are:

- hosted with a fairly reputable host, on a VPS plan
- the mySQL db currently has about 2.2 million rows in the largest table
- the db is basically write only. No rows are ever deleted. The largest table is only added to, and others get updated often, but added to slowly. We're getting to the point where 1000 inserts a day might be possible.
- the last backup I did through cPanel's Backup Wizard was about 98MB as a .gz file
- we're using myISAM tables

One problem I'm noticing is that using the Backup Wizard in cPanel seems to adversely affect the site's operation, and some transactions are timing out while the file is downloading. I'm guessing there's some locking going on, or something.

If it affects a good backup plan, I would be very happy to consider including a culling/archiving strategy, where I only allow my customers (say) 6 months of transaction history (i.e. the data stays in the live tables) and anything older gets automatically archived and would have to be requested specially. (I bring this up because 2.2 million rows on a live table, added to daily, makes me a bit nervous! Should it?)

So I'm looking for some ideas on how to handle this amount of data. I'm sure lots of people must have more, but with the biz growing, and more/larger customers coming online, the rate at which data is getting added is increasing, so I need to start making plans :)

Any links, advice, anecdotes, or other info would be very much appreciated.

Thanks in advance,
Dave