Getting 'attachments' out of MySQL BLOB fields, and into the filesystem

Question

I have a standard LAMP server, and currently there is a way to attach/upload PDF's, or JPEG's with an inventory record. I designed this system way back in 1999/2000, and it has of course grown fairly large in size. The table that holds the attachments is approaching 10GB, which has made working with the data somewhat slow (especially when I want to query everything, and sort by filesize to see where the large attachments are)

I would like to 'move' this attachment storing out of MySQL, and into the filesystem. I basically just want to somehow store the PATH of the filename, in place of the current BLOB field.

Does anyone or has anyone done anything similar to this, and if so, what approach did you take to get the attachments out of the db, and into the filesystem? Currently, the app is in PHP, so I'm thinking of writing a script to iterate through every record, and save the current BLOB data to the filesystem.

Any thoughts or ideas on how to best accomplish this?

Many thanks,

Bruce

Answer 1

Currently, the app is in PHP, so I'm thinking of writing a script to iterate through every record, and save the current BLOB data to the filesystem.

I think that's pretty much it. Set PHP's max_execution_time to a huge value, and run through every record, fwrite() ing out the contents - not much else to it (unless I'm getting your question wrong).

After successful writing, write back the written file path into the new filepath field, and clear the BLOB field's contents (possibly after performing a CRC32 comparison to make really, really sure the contents were written out correctly).

If the file doesn't get written right, don't clear the BLOB field. That way, you can resume a crashed run without having to run through each record again.