Is you're mainly archiving binary data and not serving it, is it better to store as BLOBs in MySQL or on S3?

I have an application where customers upload files like Powerpoints and Excel spreadsheets to the application through a web UI. The files then have meta data associated with them and they are stored as BLOBs in a MySQL database. The users may download these files occasionally, but not very often. The emphasis here is on archiving. Security of data is also important.

If that is the case, what are the pros and cons of storing the files as BLOBs in MySQL as opposed to putting them on Amazon S3? I've never used S3 before but h开发者_开发技巧ear that it's popular for storing files.

The main advantage of relational databases (such as MySQL) is the elegance it permits you to query for data. BLOB columns, however, offer very little in terms of rich query semantics compared to other column types, so If that's your main use case, there's hardly any reason to use a relational database at all, it doesn't offer much above and beyond a regular filesystem or simple key-value datastore (such as s3).

Dollars to bytes, s3 is likely much more cost effective.

On the other hand, there are some things that a relational database can bring that would be worhtwhile. The most obvious is transactional semantics (only on the InnoDB engine, not available with MyISAM), so that you can safely know that whole groups of uploads or modifications take place consistencly. Another advantage is that you can still add metadata about your blobs (even if it's only over time, as your application improves) so you can still benefit some from the rich queries MySQL supports.

storing binary data into blob

• make your database fat
• size limitation (is overcome at the later version in mysql)
• data portability is not there (you need a mysql api/client to access the data)

there is no true security

If you are archiving the binary data,
store into normal disk file

If security is important,
consider separate between your UI server and storage server,
but is hard to archive,

you can always consider to embed password / encryption into these binary files

security over amazon s3

• http://docs.amazonwebservices.com/AmazonS3/latest/dev/index.html?UsingAuthAccess.html
• http://docs.amazonwebservices.com/AmazonS3/latest/dev/index.html?S3_QSAuth.html

Security of data is also important.

Do note that files on S3 are not stored on encrypted disks, so you may have to encrypt client-side or on your servers before sending it up to S3.

I've been storing data in S3 for years and completely love it! What I do is upload the file to S3 (where its copied multiple times by the way) and then store a reference to the file path and name into my MySQL files table. If anything else, it takes that much load off of the MySQL DB and S3 now offers AES256 bit encryption with revolving master keys so you know its secure!