Encrypt an entire MySQL Database to improve the security of your data in transit and at rest. In light of recent security breaches, many organizations have begun to take data encryption seriously. Database servers are more often than not a popular target for attackers, as they house the most valuable asset of most businesses.
Once an intruder gains access to your server’s sensitive data, they are likely to steal it. They then use the data to extract ransom, data exploitation, or other financial benefits from the company they targeted.
To Encrypt an entire MySQL Database it uses an algorithm to turn the data in a database into “cipher text” (unreadable text). To decrypt the text, you must use a key generated by the algorithm. The database encryption method is highly recommended, especially for businesses that deal with financial, healthcare, or e-commerce data.
Cyber-attacks, data theft, and data breaches have become more common in recent years and have increased concerns about personal data. People are aware of the importance of data privacy and security and want their data protected and only used when necessary.
Advantages of database-level encryption –
- Full functionality of the database management system is available.
- Easy to put into practice – Data is only visible to database users.
- Only databases have access to the information – Performance, encryption per table, and key per table.
- Performance, encryption per table, and key per table
Disadvantages of database-level encryption –
- It is not possible to do this on a per-user basis.
- Does not protect you from a malicious root user.
Steps to encrypt an entire MySQL database
Before we go into how to Encrypt an entire MySQL Database, it’s crucial to understand what should and shouldn’t be encrypted. It’s best to avoid encrypting any primary or foreign key fields if you wish to (or need to) encrypt all of your data. In the interactive examples above, for example, you’ll observe 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 department’s table and dept emp. dept no is a key constraint, encrypting the dept no fields makes no sense. It is the same with the employees. emp no fields. The tables are linked by these master and foreign keys. These fields as well as their values should not be considered sensitive data.
Typically, only sensitive data (credit card information, passwords, SSNs, etc.) needs to be encrypted, but in other industries, all data needs to be encrypted. For this page, “all data” refers to “all sensitive data”, and “all data” refers to all data except the master and foreign key fields in the broadest sense.
To get started to Encrypt an entire MySQL Database, create a clone of the database. We want to work with the cloned database to avoid overwriting or errors in the production database.
Clone each table you want to encrypt with a new name; we appended “_encrypted” to each table name for our encryption. For example, Employees would be cloned to Employees Encrypted.
Create an encryption key. You can choose any term or phrase and then perform an md5 hash to get your encryption key.
Each table has its own menu. Iterate through the fields in the table. You will need a map for each field (or column) with the following information:
Name of the field
Type of the field
Is it true that the field is a key
Maximum field size
Go through each field once you have saved the above data. For each field that is not a key, do the following: Duplicate the field in the same table, but add “_encrypted” to the name. This new field must be either a varbinary or a blob field.
You can use varbinary if the maximum length of the field is less than 512. Any object larger than a blob should be considered a blob. For example, employee encoded.firstname becomes employee encoded.firstname encoded.
Iterate through each field once the above data has been stored. For each field that is not a key, do the following: Duplicate the field in the same table, but add “_encrypted” to the name. This new field must be either a varbinary or a blob field.
You can use varbinary if the maximum length of the field is less than 512. Any object larger than a blob should be considered a blob. For example, employee encoded.firstname becomes employee encoded.firstname encoded.
NOTE: Encrypted data is usually larger in size (or length) than unencrypted data. The size of your new field [field] + “_encrypted” should be larger than the size of the unencrypted field. We have found that a multiple of 16 is most effective.
Use the following query to update the data in each encrypted field: UPDATE Set [field] encrypted = AES ENCRYPT([field], ‘[Encryption Key]]); [table name] encrypted set [field] encrypted = AES ENCRYPT([field], ‘[Encryption Key]]);
Then remove all fields that are not keys. In other words, all fields that are not keys and do not have the “_encrypted” suffix in their name should be removed.
Then for each column name that contains “_encrypted”, remove it. This would have caused a duplicate field problem, but we removed the original unencrypted fields (except for the key fields) in the step above, so you should not have a problem with duplicate fields or columns.
Finally, repeat the process, but this time at the table level. For example, drop the Employees table and rename Employees Encrypted to Employees. Now you should have a table named “Employees” where all fields are encrypted (except for the key fields) and whose field names are identical to those of the unencrypted source database.
Example to Encrypt an entire MySQL Database –
Add the following option to my.cnf to enable encryption:
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/mount/mysql-keyring/keyring
We can unmount the “/mount/mysql-keyring/” partition after MySQL starts.
Since MySQL does not encrypt tables by default, we need to run modify table names. To start encrypting the tables, we will need to run alter table table_name encryption=’Y’ to start encrypting the tables. The latest version of Percona Xtrabackup is now encryption-enabled and can back up encrypted tables.
We can use the schema.INNODB SYS TABLESPACES information and the flag field to find all encrypted tablespaces in MySQL/ Percona Server 5.7.11. For example, use the following query to find typically encrypted tables:
mysql> select * from information_schema.INNODB_SYS_TABLESPACES where flag = 8225G
*************************** 1. row ***************************
SPACE: 4688
NAME: test/t1
FLAG: 8225
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
*************************** 2. row ***************************
SPACE: 4697
NAME: sbtest/sbtest1_enc
FLAG: 8225
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 255852544
ALLOCATED_SIZE: 255856640
2 rows in set (0.00 sec)
One can also use this query instead: select * from information_schema.tables where CREATE_OPTIONS like ‘%ENCRYPTION=”Y”%’;.
Overhead in performance
This is a controversial topic, especially in the MariaDB implementation where everything is encrypted. In my tests, I found an overhead of 10% for the single MySQL instance and 20% for Galera Cluster. Tablespace level encryption in MySQL 5.7/ Percona Server 5.7 has a very low overhead but needs to be evaluated under different scenarios.
CONCLUSION
If you have done all of the above steps correctly (and assuming your code works), you should Encrypt an entire MySQL Database where the MySQL data is encrypted in every field of every table, except for the key columns. Even with all of the above drawbacks, if the application cannot be updated, database-level encryption may be a better alternative than filesystem-level encryption.
This blog has mentioned a few steps that can easily help anyone Encrypt an entire MySQL Database.