How to encrypt an entire or full MySQL Database

What does it mean to encrypt a mysql database?

Encrypting a database is the process of converting the plain text and text-readable data within a database, into a non-readable hashed text by means an [encryption] algorithm.

Normally, when a user browses data within a database, the data (depending on the column type) is text readable. Excluding blobs and a few other field types, many of the typically used field types for database columns -- such as INT, VARCHAR, DATETIME, and TEXT - are humanly readable, as shown in the following interactive database table explorer:

In the above Interactive Database Table Explorer, if you click on a table name on the left hand side, the data for that particular table will be displayed on the right. Notice that each of the cell rows are generally plain text and human readable.

What would this database and its tables look like if all of the data were encrypted? Below is another database table explorer, but this time, we have encrypted all of the data (well not ALL of it, but most of it. More on this below regarding keys and constraints ...)

How to Encrypt Entire MySQL Database (All Mysql Database Tables)

Before we discuss how to encrypt all of your data within your database, it is important to know what should and should not be encrypted. In general, if you want to (or need to) encrypt all of your data, it's important to avoid encrypting any primary or foreign key fields. For example, you'll notice in the interactive examples above that:

  • departments.dept_no is not encrypted
  • employees.emp_no is not encrypted
  • dept_emp.dept_no is not encrypted
  • dept_emp.emp_no is not encrypted
Since dept_no is a primary key for the deparments table, and the dept_emp.dept_no is a key constraint, it doesn't make sense to encrypt the dept_no fields. The same is true for the employees.emp_no fields. These primary and foreign keys will be used to join tables like employees to dept_emp and dept_emp to departments, and thus in a sense these fields and their values should not be considered sensitive data. Typically only sensitive data must be encrypted (credit cart numbers, passwords, SSNs, etc), but in some industries, such as with fintech, all of the data must be encrypted. For the purposes of this page, "all data" will mean "all sensitive data", and at it's broadest scope, means all data except for any primary or foreign key fields.

