How to encrypt existing MySQL database?

Question

Our company needs to scramble the confidential data first before they send it to us for PHP development. They're asking us what's the best approach to this kind of situation.

The requirement is that the data must be decrypted.

Is there a free/commercial tool to do this or it can be done by PHP or Linux command only?

Answer 1

MySQL already includes reversible encryption functions, such as AES_ENCRYPT().

You can scramble sensitive data on a column-by-column basis in the following way:

UPDATE SomeTable SET sensitive_column = AES_ENCRYPT(sensitive_column, 'password');

This works at least for string data.

Answer 2

Seems to me based on the requirements one wouldn't really want to encrypt the data per se. One would likely want to mask the data, meaning eg. changing the values in the tables from a real name, address, phone-number to a new fictitious value for the purpose of development and application testing. http://en.wikipedia.org/wiki/Data_masking