tags:

views:

150

answers:

2

I have a MySQL database that I want to archive. What is the best way to do this?

Note: I don't want to just do a backup. I want to do a one time export of the data for long term storage in a way that I can get at on a later date. Particularly, I want to not be tied to MySQL, a database or preferably any given software (I'd really like it to be trivial to wright a program that can read it back in, something like a few dozen lines of C or perl).

My current plan is to dump stuff to a table using the CSV engine and then burn that to DVD. The is nice because CSV can be loaded by so many different programs. The only gotcha in this is that the bulk of the data is in Blob columns as in binary so I'll need to decode how that is encoded.

+2  A: 

Exporting to CSV, encoding binary data with Base64 encoding, and then compressing with gzip sounds like a good way to go. I'd advise you to think well about your choice of DVDs though. Recordable media tends to deteriorate rapidly over the years.

yuriks
What would be better? My main point is to get it off the HDD and tape would be harder to access (and it to is a recordable media).
BCS
DVD will generally deteriorate much faster than tape. Online storage like Amazon's S3 might be another option.
Harley
Store redundancy data (e.g., with par2) on the DVDs, and routinely verify them. If *any* problems show up, use the redundancy data to recover, and burn a new disc. Also burn new discs routinely (say, every 5 years or so). http://en.wikipedia.org/wiki/Optical_media_preservation has nice links.
derobert
5 years? That is a "long time" in my time frame for this.
BCS
+2  A: 

You could dump the entire DB into a .sql file and use a VCS to archive each version. If you needed an old version, you could export it out of the repository and restore from it.

barfoon