I am currently struggling with how to correctly implement storage of encrypted fields in a MySQL db, without knowing the encryption key. The result I am trying to achieve is to encrypt certain pieces of user provided information, which if I was legally obliged to provide to third parties would be encrypted without me being able to provide the key/s. Thus I concluded full partition encryption wouldn't make much sense, as I'd need to know the password for starting/resetting the server.
The only solution I could come up was to use the standard MySQL AES encryption, and derive the key from plaintext user password when it is supplied upon registration and login. A small problem associated with this is that I won't be able to process the encrypted data unless the user is logged in (hence cronjobs are out of the question, which might be quite troublesome), and I will have to keep the password in session, which isn't too much trouble I suppose.
I would be open to any alternative suggestions, or improvements of the simple implementation above, including using multiple servers if that would help.