So - how do we encrypt all of the data in a database? For our solution, we approached it by writing a tool that does the following (you'll need intermediate coding skills, or you can use our free tool below as a sample, or if you still need help, feel free to contact us):

  1. First, clone the database. We want to work off the cloned database to ensure we don't overwrite or make any mistakes on our Production database.
  2. Next, for each table that you want encrypted, clone each table with a new name; for our encryption, we appended "_encrypted" to each table name. For example, employees would be cloned to employees_encrypted.
  3. Next, we need to come up with an encryption key. You may choose any string or phrase you want; then, md5 hash it, and that will be your encryption key. For example, md5'ing the string/phrase 'TestKey' (without quotes, they are just used for emphasis) yields an encryption key of af84adb3a64dc4187b4e279156f476b8.
  4. Now that we have our cloned tables, we can begin writing the tool. Below is how we did it:
    • Foreach table
      • Iterate through the table's fields. For each field (or column), you'll need a map containing:
        • Field Name
        • Field Type
        • Is the Field a Key or not?
        • Field's Max Size
      • Once that above data is stored, iterate through each field. For each field that is not a key:
        • Clone the field within the same table, but append "_encrypted" to its name. Make sure this new field is either a varbinary, or a blob field.
          Note that if the max length of the field is less than 512, you can use varbinary. Anything larger should be a blob. As an example, employees_encrypted.firstName would become employees_encrypted.firstName_encrypted.
        • NOTE: The size (or length) of encrypted data will generally be larger than unencrypted data. Your new [Field] + "_encrypted" should be larger than the unencrypted field. We found that a multiple of 16 worked well; that is, if you have a VARCHAR 100, your new encrypted field should be 100 * 16 = 1600, and since 1600 > 512, it will be a BLOB instead of VARBINARY.
        • Execute a query to update each encrypted field's data using the following:
          UPDATE [Tablename]_encrypted set [Field]_encrypted = AES_ENCRYPT([Field], '[Encryption Key]]);
      • Next, drop all of the original non-key fields. Another way to say this, is to drop all fields that are not keys, and that do not have "_encrypted" appended to the field's name.
      • Then, strip the "_encrypted" from each column name that has it. This would have ran into a duplicate field issue, but in the step above, we removed the original un-encrypted fields (except for the key fields), and so you shouldn't run into a duplicate field or column issue.
      • Finally, repeat this same step, but at the table level. Eg - remove table "employees", and then rename "employees_encrypted" to "employees". Now you should have a table called "employees", where all fields are encrypted (except for the key fields), and the field names will be the same as the original source un-encrypted database!
If you performed each of the steps above correctly (and assuming your code works ) then you should have a database whereby each field, within each table, excluding key columns, will have its data encrypted!

Great! Now - How do I decrypt my encrypted data?

Given that we've encrypted the data using the MySQL encryption method AES_ENCRYPT(), we can decrypt any encrypted column using MySQL's matching AES_DECRYPT() method.

However, since the data is now encrypted as binary, we also need to CAST the data back into their initial types. For example, if we wanted to view the employees_encrypted.first_name, we used the following:

							select CAST(AES_DECRYPT(first_name, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as first_name from employees;

Note that because one or more columns of the data in the tables are now encrypted, we can no longer use the asterisk (*) selector to select ALL columns. This query will still yield results, but the data will still be encrypted:

							select * from employees;
How to select all MySQL encrypted columns

So, how do we select ALL columns to view the data as un-encrypted? We must still use the CAST() and AES_DECRYPT() MySQL methods on all encrypted fields, and standard field names for the non-encrypted key fields, for example:

							select emp_no, 
							  CAST(AES_DECRYPT(birth_date, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as birth_date, 
							  CAST(AES_DECRYPT(first_name, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as first_name, 
							  CAST(AES_DECRYPT(last_name, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as last_name, 
							  CAST(AES_DECRYPT(gender, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as gender, 
							  CAST(AES_DECRYPT(hire_date, 'af84adb3a64dc4187b4e279156f476b8') as CHAR) as hire_date 
							from employees;

PROTIP: If you're application uses a lot of "SELECT *" select statements, you may want to consider a custom tool that can parse all of your code files, and modify the SELECT * statements to look like the above. This isn't a trivial exercise and would likely require some advanced coding. It is something we might be able to help you with, so if you'd like some help, please feel free to contact us!

Encryption Security

So, what happens if a user tries to decrypt the data, but they don't have the correct decryption key to do it? The short answer is - they can't. In order to decrypt the data, the exact same private encryption key that was used to encrypt the data, is required to decrypt it. If even one character within the key is modified, the results will be gibberish. For example, if we try the exact same query from above, but we change the key from af84adb3a64dc4187b4e279156f476b8 to bf84adb3a64dc4187b4e279156f476b8, we get the following:

Free Tool To Encrypt Your MySQL Database

Feel free to use our interactive tool(s) below to see how MySQL encryption works. Note that for security reasons, we do not allow *.SQL uploads. To use the tool below, simply:

  1. Export one or more of your MySQL tables to a CSV file (be sure to include field/column names in the first row)
  2. Click the upload button, or simply drag'n'drop one or more CSV files onto the drop zone.
  3. Our system will automatically ingest those files into one or more database tables.
  4. Our system will automatically encrypt them, using the key that you've specified.
    Note For security reasons, please make up a sample/test key, and do not use one that you intend to use for your Production environment!

Once your data is ingested, feel free to use our interactive Database Table Explorer to run queries against the encrypted data!

NOTE: Our server needs to download your CSV file(s), ingest them into Database Table(s), encrypt the data within those table(s), and then present the results to you. This process can take a few seconds up to a few minutes depending on the size of your data. Please be patient.

Sample Data

Need sample data to test this tool? Click Here to download a sample CSV data file, which you can then upload into the Database Encryption Tool below.

Do you need enhanced tools or additional help?

Do you need any additional help, or would you like a customized version of this tool with more features for your business, website or server? Help us help you - contact us and tell us a little bit about your project and let us know what you need. We are confident we can help!